Combo box After Update won't update the recordset/table
in [Help]
|
From: PW on 22 Jan 2010 14:45 Hi, Am using Access 2003. I put a combobox on a form. When the user picks an entry, I want to update a table so I call a subroutine in the AfterUpdate property. However the recordset never gets updated. I stepped through it and the line of code with .Update executes but when I go to the table the change was never made. However, if I call the same subroutine from the on-click event of a command button the table gets updated! I guess there is something to the AfterUpdate property that I don't know. Any ideas? I would rather the user not have to press a save button but no biggie. Just curious. -paulw
From: Allen Browne on 22 Jan 2010 21:40 What's in the combo's ControlSource property? Is it bound to a field? If so, you don't need to use a recordset to save the data. In its AfterUpdate event procedure, you can save it to the table just by saving the record in the form: Me.Dirty = False If it's unbound, it's AfterUpdate event should work. Post the code. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PW" <emailaddyinsig(a)ifIremember.com> wrote in message news:8qvjl51g42v4lpdnlnohic7tnp7035kabo(a)4ax.com... > Hi, > > Am using Access 2003. > > I put a combobox on a form. When the user picks an entry, I want to > update a table so I call a subroutine in the AfterUpdate property. > However the recordset never gets updated. I stepped through it and > the line of code with .Update executes but when I go to the table the > change was never made. > > However, if I call the same subroutine from the on-click event of a > command button the table gets updated! > > I guess there is something to the AfterUpdate property that I don't > know. > > Any ideas? I would rather the user not have to press a save button > but no biggie. Just curious. > > -paulw
From: PW on 22 Jan 2010 23:04 On Sat, 23 Jan 2010 10:40:20 +0800, "Allen Browne" <AllenBrowne(a)SeeSig.invalid> wrote: >What's in the combo's ControlSource property? >Is it bound to a field? > >If so, you don't need to use a recordset to save the data. In its >AfterUpdate event procedure, you can save it to the table just by saving the >record in the form: > Me.Dirty = False > >If it's unbound, it's AfterUpdate event should work. Post the code. Hi Allen, cbo is unbound: This code works (updates the table) with the save button but not with the Afterupdate event. Again, in debug it does "work" ( rstObjPerms.Update does happen) but does not actually update the table in the AfterUpdate event of the combobox: Have at it! :-) This was the code for the combobox which I moved to the save button: Private Sub cmdSave_Click() 'First Save a Record to the tblMenuPermWorkF, just in case DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 PopPermissions End Sub PopPermissions is as follows: Private Sub PopPermissions() Dim strFind As String Dim strObjectName As String Dim strGroupName As String Dim rstWorkf As Recordset Dim rstObjPerms As Recordset Dim db As Database Set db = CurrentDb() Set rstWorkf = db.OpenRecordset("tblMenuPermWorkf") Set rstObjPerms = db.OpenRecordset("tblObjectPermissions") strGroupName = Trim(Me.cboGroupNames) rstWorkf.MoveFirst Do While Not rstWorkf.EOF ' Need to get the proper object to lookup in the permissions table. ' If the Level2Menu is empty, use Level1Menu which means its a main menu. ' If Level3Menu contains something, us it as it is a "sub-sub" menu. ' If Level2Menu contains something, and Level3Menu is empty, then it's a submenu: If IsNull(rstWorkf!Level2Menu) Then strObjectName = Trim(rstWorkf!Level1Menu) End If If Not IsNull(rstWorkf!Level2Menu) And IsNull(rstWorkf!Level3Menu) Then strObjectName = rstWorkf!Level2Menu End If If Not IsNull(rstWorkf!Level3Menu) Then strObjectName = rstWorkf!Level3Menu End If strFind = "[objectname] = '" & strObjectName & "' And Trim([GroupName]) = '" & strGroupName & "'" rstObjPerms.FindFirst strFind ' If no record for the group + object (will this ever happen?), add it. Otherwise just populate the PermissionYN field: If rstObjPerms.NoMatch Then rstObjPerms.addnew rstObjPerms!ObjectName = strObjectName rstObjPerms!GroupName = strGroupName rstObjPerms!ObjectType = "M" rstObjPerms.Update Else rstObjPerms.Edit rstObjPerms!PermissionYN = rstWorkf!PermissionYN rstObjPerms.Update End If rstWorkf.MoveNext Loop End Sub Thanks!
From: Allen Browne on 23 Jan 2010 04:06 You can debug the code, adding Debug.Print right below the line that executes the change, to verify it is made. If it is, but the table is later unchanged, you may have something (bound form?) changing it back. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PW" <emailaddyinsig(a)ifIremember.com> wrote in message news:a0tkl5dmc1o8mh3dufq98lmi98k3e8f5u4(a)4ax.com... > On Sat, 23 Jan 2010 10:40:20 +0800, "Allen Browne" > <AllenBrowne(a)SeeSig.invalid> wrote: > >>What's in the combo's ControlSource property? >>Is it bound to a field? >> >>If so, you don't need to use a recordset to save the data. In its >>AfterUpdate event procedure, you can save it to the table just by saving >>the >>record in the form: >> Me.Dirty = False >> >>If it's unbound, it's AfterUpdate event should work. Post the code. > > Hi Allen, > > cbo is unbound: > > This code works (updates the table) with the save button but not with > the Afterupdate event. Again, in debug it does "work" ( > rstObjPerms.Update does happen) but does not actually update the table > in the AfterUpdate event of the combobox: > > Have at it! :-) > > This was the code for the combobox which I moved to the save button: > > Private Sub cmdSave_Click() > 'First Save a Record to the tblMenuPermWorkF, just in case > DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , > acMenuVer70 > > PopPermissions > > End Sub > > PopPermissions is as follows: > > Private Sub PopPermissions() > > Dim strFind As String > Dim strObjectName As String > Dim strGroupName As String > > Dim rstWorkf As Recordset > Dim rstObjPerms As Recordset > > Dim db As Database > > Set db = CurrentDb() > Set rstWorkf = db.OpenRecordset("tblMenuPermWorkf") > Set rstObjPerms = db.OpenRecordset("tblObjectPermissions") > > strGroupName = Trim(Me.cboGroupNames) > > rstWorkf.MoveFirst > > Do While Not rstWorkf.EOF > > > ' Need to get the proper object to lookup in the permissions table. > ' If the Level2Menu is empty, use Level1Menu which means its a main > menu. > ' If Level3Menu contains something, us it as it is a "sub-sub" menu. > ' If Level2Menu contains something, and Level3Menu is empty, then it's > a submenu: > > If IsNull(rstWorkf!Level2Menu) Then > strObjectName = Trim(rstWorkf!Level1Menu) > End If > > If Not IsNull(rstWorkf!Level2Menu) And IsNull(rstWorkf!Level3Menu) > Then > strObjectName = rstWorkf!Level2Menu > End If > > If Not IsNull(rstWorkf!Level3Menu) Then > strObjectName = rstWorkf!Level3Menu > End If > > strFind = "[objectname] = '" & strObjectName & "' And > Trim([GroupName]) = '" & strGroupName & "'" > > rstObjPerms.FindFirst strFind > > > ' If no record for the group + object (will this ever happen?), add > it. Otherwise just populate the PermissionYN field: > > If rstObjPerms.NoMatch Then > > rstObjPerms.addnew > rstObjPerms!ObjectName = strObjectName > rstObjPerms!GroupName = strGroupName > rstObjPerms!ObjectType = "M" > rstObjPerms.Update > > Else > > rstObjPerms.Edit > rstObjPerms!PermissionYN = rstWorkf!PermissionYN > rstObjPerms.Update > > End If > > rstWorkf.MoveNext > > Loop > > End Sub > > > Thanks!
From: PW on 23 Jan 2010 19:10
On Sat, 23 Jan 2010 17:06:26 +0800, "Allen Browne" <AllenBrowne(a)SeeSig.invalid> wrote: >You can debug the code, adding Debug.Print right below the line that >executes the change, to verify it is made. > >If it is, but the table is later unchanged, you may have something (bound >form?) changing it back. I thought about that but.... It executes the line that updates the record. I see it in debug. But the changes are not actually saved to the table. However, when I execute the routine from a command button the change gets saved to the table. Must be something funky with the after update event of a combobox or something. The bound table of the form is the work file, not tblObjectPermissions. Thanks -paul |