Passing a value through a combo box

[Patrick]

Hi there,

I have a combo box on my form, which takes the id and name of
departments, from the departments table. The id is hidden, so only the
name of the department shows in the dropdown menu.

The user selects the deparmtent they want to modify the name of, and
use a textbox to change the name. Pressing edit, it needs to take the
value of the textbox, and update the entry where the id is the same as
the id of the combobox.

could someone explain how i go about doing this? i have everything
sorted except getting the id from the combo box.

thanks

Patrick

 

[Allen Browne]

This example shows how to use an Update query to change the DeptName field
to whatever is in the text box Text0, for the department chosen in the combo
Combo1:

Dim strSql as String
If IsNull(Me.Text0) Or IsNull(Me.Combo1) Then
MsgBox "Nobody home"
Else
strSql = "UPDATE tblDepartment SET DeptName = """ & _
Me.Text0 & """ WHERE DeptID = " & Me.Combo1 & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
End If

A better option might be to use the DblClick event of the combo to open the
form where the user can modify the info:
Private Sub Combo1_DblClick(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.Combo1) Then
DoCmd.OpenForm "Form2", DataMode:=acFormAdd
Else
strWhere = "DeptID = " & Me.Combo1
DoCmd.OpenForm "Form2", WhereCondition:= strWhere
End If
End If

Either way, you will need to Requery the combo afterwards so it hears about
the new value.

 

[Patrick]

hey thanks. somehow i managed to find something on the internet that
used the column attribute of the combo box. I tried this and it worked
great. So thanks for your help on the matter.