Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unbound Form (Access 97)

    Hi

    I have an unbound form where its recordsource (using a SQL statement) and controlsource of fields are set when the form is opened. The recordsource etc differ depending on the criteria selected in another form, which opens this form. One of the fields is a field for a debtor code.

    What I wish to do is have a label to flash when the debtor code field is empty to warn the user that it needs entering (via another form). However, cos the field is unbound, using isnull function to determine whether there is a debtor code or not does not work.

    Any ideas on how to do this, or I am missing something obvious.

    Thanks & Regards
    WTH

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Unbound Form (Access 97)

    The fact that a form is unbound doesn't mean that a control can't have a value - you should be able to do a null check without problems. On the other hand if the record source is being set in code based on criteria being set elsewhere, then the form isn't really unbound. It just a late bound form. I think you'll find the flashing of the label to be a more difficult problem however. Have you considered changing the color background of the control if the control is null (or an empty string)?
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound Form (Access 97)

    Hi

    Thanks for the reply, but the problem is the form is always recognising the debtor code as null.

    This is how I wish to handle the label flashing:

    If IsNull(Me.TxtDebtorCode) Then
    Me.LabNoDebtor.Visible = True
    Me.TimerInterval = 500
    Else
    Me.LabNoDebtor.Visible = False
    Me.TimerInterval = 0
    End If

    in the form's current event.

    This is sample of the code in the form's open event setting the recordsource etc:

    SQLstrorgan = "SELECT People_new.* FROM People_new " _
    & " WHERE (((People_new.nperson_id) = " & Forms![frmFindInvoice]![list]"
    Me.Txtpostaladdr.ControlSource = "=nppostal_addr"

    The problem is that in the isnull(Me.TxtDebtorCode) check result is always null, even when the form displays a debtor code for the form.

    Regards
    WTH

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Flashing Label (Access 97)

    If you really want a flashing label (I would recommend a subtler approach), here is example of how to implement it. For Form Timer event:
    <pre>Private Sub Form_Timer()

    With Me.lblDebtorCode
    .Visible = Not .Visible
    End With

    End Sub</pre>

    Then add this sub:
    <pre>Private Sub ResetTimer()
    With Me
    If IsNull(.TxtDebtorCode) Then
    .TimerInterval = 500
    Else
    .TimerInterval = 0
    .lblDebtorCode.Visible = True
    End If
    End With
    End Sub</pre>

    You call the ResetTimer sub from both Form Current event and textbox AfterUpdate event:
    <pre>Private Sub Form_Current()
    ResetTimer
    End Sub

    Private Sub TxtDebtorCode_AfterUpdate()
    ResetTimer
    End Sub</pre>

    The control names I used may be slightly different than your example You want the Timer event to fire (or not fire) when you move to new record AND after you update the textbox. Note that it is important to set label's visible property back to True if Timer turned off (highlighted in bold), or else it may remain invisible if your, er, timing is off....

    Again, keep in mind the flashing can get annoying, but I suppose that's the idea.

    HTH

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flashing Label (Access 97)

    Hi

    Thanks for the alternative method.

    However, as alluded to in my 2nd post, the technique is not so much the problem, rather the test part ie IsNull(.TxtDebtorCode) is not working. As stated previously, it always reads this as null, even when the field does have a debtor code.

    Any clues on why this occurs.

    Regards
    WTH

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Flashing Label (Access 97)

    Is there a special reason for setting the controlsource like this:

    Me.TxtDebtorCode.ControlSource = "=nperson_debtor_no"

    If nperson_debtor_no is the name of a field in the table or query, you don't need the "=" in that string at all. Are you sure the controlsource is being properly interpreted as a field?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flashing Label (Access 97)

    Hi

    No I'm not sure whether the controlsource is being properly interpreted as a field. In fact, I'm pretty sure it is not as if it was it should be working. I tried the suggestion on removing the = sign, but still no joy.

    Any other suggestions?

    Thanks & Regards
    WTH

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Flashing Label (Access 97)

    You might try putting square brackets around the field name just to be sure Access doesn't decide to interpret it as a string. If that doesn't work, I would suspect that the field name doesn't actually exist as entered in the forms recordsource. You might add a line in there somewhere attempting to read the value in the field using that name. Oh, are your controls named the same as some of the fields? If so, you might try renaming the controls to avoid confusion.
    Charlotte

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Flashing Label (Access 97)

    Can't you handle this back in the other form. Don't let the user try to open this form without having a debtor code selected in the other form.
    Presumably that other form has a command button. In the code for that button check that a debtor code is selected, and if not, don't open the new form. Instead display a message and return to the focus to the debtor code field.
    Regards
    John



  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Flashing Label (Access 97)

    As noted, I'm not sure why you're setting textbox ControlSource with equal sign preceding field name. Assuming field name is valid field in underlying recordsource, the textbox cannot be edited directly; you get message in Status bar "Control cannot be edited; it's bound to the expression txtDebtorCode." Unless it is your intention that user not be able to edit these controls directly, I would not use the equal sign except for those controls using an expression like the one that concatenates two other fields.

    What code is being used to update this control from the other form? You may want to make sure that if field is blank that it is in fact Null and not a zero-length string, which IsNull will return as False. As far as flashing label goes, you'd need code to trigger Timer event from other form, the control's AfterUpdate event only takes place when you edit control directly, not when you update its value thru some other method.

  11. #11
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flashing Label (Access 97)

    Hi

    Thanks for everyone's advice, much appreciated. I solved the problem by placing the code in the form's open event rather than the current event.

    Regards
    WTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •