Access VBA error on SQL

Hi,

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)
DoCmd.RunSQL SQL
Me.NRIC___PP = rs!NRIC1


Set rs = Nothing
Set db = Nothing
End If
End Sub
LVL 1
Vaibhavjoshi2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdebaetsCommented:
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:

2;0

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

=Name2.Column(1)
0
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.
0
pdebaetsCommented:
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
else
    Me.NRIC___PP = s
end if

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrogSintaCommented:
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)
0
Vaibhavjoshi2005Author Commented:
Worked very well
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.