1

I have a database with a form that was working just fine, however now after I split the Database I'm getting an error:

There is an invalid use of the . (dot) or ! operator or invalid Parentheses.

This is the code targeted by the Debugger

DoCmd.RunSQL ("Insert Into TestingStatistics (BadgeNumber, TestName, College, Instructor, SigninTime, Special) " & _
"Values ('" & Me.txtBadgeNo.Value & "', '" & strTestName & "', '" & strCollege & "', '" & strInstructor & "', Now(), '" & Me.chkSpecial.Value & "');")

When I remove the Me.txtBadgeNo.Value and Me.chkSpecial.Value

"Values (" & strTestName & "', '" & strCollege & "', '" & strInstructor & "', Now());")

I get the error:

Syntax Error (Missing operator) in query expression 'Medicine', 'Medicine, 'Larsen', Now());'.

Again, this worked before I split the database, I need this insert statement to work, what's going on?

Skathix
  • 278
  • 1
  • 3
  • 14
  • 2
    Replace `DoCmd.RunSQL` with `Debug.Print` and update your question with the exact SQL that is failing. – Comintern Aug 08 '16 at 20:41
  • `Debug.Print` isn't showing anything besides the first error – Skathix Aug 08 '16 at 20:50
  • Adding the quotes didn't change the error, though it's a fair point. – Skathix Aug 08 '16 at 20:50
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) – Andre Aug 08 '16 at 21:20
  • I followed How To link. it's now giving me the same error on the strSQL **There is an invalid use of the . (dot) or ! operator or invalid Parentheses.** – Skathix Aug 08 '16 at 21:30
  • This is a head scratcher, there doesn't seem to be anything directly wrong from what I can see, and if you're looking for the specific SQL error you don't see anything glaringly wrong either. – Skathix Aug 08 '16 at 21:34
  • No, the most complicated value is Now() which is Date/Time. – Skathix Aug 08 '16 at 21:41
  • `TestingStatistics` is a table – Skathix Aug 08 '16 at 21:46
  • I mean I'm totally willing to show that to you. I don't have the ability to give you more than Access is giving me. I've followed the directions, if you have better or more suggestions I'm willing. – Skathix Aug 08 '16 at 21:56
  • 1
    Check out this [Access Programmers thread](http://www.access-programmers.co.uk/forums/showthread.php?t=130986) which solved the OP's problem with your exact error. You may have an [Access References Problem](http://www.accessmvp.com/djsteele/AccessReferenceErrors.html) and need to refresh. – Parfait Aug 08 '16 at 22:23
  • Parfait's suggestions sounds good. If it doesn't help, you can narrow the problem down by adding lines `Debug.Print Me.txtBadgeNo.Value` etc. Also try `Debug.Print Me!txtBadgeNo.Value`. – Andre Aug 09 '16 at 09:17
  • [Decompile](http://stackoverflow.com/a/3268188/3820271) may help also. – Andre Aug 09 '16 at 09:20
  • I'm an idiot and have been fighting myself. Close this thread with a dunce cap, I started investigating references, I was opening a form directly while the variables were referencing an unopen form. – Skathix Aug 09 '16 at 14:36

2 Answers2

0

You cannot just delete values; the count must match the count of fields.

Try this:

DoCmd.RunSQL ("Insert Into TestingStatistics (BadgeNumber, TestName, College, Instructor, SigninTime, Special) " & _
"Values (" & Me!txtBadgeNo.Value & ", '" & strTestName & "', '" & strCollege & "', '" & strInstructor & "', Now(), " & Me!chkSpecial.Value & ");")

Edit, to debug:

Dim SQL As String

SQL = _
    "Insert Into TestingStatistics (BadgeNumber, TestName, College, Instructor, SigninTime, Special) " & _
    "Values (" & Me!txtBadgeNo.Value & ", '" & strTestName & "', '" & strCollege & "', '" & strInstructor & "', Now(), " & Me!chkSpecial.Value & ");"

Debug.Print SQL

DoCmd.RunSQL SQL
Gustav
  • 53,498
  • 7
  • 29
  • 55
0

"Values (" & strTestName & "', '" & strCollege & "', '" & strInstructor & "', '" & Now() & "');")

user3598756
  • 28,893
  • 4
  • 18
  • 28