Button run sql insert into query from form fields

[DaveyEdgar]

I have a query based on two tables with a one to many relationship.

Employees Table
recordid (one side)

Attendance Table
recordid (many side)
type
from
to
returndate
daycount

Attendance query - has all the above fields.

and a form...
Form field/controls (data source is not based on attendance query; the form is based on a different query)
cboType
From
To
returndate
daycount
recordid

I have a button that when clicked will take the data entered into the Form field/controls listed above and enter it into the Attendance query as a new record into the respective fields.

My code...

Private Sub cmdaddatt_Click()
DoCmd.RunSQL "INSERT INTO [qryattendance](type,from,to,returndate,daycount,recordid)VALUES (" & "'" & Me![cbotype] & "'," & "#" & Format(Me![from], "yyyy-mm-dd") & "#," & "#" & Format(Me![to], "yyyy-mm-dd") & "#," & "#" & Format(Me![returndate], "yyyy-mm-dd") & "#," & Me![daycount] & "'," & Me![recordid] & " ',)"
End Sub

The code doesn't work. Can anyone tell me what I did wrong?

 

[PHV]

DoCmd.RunSQL "INSERT INTO [qryattendance](type,from,to,returndate,daycount,recordid)VALUES (" & "'" & Me![cbotype] & "'," & "#" & Format(Me![from], "yyyy-mm-dd") & "#," & "#" & Format(Me![to], "yyyy-mm-dd") & "#," & "#" & Format(Me![returndate], "yyyy-mm-dd") & "#," & Me![daycount] & [tt]"[!],'[/!]" & Me![recordid] & "[!]')[/!]"[/tt]

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

 

[Remou]

At a guess, it seems to have gone a little astray in the end:
[tt]DoCmd.RunSQL "INSERT INTO [qryattendance](type,from,to,returndate,daycount,recordid)VALUES (" & "'" & Me![cbotype] & "'," & "#" & Format(Me![from], "yyyy-mm-dd") & "#," & "#" & Format(Me![to], "yyyy-mm-dd") & "#," & "#" & Format(Me![returndate], "yyyy-mm-dd") & "#," & Me![daycount] [blue]& ",'" & Me![recordid] & "')"[/blue][/tt]

 

[DaveyEdgar]

Great thanks all

I tried it today and I'm still getting the debug error saying there is something wrong with the sql syntax but it doesn't specify. The code is highlighted yellow in the debugger but I don't know specifically what is wrong. I'm sorry I can't give you more specific information.

Do the date fields need to agree in format or input mask? I do not have those specified in the underlying table.

Would the sql work without specifying the date format in the string?

Thanks for your help

 

[Remou]

I think Jet is happiest with "mm-dd-yyyy". You have RecordID as text, is that right?

 

[DaveyEdgar]

ah no its not it is an autonumber. How would I adjust the syntax?

 

[Remou]

If it is an autonumber, there is no need to include it.
[tt]DoCmd.RunSQL "INSERT INTO [qryattendance](type,from,to,returndate,daycount)VALUES (" & "'" & Me![cbotype] & "'," & "#" & Format(Me![from], "yyyy-mm-dd") & "#," & "#" & Format(Me![to], "yyyy-mm-dd") & "#," & "#" & Format(Me![returndate], "yyyy-mm-dd") & "#," & Me![daycount] & " )"[/tt]

 

[DaveyEdgar]

oops my bad it is a number field, and I need to have it since this field is the primary key of the parent table.
What would be the syntax if I were to keep the recordid and format it as a number field in the sql string?

 

[Remou]

Just leave out the quotes:
[tt]DoCmd.RunSQL "INSERT INTO [qryattendance](type,from,to,returndate,daycount,recordid)VALUES (" & "'" & Me![cbotype] & "'," & "#" & Format(Me![from], "yyyy-mm-dd") & "#," & "#" & Format(Me![to], "yyyy-mm-dd") & "#," & "#" & Format(Me![returndate], "yyyy-mm-dd") & "#," & Me![daycount] & "," & Me![recordid] & " )"[/tt]

 

[JerryKlmns]

DoCmd.RunSQL "INSERT INTO [qryattendance](type, from, to, returndate, daycount, recordid)[COLOR=red yellow] [/color]VALUES ('" & Me![cbotype] & "', #" & Format(Me![from], "yyyy-mm-dd") & "#, #" & Format(Me![to], "yyyy-mm-dd") & "#, #" & Format(Me![returndate], "yyyy-mm-dd") & "#, " & Me![daycount] & ", " & Me![recordid] & ")"

Remou,

A >5k stars general poster, has proven to many (me included) that yyyy-mm-dd is "near" ANSI standard, if I express that correctly.

--------
Hint:
You could find him everywhere! Even in this one

 

[Remou]

JerryKlmns
I am sure the statement you make regarding dates is true, however, Microsoft says:

Use International Date Formats in SQL Statements [Access 2003 VBA Language Reference]
You must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.

http://msdn.microsoft.com/library/d...11/html/achowInternationalDate_HV05188229.asp

Which is why I made the comment that I thought it preferred American format.

 

[DaveyEdgar]

great thanks for all the help I appreciate it!