Run-time Errorless Bookmark/DLookUp Module Not Working

[soule]

In my .accdb, DAO Db, I'm trying to use an AfterUpdate or OnLostFocus event procedure in a class module to populate a date on two tables. My form control is
bound to the "MovieCodeSendDate" field on my master table, and I want the field of the same name on my detail table to be populated by the event procedure. I
have the following code in place which neither produced errors nor works. It first bookmarks the relevant form control and detail table field by looking up a pivot value ("MovieCode") in a listbox, then (attempts to) populate it with a DLookUp statement. I'm not sure if there's a problem in the environment, my references, or what. Like I said, the code doesn't error in run-time, yet doesn't work when I check the table for the date that's supposed to populate next to the correct movie code.

- My form is based on a query that includes every field from both tables (".*").
- The "MovieCode" field for both tables is the primary key.
- I took out the join on "MovieCode" between them because the relationship between them will always be one to one and I think the join was messing up my Form control source query statement in relation to this procedure.

Private Sub A1_Tracking_Form_Movie_Code_Send_Date_Control_On_Lost_Focus()
 
 
' When the "Movie Code Send Date" control text box is tabbed off of, this code will find the same code in the Movie Code Table that is
' populated in the listbox of the current record... 
 
' First, the fields that have already been data-entered are saved for good measure.
 
DoCmd.RunCommand acCmdSaveRecord
 
 
On Error GoTo PROC1_ERR
 
Dim rs as DAO.Recordset
 
Set rs = Me.RecordsetClone
 
rs.FindFirst "[A1 Movie Code Table].[MovieCode]= '" & Me.[A1 Tracking Form Movie Code List Box] & "'"
 
Forms![A1 Onboarding Tracking Form].Bookmark = rs.Bookmark
 
If rs.NoMatch Then
   Msgbox "No match found.", vbInformation + vbOKOnly
Else
  Me.Bookmark = rs.Bookmark
End If
 
Debug.Print ("Find matching movie code between form control and movie code table")
 
PROC1_EXIT:
Exit Sub
 
PROC1_ERR:
MsgBox "Error finding matching movie code between form control and movie code table." & _
vbCrLf & "Check in table to see if code you picked is already used." & vbCrLf & Err.Number & " " & _
Err.Description, vbExclamation + vbOKOnly, "Find Matching Control Code In Movie Code Table"
 
Resume PROC1_EXIT
 
rs.Close
 
 
' If found, code will next populate the movie code send date cell right next to
' the correct code. This will ensure only new codes are available in the movie code listbox during
' data entry and prevent the sending of one code to more than one employee.
 
On Error GoTo PROC2_ERR
 
Dim dt As Date
 
dt = DLookup("[MovieCodeSendDate]", "[A1 Movie Code Table]", "[MovieCode] =" & Forms![A1 Onboarding Tracking Form]!MovieCodeSendDate)
 
Debug.Print ("Populate moviecodesenddate from form control to movie code table")
 
PROC2_EXIT:
Exit Sub
 
PROC2_ERR:
MsgBox "Error populating moviecodesenddate from from control to movie code table." & _
vbCrLf & "Check in table to see if code you picked is already used." & vbCrLf & Err.Number & " " & _
Err.Description, vbExclamation + vbOKOnly, "Populate Form Send Date In Movie Code Table"
 
Resume PROC2_EXIT
 
End Sub

I can't for the life of me figure out what is going on. No results, no run-time errors. To my understanding the bookmark procedure only marks the cells to share values, and the DLookup procedure actually populates the receiving cell. Any ideas about what is going wrong here are greatly appreciated. Thank you.

Frank

[ADezii]

I did not read the Post in depth for now, but only glanced at the Code, so forgive me if I am wrong.

1. Make sure that the Recordset supports Bookmarks, namely:
   

   MsgBox IIf(rs.Bookmarkable, "Supports", "Does NOT Support") & " Bookmarks"

2. In Line# 20, you are assigning the Bookmark Property of Form [A1 Onboarding Tracking Form] to the results of the FindFirst() Method, without knowing whether or not a Match was returned.
3. Bookmark may not be transferable to the Form mentioned above.

[soule]

Hello, ADezii. Thank you so much for replying to my problem. This task has given me 3 weeks worth of grief. Here's where I am now:

