Access 97

I have tables tblbookings, tblvenue


I have made a form from tblbookings.with the form wizard
draw across booking id, name, address from the booking table.
from the tblvenue venueaddress.

Now on my form I want a combo box that when I make a new record it will show all my venues names.

When I click one it will put in my form the name of the venue and also the address.

What I have done is used the combo box wizard and done it like this.
Ticked " i want the combo box to look up values in a table.
NEXT selected tblvenue
NEXT selected venueID
NEXT
NEXT remember the value for later use.
NEXT finish
Clicked on the properties for that combo box and gone to event
selected after update and put in
Me.RecordsetClone.FindFirst "[venueID] = " & Me![Combo16]
Me.Bookmark = Me.RecordsetClone.Bookmark

BUT what happens is it puts in all the things on the form and not just the name of the venue and address

Have I got this code wrong????????


So if I have info in my data base like

Name tom, address 23 smith street, venue Black hole, address 23 black hole street.

And I make a new record and select Black hole It would put in black hole and the address for that BUT it would also put in the dam name for tom etc.

How can I make it just put in the name of the venue and address then let me put in the rest of the details.

Regards

John

You should not store venue name and address in the bookings table. You should only store venueID. You can display the venue name and address via a join to the venue table.

1. Add two unbound fields to the form. One for venue name and the other for venue address.

2. Change the recordsource of the venue combobox to include the three columns. Use the wizard to build the combo (I'll have less explaining to do). Bind the venueID but choose the option to hide it.

3. In the AfterUpdate event of the combobox, set the values of the two unbound fields -
Me.txtVenuename = Me.MyCombo.Column(1)
Me.txtVenueAddress = Me.MyCombo.Column(2)

The indexes I have used for the combo box assume that the select clause orders the fields - venueID, VenueName, VenueAddress. You'll notice that name is referred to as 1 and address as 2. That is because the index of a combobox uses a zero based index.