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 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.

* This topic also is posted here
http://bytes.com/topic/access/answe...okmark-dlookup-module-not-working#post3701442
and
http://www.utteraccess.com/forum/Run-time-Errorless-Bookma-t1981711.html
and
http://www.accessforums.net/showthread.php?p=102596#post102596

Frank

 

[boblarson]

Did you manually type this line in:

Private Sub A1_Tracking_Form_Movie_Code_Send_Date_Control_On_Lost_Focus()

If so, that is your problem. That is a non existent event.

You need to copy your code from that non-existent event and then delete the event. And then go select the control while in design view, and go to the EVENTS tab on the properties dialog and then find the ON LOST FOCUS event and then select [EVENT PROCEDURE] from the drop down that appears when clicking in it. Then click the Ellipsis (...) that appears to the far right of that when you have selected Event Procedure. It will then take you to the VBA window and you will be in the correct event.

 

[Soule]

Thank you for your reply, Bob.

I made sure my code was in the correct object box and under the correct procedure/event box in the class module behind my form. I'm still getting the same non-event when I tab out of my "moviecodesenddate" control.

It's not that I can't use a bookmark procedure and a DLookUp procedure together, right?

I can't figure out what the problem is...

 

[boblarson]

If your event header is this:

Private Sub A1_Tracking_Form_Movie_Code_Send_Date_Control_On_Lost_Focus()

Then it is NOT in the right place. There is no event named ON LOST FOCUS when it is in the VBA Window. It would be

YourControlNameHere_LostFocus()

 

[Soule]

I see what you mean, Bob. I had it wrong in my post, but correct now. I'm still getting the same non-error/result when I tab off of my date control and the event procedure is supposed to fire.

Is there anything else glaring in that code that would obviously hang it up that I'm just not seeing?

Thank you for your time (again),

Frank

 

[ChrisO]

Is the procedure being called? Insert a Msgbox to test.

Reduce the code to test the structure of the procedure.

As it stands, the procedure will only perform the Bookmark and not the DLookup.

Private Sub A1_Tracking_Form_Movie_Code_Send_Date_Control_On_Lost_Focus()
 
    On Error GoTo PROC1_ERR
    
    [color=green]' Execute some code.[/color]

PROC1_EXIT:
    Exit Sub
 
PROC1_ERR:
    MsgBox "Some error at PROC1_ERR."
    Resume PROC1_EXIT
 
 
 
 
 
    [color=green]' We can not execute this code because we can't get here.[/color]
 
    On Error GoTo PROC2_ERR
 
 
PROC2_EXIT:
    Exit Sub
 
PROC2_ERR:
    MsgBox "Some error at PROC2_ERR."
    Resume PROC2_EXIT
 
End Sub

Chris.

 

[vbaInet]

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.

Sounds like a design flaw to me. If it's a one-to-one relationship what's the need of duplicating the same information across both tables?

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] & "'"
[COLOR=Red]Forms![A1 Onboarding Tracking Form].Bookmark = rs.Bookmark[/COLOR]

What are you doing here?

 rs.FindFirst "[A1 Movie Code Table].[MovieCode]= '" & Me.[A1 Tracking Form Movie Code List Box] & "'"

dt = DLookup("[MovieCodeSendDate]", "[A1 Movie Code Table]",  "[MovieCode] =" & Forms![A1 Onboarding Tracking  Form]!MovieCodeSendDate)

What's going on here? MovieCode is Text in the top part but in the second part it's Number. Like Chris I also supect that it's not reaching the second segment of your code.