query to filter by current record id?

  • Thread starter Thread starter Seddon Acaster
  • Start date Start date
S

Seddon Acaster

2 tables called customer and service

customer table has unique numerical customer id field, this is the primary
key. an example value for this field would be 3

service table has customer id field and serviceno field. the customer id
field is not unique but the serviceno field is

i want a customer form to have a combobox that looks up a query that will
only show the serviceno fields that relate to that particular customer

how would i do this?
 
Seddon -

Set the recordsource for the combobox to be a query with the criteria to set
it to the current customer ID. It will be something like this:

SELECT serviceno from [service table] where [Customer ID] = forms![<Customer
form name>]![Customer ID]
 
This is what i've entered in the Row Source for the Combo Box:

SELECT scan_id FROM service WHERE [ac_ref]=forms![customer_full]![ac_ref];

The data the Combo Box shows is the same for all records at the moment which
obviously is not what I want. Have I missed something out?

And er thanks by the way!

Seddon

Daryl S said:
Seddon -

Set the recordsource for the combobox to be a query with the criteria to set
it to the current customer ID. It will be something like this:

SELECT serviceno from [service table] where [Customer ID] = forms![<Customer
form name>]![Customer ID]

--
Daryl S


Seddon Acaster said:
2 tables called customer and service

customer table has unique numerical customer id field, this is the primary
key. an example value for this field would be 3

service table has customer id field and serviceno field. the customer id
field is not unique but the serviceno field is

i want a customer form to have a combobox that looks up a query that will
only show the serviceno fields that relate to that particular customer

how would i do this?
Click to expand...
Click to expand...
 
Seddon -

You need to have the combo box requery after a customer record is selected.
I don't know how your form is set up or how a user navigates to the correct
customer, but the event that loads the record should have a requery for the
combo box, something like this:

Me.cboname.Requery

You may want this in the OnCurrent event in the form. If this doesn't work,
tell us what is in the combo box - is it all records?
--
Daryl S


Seddon Acaster said:
This is what i've entered in the Row Source for the Combo Box:

SELECT scan_id FROM service WHERE [ac_ref]=forms![customer_full]![ac_ref];

The data the Combo Box shows is the same for all records at the moment which
obviously is not what I want. Have I missed something out?

And er thanks by the way!

Seddon

Daryl S said:
Seddon -

Set the recordsource for the combobox to be a query with the criteria to set
it to the current customer ID. It will be something like this:

SELECT serviceno from [service table] where [Customer ID] = forms![<Customer
form name>]![Customer ID]

--
Daryl S


Seddon Acaster said:
2 tables called customer and service

customer table has unique numerical customer id field, this is the primary
key. an example value for this field would be 3

service table has customer id field and serviceno field. the customer id
field is not unique but the serviceno field is

i want a customer form to have a combobox that looks up a query that will
only show the serviceno fields that relate to that particular customer

how would i do this?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Daryl, that did the job nicely.

Seddon

Daryl S said:
Seddon -

You need to have the combo box requery after a customer record is selected.
I don't know how your form is set up or how a user navigates to the correct
customer, but the event that loads the record should have a requery for the
combo box, something like this:

Me.cboname.Requery

You may want this in the OnCurrent event in the form. If this doesn't work,
tell us what is in the combo box - is it all records?
--
Daryl S


Seddon Acaster said:
This is what i've entered in the Row Source for the Combo Box:

SELECT scan_id FROM service WHERE [ac_ref]=forms![customer_full]![ac_ref];

The data the Combo Box shows is the same for all records at the moment which
obviously is not what I want. Have I missed something out?

And er thanks by the way!

Seddon

Daryl S said:
Seddon -

Set the recordsource for the combobox to be a query with the criteria to set
it to the current customer ID. It will be something like this:

SELECT serviceno from [service table] where [Customer ID] = forms![<Customer
form name>]![Customer ID]

--
Daryl S


:

2 tables called customer and service

customer table has unique numerical customer id field, this is the primary
key. an example value for this field would be 3

service table has customer id field and serviceno field. the customer id
field is not unique but the serviceno field is

i want a customer form to have a combobox that looks up a query that will
only show the serviceno fields that relate to that particular customer

how would i do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top