Inserting data into table with vba (1 Viewer)

[JIMB]

Hi, I am new to VBA. Trying to enter data into a table names Signals. The command I'm using is

DoCmd.RunSQL "INSERT INTO signals ([date], sw, c )VALUES (s_date, buyflag, rut);"

It displays a parameter box to enter a value rather than using the variables I have defined.

I'm stumped.





Public Sub VXO()

'Set up the connection, name it cnn1 .
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection

'Declare record set
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Define variables
Dim diff As Single, BuyFlag As Integer
Dim ma19 As Single, ysumix As Single, ysumix49 As Single, yvxo As Single
Dim ma39 As Single, yd2dma As Single
Dim madiff As Single, sumix49ma As Single, sumix2ma As Single, d2dma As Single

Dim total As Single, ysum2dma As Single
Dim calc1 As Single, s_date As Date
Dim calc2 As Single, rut As Single

Dim net As Single
rst.LockType = adLockOptimistic
rst.Open "select * from otc", CurrentProject.Connection

BuyFlag = 0
Do Until rst.EOF


'Calculate moving averages on Summation Ix
sumix49ma = (rst.Fields("sumix") * 0.04) + (sumix49ma * 0.96)
sumix2ma = (rst.Fields("SUMIX") * 0.67) + (sumix2ma * 0.33)
d2dma = (rst.Fields("SUMIX") * 0.5) + (d2dma * 0.5)
rut = rst.Fields("c")


If BuyFlag = 0 Then
GoTo BuyRtn
Else
GoTo SellRtn
End If


BuyRtn:
'Buy Rule 1

If ysumix > ysumix49 _
And yvxo < 38 _
And ysumix > 390 And ysumix < 870 Then _
BuyFlag = 1

'Buy Rule 2

If BuyFlag = 0 Then
If ysumix < 0 Then
BuyFlag = 1
End If
End If

GoTo GetNextRec
SellRtn:
If ysumix < yd2dma _
And ysumix >= -48 And ysumix <= 882 Then _
BuyFlag = 0
GoTo GetNextRec
GetNextRec:
ysumix = rst.Fields("sumix")
ysumix49 = sumix49ma
ysum2dma = sumix2ma
yd2dma = d2dma
' yvxo = rst.Fields("VXO")
s_date = rst.Fields("date")

Debug.Print s_date, BuyFlag
DoCmd.RunSQL "INSERT INTO signals ([date], sw, c )VALUES (s_date, buyflag, rut);"


rst.MoveNext



Loop

rst.Close
Set rst = Nothing


End Sub

 

[Galaxiom]

SQL does not have direct access to VBA variables. They must be concatenated into the string.

If I have understood the variables without actually reading the whole post:

DoCmd.RunSQL "INSERT INTO signals ([date], sw, c )VALUES (" & s_date & ", " & buyflag & ", " & rut & ");"

 

[JIMB]

Thanks so much,
That worked with one exception. The date is being loaded as 12/31/1899, when then dates are 1/2/1991 , 1/3/1991 etc.

Also one other question. Since I am looping through 4000 records it is obviously going to ask me to i want to append. Is there any way to get around this. I come from a transaction based programming background and this is confusing

 

[Galaxiom]

Sorry didn't notice the date datatype. It need date delimiters around the value.

DoCmd.RunSQL "INSERT INTO signals ([date], sw, c )VALUES (#" & s_date & "#, " & buyflag & ", " & rut & ");"

Also note that the date format must be either US (mm/dd/yyyy) or ISO (yyyy/mm/dd).

That date is the Access zero date. Probably because the delimiters were not included. Otherwide it indicates a variable is not being assigned a value.

The insert statement is complete. The variables are the values that will be appended.

 

[Galaxiom]

BTW. Even though US users don't need to reformat the date it is a good idea to always convert the date to as string in either US or ISO format so that the program will work when used in dd/mm/yyyy zones.,

I always use ISO because it is completely unambiguous and stops Access guessing at the intention. Access will automatically convert a value that is valid in an out of zone format and this can lead to really confusing errors.

 

[Ctimson]

Hi guys - I need help, I've wrote this query, however I get a 3134 run time error.

The code:

Private Sub cmdadd_Click()

CurrentDb.Execute "INSERT INTO Crit_Code(crit_code, model/year, launch/series/mixed)VALUES (" & Me.CritCode & ", " & Me.model & ", " & Me.launch & ");"

End Sub

I have tried many different ways, but this is however my latest attempt.

Any help would be appreciated.

 

[mdlueck]

Private Sub cmdadd_Click()

CurrentDb.Execute "INSERT INTO Crit_Code(crit_code, [B][COLOR=Blue]model/year[/COLOR][/B], [B][COLOR=Blue]launch/series/mixed[/COLOR][/B])VALUES (" & Me.CritCode & ", " & Me.model & ", " & Me.launch & ");"

End Sub

Those are some nasty field names! I dare say at the least the database would be griping at the use of the "/" character in the field name.

I would also suggest going the extra mile to use ADO objects instead of CurrentDb.Execute to initiate SQL. I have some example posts the following links:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730 #post1214730

 

[Ctimson]

I have changed it too this now:

Private Sub cmdadd_Click()

CurrentDb.Execute "INSERT INTO Crit_Code(crit_code, model_year, launch_series_mixed) VALUES (" & Me.CritCode & ", " & Me.model & ", " & Me.launch & ");"

End Sub

And I now get a runtime error of 3061 -

I looked at the links, but they just confused me even more !

 

[mdlueck]

I have changed it too this now:

Did you also remember to change the actual column names in the table? The SQL must match up with the actual table it is interfacing with.

 

[Ctimson]

Yes, all table column names are now exactly the same, I have double checked that all spelling is correct for the text boxes also. I however tested it without any data in the text boxes it and comes up with a syntax error in the actual UPDATE INTO statement.

I am using Access 2010, so I don't know if the way you'd write it is different?

 

[mdlueck]

I however tested it without any data in the text boxes it and comes up with a syntax error in the actual UPDATE INTO statement.

This is a simple fix. Empty text fields need a default value supplied for them. I suggest using the following code in a VBA module to safely read the form controls, and support the default value if the field is completely blank:

Safely read form field text controls
http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

 

[mdlueck]

It displays a parameter box to enter a value rather than using the variables I have defined.

This occurs when there is a "speling" mistake between the query and the actual table, with the column name in the parameter popup box.