INSERT Statement in Form

[jera]

Hi guys,

I've got a form that saves construction sites into a table "Baustelle" in a database where I can also save the customer, address, project leader and construction leader of the construction site all in different tables. I therefore only save the IDs of the fields described in the table of the actual construction site.

When clicking the "save construction site" button, four records are saved in tables:
The Address in the Adresse table, customer in the Kunde table, project leader in the Projektleitung table and the construction leader in the Bauleitung table.

The last record I want to save is the construction site itself. Therefore, I created the INSERT statement

insert = "INSERT INTO Baustelle " & _
"(Projektname, Kunde, Adresse, Auftragsnummer, Division, Projektleitung, Bauleitung, Baustellenstart, voraussichtlichesEnde, erledigt) VALUES " & _
"('" & Projektname.Value & "'," & KundeID & "," & AdresseID & ",'" & Auftragsnummer.Value & "','" & Division.Value & "'," & ProjektleiterID & _
"," & BauleiterID & ",'#" & Baustellenstart.Value & "#','#" & voraussichtlichesEnde.Value & "#', " & False & ")"
    db.Execute (insert)

And every time I get a runtime error 3061: Too few arguments. Expected 1.

I believe to know that there's something wrong with the statement but I re-re-re-checked the spelling, field types and such obvious things but somehow I still get the error.

Has anyone any ideas?

 

[pbaldy]

See if this helps:

http://www.baldyweb.com/ImmediateWindow.htm

If you don't spot the problem, post the SQL here.

 

[jera]

Thanks for the quick reply!

I already looked at the statement to be executed. I even watched every single variable I put into the statement for it has the right field type. It looks fine to me ..
The only issue I could think of is inserting the dates into the fields but I tried several different versions (without ', with '#, with #) and it all seems to get back to either 'date' or '#date#' but it still produced to error ..

Anyway here is the statement

INSERT INTO Baustelle (Projektname, Kunde, Adresse, Auftragsnummer, Division, Projektleitung,
Bauleitung, Baustellenstart, voraussichtlichesEnde, erledigt)
VALUES ('Test',41,42,'Test','I DT',50,46,'#08.01.2013#','#08.01.2013#', Falsch)

 

[pbaldy]

If the fields are date/time, they should only have the # around them; no '.

 

[jera]

When I delete the ' I get the error message syntax error in statement blabla 08.01.2013 ..

 

[JANR]

Try:

insert = "INSERT INTO Baustelle " & _
"(Projektname, Kunde, Adresse, Auftragsnummer, Division, Projektleitung, Bauleitung, Baustellenstart, voraussichtlichesEnde, erledigt) VALUES " & _
"('" & Projektname.Value & "'," & KundeID & "," & AdresseID & ",'" & Auftragsnummer.Value & "','" & Division.Value & "'," & ProjektleiterID & _
"," & BauleiterID & "," & [COLOR=red]Format(Baustellenstart,"\#mm\/dd\/yyyy\#")[/COLOR] & "," & [COLOR=red]Format(voraussichtlichesEnde,"\#mm\/dd\/yyyy\#")[/COLOR] & "," & False & ")"
    db.Execute (insert)

More on why here: http://allenbrowne.com/ser-36.html

ps. No need to use .Value as it is the default property.

JR

 

[jera]

Thank your for that, date works fine now!

INSERT INTO Baustelle (Projektname, Kunde, Adresse, Auftragsnummer, Division, Projektleitung, Bauleitung, Baustellenstart, voraussichtlichesEnde, erledigt)
VALUES ('Test',41,42,'Test','I DT',50,46,#01/09/2013#,#01/09/2013#, Falsch)

However, I still kept getting the too few arguments error.
Being frustrated as hell I changed my table design so that none of the entries is mandatory and split the statement into 10 single statements and tested which of them threw the error.
It was the last of the 10 statements! Where I define the construction site as "not finished" via a boolean field.
I started developing the database in an english version of Access 2007 and for final testing reasons switched to a german version of Access 2003.
Obviously, and unfortunately, it is not ok with the german Access 2003 to set a boolean field to "False" which I find is ridiculous but anyway ..

So now the next question: Is there a way to fill a boolean field so that it works in both language versions or should I just work around the problem?

Thanks for the help with the putative problem!

 

[pbaldy]

You can use -1 for True and 0 for False. That's how Access stores Boolean values.

 

[jera]

Works fine now, thanks so much!

 

[pbaldy]

Happy to help!

 

[JANR]

Glad you got it to work, there is another way where you don't have to worry about delimiters and regionalsettings. You can create a temporary QueryDef and execute it

ex:

With CurrentDb.CreateQueryDef("", "Insert Into MyTable (MyNum,MyText,MyDate,MyBool) " & _
                                  "Values(p0,p1,p2,p3)")
    .Parameters(0) = Me.tNum
    .Parameters(1) = Me.tText
    .Parameters(2) = Me.tDate
    .Parameters(3) = Me.tBool
    .Execute
    .Close
End With

JR