1. I don't understand about Bookmarks being supported. Isn't that a DAO library consideration? Why would my code have to verify this every time it ran? Could you please explain?

2. Do you think this arrangement would work instead?

Dim rs as DAO.Recordset 
 
Set rs = Me.RecordsetClone 
 
rs.FindFirst "[A1 Movie Code Table].[MovieCode]= '" & Me.[A1 Tracking Form Movie Code List Box] & "'" 
 
If rs.NoMatch Then 
   Msgbox "No match found.", vbInformation + vbOKOnly 
Else 
  Me.Bookmark = rs.Bookmark 
End If
 
Forms![A1 Onboarding Tracking Form].Bookmark = rs.Bookmark
 
Debug.Print ("Find matching movie code between form control and movie code table")

3. I have my fingers crossed. More research! :S

[ADezii]

Change the Syntax, and let's see if the 2 Bookmarks are exactly the same:

Dim rs As DAO.Recordset
 
Set rs = Me.RecordsetClone
 
rs.FindFirst "[MovieCode]= '" & Me.[A1 Tracking Form Movie Code List Box] & "'"
 
If rs.NoMatch Then
   MsgBox "No match found.", vbInformation + vbOKOnly
Else
  Me.Bookmark = rs.Bookmark
    MsgBox IIf(StrComp(Me.Bookmark, rs.Bookmark, vbBinaryCompare) = 0, "Exact Match", "Bookmarks Different")
End If

[soule]

Hi, ADezii,

Thanks for taking more time to look at my problem. I inserted the IIf statement in my If..EndIf statement and it worked great. I got an "exact match" message box. Here is my current version of my code. I moved the code from the form class mod to a standard mod in order to be able to compile it. I moved down my "Dim dt As Date" statement to the second procedure so it was closer to the code that uses it. I also used my "dt" term twice in the second procedure. I'm still not getting any compile nor run-time errors, but no results either. Should I move my "Dim rs" after my DoCmd save record command? Anything you can see that I'm missing?

Option Compare Database
Option Explicit
 
Private Sub A1_Tracking_Form_Movie_Code_Send_Date_Control_LostFocus()
 
' When the "Movie Code Send Date" control text box is tabbed off of, this code will find the same code in the Movie Code Table that
' is populated in the listbox of the current record...
 
' First, the fields that have already been data-entered are saved for good measure.
 
Dim rs As DAO.Recordset
 
  DoCmd.RunCommand acCmdSaveRecord
 
On Error GoTo PROC1_ERR
 
  Set rs = Forms![A1 Onboarding Tracking Form].RecordsetClone
 
  rs.FindFirst "[A1 Movie Code Table].[RawMovieCode]= '" & Forms![A1 Onboarding Tracking Form].[A1S1 Tracking Form Movie Code List Box] & "'"
 
  Forms![A1 Onboarding Tracking Form].Bookmark = rs.Bookmark
 
  If rs.NoMatch Then
    MsgBox "No match found.", vbInformation + vbOKOnly
  Else
    Forms![A1 Onboarding Tracking Form].Bookmark = rs.Bookmark
  End If
 
  Debug.Print ("Find matching movie code between form control and movie code table")
 
  rs.Close
 
' If found, code will next populate the movie code send date cell right next to
' the correct code. This will ensure only new codes are available in the movie code listbox during
' data entry and prevent the sending of one code to more than one employee.
 
On Error GoTo PROC2_ERR
 
  Dim dt As Date
 
  dt = DLookup("[MovieCodeSendDateClone]", "[A1 Movie Code Table]")
  dt = Forms![A1 Onboarding Tracking Form]!MovieCodeSendDate
 
  Debug.Print ("Populate moviecodesenddate from form control to movie code table")
 
PROC_EXIT:
  Exit Sub
 
PROC1_ERR:
  MsgBox "Error finding matching movie code between form control and movie code table." & _
    vbCrLf & "Check in table to see if code you picked is already used." & vbCrLf & Err.Number & " " & _
    Err.Description, vbExclamation + vbOKOnly, "Find Matching Control Code In Movie Code Table"
  Resume PROC_EXIT
 
