Access - Currentdb.Execute Insert Into ..... Error 2447 invalid use of dot or !

Hello all,

This all worked last night so somewhere I've hit a wrong key today but I keep getting a

Run=time error 2447:
There is an invalid use of the . (dot) or ! operator or invalid parentheses.
It gets hung on
s = Val(Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtLocationNbr])

If I remark s out it doesn't hang on r.........????????????

Private Sub NewLVLControl()
Dim lngShiftSeqID As Long, lngShiftRptgLVLCtlID As Long, s As Long, r As Long

s = Val(Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtLocationNbr])
r = Val(Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![cboSelectedLVLRptgType])

CurrentDb.Execute "INSERT INTO ShiftReportingLVLCtl (LocationID, LVLRptgTypeID) VALUES (" & s & "," & r & ")", dbFailOnError
more code
End Sub

Open in new window

Who is Participating?
I'm betting that you have an expression in the control source of txtLocationNbr.  If that's the case, use the control inside that expression instead.

For instance, if txtLocationNbr had
then you can use:
s = Val(Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![cboLocationNbrMachines].Column(3))
Can you show this value (Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtLocationNbr]) from debugger?
add watch to it or
debug.print Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtLocationNbr]

Syntax seems correct, but you can approve it in query builder
wlwebbAuthor Commented:
Well...... this is interesting........

in the intermediate window I get 2

Got a Runtime error 3201
You cannot add or change a record because a related record is required in table 'Company_Location_DBAs'.

When I open that table the there is a LocationID = 2
wlwebbAuthor Commented:

You are correct......
Control Source for txtLocationNbr is
wlwebbAuthor Commented:
Thank you Irog
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.

All Courses

From novice to tech pro — start learning today.