VBA in unbound comboBox to bring up record (1 Viewer)

[dhop1990]

So I have a form with subforms, where the main form's source is a table called tblCharts. Within this table there is a field called ID that is autonumbered.

So I created a combo box that's row source is a query that selects the ID field from table charts and two other descriptive fields to make navigation easier. Limit List is set to yes.

The problem comes up with code that I've used multiple times, but not with an autonumber. The code typicallly looks like this when I'm searching on a text field.

Sub cboPatientSelection_AfterUpdate ()
    Dim rs As DAO.Recordset

    If Not IsNull(Me.cboPatientSelection) Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[tblCharts].[ID] = """ & Me.cboPatientSelection & """"
        If rs.NoMatch Then
            MsgBox "Not found. Perhaps the file is filtered; Remove all filters before proceeding"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
End Sub

This worked at first but then 3070 runtime error came up, the form started messing up. So I made this code

Sub cboPatientSelection_AfterUpdate ()
    Dim rs As DAO.Recordset

    If Not IsNull(Me.cboPatientSelection) Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[tblCharts].[ID] = " & Me.[cboPatientSelection] & ""
        If rs.NoMatch Then
            MsgBox "Not found. Perhaps the file is filtered; Remove all filters before proceeding"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
End Sub

This code worked at first, so I decided to make a copy of my form called Copy Of frmCharts. After this the code stopped working on the initial frmCharts but now works on the copy.

I'm not sure why this happened, any tips about the code, or any idea of what is going on?

I posted a similar question a while back but this is a different database and running in MS 2013

Edit: I also received other errors besides 2070. I know what error 3070 is but I can't figure out why it's not working on this form. I closed and opened the form a few times and it happened to start working. Very frustrating when I'm trying to make the system reliable for others to use

Edit2: The code only seems to do this when I enter design view then switch back to form view

 

[RuralGuy]

An AutoNumber is a Long Integer, not a String. You should not use the quotes.

 

[dhop1990]

So as of now this works before opening design view

rs.FindFirst "[tblCharts].[ID] = " & Me.[cboPatientSelection] & ""

when I change it to

rs.FindFirst "[tblCharts].[ID] ="  & Me.[cboPatientSelection]

Could you perhaps hint me to the right form. I'm working on different combinations and googling solutions now.

 

[RuralGuy]

rs.FindFirst "[tblCharts].[ID] = " & Me.[cboPatientSelection]

 

[dhop1990]

Thanks RuralGuy.

It's working, only breaks when I switch in to design view then back to form view without closing the form. I'll look into that more

 

[RuralGuy]

Do you have code in the OnLoad or Open events?

 

[dhop1990]

I have it on an After Update event on the cboPatientSelection

 

[RuralGuy]

I was trying to figure out why switching to Design View and the to Form View would cause it to not work properly. The question I posed in Post#6 was about Form Events.

 

[dhop1990]

Oh yes, sorry for the confusion. I was a little confused for some silly reason.

I have the following form code on an open event

Private Sub Form_Open(Cancel As Integer)
         DoCmd.GoToRecord , , acNewRec
End Sub

This is because for the form I want them to enter a new person almost all of the time. The cboPatientSelection is included to allow them to go back and select one they have filled out and correct information when they do make mistakes and close the form before finishing.


The rest of this isn't what you particularly asked for but I'm including just in case. Sorry for the long post

Private Sb Form_Current()
Dim default_value As String
default_value = ""
Me.cboPatientSelection =default_value

 

[RuralGuy]

It is probably the CurrentEvent code that is causing an issue. If you are just trying to clear the ComboBox each time you go to a new record you could just use:
Private Sub Form_Current()
Me.cboPatientSelection = Null
...or...
Me.cboPatientSelection = ""

 

[dhop1990]

Yea I gave it a try, then removed the code completely, and it's still doing the same thing. Idk what it is but I'm going to comb over the set properties for the form and the control and try to systematically problem solve what it could be.


Thanks a lot, you have been a life saver for me today. I really appreciated the help

 

[RuralGuy]

Good for me. I guess we can call this thread Solved.