Openform at specific record (again)

[Oscar_W]

Openform at specific record (again) - Example added

Sorry about this. I know there is loads on here about this but I just can't get it to work.

I have a basic table of dates.
One form based on the dates via a query

When the form opens, I want it to show today's date but allowing access to all the other date records (so no filter).

Any help please
Oscar

 

[RuralGuy]

If you are using ac2k2+ then use the OpenArgs argument to pass the date and pick it up in the OnLoad event of the next form and then do a find first to move the record pointer.

 

[Oscar_W]

RuralGuy, Thanks for your help. I have managed to transfer the current date to the Form_Open procedure below but it is still not finding the record.

Private Sub Command0_Click()

    DoCmd.OpenForm "Form2", acNormal, , , acReadOnly, , Date
  
End Sub

Private Sub Form_Open(Cancel As Integer)
 
    If Not IsNull(Me.OpenArgs) Then
        Dim testdate As Date
        testdate = Me.OpenArgs  [B]' Shows "09/05/2006"[/B]
        Dim RS As DAO.Recordset
        Set RS = Me.RecordsetClone
           RS.FindFirst "field1 =  #" & testdate & "# "
         
        testfield = Field1 [B]' for test only value of Table1 field1[/B]
      
        If Not RS.NoMatch Then
            Me.Bookmark = RS.Bookmark
        End If
    End If

End Sub

I have 3 dates in Field1 - 08/05/2006, 09/05/2006, 10/05/2006 & today is 09/05/2006.
When the code is run, the Me.OpenArgs variable shows today's date (enclosed in quotes) so I think that it is being passed over correctly but the testfield1 variable shows 08/05/2006 which is the first record of the table and RS.NoMatch = True which means the Bookmarks don't have values.
Even when I remove all records from the table except 09/05/2006 the RS.NoMatch is still = True.

Any help welcome.

 

[RuralGuy]

Try: RS.FindFirst "[field1] = #" & Me.OpenArgs & "#"

OpenArgs are *always* strings.

 

[Oscar_W]

Sorry still no joy. If the OpenArgs is a string and Field1 is a Date, do I need to convert them both to the same before comparing them ?

 

[rbarlow]

for the date thing i'd try using the CDate() method.

I have a problem thats kind of relavant to this thread. I want to have a main form open, then open a search form which gets a value of the primary key the main form is based on.

Can I use OpenArgs to do this? I want to keep the main form open so I don't know if it is possible.

 

[RuralGuy]

Oscar_W:
Enclosing the string value in the # sign coerces it to a date value. Is [Field1] a date *only* field or DateTime field that was set with Now(). You may need to strip any time component from it with the Int() function.

rbarlow:
Yes, you can use OpenArgs to pass the key value.

 

[Oscar_W]

Test DB Enclosed

Still no joy, I'm afraid. I have tried Int() & CDate(). The date was set using the Date() function.

I have removed all of the dates from the table except today's so the form now opens at todays date (obviously) but the If Not RS.NoMatch is still = True.

Test DB enclosed. I must be doing something else wrong.

 

[RuralGuy]

I added some diagnostic MsgBoxes to the code. Your code seems to work.

 

[Oscar_W]

You must be getting almost as annoyed at this as me.

I'm afraid it still doesn't work. I have added 2 more dates (yesterday and tomorrow) to the table. When run, I get your Msgbox "Unable to locate...." and the form opens at yesterday's date.

I note your location. Is this something to do with US / UK date formats ?

 

[RuralGuy]

Is this something to do with US / UK date formats ?

Entirely possible. Here's a couple of links that provide information on that topic.
International Dates in Access
Return Dates in US #mm/dd/yyyy# format

 

[Oscar_W]

Sorted - almost. I added a new date to the table in US format 05/10/2006 and it matched with 10/05/2006.
Just need to work out how to make that work for my purpose.

All, particularly RuralGuy, thanks for your assistance.

 

[RuralGuy]

Excellent! You're on the right track now. Thanks for posting back.

 

[workmad3]

when dealing with dates in your DB, I would suggest either always wrapping them in a format to force them to bristish (dd/mm/yyyy) format, or only using american format. Most date errors seem to appear when people forget that access is made by an american company and dont think to compensate for the different format Alternatively for maximum compatibility, use the ISO YYYY/MM/DD format

 

[lightray]

Being from down under I recently discovered these little gems:
Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
Global Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"

http://www.mvps.org/access/datetime/date0005.htm

lightray
Edit: Oops! didn't realise same link RuralGuy