Date Control Won't Populate Existing Record

[Soule]

Hi, All,
I'm seeing the light at the end of the tunnel with my movie code .accdb, and I'm hoping this is the last procedure of new code I will need to write.

1) I have a detail table with two fields, "moviecode" and "moviecodesenddate". I use the date field with null values in a query to populate a listbox on my form.
2) My form has a "movie code send date" text box control that is sourced to that date field on the table.
3) When I enter a test record in my form, I enter a date in the "movie code send date" control, but it doesn't populate the date field on the table that corresponds to the movie code I picked in the listbox - or any other field for that matter. I tried saving the record and checking the table, but still no go. You would think it would just save the date in the same record as the movie code that was picked in the listbox, but no.
4) I suspect this is happening because even though the date control is sourced to the date field, simple and clean, I doesn't know which movie code in the table to populate next to BECAUSE of the listbox. It's like the listbox is subverting the date control's control source. (FYI, my records disappeared in form view until I made sure the test data from my only repeating field in both of my tables was the exact same data).

This is where I could use some advice -

5) I think I need to use an AfterUpdate, OnChange, or OnLostFocus procedure on a DLookup or Like "*" procedure to get the user-entered date in the same table record as the movie code picked from the listbox. I'm leaning toward DLookup code (that looks up the chosen movie code in the listbox on the table and populates the date control value in the date field). What do you guys think would be the most straightforward method here?

This post exists on utteraccess and accessforums, also.

Thanks, again, for any bit of thought or advice.

Frank

 

[boblarson]

You need to move to the record that is selected on the list box before populating the data. It won't move itself unless you code for it in the After Update event of the listbox. Something like this:

Private Sub ListBoxNameGoesHere_AfterUpdate()
Dim rs as DAO.Recordset
 
Set rs = Me.RecordsetClone
 
rs.FindFirst "[MovieCode]=" & Me.ListBoxNameGoesHere
 
If rs.NoMatch Then
   Msgbox "No match found", vbInformation
Else
  Me.Bookmark = rs.Bookmark
End If
 
End Sub

 

[Soule]

Thank you so much for replying, Bob! And with code too!

What I don't understand is this...doesn't this DLookup code accomplish the same thing?

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

I've tested the above and can't get past a seemingly impenetrable syntax error, but if/when I do, isn't that the most straightforward way of populating an associated field of a listbox value? Unless you can't use a DLookUp statement for the actual value you want to populate...

Thank you for your time!

Frank