Insert query

[mischa]

Sometime ago some of you helped me figuring out how I should insert and update some values in my db. At the end it functioned perfectly. Recently I deinstalled Access 2013 and installed 2007 because of the unavailibility of certain functions. I dont know if this caused my following issue but Access gives an error (the code that generates the error is bold and underlined). It gives Error code 3061, not enough parameters. The expected number is 1.

I didn't change any of the controls used for this code.

            Dim myDb2 As Database
            Dim rst2 As Recordset
            Set myDb2 = CurrentDb
            [B][U]myDb2.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES( " & (Me.lstLastTImeBuy = "Yes") & "," & Me.txtActionDate & ",'" & Me.txtActionDetails & "') ")[/U][/B]
            Set rst2 = myDb2.OpenRecordset("SELECT  @@Identity as SelectedID")
            SelectedID = rst2.Fields(0)
            myDb2.Execute ("UPDATE tblSubComponent Set ActionID=" & SelectedID & " WHERE SubComponentPN= '" & Me.lstActionSelectPart & "'")
            Set rst2 = Nothing
            Set myDb2 = Nothing

I hope someone is able to help me figure out where it goes wrong.

 

[pr2-eugin]

Is Datatype of LastTimeBuy Yes/No? Why are you performing a comparison there? Did you try debugging using the immediate window? http://www.baldyweb.com/ImmediateWindow.htm

 

[mischa]

Is Datatype of LastTimeBuy Yes/No? Why are you performing a comparison there? Did you try debugging using the immediate window? http://www.baldyweb.com/ImmediateWindow.htm

Yes, LastTimeBuy is a Yes/No boolean datatype field.
The comparison is due to the fact that the combobox returns a Yes or No string.

With the debug code I get the following in the immediate window. All values in the code are correct and all field names are correct. When I try to display it doesn't show the date correctly, the rest is correct. If I put the date between 2 ' then it does show it correctly.

INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES( True,11-1-2014,'------')

Due to the fact that I didn't give any name for the fields, access is having a hard time and changes the code slightly to:

INSERT INTO tblActionsTaken ( LastTimeBuy, ActionDate, ActionDetails )
SELECT True AS Expr1, '11-1-2014' AS Expr2, '------' AS Expr3;

 

[pr2-eugin]

Try this.

myDb2.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES (" & _ 
              (Me.lstLastTImeBuy = "Yes") & ", " & [COLOR=Red][B]Format([/B][/COLOR]Me.txtActionDate[COLOR=Red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR] & ", '" & Me.txtActionDetails & "')")

 

[mischa]

Try this.

myDb2.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES (" & _ 
              (Me.lstLastTImeBuy = "Yes") & ", " & [COLOR=Red][B]Format([/B][/COLOR]Me.txtActionDate[COLOR=Red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR] & ", '" & Me.txtActionDetails & "')")

Thank you Paul!
That worked perfectly for that part of the code.

I found that the part which should return the true or false returns it in Dutch.

" & (Me.lstLastTImeBuy = "Yes") & "

After debugging the following is generated:

INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES( Onwaar,#01/11/2014#,'------')

If I put this into the query builder access asks me what I want with Onwaar (in this case (Onwaar is the translation for False)). If i change it to False than it works perfectly.

Running the code with -1 (or 0) instead of the comparison that I used to generate the true or false gives me the ability to run the code without any further error.

 

[pr2-eugin]

Something like this?

myDb2.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES (" & _ 
              [COLOR=Red][B]IIF([/B][/COLOR]Me.lstLastTImeBuy = "Yes"[COLOR=Red][B], True, False)[/B][/COLOR] & ", " & Format(Me.txtActionDate, "\#mm\/dd\/yyyy\#") & _
              ", '" & Me.txtActionDetails & "')")

 

[mischa]

Something like this?

myDb2.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES (" & _ 
              [COLOR=Red][B]IIF([/B][/COLOR]Me.lstLastTImeBuy = "Yes"[COLOR=Red][B], True, False)[/B][/COLOR] & ", " & Format(Me.txtActionDate, "\#mm\/dd\/yyyy\#") & _
              ", '" & Me.txtActionDetails & "')")

Paul, I would like to thank you again for the quick response .

Unfortunately Access doesn't give a damn about the True/False in the IIF and still shows the dutch language there :banghead::banghead::banghead:.

I am going to hardcode it (because Access doesn't get it yet) with an additional set of If statements and put the boolean value 0 or -1 into the sql. Hopefully that will work.

 

[pr2-eugin]

Then just replace the True with -1 and False with 0 in the above code.

On another hand, are you sure that the ComboBox is returning String Yes and No and not Boolean Yes or No?

 

[mischa]

Just solved it by using the 0 and -1 in the IIF statement.
Thanks!