[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

  • 3
1 Solution
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
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))
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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