Code for form giving errors.

[yeppy12]

I have a form that has several fields, one of which is called Pro_ID. I
would like to use whatever number is entered in that field to run a query
that fills in the rest of the fields with information associated with that
Pro_ID. The code below is what I have so far, but it does not work (I keep getting errors on lines 4 and 5). Any ideas as to what is wrong? Thanks.

Private Sub Pro_ID_AfterUpdate()
 Dim strSQL As String
 Dim rs As DAO.Recordset
 strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE
 [prov1]=" & Me.txtPro_ID
 Set rs = CurrentDb.OpenRecordset(strSQL)
 If rs.RecordCount > 0 Then
 Me.txtLAST_NAME = rs.Fields![ LAST NAME]
 Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
 End If
 rs.Close
 Set rs = Nothing

 

[boblarson]

[prov1] appears to be your table and you can't use

[prov1]=" & Me.txtPro_ID

It would be [Pro_ID]=" & Me.txtPro_ID (IF Pro_ID is your field name)

 

[WayneRyan]

yeppy,

I think the line break got you

strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE " & _
         "[prov1]=" & Me.txtPro_ID

Private Sub Pro_ID_AfterUpdate()
 Dim strSQL As String
 Dim rs As DAO.Recordset
 strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE [prov1] = " & Me.txtPro_ID
 Set rs = CurrentDb.OpenRecordset(strSQL)
 If rs.RecordCount > 0 Then
   Me.txtLAST_NAME = rs.Fields![ LAST NAME]
   Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
 End If
 rs.Close
 Set rs = Nothing

Alternatively, you can use the DLookUp function.

But, why not look in the Sample Database section here. There are a lot of
Search form examples.

Wayne

 

[yeppy12]

I made the changes you guys suggested, but I now get an error on the line below:

Set rs = CurrentDb.OpenRecordset(strSQL)

Any idea as to why?