Can someone help me out on this lin

[illini]

Can someone help me out on this line of code. I'm trying to get the program to add a new record to the 'temp' table.

I can get it to run without any problems, but it does not add anything to the table. By-the-way, the 'temp' table has a unique record ID (autonumber).

Here's the code:

Dim dbs As Database
Set dbs = CurrentDb

Port_Type = "M"
Date1 = Format(Now, "MM/DD/YY&quot
Date3 = Format(Now, "MM/DD/YY&quot
Eff_Time = Format(Now, "HH:MM&quot
Status = "- Done -"
Comments = z

dbs.Execute "INSERT INTO temp " & _
"(Port_Type, Date_Request, Date_Effective,Effective_Time, Status, Comments) VALUES " & _
"( '" & Port_Type & "','" & Date1 & "','" & Date3 & "','" & Eff_Time & "','" & Status & "','" & Comments & "');" -illini

 

[JohnLowell]

You need to change the Date columns to have # like this:

dbs.Execute "INSERT INTO temp " & _
"(Port_Type, Date_Request, Date_Effective,Effective_Time, Status, Comments) VALUES " & _
"( '" & Port_Type & "',#" & Date1 & "#,#" & Date3 & "#,#" & Eff_Time & "#,'" & Status & "','" & Comments & "');"

 

[illini]

Thanks for the input John. However, the changes you recommended did not change the outcome. The line of code itself is running without any errors. But when I check the 'temp' table afterwards, I do not see the new record.

-illini

 

[Rolliee]

The number of values must correspond to the number of fields identified. It does not look to be so to me.

Here is a test program I ran.

Private Sub cmdDoer_Click()
Dim SQL As String, x1 As String, x2 As String, x3 As String, okay As Boolean

okay = IsNull(Me.txtOne)
okay = okay Or IsNull(Me.txtTwo)
okay = okay Or IsNull(Me.txtThree)


If okay Then Exit Sub

'Or Me.txtTwo = Null Or Me.txtThree = Null Then Exit Sub

x1 = Chr(34) & Me.txtOne & Chr(34)
x2 = Chr(34) & Me.txtTwo & Chr(34)
x3 = Chr(34) & Me.txtThree & Chr(34)

SQL = "INSERT into Tabl1 ( one, two, three)"
SQL = SQL & "VALUES ( " & x1 & "," & x2 & "," & x3 & ""


' MsgBox SQL

DoCmd.RunSQL SQL

Me.txtOne = Null
Me.txtTwo = Null
Me.txtThree = Null

cmdSee_Click

End Sub

It works well.

rollie@bwsys.net