SQL in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have form with the fields ind_no and protocol_no. After the protocol number
is entered in, i want data in the ind_no field to appear. I but it gives me
a message saying that there's a missing operator.

Private Sub protocol_no_AfterUpdate()
Dim mysql As String

mysql = "SELECT [General SAE].ind_no"
mysql = mysql + "FROM [General SAE]"
mysql = mysql + "WHERE ([General SAE].protocol_no)=[Form_add new
form].protocol_no.value"
DoCmd.RunSQL (mysql)

End Sub
 
Try using dlookup instead
'If the protocol number
Me.[ind_no] = Dlookup("[ind_no]","[General SAE]","[protocol_no] =" &
Me.[protocol_no])

'If the protocol String
Me.[ind_no] = Dlookup("[ind_no]","[General SAE]","[protocol_no] ='" &
Me.[protocol_no] & "'")
 
In addiition, OP needs to insert some spaces:
mysql = "SELECT [General SAE].ind_no"
mysql = mysql + "FROM [General SAE]"
Click to expand...

Will make mysql = "SELECT [General SAE].ind_noFROM [General SAE]"
(Note the lack of a space before "FROM". This won't work as-is and is a
problem for the WHERE clause as well.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Ofer said:
Try using dlookup instead
'If the protocol number
Me.[ind_no] = Dlookup("[ind_no]","[General SAE]","[protocol_no] =" &
Me.[protocol_no])

'If the protocol String
Me.[ind_no] = Dlookup("[ind_no]","[General SAE]","[protocol_no] ='" &
Me.[protocol_no] & "'")

--
I hope that helped
Good luck


tope12 said:
I have form with the fields ind_no and protocol_no. After the protocol
number
is entered in, i want data in the ind_no field to appear. I but it gives
me
a message saying that there's a missing operator.

Private Sub protocol_no_AfterUpdate()
Dim mysql As String

mysql = "SELECT [General SAE].ind_no"
mysql = mysql + "FROM [General SAE]"
mysql = mysql + "WHERE ([General SAE].protocol_no)=[Form_add new
form].protocol_no.value"
DoCmd.RunSQL (mysql)

End Sub
Click to expand...
Click to expand...
 
Back
Top