Form unbound combo box, VBA code working on some forms but not others

[dhop1990]

Private Sub cboPatientSelection_AfterUpdate()
 Dim rs As DAO.Recordset

    If Not IsNull(Me.cboPatientSelection) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        [COLOR="Red"]rs.FindFirst "[VolpID] =""" & Me.cboPatientSelection & """"[/COLOR]
        If rs.NoMatch Then
            MsgBox "Not found: filtered?"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
End Sub

So basically, I have a form that used a specific SQL source for the form, and I had to remove a query that I was using because it had become obsolete (in retrospect I didn't necessarily have to remove it). But after the combobox stopped being able to load patient chart information and started providing error messages when I try to select a patient

The problem is the above code has stopped working for the field VolpID, and the unbound comboxbox I'm using is broken on this form. I've tried using the code above in different places and it has been hit or miss on whether it works.

On this particular form I have Data Entry set to Yes. The fetch default is set to yes. For the unbound combobox, is limited to list and does allow value lists.

In the background the table is based of tblpatient left joined with tblinsurance, with two queries as well tagging along to provide calculated fields.

Been working at fixing this for a few days. Any help would be much appreciated to better understand how this code( from allen browne) interacts with my form settings, etc.

 

[David R]

Does the recordsource for the form still include [VolpID]? That would be the most obvious answer to why it doesn't work... you can add it invisible if you don't want to show it to the user, but the form has to know HOW to compare records!

Do you still have a copy of that query anywhere, maybe in a backup?

 

[dhop1990]

Yea I wrote the record source in SQL for the form as such

SELECT tblPatients.*, qryPatientsExtended.Name, qryPatientsExtended.Age, qryPatientsBillingLastVisit.LastVisit, tblPatientAetna.AetnaActive, tblPatientAetna.AetnaID, tblPatientAetna.AetnaGroupNumber, tblPatientAetna.AetnaCopay, tblPatientAetna.AetnaDeductible, tblPatientAetna.AetnaStart
FROM ((tblPatients INNER JOIN qryPatientsExtended ON tblPatients.VolpID = qryPatientsExtended.VolpID) INNER JOIN qryPatientsBillingLastVisit ON tblPatients.VolpID = qryPatientsBillingLastVisit.VolpID) INNER JOIN tblPatientAetna ON tblPatients.VolpID = tblPatientAetna.VolpID;

And tblpatients includes the [VolpID], but I left out [VolpID] everywhere else (ie the other form and query).< On second thought, I included VolpID in the original for all forms and queries and then compared them in SQL. >

I don't have the query in backup, which immediately after erasing, I regretted.

 

[David R]

As for what that subroutine does...

 Dim rs As DAO.Recordset

Declares a variable to hold a 'copy' of the recordset, so it doesn't mess with your main data.

    If Not IsNull(Me.cboPatientSelection) Then

Is there anything in the lookup box? If not, none of this code will make sense.

        If Me.Dirty Then
            Me.Dirty = False
        End If

Save the record before we go moving around on it. This will also force any code you have set to run on Save to run, so all the usual error/validity checking applies.

        Set rs = Me.RecordsetClone
        rs.FindFirst "[VolpID] =""" & Me.cboPatientSelection & """"

Set up a copy (clone) of the recordset, then find the first record that matches the lookup combobox.

        If rs.NoMatch Then
            MsgBox "Not found: filtered?"

Didn't find anything. Ask the user for the most likely answer (but really this should probably be a bit more robust, like removing a filter itself and then checking again).

        Else
            Me.Bookmark = rs.Bookmark

This jiggery-pokery is the hardest to follow, but essentially it's setting your REAL location in the recordset to match what it found in the copy. So if record 17 was the match, it'll move you to the real record 17.

       Set rs = Nothing

Memory cleanup.

 

[dhop1990]

Putting back in the ID's for each form/qry pops up a modal box asking me to "enter the value parameter". When I hit ok on blank the entire form loads, except for the subforms, since its not recognizing the ID value and linking it to the subforms ID value I guess.

All the ID's have proper relationships and such (1-1 for tblpatients and tblinsurance, 1-multiple for the billing, etc.)

Edit: Thanks that post helped clear up a little confusion with the VBA. The lookup-box is generating a patient list of around 400 patients as of now, using [tblPatients].[VolpID] as the record source, and three columns to show first and last name to make finding the correct code easier.

 

[David R]

Okay, wait... is the query you deleted the ROWsource for Me.cboPatientSelection ? That might explain the error you're getting... but yeah, you shouldn't need [VolpID] on the subforms so long as the subforms have their own relationship to the main form correctly identified still (they do, don't they? I assume so since I'm assuming the form loads correctly when you're not trying to search)

If you can post a stripped-down database with no patient/client information left, just a dummy record or two, that would help.

 

[dhop1990]

I removed all the records and put in a basic two basic dummy records, with about half of the fields filled in.

Do you want me to remove unnecessary forms, as to get rid of any embedded VBA?

Edit: I erased a lot of functionality to get it under 2MB, this one still has VBA in a few of its forms

 

[David R]

Okay, something had gotten screwed up in your Billing subform on the third tab. Go back into the Design View, click on fsubPatientsBillingDataSheet, go to Properties>Data and click the [...] button at the end of Link Master Fields. Access will probably spin for a minute, and then tell you something is wrong. Manually reselect tblpatients.VolpID for the master and VolpID for the Child Field, and Save. Your combo worked fine for me after that.

 

[dhop1990]

Thanks a lot, it all makes sense now. I can't believe I looked over that. ha

OT: K.C. bbq is great, I'm envious that you have access to that great bbq (assuming you like BBQ). Need to go back some day.

 

[David R]

Glad it worked! I didn't look real closely at why your query was specifying tblpatients.VolpID in the master, so you might want to save a copy of the database and go digging.

And yeah, KC BBQ is pretty fantastic. Even within the town though there are factions as to who is best (me, I like Gates, Jack Stack, and Oklahoma Joe's).

 

[dhop1990]

Yea, I'll make sure to do that this time. Thanks again for all of your help. I think I just need to learn a lot more so I can be more thorough in the future.

Jack Stack was amazing when I went out there last. I'm definitely gonna save the other names for future reference.

I guess its like any type of food a town may be famous for, there seems to be a never ending battle for has the best cheesesteak in Philadelphia.