autofilling a field

[bite_tony]

I have a database with a table of customer details and then a table of service calls to those customers.

Following advice from these forums I used an 'autonumber ID' for the customer table as the PK

In the service calls table there is an 'autonumber servicecall ID' field as the PK, and has the customer ID as the foreign key.

However, when fellow employees will enter servicecall details they wont have the autonumber ID to hand, they will have the address. As a result I have included a field 'customer address' in my servicecalls table and made it a 'lookup field' to the customer street address field in the customers table, so if an employee types in '59 H' it will bring up '59 High street'

Can I make it so that once they've clicked the right address, access automatically populates the 'customer ID' field with the corresponding ID number?

Or, if I am using this look up, does that not eliminate problems of people inputting addresses incorrectly and then I can just use the customer address as my primary key and not have to worry about linking back to the autonumber customer ID?

your thoughts on this are much appreciated.

 

[Mr. B]

It is never a good idea to use a field like the address as a primary key field. You are on the right track with your table structure as far as the auto number field goes.

I would assume that in your customer details table you have the customer name as well as the address. Do your fellow employees relate more to the address than the customer name?

You can use the address to acquire the record ID for that customer but you can just use the customer name. Which ever you prefer. Normally we see the Customer Name used to do this as opposed to the address.

In either case you need to use a combo box on your form and set the "Control Source" for this combo box to be the CustomeID field from your Customer details table. To populate the "Row Source" property of the combo box, you need to click the button at the right end of the "Row Source" property of the combo box and use the QBE to create an sql statement that will have the CustomeID (auto number) field in the first column. This will make the selected Customer name or customer address (which ever you are going to use) to populate the CustomerID field in the Service Calls table. If you are going to show the customer names as a list in the combo box, you would have the customer name in the second column. If you are going to use the Address then you would have the Address field in the second column.

Next, you need to set a couple of other properties of the combo box. On the Format tab of the Properties pane, set the "Column Count" property to 2. Put a zero (0) in the Column Widths property. This will cause the first column to be hidden and you will not see the customerID values but these values will be saved to the foreign key field when a customer name (or in your case a customer address) is selected. As a note: if you had more than two fields that you wanted to display when the combo box is dropped down, you would add additional values in the Column Width property, separating them with a semicolon.

When you have all of this complete, display your form in form view and try the combo box. You will be able to select a customer name or address which ever you used and then if you check your table after saving the record, you will find that the customer ID value for that customer has been written to the Service Call table in the CustomerID field.

 

[bite_tony]

Ah wow, thanks for such a concise answer!!!
We work in street addresses because we are a double glazing company... a 'job' will be a household..
also, we get A LOT of names repeated...

Im still getting to grips with Access, so what youve said above will take me a while to get my head round, but its very helpful...

No doubt I'll be back later..
thanks again...

 

[Mr. B]

As you move forward, please post back with specific questions and someone will try to help. There is a lot to learn and to get your head around. However, just take things one step at a time. When you get to the point that you need assistance, this is good place to find the help you need.

Just remember, we have all been right where you are.

 

[bite_tony]

I have a relationship between the customerID in the customers table and customerID in the servicecalls table.
Is it worth/does it make a difference, if i make a second relationship between the customer address in both these tables as well?

 

[Mr. B]

You should not have the address in both tables. You only need the relationship between the CustomerID fields. The address would be related to the customer, and the customer is related to the service call. You would retrieve the address from the Customers table when you need it. You never want to duplicate data.

 

[bite_tony]

You should not have the address in both tables. You only need the relationship between the CustomerID fields. The address would be related to the customer, and the customer is related to the service call. You would retrieve the address from the Customers table when you need it. You never want to duplicate data.

please bear with me, there's a chance this question is fairly dumb but:

I have put the customer address in there because when employees enter service call information they wont know the customer ID, they will know the street address e.g '10 high street'. Thats why im trying to use that to autopopulate the customer ID.

Am I missing something in my design then?
Is this an issue of having a form which covers more than one table?

 

[Mr. B]

First, the only dumb questions is the one you did not ask. There really are no dumb questions.

Did you create the combo box on the form where you want users to create a service call as I described in my previous response? When you create this combo box your uses will be able to select the address from that combo box. Because the data that is being displayed in the combo box is actually in the Customers table, when they select the address they will actually be selecting and entering the CustomerID value. You will have the CustomerId field from the Service calls table as the Control Source for the combo box.

You may want to post your database here and let us take a look.