DoCmd.SqlRun Insert Record (1 Viewer)

msgt

Registered User.
Local time
Today, 18:50
Joined
Jun 5, 2007
Messages
11
I'm using a front-end Access 2010 form with a back-end MS SQL 2008 database. Using a dialog form, I populate two of three text boxes from another main form. The third dialog box user will input value. I can make a call to the db using a global ADODB.Connection with main form. My objective is to use an INSERT statement to populate three db fields from dialog form:
partno varchar (25)
gap numeric (18,2)
gapdate datetime

What I think I need to do is to some how format the text boxes to the correct datatype of the corresponding db field.

I'm not sure how to do that. I get the following error:
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Please point me in the right direction. Thanks.

Code:
Private Sub CmdUpdateGapbtn_Click()
On Error GoTo Err_Btn_Proceed_Click

Dim dtDate As Date
'Dim gappartno As String

dtDate = Now()
'gappartno = CStr(Me.TextPartno.Value)

DoCmd.RunSQL "Insert into dbo_Gaphistory (dbo_Gaphistory.partno, dbo_Gaphistory.gap, dbo_Gaphistory.gaphistorydate)" _
            & "Values ('" & Me.TextPartno.Value & "', '" & Me.TextGapOld.Value & "', '" & dtDate & "')"

Exit_Btn_Proceed_Click:
    Exit Sub

Err_Btn_Proceed_Click:
    MsgBox Err.Description
    Resume Exit_Btn_Proceed_Click

End Sub
 

DCrake

Remembered
Local time
Today, 22:50
Joined
Jun 8, 2005
Messages
8,632
String need wrapping in single quotes, dates with #'s and numbers with nothing.
 

msgt

Registered User.
Local time
Today, 18:50
Joined
Jun 5, 2007
Messages
11
DCrake

Thanks for pointing me in the right direction. The following is my final result. I left out the date/time stamp by setting the db date/time field to GetDate() function as the default.

Code:
DoCmd.RunSQL "INSERT INTO dbo_Gaphistory (partno, gap)" _
            & "Values ('" & gappartno & "', " & gapnum & ")"
 

Mark_

Longboard on the internet
Local time
Today, 15:50
Joined
Sep 12, 2017
Messages
2,111
Is there any particular reason you are not using a bound form?
With a bound form ACCESS takes care of most of this for you.
 

Users who are viewing this thread

Top Bottom