InsertInto when datatypes do not match

[shane]

I'm using the following code

Private Sub shift_AfterUpdate()
dim update key as string
updatekey = "insertinto me.foreignkey" & "values('" & Me.pdate&
'"&'"&me.shift&"')"
DoCmd.RunSQL updatekey
End Sub

pdate is formatted as a date
shift is formatted as text
foreignkey is formatted as text

How can I make it so that I do not get a datatype error?

Thanks in advanced.

 

[Stefan Hoffmann]

hi Shane,

Private Sub shift_AfterUpdate()
dim update key as string
updatekey = "insertinto me.foreignkey" & "values('" & Me.pdate&
'"&'"&me.shift&"')"
DoCmd.RunSQL updatekey
End Sub

You should use copy'n'paste..

pdate is formatted as a date
shift is formatted as text
foreignkey is formatted as text
How can I make it so that I do not get a datatype error?

...otherwise this is not the only error you get.

Private Sub shift_AfterUpdate()

Dim SQL As String

SQL = "INSERT INTO ForeignKey " & _
"VALUES (" & _
SQLDate(Me.pdate) & ", '" & Replace(Me.Shift, "'", "''") & "')"
CurrentDb.Execute SQL, dbFailOnError

End Sub

ForeignKey must be correct table name.

For SQLDate see:

http://allenbrowne.com/ser-36.html


mfG
--> stefan <--

 

[shane]

Thanks Stefan.

I pasted the code you responded with along with the SQLDate function from
Mr. Browne. I did replace "foreignkey" with the table name "maintenance".

When I update shift on the form I get the following error:

Run Time Error 3346

Number of query values and destination fields are not the same.

 

[Stefan Hoffmann]

hi Shane,

I pasted the code you responded with along with the SQLDate function from
Mr. Browne. I did replace "foreignkey" with the table name "maintenance".

When I update shift on the form I get the following error:

Run Time Error 3346
Number of query values and destination fields are not the same.

Then your table contains more then two fields and you need to specify a
field list for the INSERT INTO command:

SQL = "INSERT INTO Maintenance (field1, field2) " & _ ...


mfG
--> stefan <--

 

[shane]

Maby I wasn't clear, I'm a fairly novice user.

I would like both the date and shift to end up in the same field.

Date = 3/14/09
Shift = A
Foreignkey = 3/14/09A and is updated automatically after a shift is entered

 

[Stefan Hoffmann]

hi Shane,

Maby I wasn't clear, I'm a fairly novice user.

That's true.

I would like both the date and shift to end up in the same field.
Date = 3/14/09
Shift = A
Foreignkey = 3/14/09A and is updated automatically after a shift is entered
on the form.

So, you have a bound form? Then simply use then forms Before Update
event, basically somthing like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me![Foreignkey] = Format(Me![Date], "\#m\/d\/yy\#") & Me![Shift]

End Sub

btw, Date is a reserved word and an used method name. So you should
consider changing it to a more sematically meaningful name.


mfG
--> stefan <--