PROC2_ERR:
  MsgBox "Error populating moviecodesenddate from from control to movie code table." & _
    vbCrLf & "Check in table to see if code you picked is already used." & vbCrLf & Err.Number & " " & _
    Err.Description, vbExclamation + vbOKOnly, "Populate Form Send Date In Movie Code Table"
  Resume PROC_EXIT
 
End Sub

Thank you for your previous help. Anything about this version will be appreciated too.

Frank

[ADezii]

I still think that the problem lies in the fact that you cannot simply assign a Bookmark in the manner which you have described. Try modifying the Form's Recordset or Record Source properties based on the Bookmarked Record Values.

[Stewart Ross]

You are setting a local VBA variable called dt (line 41) and overwriting it immediately (line 42). You are not populating any field at all with its value. Variable dt has no existence outside of the sub concerned - its value cannot persist unless you assign it to a bound control on a form, or directly via SQL to a field in a table.

In the original post you made you were not assigning the value of dt to anything at all either (line 51 in post #1), so this is an error in your approach all along.

The second assignment at line 42 in post # 6 above should surely be reversed if you want to set the value of the moviecodesenddate field:

Forms![A1 Onboarding Tracking Form]!MovieCodeSendDate = dt

I'm not convinced that you understand the logic of what you are trying to do. In the first post you appeared to believe your Dlookup would populate MovieCodeSendDate - but it was just rather circularly looking up the value from a where clause that itself referred to MovieCodeSendDate from the same table. In your last post your Dlookup is loooking up a field called MovieCodeSendDateClone, whatever that is, which may or may not be what you want to achieve - I can't say it is clear that the lookup value is the one you want at all, or that this has any relation at all to the bookmarking of the cloned recordset.

Perhaps if you told us what the lookup and bookmarking was trying to achieve we could assist with clearing up the logic concerned.

-Stewart

[soule]

Hello, Stewart,

Thank you very much for taking a look at my LostFocus code. I really appreciate it.

Though I know it isn't good Db design, I need to store a value in two tables. I need to so I can update the "used or unused" status of alphanumeric movie codes that will be added to that table and that feed a queried listbox on my form. I have a master table and a detail table. When a user enters a "movie code send date" on the form, it populates the field on the master table it's bound to, but I also need it to do the same on the correct movie code line on LostFocus in the detail table. I'm intending the bookmarking to recognize matching movie codes on the form and in the detail table by value, and then populate the corresponding "moviecodesenddate" field of that match with the send date entered on the form when that date control loses focus. Because people don't store data in two places (a wise practice), no one seems to know how to do this.

I'll take another look at my dimensioning like you recommended and make sure the logic is sound...thanks.

[Stewart Ross]

I should also mention, as ADezii has already done, that bookmarking is not necessarily the right approach. You can only apply bookmarks to and from recordsets which are identical - as is the case when recordsetclone is used to clone the current form's recordset, a find is performed, a match found, and the form set to the clone bookmark to move it to the right position.

Bookmarks will not assign correctly or give meaningful results from one recordset to another if the underlying recordsets are not identical.

In any case, from what you have shown us so far I think if you clarify the logic you are applying it will be possible to do what you require either by joining suitable tables together for a special-purpose query, or by using the existing DLookup or similar domain functions WITHOUT trying to sync a form first.

It all depends on knowing the steps you wish to take.

Relational databases are designed to avoid data redundancy in most situations; keeping copies of specific field values in separate places is not normal practice, unless there are very good reasons to do so.

-Stewart

[soule]

Thanks for replying again, Stewart.

I'm in a professional situation where I don't have the time or resources to learn the finer points of VB and it's making this project extremely difficult...I'm having to learn VB while I'm creating a Db with e-mail automation buttons and a table structure that demands two fields of redundant data. Thanks for the advice about bookmarking and your time.

To my understanding, my "DoCmd.RunCommand acCmdSaveRecord" command will ensure the form recordset and its clone are identical by updating any data-entered controls into the recordset before cloning. Am I missing something?

I tried a DLookup function method before adding bookmarking, but couldn't get it to work. It wouldn't work because I need to attach a date in a date field of a specific record in my detail table, and just couldn't make it happen after 2+ weeks of research and trying. I don't know if anyone knows how to make a seemingly simple event code happen. I'm so fried I don't even remember the error.

The degree to which I'd appreciate any more advice (preferably in the form of code) you'll never know.

Frank