Query criteria gives values but does not in sub form mode

[Shiv Garuda]

I wrote a query criteria as below

(SELECT Max([MyDate]) FROM [MyTable])
to see a record with reference to a date, Latest in a form which serves as a sub form.
On testing, the query gives result only when the form opens out side the master. What modification I need to do in the
above criteria so as to get the desired result when the form opens in sub form mode too.
Thank you.

[HansV]

Welcome to Eileen's Lounge!

I assume that this is the same question as Query criteria works correctly in form. But if the form opens in sub form mode the criteria does not seem to work. in the Microsoft Answers forums.

Do you use this criteria in the Record Source of the subform?
If so, please provide the following information:
- What is the name of the main form?
- What is the value of the Link Master Fields property of the subform as a control on the main form?
- What is the value of the Link Child Fields property?

[Shiv Garuda]

It was surprisingly but happily acknowledged your reference.
1. FRM VEH MAIN TO UPDATE EXP
2. vehno
3. vehno
I suppose these are you expected. Thank you.

[HansV]

Thank you. Try this:

(SELECT Max(MyDate) FROM MyTable WHERE vehno=Forms![FRM VEH MAIN TO UPDATE EXP]!vehno)

[Shiv Garuda]

(SELECT Max(dateofentry) FROM [KMS UPDATION 251213] WHERE vehno=Forms![FRM VEH MAIN TO UPDATE EXP]!vehno;)
At the instance of pasting the code in the source of the form, all the fields in the sub form get disconnected.
I would like to explain some thing more for your understanding and to help
The form in question based on a table named "KMS UPDATION 251213" has fields 'vehno' , 'dateofentry' , 'odorbefore' , 'odortoday'
This form should act as a sub form to the main form, which is based on the table named "VEHICLES" has fields 'vehno' , 'make' , 'model' , 'attachedto' etc.....
vehno is the link field.
The form based on the "KMS UPDATION 251213" gets entry every day for all the vehicles available (DB) in the main table.
So this as a sub form, my expectation is that the form may show only the latest record related to the vehicle displayed in master.
Thats all. Thank you and eagerly awaiting for answer.

[HansV]

The expression that I posted was intended to be used in the Criteria line of the query, not as the Record source of the subform. Try the following:

Activate the Create tab of the ribbon.
Click Query Design.
Access will display the Show Table dialog.
Select KMS UPDATION 251213 in the list of tables.
Click Add, then click Close.
Add each of the four fields from the table to the query grid below.
In the Criteria rows of the dateofentry column, enter the expression

(SELECT Max(dateofentry) FROM [KMS UPDATION 251213] WHERE vehno=Forms![FRM VEH MAIN TO UPDATE EXP]!vehno)

Save this query as (for example) qryKMSUpdation251213

Open the subform in design view.
Set its Record Source property to the name of the query that you just saved.

[Shiv Garuda]

Thank you, Sir, Now I got the desired result. One minor problem, please let me ask your guidance. For the same sub form I embedded a cmd button with vba code as below
Private Sub odorbf_Click()
Const cQuote = """"
RunCommand (acCmdRecordsGoToLast)
Me!odorbefore.DefaultValue = cQuote & Me!odortoday.Value & cQuote
Me!dateofentry.SetFocus
End Sub
so as to assign current value of the field "odortody" (Ex:52500)to the new value of the "odorbf". I got the value as desired on clicking the cmd. But when scrolling the main form I see the same value (ie 52500) in the "odorbf" for every record of the main form. But on clicking the cmd I got the respective value replaced. And on clicking the cmd the cursor set at the current record(ie last available record). Pressing enter key does not work, but TAB key does. Is this normal or lack of adequate codes. Would you clear my doubts. Thanks

[HansV]

I have to go now, I'll get back to you later.

[HansV]

It's not clear to me what you want to happen when the user moves to a different record in the main form.

[Shiv Garuda]

The main form shows Vehicle no only. And the sub form shows the KMs at the start and KMS at the end of a day. As the vehicle no of the main form changes (through scroll button), the sub form shows the related records. Almost every day data are entered in the sub form, based on the vehicle no displayed in the main form.
It is obvious that the KMs at the end of the previous day is the value for KMs at the start of the next day. So a cmd button was assigned on click event, as I said earlier.
Private Sub odorbf_Click()
Const cQuote = """"
RunCommand (acCmdRecordsGoToLast)
Me!odorbefore.DefaultValue = cQuote & Me!odortoday.Value & cQuote
Me!dateofentry.SetFocus
End Sub
Every thing is OK. But when scrolling the main form ((after having added records for a vehicle( here on clicking the cmd button the previous day km(52500) of the particular vehicle gets entered in the next day record perfectly))) I see the same value (ie 52500) displayed for every record of the main form until the cmd button got clicked. It would be nice to see a latest record alone in the sub form while scrolling to make entry for another vehicle. Thank you.

[HansV]

You could clear the default value when the users moves to a different record in the main form. You can use the On Current event of the main form for this:

Code: Select all

Private Sub Form_Current()
    Me![NameOfSubform]!odorbefore.DefaultValue = Null
End Sub

where NameOfSubform is the name of the subform as a control on the main form.

[Shiv Garuda]

Private Sub Form_Current()
Me![KMS RUN BETWEEN TWO DATES]!odorbefore.DefaultValue = Null
End Sub
Error message recd as below
Run time error 13
Type mismatch
What to do

[HansV]

Does this work?

Me![KMS RUN BETWEEN TWO DATES]!odorbefore.DefaultValue = ""

[Shiv Garuda]

yes, This does work. Fantastic. Thank you