update combo list

[tonyx666]

ok i have three tables..

Booking_Table
Booking_ID
Job_Date
Company_ID
Booker
Passenger

Company_Table
Company_ID
Company_Name
Address
Phone_No

Company_Staff_Table
Company_Staff_ID
Company_ID
Full_Name
Phone_No


each booking will only ever involve one company at a time

company 'A' will have several staff

person 'A' from company 'A' may book a job for themselves

person 'A' from company 'A' may also book a job for person 'B' from company 'A'


i have created a booking form

i have added a combo list displaying all the company names..(control="comp_combo")

i have also added a combo list displaying all staff names from all companies (control="staff_combo")


when the user selects company 'A' from the first combo.. i would like the staff combo to update and only display staff members for that company

how do i do this.





London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[xplo5iv]

You need to put cmbCombo2BoxName.Requery into the on change event of ComboBox 1 etc.

(Hope that's understandable)

Jonathan

 

[xplo5iv]

Sorry, just re-read your post. You need to base each cascaded combo on a query which uses the previous combo contents as well
(I'd assumed you'd done that, but then realised you hadn't said so.)

 

[tonyx666]

it is not understandable no.. i realised it would have to be an onchange event but im not sure how to code it with the right syntax

like i said.. the two combos are called

comp_combo

staff_combo (changes when comp_combo changes)

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[xplo5iv]

Assuming that staff_combo is based on a query which uses comp_combo (If not it should be...) then in the Change event of comp_combo you need to add the code

staff_combo.requery

in order to update staff_combo

Hope that's clearer!

 

[tonyx666]

oh.. ok hang on..

at the moment all i have done is create a booking form using the wizard..

i then deleted the company id field.. used the combo wizard.. added company names to the combo and chose store the value of this in the booking table - company_id

i did the same with the booker field.. i deleted it.. made a combo with all the staff names from the staff table.. and chose use this value in the booking table - booker..

and the same as above for the passenger..

is this the wrong way to do things?

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[tonyx666]

have a look at the table layout so far..

is this wrong?

http://www.londonheathrowcars.com/id.jpg

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[xplo5iv]

No, looks about right, but you may want to consider enforcing relationships.

Each combo box Row Source should be based on a table, or a query/ SQL expression, so that the combos remain dynamic.

Right click on the combo, and go to 'properties' to look up the row source. DONT use the 'control source', as any changes will amend the underlying tables. You'll need to set up the second combo with a SQL SELECT...FROM... WHERE clause, and then use code/ query to create the record when a confirm button is clicked.


(Actually getting this to work properly without screwing existing records is not easy for a newcomer to Access, but with a bit of work, and a decent book, you'll get there!)

 

[tonyx666]

ok, let me clarify things.. the ROW SOURCE for my COMPANY COMBO is this at the moment

SELECT company.companyid, company.companyname FROM company;

this displays the company names (and inputs the hidden id)

and the CONTROL SOURCE at the moment is 'companyid' which is the field that the value will be placed into.

again.. this combo was populated using the wizard and choosing the companyname field from the company table.

---------------------------------------------------

now let me explain the Booker combo box.

its pretty much the same thing.

used a combo wizard.. chose the staff name value from the staff table.. and chose to place the value into the booker field of the booking table..

so at the moment the booker combo has the CONTROL SOURCE of booker and the row source of this..

SELECT [staff].[staffid], [staff].[staffname] FROM [staff];

so.. to get my desired requery effect.. do i have to alter the booker combo Row source to something like..

SELECT [staff].[staffid], [staff].[staffname] FROM [staff] WHERE [staff].[companyid]=Me.comp_combo;

or can i just add staff_combo.requery to the change event of the comp_combo like you earlier suggested

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[xplo5iv]

Yes! Do both. Great
And delete both the Control Sources while you're about it, as you don't want these combos to alter those tables.

 

[tonyx666]

ok, i...

deleted both the control sources of the combos..

changed the staff combo row source to this

SELECT [staff].[staffid], [staff].[staffname] FROM [staff] WHERE [staff].[companyid]=Me.comp_combo;

and put staff_combo.requery in the change event of the company combo.

when i changed the value of the company combo. and error came up on the bold line below saying object required..

Option Compare Database

Private Sub comp_combo_Change()
[b]staff_combo.Requery[/b]
End Sub

can i just add.. i do actually want the values of these combos to be placed in the respective fields.. so when a company is chosen on the booking form.. i want that company to be placed into the companyid for that record.. and the same with the staff member.. so i think i might need the control source.. do i?





London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[tonyx666]

trying to make one combo requery another.. got object required error here:

Option Compare Database

Private Sub comp_combo_Change()
[b]staff_combo.Requery[/b]
End Sub

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[tonyx666]

its funny that every now and then, a post will never be replied to.. it has outlived its own tolerance

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[PHV]

Change the RowSource to:
SELECT [staff].[staffid], [staff].[staffname] FROM [staff] WHERE [staff].[companyid]=[!][Forms]![name of form here]![/!]comp_combo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xplo5iv]

Sorry, Tony, was away all day yesterday at a supplier meeting.
(I have a real job, too!)