Insert Form data into a table 3

[jpl458]

Have a form w with 40 textboxes and 40 lables and are labled Bay1 - Bay40 T1 - T40. I need to put that data into an ACCESS table On Click event. I keep getting Expected End of statement. The access table is SaveCounts and has three columns - ID(autonumber) Bayno and Count. Here is the code:

Private Sub ExitBtn_Click()

Dim I As Integer
I = 1
For I = 1 To 40
INSERT INTO SaveCounts([Bayno], [Count])
Values(me.controls("Bay"&I),Me.Controls("T"&I))
Next I


End Sub

Thanks in advance.

jpl

 

[PHV]

Replace this:
INSERT INTO SaveCounts([Bayno], [Count])
Values(me.controls("Bay"&I),Me.Controls("T"&I))
with this:
DoCmd.RunSQL "INSERT INTO SaveCounts(Bayno,[Count]) VALUES(" & Me.Controls("Bay"&I) & "," & Me.Controls("T"&I) & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

PHV - Ran your suggested code and got "Object doesn't support this property or method". Looked around and tried this variation

DoCmd.RunSQL "INSERT INTO SaveCounts (Bayo,[Count]) " & _
"VALUES (""" & Me.Controls("Bay" & I) & """, """ & Me.Controls("T" & I) & """)",

and get the same error.

Thanks for your time

jpl

 

[randy700]

DoCmd.RunSQL "INSERT INTO SaveCounts ([highlight yellow]Bayo[/highlight],[Count]) "

Typo or source of problem?


Randy

 

[jpl458]

Corrected the typo, but get the same error. Object does't support this property or method.
jpl

 

[PHV]

Which object and which property or method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

Try something like

Dim SQL As String
SQL = "INSERT INTO SaveCounts (Bayo,[Count]) " & _
"VALUES (""" & Me.Controls("Bay" & I) & """, """ & Me.Controls("T" & I) & """)",
Debug.Print SQL
DoCmd.RunSQL SQL

Put a breakpoint on the DoCmd line and look at the SQL in the immediate window (Ctrl-G). I suspect that you are having problems with the double quotes and the SQL isn't resolving correctly.

Also in the Immediate Window you can try

[blue]? Me.Controls("Bay" & I)[/blue]

and see if it raises an error or returns a correct value.

 

[jpl458]

It bombs on the"
SQL = "INSERT INTO SaveCounts (Bayo,[Count]) " & _
"VALUES (""" & Me.Controls("Bay" & I) & """, """ & Me.Controls("T" & I) & """)"
With "Object doesn't support this property or method".

Me thinks its in the (""" & Me.Controls("Bay" & I) & """, clause. Ive tried various numbers of quotes around the term Bay, but can't get it to work. If I put double quotes I get 'Expected:List serarator or )'

Thanks for everyones time.

jpl

 

[Golom]

Try single quotes

SQL = "INSERT INTO SaveCounts (Bayo,[Count]) " & _
"VALUES ('" & Me.Controls("Bay" & I) & "', '" & Me.Controls("T" & I) & "')"

I suspect that having "Bay" inside a string that is itself enclosed in double quotes is screwing you up.

 

[jpl458]

Still no dice. (Had to change name of ACCESS table to BallsAdd, and correct typo of Bayo to BayNo)
Ran it as a single line:

SQL = "INSERT INTO BallsAdd (Bayno,[Count]) " & "VALUES ('" & Me.Controls("Bay" & I) & "', '" & Me.Controls("T" & I) & "')"

As soon as it gets to this statement Get Object does not support this property or method- but doesn't indicate which object, property or method.
Very frustrating.

jpl

 

[PHV]

Are the BayX and Tx controls on the same form as the ExitBtn button ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Yes, the exit Button is on the same form.

But I tried this trying to break it down into pieces:
Dim I As Integer
I = 1
For I = 1 To 40
Dim SQL As String
SQL = "INSERT INTO BallsAdd (Bayno) values(1)"
DoCmd.RunSQL SQL
Next I
And it put a 1 in each of the rows in the table (Bayno is a Text field in the table)
Then I tried this

Dim I As Integer
I = 1
For I = 1 To 40
Dim SQL As String
SQL = "INSERT INTO BallsAdd (Count) values(I)"
DoCmd.RunSQL SQL
Next I

But I could never get the value in I to be put into the table, and tried it with quotes, but then just got the I in the table.

Just trying to take data from a form and put it in a table.

Thanks for the help

jpl

 

[PHV]

Seems like the controls have different names than Bay1 .. Bay40 or T1 .. T40

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OMG, you talked about labels !
Hopefully the labels are independant of the textboxes.
SQL = "INSERT INTO BallsAdd (Bayno,[Count]) VALUES ('" & Me.Controls("Bay" & I) & "', '" & Me.Controls("T" & I)[!].Caption[/!] & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

I have simplified the problem based on PHVs last note. All I need is to have the value that is in Me.Controls("T"&I) put into the table. I get the name by positional notation because I have an autonumber in the table that will give me the Bay number. I deleted the Bayno Field altogether.
I have this,which I think is close, but cant get the punctuation right.

For I = 1 To 40
Dim SQL As String
SQL = "INSERT INTO BallsAdd (Count) "VALUES (Me.Controls("T" & I))"
DoCmd.RunSQL SQL
Next I

 

[PHV]

SQL = "INSERT INTO BallsAdd ([Count]) VALUES ('" & Me.Controls("T" & I) & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Bingo. Thanks a ton. Really appreciate the help

jpl