Prev: Conditional formating question
Next: hyperlink or no hyperlink
From: PW on 26 Jan 2010 18:37
On Sun, 24 Jan 2010 17:03:08 +0800, "Allen Browne"
<AllenBrowne(a)SeeSig.invalid> wrote:

>Sounds like you are not clear on how to verify the update is actually
>happening.
>
>Immediately after the Update row in your code, add some Debug.Print
>statements to indicate what's happened, e.g.:
>
>
> If rstObjPerms.NoMatch Then
> rstObjPerms.addnew
> rstObjPerms!ObjectName = strObjectName
> rstObjPerms!GroupName = strGroupName
> rstObjPerms!ObjectType = "M"
> rstObjPerms.Update
> rstObjPerms.Bookmark = rstObjPerms.LastModified
>
> Debug.Print "rstObjPerms.Update executed at " & Now() & _
> " creating record " & rstObjPerms![YourPrimaryKeyNameHere] & _
> ". " Error was " & Err.Number
> Debug.Print "Verifying it made it into the table: " & DLookup("...


From the immediate window (one set for each record. What do you want
the DLookup statement to do? Look up the primary key?):

rstObjPerms.Update executed at 1/26/2010 4:33:54 PM creating record
96. & Err.Number
Verifying it made it into the table:

-paul
From: Allen Browne on 27 Jan 2010 08:33
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

--
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:s3vul5t0b2jftel96ipfu77jnvh4krb600(a)4ax.com...
> On Sun, 24 Jan 2010 17:03:08 +0800, "Allen Browne"
> <AllenBrowne(a)SeeSig.invalid> wrote:
>
>>Sounds like you are not clear on how to verify the update is actually
>>happening.
>>
>>Immediately after the Update row in your code, add some Debug.Print
>>statements to indicate what's happened, e.g.:
>>
>>
>> If rstObjPerms.NoMatch Then
>> rstObjPerms.addnew
>> rstObjPerms!ObjectName = strObjectName
>> rstObjPerms!GroupName = strGroupName
>> rstObjPerms!ObjectType = "M"
>> rstObjPerms.Update
>> rstObjPerms.Bookmark = rstObjPerms.LastModified
>>
>> Debug.Print "rstObjPerms.Update executed at " & Now() & _
>> " creating record " & rstObjPerms![YourPrimaryKeyNameHere] & _
>> ". " Error was " & Err.Number
>> Debug.Print "Verifying it made it into the table: " & DLookup("...
>
>
> From the immediate window (one set for each record. What do you want
> the DLookup statement to do? Look up the primary key?):
>
> rstObjPerms.Update executed at 1/26/2010 4:33:54 PM creating record
> 96. & Err.Number
> Verifying it made it into the table:
>
> -paul

From: PW on 27 Jan 2010 15:42
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


I get a value back:

Verifying it made it into the table: Bar Inventory
rstObjPerms.Update executed at 1/27/2010 1:36:29 PM creating record
358.

Using:

Debug.Print "rstObjPerms.Update executed at " & Now() & _
" creating record " & rstObjPerms![RecID]

strReturnVal = DLookup("[objectname]", "tblObjectPermissions",
"[recid] = " & rstObjPerms![RecID])

Debug.Print "Verifying it made it into the table: " &
strReturnVal

But the record was never changed (I unchecked a Yes/No field and it
remained checked in the table).

I do not know why my code works in the command button click event but
not the AfterUpdate event of the combobox.

Oh well,

Thanks Allen
From: Salad on 27 Jan 2010 16:20
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(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!

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.
From: PW on 27 Jan 2010 16:37
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(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!
>
>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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Conditional formating question
Next: hyperlink or no hyperlink