• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

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.
  • 2
  • 2
1 Solution
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].
waverobberAuthor Commented:
Thanks Irog. I was making it way more complicated than it needed to be. Forgot KISS!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now