Storing ID from form

[Guest]

Hi

This is a basic question. I have a form that has combo boxes which pull data
from multiple look_up tables. This is what I want to do. When I choose a
value from the combo box, I want its corresponding ID to be stored rather
than the text. For instance, let's say I have a form I am entering
information about a customer. For the gender field, let's say there is a look
up table where the value 0 (male) and 1(female) exists. Now on the form, I
want to display male and female and when the user chooses either of these
options, I want to store 0 or 1 in the customer table.

Thanks
Hadavidi

 

[Guest]

Hi, Hadavidi.

First of all, you're seeking to use a combo box exactly as you should, by
storing a foreign key numeric code, while displaying more meaningful text to
the user.

There are several key properties that control the operation of a combo box:

Control Source
The field of the underlying table in which to store the selection.

Row Source
An SQL query statement, containing one or more columns (fields) that
populates the rows of the combo box.

Bound Column
The column to be stored in the Control Source

Column Widths
The amount of space to be dedicated to displaying each column in the Row
Source statement. The first non-zero-width column is what is displayed.

So, for your Gender example, you might have:

Control Source Gender
Row Source SELECT Genders.ID, Genders.Text FROM GENDERS;
Bound Column 1
Column Widths 0";1"

Genders.ID would be stored since the bound column is 1, while the text is
displayed because it is the first non-zero-width column.

By the way, if you have the wizard enabled (View, Toolbox, <toggle on the
wand & stars button>), and you select the "Hide Key Field (Recommended)"
option, Access sets the combo box up this way, and sets the column widths
appropriate to the longest entry(ies).

Hope that helps.
Sprinks

 

[Guest]

Thanks Sprinks for your help. All I had to change was the Bound Column from
the default value of 1 (the column containing the text value in the look up
table) to 0 (which has the unique ID). One additional question I have is,
after the user finishes entering demographic information, I want a pop-up to
come and ask if the the user would want to enter information may be about
orders. If they choose yes, it should take them to that form, if not, it
should close. Am not sure how easy or tough that is. Thanks once again for
your help.

Hadavidi

 

[Guest]

Hadavidi,

It's a little tricky to determine just when the user has finished entering
demographic information, while still enabling him to review and correct his
choices. It is more straightforward to place a "Enter Order Information"
button on the form, allowing him to enter new orders related to the current
customer:

On Error GoTo Err_YourCommandButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourOrdersForm"

' Change "CustomerID" to the name of the field in the Orders table
' and the name of the current form control that is bound to the ID,
respectively
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

' Optional code to close the Csstomer entry form, saving
DoCmd.Close acForm, "YourCustomerForm", acSaveYes

Exit_YourCommandButton_Click:
Exit Sub

Err_YourCommandButton_Click:
MsgBox Err.Description
Resume Exit_YourCommandButton_Click