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

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

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

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

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

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.

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.

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.

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

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.