Results 1 to 9 of 9
  1. #1
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16

    Question Combo box lookup runtime error

    I have an unbound combo box [cboState] on a form, a sub-form running off a query [qryHeavyMaintenanceRegister] (I'm using a query for the sub-form as I have calculations running in the query which I want to display in the sub-form). On the AfterUpdate event I want the sub-form to show only the data for the State selected in the combo box.



    I'm sure the issue is looking me right in the face but I'm not seeing it.

    I'm getting a Run-time error that my select statement is missing or the punctuation is incorrect. I can't figure out what it is though, I've had this code working before and it's exactly the same.

    Private Sub cboState_AfterUpdate()
    Dim myState As String
    myState = "Select from qryHeavymaintenanceregister where ((state) = " & Me.cboState & ")"
    Me.sfrmHeavyMaintenanceRegister.Form.RecordSource = myState
    Me.sfrmHeavyMaintenanceRegister.Form.Requery

    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why do you have a subform?

    Is the main form UNBOUND?

    Is [state] a text field? If yes, need apostrophe delimiters for the parameter.

    myState = "Select from qryHeavymaintenanceregister where state = '" & Me.cboState & "'"

    If the main form is unbound, consider a solo form in Continuous or Single view and combobox in the form Header and data controls in Detail section

    I prefer to set Filter property instead of Recordsource.

    Then code is:

    Me.Filter = "State='" & Me.cboState & "'"
    Me.FilterOn = True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, you are missing the field list in the select statement - either a minimum of 1 field or the asterisk.
    Use
    Code:
    myState = "Select City, State, Equip from qryHeavymaintenanceregister where state = '" & Me.cboState & "'"
    or
    Code:
    myState = "Select * from qryHeavymaintenanceregister where state = '" & Me.cboState & "'"
    Be aware that "State" is a reserved word and shouldn't be used as an object name.
    http://allenbrowne.com/AppIssueBadWord.html#S

  4. #4
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    Sorry I should have mentioned, the main form is a Switchboard with a Tab Control on the main form to display the sub form in continuous view on the first tab and command buttons to open reports on the second tab.

    The [State] is a combo box which is linked to the table tblState to select a state.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ooops, I should have seen that.

    If you prefer to use form/subform (I have done that even when main form was not bound), can still use code to set the subform Filter and FilterOn property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    This is the code I have currently, it runs with no run time errors but doesn't return any results.


    Private Sub cboState_AfterUpdate()
    Dim myState As String
    myState = "Select State from qryHeavyMaintenanceRegister where state = '" & Me.cboState & "'"
    Me.Filter = "State='" & Me.cboState & "'"
    Me.FilterOn = True
    Me.sfrmHeavyMaintenanceRegister.Form.Requery
    End Sub

    I'm now completely lost

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is it still a form/subform?

    If a subform then need to set subform Filter.

    Me.sfrmHeavyMaintenanceRegister.Form.Filter = "State='" & Me.cboState & "'"
    Me.sfrmHeavyMaintenanceRegister.Form.FilterOn = True

    Don't need the Requery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    Thanks so much, it works great. Should I have a Null function in there somewhere? so that if i delete the text from the cboState combo box the sub form will show all records?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use LIKE operator with wildcard.

    "State LIKE '" & Me.cboState & "*'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-26-2016, 12:34 PM
  2. Replies: 2
    Last Post: 08-22-2015, 11:26 AM
  3. Replies: 2
    Last Post: 10-15-2014, 04:23 AM
  4. Replies: 13
    Last Post: 06-12-2012, 09:52 PM
  5. Replies: 2
    Last Post: 11-12-2011, 08:37 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