How to determine the selected value from a combobox

[Mohamed osman]

I have 2 tables: (1)Items: ItemID, ItemName, ItemQuantity (2) Transaction: TransID, ItemID, OperationCode (in, Out), TransQuantity. The two tables are related by ItemCode (1 to many). I designed a form in which there is a combo box based on the item table. The combobox displays ItemName and hides ItemCode. What I want is this: when user selects an item I want that specific record to be updated using the quantity that I user enters in the form. Whenever I select any item the updated record is always the FIRST one. This is the code:
Option Compare Database

Private Sub Command8_Click( )

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordse t("TransT")
Set rs2 = db.OpenRecordse t("ItemT")

rs1.AddNew
'rs1!itemID = ItemList.ItemsS elected(itemnam e)
rs1!TransQty = Quantity
'MsgBox (rs2!itemname)
rs1.Update
rs1.Close

rs2.Edit
rs2!ItemQty = rs2!ItemQty + Quantity
rs2.Update
rs2.Close

Set db = Nothing
MsgBox ("Done!")
End Sub

[ADezii]

The combobox displays ItemName and hides ItemCode

Do you mean: The combobox displays ItemName and hides ItemID?

[Mohamed osman]

You are right

Thank you ... you are right ...yes hides ItemID. Can you help?

[ADezii]

Yep, I'll be back in a little.

[ADezii]

First of all, a few simple assumptions:

1. The Combo Box is named cboItems.
2. cboItems has a Column Count of 2 (Item and Name).
3. The Bound Column of cboItems is ItemID.
4. The Text Box on the Form where the User enters a revised Quantity is named txtQuantity.
5. The following code should do the trick, any questions feel free to ask.
   

   Dim txtQ As TextBox
   
   'If this is a New Record then get Outta Dodge!
   If Me.NewRecord Then Exit Sub
   
   Set txtQ = Me![txtQuantity]
   
   'If either the Combo Box or the Text Box does not contain a Value (Is Null),
   'then get Outta Dodge!
   If IsNull(Me![cboItems]) Or IsNull(txtQ) Then
     Exit Sub
   Else
     'OK, we have 2 Values, but is the Value in the Quantity Text Box Numeric,
     'and is it within a certain Range defined by you?
     If Not IsNumeric(txtQ) Or txtQ < 1 Or txtQ > 9999 Then
       'Yep, you guessed it, get Outta Dodge!
       Exit Sub
     Else
       'If you get here, you have a Valid Entry for Quantity, namely (>= 1 AND <= 9999)
       'Update TransQuantity in the Transaction Table for the selected ItemID in cboItems
       CurrentDb.Execute "UPDATE Transaction SET [TransQuantity] = " & txtQ & _
                         " WHERE Transaction.[ItemID] = " & Me![cboItems], dbFailOnError
     End If
   End If