Load Form with Max Date

[JBrogue]

Hey, I'm trying to open a specific set of records when the form is first opened (namely the most recent date that was added). I'm trying something along these lines and am simply unable to get it to work:

Private Sub Form_Load()

Dim strQry As String
strQry = DMax("TranDte", "Transaction")
DoCmd.OpenForm "FrmSalesInp", acNormal, "", "[Transaction]![TranDte]="
& strQry, , acNormal

End Sub

I feel as though I'm missing something minor. Any suggestions?

 

[SOS]

If you want the most recent date that was added, just use a query for the form's record source like:

SELECT * From Transaction ORDER BY TranDte DESC

 

[JBrogue]

I'm using this form to go forward/backward through dates and allow for searches. So the form needs to be multi-purpose. I have all of the functionality working except for the On-Load (where I want to start with the most recent date).

If I base the form off of this specific query I would probably have to go back to the drawing board on a lot of these functions. I was just wondering if anyone knew what I may be able to change the the VBA to get this working. Otherwise, I can just create a date/time stamp in one of my tables and compare with that.

Also, I am not pulling just one record. I am pulling any/all records with the most recent date.
Here is the SQL the form is based on:

SELECT DISTINCTROW Account.ChrgType, Transaction.TranCd, Transaction.TranAmt, Transaction.TranDte
FROM Account INNER JOIN [Transaction] ON Account.TranCd = Transaction.TranCd;

On the form load I'd like to add a WHERE Transaction.TranDte = Max(TranDte) or something to that effect (I just dont know the syntax)

 

[JBrogue]

Alright I've added an ORDER BY to my Query and selected the TOP 1 in my form. Oddly, my top date in the query is 7/19/2013, but the form is starting on 7/1/2013. Progress. Thanks for pointing me in the right direction.

Private Sub Form_Load()

DoCmd.OpenForm "FrmSalesInp", acNormal, "", "[Transaction]![TranDte]=(SELECT TOP 1 TranDte FROM Transaction)", , acNormal

End Sub

SELECT DISTINCTROW Account.ChrgType, Transaction.TranCd, Transaction.TranAmt, Transaction.TranDte
FROM Account INNER JOIN [Transaction] ON Account.TranCd = Transaction.TranCd
ORDER BY TranDte DESC;

I need [Transaction]![TranDte]=TOP 1 of my new query.

 

[SOS]

Sounds like your date is text and not numeric.

Also, you really only want one record in your form?

 

[JBrogue]

TranDte type is Date&Time in the table. I want the form to load only one day worth of sales. There is a record for each product type per day. So the form might have 9 or 10 records displayed (one for each product that day) and the user can move through days to view the sales.