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

Posted on 2012-09-12
Last Modified: 2012-09-12
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

Question by:wlwebb
    LVL 39

    Expert Comment

    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
    LVL 29

    Accepted Solution

    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))

    Author Comment

    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

    Author Comment


    You are correct......
    Control Source for txtLocationNbr is

    Author Closing Comment

    Thank you Irog

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now