Inserting Option Button Group Values into SQL server

[msgt]

The problem I am having is inserting the option button group value into SQL server table. Below is the code. The problem area is under comment 'Insert a new record and create a new query for CPA Table.
I'm not that savvy with programming. Any help will be appreciated. Thanks.

Private Sub Continue1_button_Click()
On Error GoTo Err_Continue1_button_Click

Dim stDocName As String
Dim stLinkCriteria As String

'Validates Option Buttons have been chosen
If Me.Frame1.Value = 0 Then

MsgBox "Please recheck answer(s) to ensure option button was selected."
'Me.Frame1.BackColor = RGB(255, 0, 0)
Exit Sub
End If

'Insert a new record and create a new query for CPA Table
DoCmd.RunSQL "Insert into CPA (Q1) values ( Me.Frame1 );"
strSQL = "Select Q1 from CPA where ID = " & GBL_ID & " ;"

stDocName = "CPA_form_2"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Continue1_button_Click:
Exit Sub

Err_Continue1_button_Click:
MsgBox Err.Description
Resume Exit_Continue1_button_Click

End Sub

 

[pbaldy]

CurrentDb.Execute "Insert into CPA (Q1) values (" & Me.Frame1 & ")"

 

[msgt]

Thanks Paul. I changed the code as suggested. While hitting F8 I can follow the process until a I get an error message:
Object variable or with block variable not set.

What am I doing wrong? It's got to be so simple.

-----------------------------------------

Private Sub Continue1_button_Click()
On Error GoTo Err_Continue1_button_Click

Dim stDocName As String
Dim stLinkCriteria As String

'Validates Option Buttons have been chosen
If Me.Frame1.Value = 0 Then

MsgBox "Please recheck answer(s) to ensure option button was selected."
'Me.Frame1.BackColor = RGB(255, 0, 0)
Exit Sub
End If

'Insert a new record and create a new query for CPA Table
CurrentDb.Execute "Insert into CPA (Q1) values (" & Me.Frame1 & ");"
strSQL = "Select Q1 from CPA where ID = " & GBL_ID & " ;"


stDocName = "CPA_form_2"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Continue1_button_Click:
Exit Sub

Err_Continue1_button_Click:
MsgBox Err.Description
Resume Exit_Continue1_button_Click

End Sub

 

[pbaldy]

On what line? I notice you don't declare strSQL.

 

[msgt]

This is what is going on now. I got the global variable to transfer to CPA form. I found a typo in the Global Module. The following is code from CPA form 1. I get a syntax error near the where clause. While hitting F8 key I can follow the variable values through the code and everything is OK. What am I doing wrong. I'm struggling but making some profress. Thanks for the help.

DoCmd.RunSQL "Insert into CPA (Q1) values (" & Me.Frame1 & ") where ID = " & GBL_ID & ");"

-------------------------------------------------------

Private Sub Continue1_button_Click()
On Error GoTo Err_Continue1_button_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim strSQL As String

'Validates Option Buttons have been chosen
If Me.Frame1.Value = 0 Then

MsgBox "Please recheck answer(s) to ensure option button was selected."
'Me.Frame1.BackColor = RGB(255, 0, 0)
Exit Sub
End If

'Insert a new record and create a new query for CPA Table
DoCmd.RunSQL "Insert into CPA (Q1) values (" & Me.Frame1 & ") where ID = " & GBL_ID & ");"
strSQL = "Select Q1 from CPA where ID = " & GBL_ID & " ;"

stLinkCriteria = "ID = " & GBL_ID
stDocName = "CPA_form_2"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Continue1_button_Click:
Exit Sub

Err_Continue1_button_Click:
MsgBox Err.Description
Resume Exit_Continue1_button_Click

End Sub

 

[texbender]

try replacing your currentdb.execute with debug.print and then look at was text is being created. Wondering if perhaps you don't have a null value in there somewhere

i.e. your sending: "Insert into CPA (Q1) values ();" to the database

you also might want to code it as follows:

DoCmd.RunSQL "Insert into CPA (Q1) values (" & cstr(Me.Frame1 & "") & ");"

also not quite sure what you're doing with the strSQL string... you don't call it anywhere that I can see.

 

[boblarson]

also not quite sure what you're doing with the strSQL string... you don't call it anywhere that I can see.

I also had that question. You're assigning something to strSQL but never using it; at least in this event.

 

[texbender]

Correct me if I'm wrong, but doesn't DoCmd.RunSQL prompt the user??
you might want to turn off warning prompts around the command

if I recall correctly the syntax is

docmd.setwarnings false
docmd.runsql <your sql here>
docmd.setwarnings true

you will also want to put a docmd.setwarnings true into your error handling code so that it gets turned on again if your sql fails.


I'm using a SQL Server back end so I use stored procedures for these issues, much more efficient.

 

[boblarson]

Correct me if I'm wrong, but doesn't DoCmd.RunSQL prompt the user??
you might want to turn off warning prompts around the command

if I recall correctly the syntax is

docmd.setwarnings false
docmd.runsql <your sql here>
docmd.setwarnings true

you will also want to put a docmd.setwarnings true into your error handling code so that it gets turned on again if your sql fails.


I'm using a SQL Server back end so I use stored procedures for these issues, much more efficient.

Don't use the setwarnings - it's not necessary if you use

CurrentDb.Execute strSQL

But, if it is a SELECT query there are no messages. If it is an action query then you can avoid the messages by using that CurrentDb.Execute

 

[msgt]

Thanks for the help guys. I was focused on the Insert statement when I should have been using an Update Statement. In the previous form I used the Insert statement to create a record with the ID field. When the Proceed button was clicked, the subsequent form CPA form 1 needed an UPdate statement. Next step is to cleanup the residual code and do more testing.