Question Add new record via VB

[vexatu]

Hi,

I have a database with one Table and one Form.
The table "Table1" fields: ID (AutoNumber), Date (Date/Time), Mark (Number)
In Form "Form1" is a button. When I press the button I want to add a new record in table "Table1" with these values: "26-Aug-2009" and "21" for Date and Mark.

Currently I have this code for onClick event:

Private Sub Command2_Click()
Dim strSQL1 As String

strSQL1 = "insert into Table1 (Date, Mark) Values ('26-Aug-2009', '22')"

DoCmd.RunSQL strSQL1
End Sub

But I get an error: "Syntax error in INSERT INTO statement". Why ? Because of field ID which is AutoNumber type ?

 

[Dennisk]

Firstly never use a reserved word as a column (Date is a reserved word)

Dates are bracketed by #, numbers by nothing, Strings by ", unless the string is in a string then you can use a singler quote '.

you are using T SQl notation and not Jet SQL notation

 

[vexatu]

Thank you. It works fine

Private Sub Command2_Click()
Dim strSQL1 As String

strSQL1 = "insert into Table1 (Calendar, Mark) Values (#29-Aug-2009#, 24)"

DoCmd.RunSQL strSQL1
End Sub

When I press the button, it records a new value in the Table1. Good for now!
But before record it, a confirming message encounter me: You are about to append 1 row(s). Once you click Yes, you can't use the Undo command to reverse the changes. Are you sure you want to append the selected rows ?
How could I pass over this msg, to auto submit it without confirming. I don't want to click Yes every time.

 

[irish634]

Thank you. It works fine

Private Sub Command2_Click()
Dim strSQL1 As String

strSQL1 = "insert into Table1 (Calendar, Mark) Values (#29-Aug-2009#, 24)"

DoCmd.RunSQL strSQL1
End Sub

When I press the button, it records a new value in the Table1. Good for now!
But before record it, a confirming message encounter me: You are about to append 1 row(s). Once you click Yes, you can't use the Undo command to reverse the changes. Are you sure you want to append the selected rows ?
How could I pass over this msg, to auto submit it without confirming. I don't want to click Yes every time.

To get rid of the message disable the warnings:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.SetWarnings True

 

[vexatu]

Very good.

Now I have another problem.
Code:

Private Sub cmdGradeTest_Click()
    Dim strSQL1 As String

    strSQL1 = "insert into Examene (CNP, DataExamen, Punctaj) Values (1900213100195, #2/13/2009#, 26)"

    DoCmd.RunSQL strSQL1
End Sub

With above code, it is added a new record in table "Examene" with those values.

But with this code:

Private Sub cmdGradeTest_Click()

    Dim cod_numeric As String
    Dim data As String
    Dim corecte As String
    Dim strSQL1 As String
    
    cod_numeric = [Forms]![TestSetup].[txtName] ' it's = 1900213100195 
    data = [Forms]![TestSetup].[txtDate] ' = 2/13/2009
    corecte = DSum("[Correct]", "Test Scoring") ' = 26
    strSQL1 = "insert into Examene (CNP, DataExamen, Punctaj) Values ('cod_numeric', 'data', 'corecte')"

    DoCmd.RunSQL strSQL1

End Sub

I get an error: attach
Theoretically both are equivalent, right ? The difference is the 1st get the values manually, 2nd get the values from 3 variables.

 

[vexatu]

Any help please ?

 

[boblarson]

Unless you are storing to text fields in the table (which I don't think is true based on your first code sample that you said works), change your code to this:

Private Sub cmdGradeTest_Click()

    Dim cod_numeric As [COLOR="Red"]Long[/COLOR]
    Dim data As [COLOR="Red"]Date[/COLOR]
    Dim corecte As [COLOR="red"]Integer[/COLOR]
    Dim strSQL1 As String
    
    cod_numeric = [Forms]![TestSetup].[txtName] 
    data = [Forms]![TestSetup].[txtDate] 
    corecte = DSum("[Correct]", "Test Scoring") 
    strSQL1 = "insert into Examene (CNP, DataExamen, Punctaj) Values ([COLOR="red"]" &[/COLOR] cod_numeric [COLOR="red"]& ",#" &[/COLOR] data [COLOR="red"]& "#," &[/COLOR] corecte [COLOR="red"]& "[/COLOR])"

    DoCmd.RunSQL strSQL1

End Sub

 

[vexatu]

As you said, boblarson. The BIG meesage has come: A text box saying: Overflow.

Solve it.

I changed

Dim cod_numeric As String

because the field CNP where is stored cod_numeric has Text as datatype, because on Integer datatype i can't store big numbers like: 1900213100195

Ty. It was helpful.