Populating a Text Box (A2K SR-1)

Topic

New Reply

On a form (frmSUBINFO2), I have a combo box (called ‘L4’) and a bound text box (called ‘ShipContact’). The combo box is populated with a person’s initials after the user selects a name. The table underlying the combo box (tblDVCPERSONNEL) has 3 fields: Initials, FirstName, and LastName, with Initials as the bound column. After the user selects a name in the L4 combo box and populates it with initials, I want to automatically populate the ShipContact text box with the full name equivalent of those initials, but I can’t get it to work. Here’s the code I’ve placed in the After Update event of the L4 combo box:

Private Sub L4_AfterUpdate()
Dim strSQL As String
strSQL = “SELECT DISTINCTROW tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName ” _
& “FROM tblDVCPERSONNEL ” _
& “WHERE ((tblDVCPERSONNEL.Initials)=[Forms]![frmSUBINFO2]![L4])”
Me!ShipContact.ControlSource = strSQL
End Sub

This code generates a #Name! error message in the ShipContact field. What am I doing wrong? As always, many thanks for your help.

Reply | Quote

Replies

You have to open the recordset and read the field. This on the fly code :
Private Sub L4_AfterUpdate()
Dim db as DAO.DataBase
Dim rst as DAO.RecordSet
Dim strSQL As String
Set db = CurrentDb
strSQL = “SELECT DISTINCTROW tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName As FullName” _
& “FROM tblDVCPERSONNEL ” _
& “WHERE ((tblDVCPERSONNEL.Initials)=[Forms]![frmSUBINFO2]![L4])”
Set rst = db.OpenRecordSet(strSQL, dbOpenDynaset)
If rst.RecordCount 0 Then
Me!ShipContact = rst.FullName
Else
Me!ShipContact = “” ‘Fill in what you want if no reords are selected
End If
Set rst = Nothing
Set db = Nothing
End Sub

Don’t forget to add a reference to Microsoft DAO 3.6 Object Library in Tools/References

Reply | Quote

Thank you, Francois. It’s great to hear from you again. I’ll give this a try.

Reply | Quote

Francois,

When running your code, I’m presented with an error message on the ‘Me!ShipContact = rst.FullName’ line that says ‘Compile Error: Method or data member not found’. I double-checked to ensure the code was typed correctly and I added the MS DAO 3.6 Object Library reference. Suggestions?

Reply | Quote

Try to add a space after FullName in the strSQL line :
strSQL = “SELECT DISTINCTROW tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName As FullName ” _
Sorry, my mistake.

Reply | Quote

Also replace the dot by a bang in the line:
Me!ShipContact = rst!FullName
Second mistake, sorry again

Reply | Quote

Here’s the code:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = “SELECT tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName As FullName ” _
& “FROM tblDVCPERSONNEL ” _
& “WHERE ((tblDVCPERSONNEL.Initials)=[Forms]![frmSUBINFO2]![L4])”
Set rst = db.OpenRecordSet(strSQL, dbOpenDynaset)
If rst.RecordCount 0 Then
Me!ShipContact = rst!FullName
Else
Me!ShipContact = “”
End If
Set rst = Nothing
Set db = Nothing
End Sub

Now I’m getting ‘Run-time error: ‘3061’, Too few parameters. Expected 1.’ for the ‘Set rst=db.OpenRecordSet(strSQL,dbOpenDynaset) line.

Reply | Quote

New correction:

& "WHERE tblDVCPERSONNEL.Initials = '" & [Forms]![frmSUBINFO2]![L4] & "'"

Reply | Quote

That did it, Francois. Works perfectly. Many thanks.

Reply | Quote