Form filter on two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I need to filter based on two fields on different tables.

I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.

I currently have the following code:

DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"


When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.

The form is bound to the tblVehicleInfo

Thanks in advance
 
Paul,

To begin with, your concatenation and the quotes around it are not correct.
Also, I think you are not referencing the recordsource fields properly. Try
this:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = " & "'T'
AND" & _
" tblVehicles.[CurrentDisposition] = " & "'Available'"

Also, why break down the string just to assign hard-coded values? Why not
just:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = 'T' AND"
& _
" tblVehicles.[CurrentDisposition] = 'Available'"

HTH,
Nikos
 
Sorry, tried both, doesn't work. I still get a pop up asking for one or the
other value and it does not filter.

Any other ideas?

Cheers


Nikos Yannacopoulos said:
Paul,

To begin with, your concatenation and the quotes around it are not correct.
Also, I think you are not referencing the recordsource fields properly. Try
this:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = " & "'T'
AND" & _
" tblVehicles.[CurrentDisposition] = " & "'Available'"

Also, why break down the string just to assign hard-coded values? Why not
just:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = 'T' AND"
& _
" tblVehicles.[CurrentDisposition] = 'Available'"

HTH,
Nikos

Paul B. said:
I have a form that I need to filter based on two fields on different tables.

I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.

I currently have the following code:

DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"


When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.

The form is bound to the tblVehicleInfo

Thanks in advance
Click to expand...
Click to expand...
 
Paul,

Check the query being the form's recordsource. Any chance you have criteria
asking for parameters in there, or just parameters declared under Query >
Parameters? If yes you should remove them.

HTH,
Nikos

Paul B. said:
Sorry, tried both, doesn't work. I still get a pop up asking for one or the
other value and it does not filter.

Any other ideas?

Cheers


Nikos Yannacopoulos said:
Paul,

To begin with, your concatenation and the quotes around it are not correct.
Also, I think you are not referencing the recordsource fields properly. Try
this:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = " & "'T'
AND" & _
" tblVehicles.[CurrentDisposition] = " & "'Available'"

Also, why break down the string just to assign hard-coded values? Why not
just:

DoCmd.OpenForm stDocName, , , "tblVehicleInfo.[VehicleAssignment] = 'T' AND"
& _
" tblVehicles.[CurrentDisposition] = 'Available'"

HTH,
Nikos

Paul B. said:
I have a form that I need to filter based on two fields on different tables.

I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.

I currently have the following code:

DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"


When I click the button, a popup window ask for the
Click to expand...
CurrentDisposition,
even
if I type one in, it does not filter.

The form is bound to the tblVehicleInfo

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
You can only filter a form on columns that are in it's recordsource otherwise
Access does not know what you are referring to and asks for the value, so to
make this work you would need to add tblVehicles to the recordsource.

Having said that there is a filter that you could use without changing the
recordsource but you might find the performance unacceptable :-

DoCmd.OpenForm stDocName, , , "VehicleAssignment = 'T' AND _
EXISTS (SELECT 1 FROM tblVehicles _
WHERE tblVehicles.Key = tblVehicleInfo.Key _
AND CurrentDisposition = 'Available'"

Substitute your key column names where I have put 'Key'.
 
John,

Thank you. your suggestion works great. A little slow as you said, but that
is okay. Just missed the closing ) at the end of the statement.

Cheers

John Smith said:
You can only filter a form on columns that are in it's recordsource otherwise
Access does not know what you are referring to and asks for the value, so to
make this work you would need to add tblVehicles to the recordsource.

Having said that there is a filter that you could use without changing the
recordsource but you might find the performance unacceptable :-

DoCmd.OpenForm stDocName, , , "VehicleAssignment = 'T' AND _
EXISTS (SELECT 1 FROM tblVehicles _
WHERE tblVehicles.Key = tblVehicleInfo.Key _
AND CurrentDisposition = 'Available'"

Substitute your key column names where I have put 'Key'.

--
HTH
John

Paul B. said:
I have a form that I need to filter based on two fields on different tables.
I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.
I currently have the following code:
DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"
When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.
The form is bound to the tblVehicleInfo
Click to expand...
Click to expand...
 
Sorry about the bracket, that's the trouble with air-code; it does not spot the
typos!

Glad it helped anyway
John

Paul B. said:
Thank you. your suggestion works great. A little slow as you said, but that
is okay. Just missed the closing ) at the end of the statement.
You can only filter a form on columns that are in it's recordsource otherwise
Access does not know what you are referring to and asks for the value, so to
make this work you would need to add tblVehicles to the recordsource.
Having said that there is a filter that you could use without changing the
recordsource but you might find the performance unacceptable :-
DoCmd.OpenForm stDocName, , , "VehicleAssignment = 'T' AND _
EXISTS (SELECT 1 FROM tblVehicles _
WHERE tblVehicles.Key = tblVehicleInfo.Key _
AND CurrentDisposition = 'Available'"
Substitute your key column names where I have put 'Key'.

Paul B. said:
I have a form that I need to filter based on two fields on different tables.
I need to filter [VehicleAssignment] from the tblVehicleInfo and
[CurrentDisposition] from the tblVehicles.
I currently have the following code:
DoCmd.OpenForm stDocName, , , "[VehicleAssignment] = " & "'T' AND _
[CurrentDisposition] = " & "'Available'"
When I click the button, a popup window ask for the CurrentDisposition, even
if I type one in, it does not filter.
The form is bound to the tblVehicleInfo
Click to expand...
Click to expand...
Click to expand...
 
Back
Top