Query records on one table from a combobox on a form that uses data from another tabl

[Kranky]

Hi Access Forums

Sorry to bother you with this seemingly easy question. I have searched far and wide for the answer, including here but have had no joy.

Goal: I have a form with 2 date boxes and 2 combo boxes (comboStaff and comboDept). All I want to do is query the main table (tblMain) using the dates and comboboxes as criteria but want the combo boxes to use a separate table to provide their values (tblStaff).

I have the dates working fine so thats not an issue. The mechanism for comboDept will be the same as comboStaff so i'll just work with comboStaff here.

The comboStaff will not work using the tblStaff.Staff as the source of the data. It will work if it is set to read from tblMain.StaffMember and filter the records fine.



So basically I want to choose the dates, choose either a staff member (or none) OR a dept (or none) so that everything between the dates that meets those 0 or 1 or 2 criteria is returned.

The current code the the query is Like [Forms]![Date_Filter]![comboStaff] & "*" on tblMain.StaffMember.

Hope that makes sence??

Cheers
Kranky

[June7]

Don't understand all of that. You say you want to use tblStaff as RowSource for combobox then say that won't work?

Want to provide db for analysis? Follow instructions at bottom of my post.

[Kranky]

Don't understand all of that. You say you want to use tblStaff as RowSource for combobox then say that won't work?

Yes I want to use tblStaff to provide the values for the combo, then once selected find all the records with the same value in tblMain via a query. null will be all records.

Tried to upload a copy but it exceded the 500kb limit sorry.

Thanks

Kranky

[Kranky]

Zipped and attached

Copy.zip

[qa12dx]

use an unbound combobox. in row source use the query u want. in the lookup query reference this combobox name.

hope it gets the joy back

[Kranky]

use an unbound combobox. in row source use the query u want. in the lookup query reference this combobox name.

Did all those ( I think - done so much fiddling with it my head is spinning)

hope it gets the joy back

Not much joy had here today, I miss excel

thanks for your comments - I'll go and double check everything.

[qa12dx]

post a db here. access 2007 32bit version. i'll get ur joy back. i use this type of query all the time

[Kranky]

post a db here. access 2007 32bit version. i'll get ur joy back. i use this type of query all the time

the version above no good for you? Our version 2007-2010 so should be good.

[Kranky]

the version above no good for you? Our version 2007-2010 so should be good.

No dont do that - I changed the names of the tables to protect the innocent. So the form doesnt function anyway. Its been a long day and I'm going home for a beer (or many) in half hour. I'll worry about this disfunctional software tomorrow!

thanks for your replies

[Kranky]

use an unbound combobox. in row source use the query u want. in the lookup query reference this combobox name.

went back and did all this again and it did it
Dont know what changed but its doing what its supposed to do.


That is some serious joy back right there

[qa12dx]

i saw the db u uploaded. the way u done it, it's a all inclusive query.
so if you want date and staff, say larry, it will still get u results even if larry isn't in the record set. same with dept. if u want date and see particular dept, no matter what u choose it will show u results in the date range.
see how i do it. my qry and form has qa12x in its name.

Copy_qa12dx.zip

[Kranky]

so if you want date and staff, say larry, it will still get u results even if larry isn't in the record set. same with dept. if u want date and see particular dept, no matter what u choose it will show u results in the date range.

In the end I had it producing results the same as yours does.... however

see how i do it. my qry and form has qa12x in its name.

yours is a much better solution and I will alter my original file to work the same way . As they say - "more than one way to skin a cat".

Thankyou for your time qa12dx

regards
Kranky

[qa12dx]