Results 1 to 5 of 5
  1. #1
    HelenM is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    7

    Combo box retaining query data

    Hi, I have a combo box that uses a query to concatenate membership number and name, and allows the user to type in either to select the right person for a new record (tables are membership details and fishing register, with membership number as the link field).

    this works well, for the current record, and when I move forward to another new record, it allows me to type in a new request, but only displays the result of the old request if I drop the arrow on the combo box. Unless I delete the request by typing a space.

    when I move backwards through the existing records, the box also retains the selection that was made and doesn’t display any text. If I drop the box down it just displays the record that I previously searched for, as before, but in this case if I make an edit to the combo I also edit the underlying record which I don’t want to do.

    how do I reset the text that the combo box is working from without altering the underlying record please.

    This is the query.

    Private Sub cmb_memb_no_Change()
    Dim SQL As String

    SQL = "Select comboname,[Memb No] from qry_name_for_register_combo where comboname Like '*" & [cmb_memb_no].[Text] & "*' "
    cmb_memb_no.RowSource = SQL
    SendKeys "%{DOWN}"
    End Sub

    memb no is what is saved in the record
    I’ve realised my select statement might be confusing, comboname isn’t the name of the combo, it’s the name and number configured for display in the combo.



    thank you

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not very clear what you are trying to achieve, but sounds like you need to 'reset' the combo rowsource in the form current event.

    To not display anything, set the rowsource

    Select comboname,[Memb No] from qry_name_for_register_combo where FALSE

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The change event fires for almost every keystroke. Are you sure you want to run the sql for each key press? If so, it looks to me like you should be doing a requery on the combo for every key press. Not very efficient? SendKeys can be wonky - suggest you do away with it or at least don't fall into the habit.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    DittoBird's Avatar
    DittoBird is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2015
    Location
    Newfoundland & Labrador, Canada
    Posts
    59
    DON'T KNOW HOW TO DELETE THIS - IGNORE, BAD ADVICE


    From my experience in A2003, don't use SQL as a variable. SQL is an actual property in something called a queryDef (if you are familiar with this) in VBA development. Instead, use strSQL, it will be much safer.

    How many members are we talking about here? Requerying the combo box's underlying query with key strokes in the actual combo box is not something I'd recommend. The combo box will autofill as you type in a name. As far as I can tell, you don't need an an change event.

    As far as the name "comboname", it really helps you and anyone who comes along afterwards to give a more descriptive name to that query or table field.

    I've done what you are trying to do in the past but instead, I have an unbound text box, say "txtSearchCombo" near the combo box to enter parameters of name or membership number. You would then set your query to be:

    strSQL = "Select comboname,[Memb No] from qry_name_for_register_combo where comboname Like '*" & txtSearchCombo & "*' "

    You would then set an on change event for txtSearchCombo like the following. Note, the error handling thing is an anal retentiveness of mine, you may not need it, but I have had it hammered into me in the past on the old news group, comp.databases.ms-access since the 90s.

    Code:
    Private Sub txtSearchCombo_Change()
        On Error GoTo Err_Proc
        Me.cmb_memb_no.Requery
    Exit_Proc:
        Exit Sub
    Err_Proc:
        Select Case Err.Number
            Case Else
                MsgBox "Access Error " & Err.Number & " " & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
                Resume Exit_Proc
        End Select
    End Sub
    On the form on_current event, reset the txtSearchCOmbo to a zero length string and then call the above on_change event:

    Code:
    Private Sub Form_Current()
        On Error GoTo Err_Proc
        Me.txtSearchCombo = ""
        txtSearchCombo_Change
    Exit_Proc:
        Exit Sub
    Err_Proc:
        Select Case Err.Number
            Case Else
                MsgBox "Access Error " & Err.Number & " " & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
                Resume Exit_Proc
        End Select
    End Sub
    --
    Tim

  5. #5
    HelenM is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    7
    Thank you all. You have made me rethink my design, which to tell the truth evolved from something else. I’ve step back and created separate but related combo boxes, so that the user can either enter either membership number or name and both are populated. Much simpler.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Retaining Final Data for Historical Reporting
    By rjgriffin46 in forum Access
    Replies: 3
    Last Post: 08-22-2018, 01:18 PM
  2. Retaining Data in Next form entry
    By Darth_Elicious in forum Access
    Replies: 2
    Last Post: 04-19-2017, 07:19 AM
  3. Replies: 5
    Last Post: 12-30-2011, 01:01 AM
  4. retaining data on a form
    By appleb007 in forum Forms
    Replies: 1
    Last Post: 06-07-2011, 11:07 AM
  5. Replies: 1
    Last Post: 07-19-2010, 09:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums