Autofill additional fields from AutoNumber (1 Viewer)

M

martyb86

Registered User.

Local time

Today, 04:37

Joined

Jul 8, 2014

Messages

16

• Jul 8, 2014

• #1

Hi very new to Access and I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.

In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.

The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?

Thanks

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 04:37

Joined

Feb 19, 2013

Messages

16,610

• Jul 8, 2014

• #2

in the form for the booking table, change the type of control (which is probably a text box) you have for the customer to a combo box. Set the following combobox properties although you can use the wizard:

rowsource: SELECT customerID, customersurname, customerfirstname from tblecustomers order by customersurname
number of columns - 2
column widths 0;2
allowlistedits No

 

M

martyb86

Registered User.

Local time

Today, 04:37

Joined

Jul 8, 2014

Messages

16

• Jul 9, 2014

• #3

Hi thanks for the quick reply!
I tried it with the qizard and now have a drop down box with the passengers (thanks again)

However the bookings do not seem to be linking to the passengers in any form - on the add passenger table, the little plus sign appears but there is no booking data when I click it

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 04:37

Joined

Feb 19, 2013

Messages

16,610

• Jul 9, 2014

• #4

Is the control source for the combo box set to the customerID?

 

M

martyb86

Registered User.

Local time

Today, 04:37

Joined

Jul 8, 2014

Messages

16

• Jul 9, 2014

• #5

aaaah, thought I had but in fact had not.

Thats excellent thank you for your help/

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 04:37

Joined

Feb 19, 2013

Messages

16,610

• Jul 9, 2014

• #6

on the add passenger table

Click to expand...

The combo box should be in the form, not the table. If my post has prompted you to go down the route of using lookups in tables, I would strongly encourage you to turn around - in that direction 'there be dragons'

 

M

martyb86

Registered User.

Local time

Today, 04:37

Joined

Jul 8, 2014

Messages

16

• Jul 9, 2014

• #7

No thats great thanks it is definitely in the form!
By that I had meant when I go back to the table to check the data entered it wasnt displaying the bookings under the passengers, it is now though!

 

V

vbaInet

AWF VIP

Local time

Today, 04:37

Joined

Jan 22, 2010

Messages

26,374

• Jul 14, 2014

• #8

marty I think you should show CJ your database and he'll echo the same thing I mentioned in your other thread.