Shoutbox

Let's say for example I have a customer table:

customer { customerid, firstname, lastname, address, phone }

customerid is a key field.

and a reservation table:

reservation { reservationid, customerid, apartmentid, arrivaldate, departuredate }

reservationid, customerid, and apartmentid are key fields.

reservationid is auto-incremented, customerid is the id of the customer making the reservation, apartmentid is the id of the apartment being rented (there's another table for that, which isn't really relevant here).

In the relationships customerid from the reservation table is linked to customerid from the customer table.

(this is just an example, btw)

So say I make a form out of the reservation table. On it I have a customerid field, which shows the number. What I want to do is instead of having the field simply show the customer's id (which isn't very helpful for the user who wants to enter the customer in there), I want it to be a drop-down combobox showing a list of all customers' first and last names, from which the user can select a customer.

So it basically displays the firstname and lastname but in the background stores it as the customerid.

Hopefully that made sense, if not just let me know, and hopefully CookieRevised sees this post too.

Either go the wrong easy but not smart was is requerying the server to find out the {customerid} of {firstname lastname}

Not sure what language you want to do it in though, but you could create an array, like $array[0][customerid] $array[0][firstname] and look up what $array[X] has firstname = $array[0][firstname] then grab the customerid.

What I once did that worked with a JS control is, I had a datatable with 5 columns and X rows. I say X rows because it depended on how much data I retrived, because the system worked in a way I had to give an array to it so it would create the datatable.  I gave an array but each column has 6 columns, not 5 (6th being the ID).  It didn't show the 6th because it was supposed to be 5 columns, then when I needed the ID, I just used the same array and went to the 6th record.  -- This depends on the language though.

Uh, it's MS Access (see topic title), so I'll just be using crappy VBA, if necessary. But I know there's a way to do this without having to code, I just can't find the option.

What you need to do is create a select query which will output the names and the ID fields that you need.  Then go back into the form designer, add a combobox, go through the wizard that pops up, choose the query you just made and then go through the rest to wizard to finish making it appear as you wish.

The only part I'm not 100% sure on is the saving part (which is an option in the wizard also), although there's probably information on that in the help (which I don't have handy at the moment).