Results 1 to 4 of 4
  1. #1
    Soule is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2011
    Posts
    58

    Run-time Errorless Bookmark/DLookUp Module Not Working

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you step debugged? Follow the code as it executes, see where it deviates from expected behavior, fix, repeat. Refer to link at bottom of my post for a tutorial on debug techniques.

    Is MovieCode a number datatype?

    The form is based on a query that includes two tables that have a 1-to-1 relationship but no join clause? Why both tables? Is this form used for data entry? To what table? Why does date need to be in both tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Soule is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2011
    Posts
    58
    Have you step debugged?
    Yeah, the line it's hanging up on is:
    Code:
     
    rs.FindFirst "[A1S1 Movie Code Table].[MovieCode]= '" & Me.[A1S1 Tracking Form Movie Code List Box] & "'"
    Is MovieCode a number datatype?
    No, I set the datatype of them to "text" because they are alphanumeric on both tables.

    The form is based on a query that includes two tables that have a 1-to-1 relationship but no join clause? Why both tables? Is this form used for data entry? To what table? Why does date need to be in both tables?
    Yes, the form is based on a query (w/JOIN clause) that includes 2 tables and have a 1-to-1 relationship. I put the join back between them in the "relationships" layout, and so far, it has not caused the problem it had before.

    Yes, the form will be used for data-entry.

    I need to put the "MovieCode" and "MovieCodeSendDate" on both tables because they need to be associated with employees who are stored on my master table, plus, they need to feed my form's movie code listbox (and added to when they're all used) from my detail table. My listbox's query knows which ones are used or not based on if they have a date associated with them on that detail table. Plus, with the movie code and send date on the detail table, I can create a simple report that shows how many are used and unused.

    Thank you, again, June, for taking the time to look at my crazy project. I promise this is the last procedure of new code I will be writing.

    Frank

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Promises!Promises!

    Still fuzzy to me what is going on.

    The form RecordSource is two joined tables with a 1 to 1 relationship.

    You need to update a date field in which table(s)?

    Just caught on re-read of first post your reference to a 'pivot value' in a listbox. What is that?

    Do you want to provide project for analysis? Can attach to post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Working days module help for noob
    By AndycompanyZ in forum Modules
    Replies: 5
    Last Post: 07-27-2011, 01:43 AM
  2. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  3. order by dlookup not working
    By broof in forum Queries
    Replies: 3
    Last Post: 01-11-2011, 03:15 PM
  4. DLookup code not working
    By lukekelly in forum Programming
    Replies: 9
    Last Post: 06-15-2010, 06:08 AM
  5. Replies: 10
    Last Post: 05-19-2010, 10:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums