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 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.
Code:
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.
* This topic is also posted here...
http://bytes.com/topic/access/answer...ng#post3701442
and
http://www.utteraccess.com/forum/Run...-t1981711.html
Frank