insert a variable value using INSERT INTO

[beckyh]

Why doesn't this SQL statement work?

<code>Dim MyDb As Database
Dim MyTable As Recordset
Dim NextCounter As String


Set MyDb = CurrentDb()
Set MyTable = MyDb.OpenRecordset("next_rdd_number")


NextCounter = MyTable("next_number")


GetItemNumber = (NextCounter)
DoCmd.RunSQL "INSERT INTO rdd_drawings_tbl (RDD_Number, Assigned_by, Type, [Item Description]) VALUES (NextCounter, 'Scott McCauley', 'Grinding Wheel', NextCounter);"

Exit Function</code>

 

[PHV]

You don't need recordset at all:
NextCounter = DLookUp("next_number", "next_rdd_number")
DoCmd.RunSQL "INSERT INTO rdd_drawings_tbl (RDD_Number, Assigned_by, Type, [Item Description]) VALUES (" & NextCounter & ", 'Scott McCauley', 'Grinding Wheel', & NextCounter & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[earthandfire]

Try something along the lines of the following:

Dim strSQL as string

strSQL = "INSERT INTO rdd_drawings_tbl ("
strSQL = strSQL & RDD_Number & ", " & Assigned_by & ", "
strSQL = strSQL & etc. etc.


DoCmd.RunSQL strSQL

Hope this helps

 

[beckyh]

PHV - that doesn't quite work. i get a Enter Parameter Value pop up box.

 

[PHV]

What is next_rdd_number (a table, a query) ?
Is next_number a field in next_rdd_number ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckyh]

next_rdd_number is a query, next_number is a field within the query

 

[PHV]

Can you please post the SQL code of next_rdd_number ?
OOps, BTW, a typo in my 1st post:
DoCmd.RunSQL "INSERT INTO rdd_drawings_tbl (RDD_Number, Assigned_by, Type, [Item Description]) VALUES (" & NextCounter & ", 'Scott McCauley', 'Grinding Wheel',[highlight]'"[/highlight] & NextCounter & "[highlight]'[/highlight])"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckyh]

I found that typo. i also needed to apply that to the first next counter with single quotes. Problem is fixed now! Thanks PHV!

 

[beckyh]

PHV - Any idea why I get an error stating,"You can't carry out this action at the present time."

The SQL syntax is correct, but it won't run. I have the default value of a field on my form set to this function() which contains the nextCounter and SQL statement.

 

[beckyh]

when i changed it bacl to the original code with recordset opening, and changed docmd.runsql to mydb.execute....then I do not get the error.