query to filter by current record id?

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]

--
Daryl S


"Seddon Acaster" wrote:

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?

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" wrote:

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" wrote:

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 -

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" wrote:

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" wrote:

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" wrote:

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?

Thanks Daryl, that did the job nicely.

Seddon

"Daryl S" wrote:

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" wrote:

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" wrote:

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" wrote:

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?