From: PW on
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
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
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
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
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