Combo box After Update won't update the recordset/table
in [Help]
|
From: Salad on 27 Jan 2010 17:08 PW wrote: > On Wed, 27 Jan 2010 13:20:03 -0800, Salad <salad(a)oilandvinegar.com> > wrote: > > >>PW wrote: >> >> >>>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(!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! >> >>At your line >> rstObjPerms.FindFirst strFind >>maybe add something like this so >> msgbox strFind >> msgbox IIf(rstObjPerms,"Found",Not Found") >> msgbox "Object " & strObjectName >> msgbox "Group " & strGroupName >> >> >>Perhaps strFind doesn't hold the values you think they do. Then it adds >>incorrect values/unexpected values to new recs to something you don't >>expect or finds an incorrect record to update. > > > It does. I have spent a bunch of time checking out the process in > debug. The same code works when run from a command button but not > when run from the AfterUpdate event of the combobox (the value of the > yes/no field never gets changed. Weird!). > > Thanks > > -paul Don't know what the value is of rstWorkf!PermissionYN. Maybe it's Null. Also, maybe put some code in the Before/After_Update events. msgbox "I'm in Before " & Cancel and msgbox "I'm in After." to verify the record get's updated.
From: PW on 27 Jan 2010 21:35 On Wed, 27 Jan 2010 14:08:05 -0800, Salad <salad(a)oilandvinegar.com> wrote: >PW wrote: > >> On Wed, 27 Jan 2010 13:20:03 -0800, Salad <salad(a)oilandvinegar.com> >> wrote: >> >> >>>PW wrote: >>> >>> >>>>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(!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! >>> >>>At your line >>> rstObjPerms.FindFirst strFind >>>maybe add something like this so >>> msgbox strFind >>> msgbox IIf(rstObjPerms,"Found",Not Found") >>> msgbox "Object " & strObjectName >>> msgbox "Group " & strGroupName >>> >>> >>>Perhaps strFind doesn't hold the values you think they do. Then it adds >>>incorrect values/unexpected values to new recs to something you don't >>>expect or finds an incorrect record to update. >> >> >> It does. I have spent a bunch of time checking out the process in >> debug. The same code works when run from a command button but not >> when run from the AfterUpdate event of the combobox (the value of the >> yes/no field never gets changed. Weird!). >> >> Thanks >> >> -paul > >Don't know what the value is of rstWorkf!PermissionYN. Maybe it's Null. Either -1 or whatever. Not null. > >Also, maybe put some code in the Before/After_Update events. > msgbox "I'm in Before " & Cancel >and > msgbox "I'm in After." >to verify the record get's updated. It is getting updated but the table actually does not. Same code works in a command button! Thanks Salad
From: PW on 27 Jan 2010 21:45 On Wed, 27 Jan 2010 21:33:48 +0800, "Allen Browne" <AllenBrowne(a)SeeSig.invalid> wrote: >DLookup the field in the table to see if the value really is that at this >point (even if you say it disappears again later.) > >If you need help with DLookup: > http://allenbrowne.com/casu-07.html Code is working in the OnChange event of the combobox! -pw
From: PW on 27 Jan 2010 21:46 On Wed, 27 Jan 2010 14:08:05 -0800, Salad <salad(a)oilandvinegar.com> wrote: >PW wrote: > >> On Wed, 27 Jan 2010 13:20:03 -0800, Salad <salad(a)oilandvinegar.com> >> wrote: >> >> >>>PW wrote: >>> >>> >>>>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(!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! >>> >>>At your line >>> rstObjPerms.FindFirst strFind >>>maybe add something like this so >>> msgbox strFind >>> msgbox IIf(rstObjPerms,"Found",Not Found") >>> msgbox "Object " & strObjectName >>> msgbox "Group " & strGroupName >>> >>> >>>Perhaps strFind doesn't hold the values you think they do. Then it adds >>>incorrect values/unexpected values to new recs to something you don't >>>expect or finds an incorrect record to update. >> >> >> It does. I have spent a bunch of time checking out the process in >> debug. The same code works when run from a command button but not >> when run from the AfterUpdate event of the combobox (the value of the >> yes/no field never gets changed. Weird!). >> >> Thanks >> >> -paul > >Don't know what the value is of rstWorkf!PermissionYN. Maybe it's Null. > >Also, maybe put some code in the Before/After_Update events. > msgbox "I'm in Before " & Cancel >and > msgbox "I'm in After." >to verify the record get's updated. The code is working in the OnChange event of the combobox. But not in it's AfterUpdate method. Interesting. Thanks -pw
From: Allen Browne on 28 Jan 2010 06:55 "PW" <emailaddyinsig(a)ifIremember.com> wrote in message news:ogu1m5dlv890o3dngoi7s0dp9vp4gddk64(a)4ax.com... > Code is working in the OnChange event of the combobox! There's the problem. The combo's Value has not been updated yet. -- 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.
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 5 Prev: Conditional formating question Next: hyperlink or no hyperlink |