Returning primary key value instead of combo box selection

[feathers212]

I have a form that uses an unbound combo box to choose a material to receive. The material is choosen from a master list. Each material on the master list has it's own material ID which is set up as the primary key.

The form also has unbound text boxes for the date and quantity of the receipt. When the submit receipt button is clicked, the receipt is added to the transaction table:


Private Sub Submit_Receipt_Click()
On Error GoTo Err_Submit_Receipt_Click

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Material Transactions")

With rs
.AddNew
![Material Name] = Me.ReceiptMaterial.Value
![Date] = Me.ReceiptDate.Value
![Transaction] = "Receipt"
![Quantity] = Me.ReceiptQuantity.Value
.Update
.Close
End With

DoCmd.Close acForm, "Receipts Form", acSaveYes
MsgBox ("Receipt entered into database.")

Exit_Submit_Receipt_Click:
Exit Sub

Err_Submit_Receipt_Click:
MsgBox Err.Description
Resume Exit_Submit_Receipt_Click

End Sub


Everything works just fine, except for the Material Name portion. The programming enters in the associated material ID rather than the actual name that was chosen from the drop down.

I'm not sure about the Me.ReceiptMaterial.Value. Is this correct, or should I not be using the .Value?

 

[modest]

what datatype is Material Name

 

[boblarson]

From your code above I assume that Me.ReceiptMaterial is the combobox. If so, you would use Me.ReceiptMaterial.Columns(1) to use the name instead of the ID if you have two columns ID and Name in your underlying query for the combo box. If you want to use Me.ReceiptMaterial (you don't need .Value) you can set the bound column to be the name column (2) instead of (1) in your properties (remembering that in the properties the columns are numbered starting with 1 and in the Me.ReceiptMaterial.Columns(x) are numbered starting with zero (0).

 

[feathers212]

Thought I had it all setup correctly, but upon minor adjustment of the bound column number, the whole thing works perfectly without having to change the .Value.

Thanks.