Insert into table

Question

Insert into table

SSC Enthusiast

Points: 185

September 16, 2004 at 11:34 am

I am running backend SQL2000 and front end Access 2003 ADP. I have a view created on two tables and a Form on the view. Form has some fields and user enter the information and click the save to insert the records to new table. In the form has two date field one system date which is fills automattically when Form opens and second date field user enters the date.

I am able to insert all the records to new table without system date field and date enter by user

This is new table:

Field name Data Type

ID numeric 9 0

Letter_Date datetime 8 0

Dealer_Name nvarchar 50 0

CUDL_APP_NUM nvarchar 50 0

Note text 16 0

Funding bit 1 1

Delay_funding bit 1 1

Return_Date datetime 8 0

Here is code for SAVE button:

Private Sub frmDlrLtrUndo_Click()

Dim strSql As String 'Store the sql text

Dim cn As ADODB.Connection 'Connection object

On Error GoTo ErrMsg

'// storing the values of the text boxes on the ADP form to Table C

'//and making the sql statement and storing in the variable strsql

strSql = "INSERT INTO Letter_Dealer (Letter_date, Dealer_Name, CUDL_APP_NUM, Note, Funding, Delay_funding, Return_date) VALUES (" _

& Format(Trim([Forms]![CUDL Letter]![Letter_Date]), "dd/mm/yyyy") & " ,'" _

& Trim([Forms]![CUDL Letter]![Dealer_Name]) & " ','" _

& Trim([Forms]![CUDL Letter]![CUDL_APP_NUM]) & "','" _

& Trim([Forms]![CUDL Letter]![Note]) & "','" _

& Trim(IIf([Forms]![CUDL Letter]![funding] = True, 1, 0)) & "','" _

& Trim(IIf([Forms]![CUDL Letter]![delay_funding] = True, 1, 0)) & "'," _

& Format(Trim([Forms]![CUDL Letter]![Return_Date]), "dd/mm/yyyy") & ");"

Set cn = CurrentProject.Connection 'Setting the connection object

cn.BeginTrans 'Begin the transaction

cn.Execute strSql, adAffectCurrent 'executing the sqlstatement stored in strsql

cn.CommitTrans 'Commiting the transaction

ErrMsg:

'Trapping the error

If Err.Number > 0 Then

MsgBox Err.Number & ":" & Err.Description, vbCritical

End If

End Sub

Here is the new table after entered some records:

**dbo.Letter_Dealer**
ID	Letter_Date	Dealer_Name	CUDL_APP_NUM	Note	Funding	Delay_funding	Return_Date
1	12:00:00 AM	Folsom Imports	3814943	retrtttttttttttttttttttttttttttttttttt	True	False	1/1/1900
2	12:00:00 AM	Lithia Toyota of Vacaville	1	retrtttttttttttttttttttttttttttttttttt	True	False	1/1/1900
3	12:00:00 AM	1ST CHOICE AUTO	13	retrtttttttttttttttttttttttttttttttttt	True	False	1/1/1900

Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

I'm not entirely sure what your question is, but I'm guessing you're wanting different dates to get through to SQL Server. Have you tried doing a debug.print or a msgbox (or inputbox, so that you can copy it easier) to see what strSQL contains before you execute it? Have you tried copying that string into Query Analyzer to see what SQL will do with it there. Maybe this would help you see where things aren't going according to your plans.

-Chris