Combo Box, List Box & a Sub Form

[Chad Cameron]

Hi All,

I have a combobox that lets me select a company. Once the company is
selected, my listbox populates with the contractors from that company. I
got that part done.

Now I want to select a contractor (Last & First Name displayed) from the
listbox and have my subform populate with all the procedures that that
contractor is signed off on & add new ones if required.

I am still fairly new at access.

Thanks
Chad

 

[Jeff Boyce]

Chad

"How" depends on "what", as in what data are you working with -- it all
starts with the data.

For example, if your listbox is 'filled' by a query that gets ContractorID,
ContractorName, then you might be able to set the parent and child
properties of that subform control to join on the ContractorID (?the first
field in your listbox, shown or not).

You'd need to have a form that shows all procedures, and includes
ContractorID, so that you can use that as a subform and join on the
ContractorID.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chad Cameron]

Thanks Jeff & Steve,
I seem to be backtracking now. and nothing works
Steves way seemed to make more sense since I was filtering on 2 different
levels.

So, I created a new table for the company. I have the combobox
cbCompanyName looking at Company.CompanyName

But now my filter doesn't work. I have looked at so many webpages with
different ways to do it, I have totally confused myself. So my next step is
to fill the listbox lbContractorName and then filter it by the
cbCompanyName.

It sounds so easy, just cannot make it happen. Please help
Thanks
Chad

 

[Chad Cameron]

Thanks Steve,

Maybe I am going at this the wrong way. How should my form look (in your
opinion).

I was thinking: At the top of the main form I would have a combobox which
will be populated with my CompanyName field from the Company Table.

Then should I have a subform with a listbox listing all the names of the
contractors or should the listbox still be on the main form?

Then the list of all the procedures that that contractor has. Should that
be on a subform or the main form?

I don't have enough theory of databases to make the wise choice. Basically
All I have been doing is using the wizard and moving things around to make
something that works.

Also Table Structures:
Company has CompanyID & CompanyName
Contractor has ContractorID & CompanyID & FirstName & LastName
Procedure has ProcedureID & ProcedureNum & ProcedureName

Main has ContractorID & ProcedureID & Date (Date is when the contractor
was signed off on the procedure)

Any thoughts, concerns, ideas would be great.
Thanks Again
Chad

PS: The main theory behind this database is too know which contractors have
been signed off on certain procedures. I am not worried about the
procedures, I can manually add new ones into the table when needed. On my
main form I would like the ability to add procedures to a contractor 'on the
fly'. If I have to add a company or a contractor, that can be done on a
separate form unless adding that feature to the main form is possible.

HTH
Chad

 

[Chad Cameron]

Well Steve hope you are still looking at this post.

I have the form. I have the combobox to select the company and when you do,
it does filter my data to show all contractors and the procedures they have.

I also have a listbox which also is filtered to only show the contractors
(First & Last name in separate columns) that work for the company in the
combobox.

Now, I need to "double" filter my data. I already have it filtered by
company, now I need to extend the filter to filter my the company in the
combobox and the name selected in the listbox.

I am stumped on trying to get 2 filters talking to each other.
Here is my snipit of code. How can I get 2 separate items in the .Filter?

Me.Filter = "[CompanyID] = " & cbCompanyName
Me.FilterOn = True
lbContractor.Requery

Thanks for all your help,
Chad