• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

Access VBA error on SQL


On an access subform on Name2 filed on change event I have written following event. What I want to happen is as follows ; If the name2 is changed the program should fetch NRIC / PP value for that name from table and populate it in Me. NRIC / PP field.

But it gives me error.

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

If IsNull(Me.Nationality) Then

SQL = "SELECT Officers.[NRIC / PP] as NRIC1 FROM Officers WHERE Officers.Name1=Me.Name2  AND Officers.UEN_Number=Forms!Organisation![UEN Number];"
Set rs = CurrentDb.OpenRecordset(SQL)
Me.NRIC___PP = rs!NRIC1

Set rs = Nothing
Set db = Nothing
End If
End Sub
  • 2
  • 2
1 Solution
You can do this without coding. Make the Name2 field a combobox and use the Officers table in an SQL statement as a row source. Ex:

Select Name1, [NRIC / PP] from Officers Order by Name1;

Set the ColumnWidths property of the Name2 control so that the second column is hidden. You can do that with this setting in the ColumnWidths property:


For this ColumnWidths property setting, you'll probably also want to set the ListWidth property to 2.25 or so.

Now, set the control source of the [NRIC / PP] control to

Vaibhavjoshi2005Author Commented:
Name2 is already a combo box. I want to trigger fetching of data on selection of one of the proposed name. It is possible that I actually may type in a new name, in that case no defaults will be proposed.
DLookup can help. Please note that if the UEN_Number field is a Text data type, then you'll need to surround "Forms!Organisation![UEN Number]" in quotes:

dim s as string
s = "" & dlookup("[NRIC / PP]", "Officers", "Officers.Name1= " & chr(34) & Me.Name2 & chr(34) & "  AND Officers.UEN_Number= " & Forms!Organisation![UEN Number])

if s = "" then
    Me.NRIC___PP = s
end if

Open in new window

You get the error because you only use the RunSQL command on UPDATE, INSERT, and DELETE statements and not SELECT statements.  That line is not needed.

But why go to the trouble of writing may lines of code just to get 1 value?  A DLookup, as mentioned by pdebaets, would work fine.  

Another option is to add the field [NRIC / PP] as a hidden column in your combo box.  You do this by joining the OFFICERS table to the table used in Name2's rowsource (link the UEN_Number and Name1 fields).  You would also increase your ColumnCount property by 1 and hide the column by adding an additional ColumnWidth of 0.  Then in the AfterUpdate event of Name2, just add:
Me.[NRIC / PP] = Me.Name2.Column(number of columns minus 1)
Vaibhavjoshi2005Author Commented:
Worked very well
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now