Auto Populate Text Fields via Combo-Box Selection issue

[Olu Solaru]

The subject is pretty self-explanatory. I have tried using the DLookup
option too. But I can't seem to get my text fields to auto-populate based on
my selection in the combo box. Not quite sure if this is the right forum,
please redirect if so. The Combo Box is doing a search by BprNumber - which
has a text value (combination of numbers/text)

Code Sample :

Private Sub cboBpr_AfterUpdate()

On Error Resume Next

cboBpr.SetFocus

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

cboBpr.SetFocus
If cboBpr.Value > 0 Then
strSQL = "SELECT * FROM tblBPRNumber WHERE BPRNumber = """" &
cboBpr.Value"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.ProcessDate = rs("ProcessDate")
Me.Sop1 = rs("Sop1")
Me.Sop2 = rs("Sop2")
Me.Sop3 = rs("Sop3")
Me.Sop4 = rs("Sop4")
Me.Sop5 = rs("Sop5")
Me.Sop6 = rs("Sop6")
Me.Sop7 = rs("Sop7")
Me.Sop8 = rs("Sop8")
Me.Sop9 = rs("Sop9")
Me.Sop10 = rs("Sop10")
Me.Sop11 = rs("Sop11")
Me.Sop12 = rs("Sop12")
Me.Sop13 = rs("Sop13")
Me.Sop14 = rs("Sop14")
Me.Sop15 = rs("Sop15")
Me.Sop16 = rs("Sop16")
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If

End Sub

 

[Douglas J Steele]

strSQL = "SELECT * FROM tblBPRNumber WHERE BPRNumber = '" & cboBpr & "'"

Exagerated for clarity, that's

strSQL = "SELECT * FROM tblBPRNumber WHERE BPRNumber = ' " & cboBpr & " ' "

This assumes that BPRNumber doesn't include apostrophes. If it does, try:

strSQL = "SELECT * FROM tblBPRNumber WHERE BPRNumber = " & Chr$(34) & cboBpr
& Chr$(34)

 

[KARL DEWEY]

Try and set the default property of the text box to the combobox.

 

[Olu Solaru]

Still didn't work. May be I should devulge more information. My form is
unbound, The only thing that is pulling data is the Combo Box Control. I
have looked at all my tables to make sure the fields are named appropriately.
Could it have something to do with my column size settings:

Here is my Combo Box - Query Code -
Row Source Type - Table/Query
Row Source - SELECT tblBPRNumber.BPRNumber FROM tblBPRNumber ORDER BY
tblBPRNumber.BPRNumber;

 

[Olu Solaru]

I got it to work !( was missing a quotation symbol - ('), but I have one
field that is not populating - ProcessDate

 

[Douglas J Steele]

Is the code actually running? Put a breakpoint in it to see. If it is, does
the recordset returned have any data in it?

I'd suggest changing:

If Not rs.BOF Then

to

If Not rs.EOF Then

Also, the db.Close is unnecessary: you only close what you open. (In older
versions of Access, that would have caused the database to shut down.
Fortunately, they've fixed that problem.)