Auto Populating Fields

[radioman000]

I have a simple service workorder database and I would like to auto populate the fields for the customer after selecting the customer name from a combo bx drop down list. The database has a table called "customers" and one called "service" which is where all the service records are stored. I have tried in vain to accomplish this, and am relatively new to programming in Access. Is there some simple code I am over looking? Would appreciate any help.

Thanks

Radioman000

 

[jbpez]

Assuming that 'fields' are textboxes and that that the service table has a CustomerID field, on the combobox AfterUpdate event write something like this

Dim rst as DAO.Recordset
Dim strSQL as string
Dim intCustomerID as integer

intCustomerID = Me.comboxName.value

strSQL = "SELECT * FROM service WHERE CustomerID=" & intCustomerID
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenSnapshot)
If rst.RecordCount > 0 Then
    Me.textbox1.value = rst("FieldName").value
    Me.textbox2.value = rst("FieldName2").value
    .....
End if
rst.close
Set rst = Nothing

John Borges

 

[willir]

Radioman000

You can accomplish this with an unbound combo box. Add the combo box to the form and follow the wizard prompte for "Find a record...".

The one "gotcha" with the wizard is that it will use the primary key in the record source to find record. It will tend to hide the primary key by setting the column width for the primary key to 0". This fine for many situations, but if are trying to find records by date or non-primary key, then you have to tweak the combo box settings.

Richard

 

[radioman000]

John,

Thanks for the response top my post. I tried the code you suggested and I am getting an error that syas, "variable not defined" in this line of the code: strSQL = "SELECT * FROM service WHERE CustomerID=" & intCustomerID

The "intCustomerID" is the part that access highlights as being in error. Should this be the primary key pof the table from which I am retreiving the info?

Brad

 

[willir]

Check your spelling / typing...

Dim rst as DAO.Recordset
Dim strSQL as string
[COLOR=blue]Dim[/color] [COLOR=yellow blue]intCustomerID[/color] as integer

intCustomerID = Me.comboxName.value

strSQL = "SELECT * FROM service WHERE CustomerID=" & [COLOR=yellow blue]intCustomerID[/color] 
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenSnapshot)
If rst.RecordCount > 0 Then
    Me.textbox1.value = rst("FieldName").value
    Me.textbox2.value = rst("FieldName2").value
    .....
End if
rst.close
Set rst = Nothing

Make sure the Dim intCustomerID as Integer matches the strSQL = "SELECT ... & intCustomerID

Richard

 

[jbpez]

I agree, check your spelling.

John Borges

 

[radioman000]

Just wanted to say thanks to everyone for the help with the auto-populate problem. Spelling was the problem along with a few periods out of place... here is the code that worked:

Private Sub Combo6_AfterUpdate()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intCustomerID As Integer

intCustomerID = Me.Combo6.Value

strSQL = "SELECT * FROM customers WHERE CustomerID=" & intCustomerID
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
Me.Customer.Value = rst("CompanyName").Value
Me.Contact.Value = rst("ContactName").Value
Me.Phone.Value = rst("PhoneNumb").Value
End If
rst.Close
Set rst = Nothing
End Sub

Thanks again for the help!

Brad

 

[willir]

As an FYI, you can thank jbpez (John) by giving him a star.