Modify recordset in split access database

I am experimenting with splitting a MS Access 2007 database. During testing, I am having difficulty updating a recordset. My main form displays/hides a label (a visual flag to the user) based upon the current record. Following is the sub procedure I'm calling from the On Current event of frmMembers

Public Sub ToggleFlags(ID As Long, b As Boolean)
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.Open "SELECT * FROM tblMembers WHERE memberId = " & ID
    If b = True Then
        rst!BigBuyer = Not rst!BigBuyer         'THIS IS WHERE IT FAILS
        rst.Update                                             'make it happen
    End If
    'now comes the visual to the user depending on the flag
    If rst!BigBuyer = True Then
        Forms!frmMembers.Form.lblBigBuyer.Visible = True
        Forms!frmMembers.Form.lblBigBuyer.Visible = False
    End If
    Set rst = Nothing

End Sub
Any help is appreciated. I have downsized this procedure to toggle one label. In actual practice, this will hide/unhide many labels on the main form.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Isn't your form frmMembers bound to the table tblMembers?  If it is, then all you need to do is add the following to your form's OnCurrent event (assuming that Bigbuyer is a Yes/No Field):

Me.lblBigBuyer.Visible = [BigBuyer]
waverobberAuthor Commented:
Thanks Irog! I was in a hurry when I wrote the first request. Your suggestion looks good and I will alter the OnCurrent code accordingly. I failed to  mention that the "Big Buyer" flag is set by the end user.

There is a command button on frmMembers that enables the user to set/unset the "BigBuyer (yes/no) flag. That is why I need to be able to change the record in the recordset. I understand I can simplify the above procedure to what you have suggested, but how do I manipulate the "bigbuyer" flag in the current record? Please keep in mind it ultimately, will be a split database...
When you say split, I assume you mean that the tables are on a separate database as your Back End and that you have a user interface as your Front End that has all the other objects.  If your tables are linked to your F/E then it doesn't matter whether or not the database is split, you would still interact with the tables as if they were all in one database.  

If the form is bound to tblMembers, then you can just add a checkbox control that is bound to the BigBuyer field which the user can check or uncheck.  If you call this checkbox chkBigBuyer, then your OnCurrent event should have:
Me.lblBigBuyer.Visible = Me.[chkBigBuyer]
If you prefer to use a command button instead of a checkbox, then the code in your button's OnClick event should be:
[BigBuyer]=Not [BigBuyer]
And your OnCurrent event should have:
Me.lblBigBuyer.Visible = [BigBuyer]

When referencing an actual control on your form, it is good practice to prefix it with the Me keyword, that way you know you are talking about a control and not a variable in your module.  Use brackets around a fieldname that is in the bound recordset but has no bound control attached to it.  So [BigBuyer] references the field in the in tblMembers (if the form is bound) whereas Me.chkBigBuyers references a control on your form that is bound to the field [BigBuyer].

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
waverobberAuthor Commented:
Thanks Irog. I was making it way more complicated than it needed to be. Forgot KISS!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.