Combo box and records

[warmunger]

I've got a table with information about our customers. Conveniently named customers. Now I need to select the customers by using a combobox. Then I need my form to go to that record so that I can change the data. The fields in my table is COMPANY_NAME, FIRST_NAME, LAST_NAME, TELEPHONE, ADDRESS, CITY, POSTAL CODE.

The COMPANY_NAME field is the one I want to use to select the Records with

 

[KenReay]

Hi

Make a form with recordsource of your table customers.

Use the wizard, and select each field you want to show on the form, these will then be bound fields and will automatically populate when a row is read from the table

make a combo box on the form, again using the wizard, tell it the columns you want to see (probably just company name in your case)

Name this combo box cboCompanyName do not make it bound.

In the after update event of the combo box, put code like so

Dim rs as recordset

Set Rs = Me.RecordsetClone

Rs.FindFirst "[COMPANY_NAME] = '" & cboCompanyName & "'"
If Rs.NoMAtch
msgbox "Not Found"
Else
Me.Bookmark = rs.bookmark
End if
Rs.Close
Set RS = Nothing Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[borg2002]

Warmunger,

this is the code I use for doing such things. This code applied to a textfield, but can be easily adapted to work for a combobox.

Hope this helps
Kind regards
Borg

Private Sub cmdZoeken_Click()
strZoek = ""
txtMatCode.SetFocus
strZoek = txtMatCode.Text
Set connMatAttr = New ADODB.Connection
Set rstMatAttr = New ADODB.Recordset
With connMatAttr
.CursorLocation = adUseClient
.ConnectionString = strc
.Open
End With
With rstMatAttr
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CacheSize = 50
.Source = "Select * from TABLE where MATERIAL_ID= " & "'" & strZoek & "'"
.ActiveConnection = connMatAttr
.Open
End With
If rstMatAttr.RecordCount = 0 Then
MsgBox "Requested material number could not be found in the attributes table", vbCritical, "Attention"
Exit Sub
End If
With rstMatAttr
cmbMat1 = .Fields(6)
cmbMat2 = .Fields(7)
cmbMat3 = .Fields(8)
cmbMat4 = .Fields(4)
cmbMat5 = .Fields(13)
cmbMat6 = .Fields(14)
cmbMatID = Right(.Fields(0), 8) & " " & .Fields(2)
End With
Set rstMatAttr = Nothing
cmbMatID_Click
End Sub

 

[borg2002]

Sorry Ken,

Didn't notice you already answered this question...

Kind regards
Borg

 

[warmunger]

Thanks a lot for the help! And yet again I've learned Something new