Results 1 to 11 of 11
  • Thread Tools
  1. Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms controls disappear (Access XP)

    I have a form whos control source is linked to a query. On the form I have an unbound text box that allows a person to type a "search word". This word is supplied to the query and acts as criteria for a field displayed on the form. I find that when a person types a word that does not match criteria, (and the query returns no records), the forms controls disappear in the details section of the form.
    How can i prevent the controls from disappearing, or at least capture this event and supply the user with some instruction of what is happening? (IE Something like the reports On No Data event?)
    Tx
    Regards,
    Rudi

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Forms controls disappear (Access XP)

    Do you have code in the On Change or After Update event of the search text box? If so, please post the code.

  4. Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms controls disappear (Access XP)

    No, there is no code in any of the forms events!
    Any ideas?
    Regards,
    Rudi

  5. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Forms controls disappear (Access XP)

    There must be some way the form gets updated when the user enters a search word in the unbound text box. This doesn't hapen automatically.

  6. Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms controls disappear (Access XP)

    Sorry, I see what you are after! I have a code linked to the on exit event of the unbound txt box (and a command button) that requeries the form!
    The code is:
    <pre>Private Sub FindPS_Exit(Cancel As Integer)
    Me.Requery
    End Sub
    </pre>


    Could this be the issue?
    Regards,
    Rudi

  7. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Forms controls disappear (Access XP)

    You can build in a check:

    Private Sub FindPS_Exit(Cancel As Integer)
    If DCount("*", "[QueryName]", "[FieldName] = " & Chr(34) & Me.FindPS & Chr(34)) = 0 Then
    MsgBox "No matching records!", vbInformation
    Else
    Me.Requery
    End If
    End Sub

    Replace QueryName and FieldName with the appropriate names. I have assumed that the field is a text field. If it is numeric, omit both instances of & Chr(34).

  8. Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms controls disappear (Access XP)

    Hi Hans,

    Tx for the assistance! The msgbox comes up every single time now! Even if the search returns records?
    Sorry for being a pest!! We are half way there though!
    Regards,
    Rudi

  9. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Forms controls disappear (Access XP)

    What happens if you use

    If DCount("*", "[QueryName]") = 0 Then

    (with the correct query name substituted, of course)

  10. Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms controls disappear (Access XP)

    Yes...now it works well!!

    The code looks like this now:
    <pre>Private Sub FindPS_Exit(Cancel As Integer)
    If DCount("*", "[qryFindPrincipleSubject]") = 0 Then
    MsgBox "No matching records!", vbInformation
    Me.FindPS.SetFocus
    Me.FindPS.SelStart = 0
    Me.FindPS.SelLength = 1000
    Else
    Me.Requery
    End If
    End Sub</pre>


    When I choose OK on the msgbox, the code refuses to select the text in the FindPS txtbox, even though I set the focus on it. Any ideas while we are still on this form?
    Tx
    Regards,
    Rudi

  11. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Forms controls disappear (Access XP)

    Instead of setting focus to FindPS, use

    Cancel = True

  12. Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms controls disappear (Access XP)

    Aaaah, solved. I set the focus on another control and then back on the txtbox. Now it is working!

    Like this:
    <pre>Private Sub FindPS_Exit(Cancel As Integer)
    If DCount("*", "[qryFindPrincipleSubject]") = 0 Then
    MsgBox "No matching records!", vbInformation
    Me.qryResult.SetFocus 'Select the command button
    Me.FindPS.SetFocus
    Me.FindPS.SelStart = 0
    Me.FindPS.SelLength = 1000
    Else
    Me.Requery
    End If
    End Sub</pre>

    Regards,
    Rudi

Posting Permissions

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