Best way to query??

[Awes]

Hello Access Gurus

I am currently working on a database for a friend and would like some advice with a problem that I have. I’ll start by setting the scene:

I have an unbound form that has a tab control with two pages. The first page contains a sub-form that allows the user to input details about a person (name, address etc) and the second page a sub-form that allows the user to input details about the person’s vehicle (Make, model etc). When all the details have been entered the user clicks a button, which after checking the validity of the data then saves the person details to a new record in the table ‘Customers’. The new record is given an autonumber id and this number is then saved to a table called ‘Vehicles’ along with all the details that the user entered about the vehicle. This number then gives a link between the vehicle and the owner. Eventually there will be the facility to add additional vehicles using the customer id number as the link to the owner.

The process that I’m currently working on is to call up the details about any customer and their vehicle(s) so that they can be edited (e.g. for a change of address). The user requirement is that on entering a registration number the relevant details will be displayed.

The reasoning for using the vehicle registration number, as the search criteria, is that it is a unique value whereas using a surname for example might identify more than one person and therefore require further input from the user to narrow the selection down to the right person.

Now as the details that are to be displayed are the same as those entered originally, I thought that the best way to display the information would be to use a form containing the same sub-forms as before but instead of the user completing the boxes this would be done as a result of the search started off by entering the vehicle registration number.

To get the right person using this approach does not appear to be the easiest. First you need to find the record in the ‘Vehicles’ table that matches the registration number and then identify the customer id number. Then you need to go to the ‘Customers’ table and find the record matching the customer id number. This process would also have to search the ‘Vehicles’ table for a second time to find additional vehicles belonging to the selected person making sure that the original vehicle isn’t display twice!! Then you need to display all the selected information on the form.

Questions:

1. Can I use an input box to trigger this process off or should I use a custom form instead?

2. What is the best way of doing the search starting with the registration number of the vehicle and ending up with all the information from the two tables?

3. Is it good practice to use the same sub-forms in different main forms or should copies of the original sub-forms be used?

I await as usual for enlightenment

Awes

 

[Awes]

Further thoughts

The more that I think this problem through the less convinced I am that the user needs to see all vehicles that a particular customer has.

The idea of this process is to either edit the details about the selected person or the details of the vehicle that triggered the search.

On the odd occassion that the user would need to edit the details of two or more vehicles belonging to the same person they would have to do each vehicle individually.

This way the resulting query should be a lot easier to sort out.

The other question I forgot to ask is:

What is the best way to get the value of the registration number (from either the input box or custom form) into the query?

Many thanks for any wisdom shared.

Awes