Drop-down lists and subforms...

[Guest]

I have a number of drop-down lists (5) that contain search criteria on my
main form, and I have a subform that has fields that I want populated based
on the above drop-down lists. Any ideas on how to accomplish this?

 

[Guest]

Create a record source for the sub form based on the combo's

Select * From MyTable Where MyField1 Like
nz(Forms![MainFormName]![Combo1Name],"*") AND MyField2 Like
nz(Forms![MainFormName]![Combo2Name],"*") AND MyField3 Like
nz(Forms![MainFormName]![Combo3Name],"*")

If no value enter in the combo it will returns all values for that field,
using NZ

 

[Guest]

Here is my subform SQL statement:

SELECT tblJobs.*, tblRolodex.*
FROM tblRolodex INNER JOIN tblJobs ON tblRolodex.CompanyName =
tblJobs.ClientName
WHERE (((tblRolodex.CompanyName) Like nz([Forms]![frmSearch]![cboName],"*"))
AND ((tblRolodex.State) Like
nz([Forms]![frmSearch]![cboCompanyLocation],"*")) AND ((tblJobs.JobNumber)
Like nz([Forms]![frmSearch]![cboNumber],"*")) AND ((tblJobs.Code) Like
nz([Forms]![frmSearch]![cboCode],"*")) AND ((tblJobs.ProjectLocation) Like
nz([Forms]![frmSearch]![cboProjectLocation],"*")));

When I select a company name, for example, my subform does not populate even
when money is the following VBA statement in the AfterUpdate event of my
cboName drop-down list. Any ideas?

Create a record source for the sub form based on the combo's

Select * From MyTable Where MyField1 Like
nz(Forms![MainFormName]![Combo1Name],"*") AND MyField2 Like
nz(Forms![MainFormName]![Combo2Name],"*") AND MyField3 Like
nz(Forms![MainFormName]![Combo3Name],"*")

If no value enter in the combo it will returns all values for that field,
using NZ

I have a number of drop-down lists (5) that contain search criteria on my
main form, and I have a subform that has fields that I want populated based
on the above drop-down lists. Any ideas on how to accomplish this?

 

[Guest]

Now, the best way to check the sql is to run the form, select a company, open
a query and paste the sql in the query and run it, to see if the query return
any records.
After selecting a company, open the immidiate window (press ctrl+g) and type
?Forms![frmSearch]![cboName]
see if any value return

The other question is, is the company name is the first column in the combo
or the second one.
If it the second record in the source then you should write
Forms![frmSearch]![cboName].column(1)

Here is my subform SQL statement:

SELECT tblJobs.*, tblRolodex.*
FROM tblRolodex INNER JOIN tblJobs ON tblRolodex.CompanyName =
tblJobs.ClientName
WHERE (((tblRolodex.CompanyName) Like nz([Forms]![frmSearch]![cboName],"*"))
AND ((tblRolodex.State) Like
nz([Forms]![frmSearch]![cboCompanyLocation],"*")) AND ((tblJobs.JobNumber)
Like nz([Forms]![frmSearch]![cboNumber],"*")) AND ((tblJobs.Code) Like
nz([Forms]![frmSearch]![cboCode],"*")) AND ((tblJobs.ProjectLocation) Like
nz([Forms]![frmSearch]![cboProjectLocation],"*")));

When I select a company name, for example, my subform does not populate even
when money is the following VBA statement in the AfterUpdate event of my
cboName drop-down list. Any ideas?

Create a record source for the sub form based on the combo's

Select * From MyTable Where MyField1 Like
nz(Forms![MainFormName]![Combo1Name],"*") AND MyField2 Like
nz(Forms![MainFormName]![Combo2Name],"*") AND MyField3 Like
nz(Forms![MainFormName]![Combo3Name],"*")

If no value enter in the combo it will returns all values for that field,
using NZ

I have a number of drop-down lists (5) that contain search criteria on my
main form, and I have a subform that has fields that I want populated based
on the above drop-down lists. Any ideas on how to accomplish this?