Inserting into a different table from a form

Question

I am trying to make an event when iputing data in a form on access, after the text box looses focus, if the box is not null I want the ID and the value to get stored into another table. After trying with the code below I get "Runtime error 3061 Too few parameters Expected 1". I have checked in debug mode and the values are getting carried over and brought to the string.

Private Sub Consolidate_LostFocus()
Dim queryString As String
queryString = "INSERT INTO [ReportMasterTable]([#], [Notes]) VALUES(" & [#].Value & ", " &   [Consolidate].Value & ")"

If Consolidate.Text <> vbNullString Then

    CurrentDb.Execute (queryString)

End If
End Sub

It's only a small in house DB, we're only using it for a few weeks for some analysis — Steven Ackley
– Steven Ackley, Commented Jan 25, 2013 at 20:50
and if you had used a parameterized query then you would not even be asking this question because there would not be this delimiter problem. Doing it right is doing it right. — Hogan
– Hogan, Commented Jan 25, 2013 at 20:53
Access parameter queries are fairly simple to create and use. The example I gave you will likely run without error as written. Give it a try; discard it if you don't like it. — HansUp
– HansUp, Commented Jan 25, 2013 at 21:07
A parameter query may be an idea, but there are far more problems here that that. This form should probably not include SQL at all, it should be bound to a table or query. In this case, parameters are a red-herring masking real design problems. It is pointing out a cut finger to a person with a broken leg. That is why @Hogan, I did not mention parameters. — Fionnuala
– Fionnuala, Commented Jan 26, 2013 at 9:24
@Remou - I guess we need to agree to disagree; a level of indirection (ie databound) does not change the design that much, but not using parameterized queries or stored procedures has a big impact in terms of maintainability, stability and security. — Hogan
– Hogan, Commented Jan 26, 2013 at 14:48

HansUp · Accepted Answer · 2013-01-25 21:01:04Z

If either the # or the Notes fields in ReportMasterTable is text data type, you must add quotes around the values you attempt to INSERT.

For example, if both fields are text type:

queryString = "INSERT INTO [ReportMasterTable]([#], [Notes])" & vbCrLf & _
    "VALUES ('" & [#].Value & "', '" & [Consolidate].Value & "')"

The situation will be more complicated if either [#].Value or [Consolidate].Value contains a single quote. You could double up the single quotes within the inserted values. However it might be easier to just switch to a parameterized query ... the quoting problem would go away.

Dim db As DAO.database
Dim qdf As DAO.QueryDef
Dim strInsert As String
strInsert = "PARAMETERS hash_sign Text (255), note_value Text (255);" & vbCrLf & _
    "INSERT INTO [ReportMasterTable]([#], [Notes])" & vbCrLf & _
    "VALUES (hash_sign, note_value)"
Set db = CurrentDb
Set qdf = db.CreateQueryDef("", strInsert)
qdf.Parameters("hash_sign").value = Me.[#]
qdf.Parameters("note_value").value = Me.[consolidate]
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing

You could also save the SQL statement as a named query and open and execute that instead of rebuilding the statement every time your procedure runs.

Fionnuala · Accepted Answer · 2013-01-25 20:34:05Z

1

Is there any reason why you do not wish ti bind the form to the ReportMasterTable?

If you really have a control and field called #, you are facing a world of trouble.

If you have bound the form to ReportMasterTable and are also updating in a query, you are going to run into problems.

The lost focus event is a very bad event to choose, any time anyone tabs through the form, the code will run. After update would be better.

You are updating a text data type, but you have not used quotes.

"INSERT INTO [ReportMasterTable]([#], [Notes]) VALUES(" & [#].Value _
    & ", '" &   [Consolidate].Value & "')"

answered Jan 25, 2013 at 20:34

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

3 Comments

Steven Ackley Over a year ago

I didn't make the names, a co-worker did. I have to work with what I have

Fionnuala Over a year ago

I am not blaming you, but these particular names are delimiters, so I would strongly advise you to change them as soon as possible, even if you just use a query and alias for now.

Steven Ackley Over a year ago

Thank you I plan on changing them. This is what happens when your PM designs your db lol

Collectives™ on Stack Overflow

Inserting into a different table from a form

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related