cannot open required record in new form

[kobiashi]

hi

im trying to open up a record in a different form,
so in one form i have a sub form with records displayed, and they are hyper linked, so when you click on one it opens it up in a form

these fields are not ids, but are dates[DateCreated], the query is filtered so the dates are "grouped by"

the form im trying to open the record to a form which has another query with all the required data in there, including the [DateCreated]

here is the vba im using to open the record in the form

Private Sub txtDateToBeViewed_Click()
    Dim DateToBeviewed As Date
    
    DateToBeviewed = Me.txtDateToBeViewed
    
    DoCmd.OpenForm "Form_ENG_MCU_Archives_InServiceHistory_View", , , WhereCondition:="[DateCreated]= #" & Format(DateToBeviewed, "yyyy/mm/dd") & "#"
End Sub

but when i click on a hyperlink, the form opens, but the [DateCreated] field is filled with todays date, not the date selected.

any ideas on what i could be doing wrong?

 

[Gasman]

No record found, so it has a blank record and the default for the date is Date() ?

 

[kobiashi]

No record found, so it has a blank record and the default for the date is Date() ?

yeh thats right, default value for that field is set in the table

 

[Gasman]

Do a DLOOKUP on any field with that criteria in the immediate window and see what is produced.

I am not sure you need to format the date control, just surround it with #

 

[kobiashi]

do i need to do that from where the code is being executed or the form it is trying to open?

 

[Gasman]

I would put a breakpoint on the DoCmd line and then enter the DLOOKUP in the immediate window to identify what is returned.

Try without the format option first though.