Subform Querie

[KevinPreston via AccessMonster.com]

I have a form that i use to book vehicles in to our workshop for work
required, along with various fields, ie customer, make, model etc there is a
field called regnumber, there will be multiple instances of regnumber,
depending on how often the vehicle comes in for work. I have put a subform on
the main form, i would like this subform to show all records for the current
vehicle i am booking in, ie

jobnumber regnumber make model description
odometer
26157 v199zzz Mercedes C220 Damage repairs to N/S 254691

subform

25439 v199zzz etc
25344 v199zzz etc


How do i write a querie to search the booking table based on the contents of
regnumber?

I can do this using another table but can't do it on the same table.

I hope that makes sense and look forward to some much apreciated assistance.

Kevin

 

[Ken Sheridan]

There is no reason why a subform can't be based on the same table/query as
its parent form. In your case you'd set the subform control's
LinkMasterFields and LinkChildFields properties to regnumber. As you
navigate to an existing record in the parent form the subform will show all
records, including the current one, with that registration number. When you
navigate to a new empty record in the parent form, as soon as you enter a
value in the regnumber control the subform will show all matching records.

An alternative would be to use an unbound parent form and a bound subform.
Add an unbound text box to the parent form in which to enter the registration
number. In this case the subform control's LinkMasterFields property would
be the name of the text box on the parent form, and its LinkChildFields
properties regnumber as before. Once again as soon as you enter a value in
the text box control on the main form the subform will show all matching
records. In this case all data entry, including adding a new record would be
via the subform of course.

Ken Sheridan
Stafford, England

 

[KevinPreston via AccessMonster.com]

Ken

Thank you very much for your assistance, it worked perfectly, after i figured
out were the LinkMasterFields were!

The reason i couldn't get it to work was totally my inexperience, i have
another subform on the same main form which takes its data from a query using
another table and i was trying to do it the same way as that.

It is all a learning curve, now i know a little bit more.

Thanks again

Kevin

There is no reason why a subform can't be based on the same table/query as
its parent form. In your case you'd set the subform control's
LinkMasterFields and LinkChildFields properties to regnumber. As you
navigate to an existing record in the parent form the subform will show all
records, including the current one, with that registration number. When you
navigate to a new empty record in the parent form, as soon as you enter a
value in the regnumber control the subform will show all matching records.

An alternative would be to use an unbound parent form and a bound subform.
Add an unbound text box to the parent form in which to enter the registration
number. In this case the subform control's LinkMasterFields property would
be the name of the text box on the parent form, and its LinkChildFields
properties regnumber as before. Once again as soon as you enter a value in
the text box control on the main form the subform will show all matching
records. In this case all data entry, including adding a new record would be
via the subform of course.

Ken Sheridan
Stafford, England

I have a form that i use to book vehicles in to our workshop for work
required, along with various fields, ie customer, make, model etc there is a

[quoted text clipped - 20 lines]

Kevin