Option Button "Value" [RESOLVED]

[StLMacMan]

I have three option buttons that I am trying to work with in a sql INSERT INTO statement. I name the db field and then where I need to insert the value of the option box, I keep getting an error message either about an undeclared variable or an illegal qualifier. Can someone help me w/ the syntax for an option button value. Cannot find any MS documentation dealing w/ this. I have declared OptCtrtType As Control. Thanks for any help. --Ed

[David.Poundall]

Yuo need to post some code.

[RobDog888]

The option buttons or checkboxes will usually give you this error when you rely on the
default property of the control instead of explicitly stating the property.

"INSERT INTO Table1 VALUES (" & optOption1.Value & ")"

[StLMacMan]

David, I'm still working on the "blank" date problem. Someone suggested using a Null replacement for any date showing "##", so I've been trying to get that to work. I'll try to figure out how to only have the user see a blank when they review the file and there has been a Null entry in the db, but first I have to get it to save the Null.--Ed

Here's the code:

Private Sub CmdSave_Click()
Dim INSERTStmt As String
Dim OptCtrtType As Control
 
Set Con = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Con.Open strCon
    
INSERTStmt = "INSERT INTO Contract (Corps_Institution, Program, Funder, " & _
"Contract_Amount, Contract_Number, Start_Date, End_Date, Contract_Type, " & _
"Application_Submission, Face_Sheet_Completed, Received_in_SS_Dept, " & _
"Presented_to_DFB, Sent_to_THQ, Received_from_THQ, Sent_to_Funder_or_Unit, " & _
"Executed_Copy_received_from_Funder, Executed_Copy_sent_to_THQ_and_Unit, Filed)" & _
"VALUES ('" & CboUnit.Text & "', '" & CboProgram.Text & "', '" & CboFunder.Text & "', " & _
"'" & TxtCtrtDollarAmount.Text & "', '" & TxtCtrtNo.Text & "', #" & TxtCtrtStartDate.Text & "#, " & _
"#" & TxtCtrtEndDate.Text & "#, '" & OptCtrtType.Value & "', " & _
"#" & TxtAppDate.Text & "#, #" & TxtFaceSheetCompleted.Text & "#, #" & TxtReceivedBySSDept.Text & "#, " & _
"#" & TxtSubmittedToDFB.Text & "#, #" & TxtSentToTHQ.Text & "#, #" & TxtReceivedFromTHQ.Text & "#, " & _
"#" & TxtSentToFunder.Text & "#, #" & TxtExecutedCopyReceivedFromFunder.Text & "#, " & _
"#" & TxtExecutedCopySentToTHQ.Text & "#, #" & TxtFiled.Text & "#)"
 
INSERTStmt = Replace(INSERTStmt, "##", "NULL")
 
Con.Execute "INSERT INTO Contract (Corps_Institution, Program, Funder, " & _
"Contract_Amount, Contract_Number, Start_Date, End_Date, Contract_Type, " & _
"Application_Submission, Face_Sheet_Completed, Received_in_SS_Dept, " & _
"Presented_to_DFB, Sent_to_THQ, Received_from_THQ, Sent_to_Funder_or_Unit, " & _
"Executed_Copy_received_from_Funder, Executed_Copy_sent_to_THQ_and_Unit, Filed)" & _
"VALUES ('" & CboUnit.Text & "', '" & CboProgram.Text & "', '" & CboFunder.Text & "', " & _
"'" & TxtCtrtDollarAmount.Text & "', '" & TxtCtrtNo.Text & "', #" & TxtCtrtStartDate.Text & "#, " & _
"#" & TxtCtrtEndDate.Text & "#, '" & OptCtrtType.Value & "', " & _
"#" & TxtAppDate.Text & "#, #" & TxtFaceSheetCompleted.Text & "#, #" & TxtReceivedBySSDept.Text & "#, " & _
"#" & TxtSubmittedToDFB.Text & "#, #" & TxtSentToTHQ.Text & "#, #" & TxtReceivedFromTHQ.Text & "#, " & _
"#" & TxtSentToFunder.Text & "#, #" & TxtExecutedCopyReceivedFromFunder.Text & "#, " & _
"#" & TxtExecutedCopySentToTHQ.Text & "#, #" & TxtFiled.Text & "#)"
 
    Con.Close
    Set Con = Nothing
    
End Sub

[RobDog888]

In order to get it to see a null in a date field you can set the fields required to No and
just dont include it in the insert statement. Passing a Null will be the same as not including
it as long as the required is No.

But a better way may be to include an imediate if statement for each date field that could
contain a null date. Then if its null just return a vbnullstring else return the formatted date
wrapped with "#"'s.?

[StLMacMan]

Rob, actually I'd rather not see the Null. This whole problem arose because I built the form, tested it with complete data, and then recognized that it will be used w/ several date fields empty initially and then filled in periodically over time. I'd like for the blank fields to show up as blank when the user searches for the saved data later. Then they can edit and save again. However, I have been unable to figure out how to save a blank date field to Access, have read and heard from others that it can't be done, so leaving out the empty fields might get me what I need. I can't figure out how to efficiently write conditionals to only include the date fields w/ data in them. There are 10 date fields on the form and only about 3 or 4 will be used initally. Over time they will all be filled in as the events they record transpire. What do you think? --Ed

[RobDog888]

Probably adjusting (if its not already) the date fields to allow zero lenth and so they are
not required. Then in you presentation to the form check for null or "12:00" values and parse
them out before it gets to the textboxes, etc. This will help also if they are doing any updating
on the records too.

[StLMacMan]

Can you tell me what you mean by "parse out" the null value date fields?

[RobDog888]

When you read a record back in for displaying it to the user, the date field will be
"null" but may in fact display a "value" of 12:00 AM. This is sometimes the value
that is returned instead of just the Null youd expect. It has to do with the field
data type being a date/time field.

[StLMacMan]

So if it is saved to the db as Null can I write something that will cause it to return a blank for the user to see when they do a search for the record?

[szlamany]

We use MS SQL SERVER, and allowing dates to be NULL is one of the most frequent uses we make of NULL values in a database.

Date fields that are not permitted to be NULL end up having a value - in SQL it's 01/01/1900. That's a really bad value to have for a "blank" date.

Whenever we get a recordset from the database, we convert all DATES to STRINGS (see the link in my signature for SQL Rules to Live by - you'll see what we do with CONVERT() for dates). We treat all DATES on the VB side as strings - so simply doing RS(column).VALUE & "" will turn a NULL string into an "empty" string - so a text box can retain that value.

Works for us!

[RobDog888]

Yes that is a valid workaround szlamany, but StLMacMan dont forget that you will
need some date validation to make sure it a real date.

[StLMacMan]

Since I'm mandated to use Access 2002, am I correct that I can use its date notation to save the Null and then convert it back to a string and then use & "" to display the blank date on the user's form when he calls the record?

[RobDog888]

You may be better off setting your field's data type to text. Then you can use
the logic mentioned for "" as nulls.

[StLMacMan]

I'm thinking through the date issue, but still cannot get past the syntax problem for the option buttons. Here's my code

Private Sub OptCtrtNew_Click()
Dim CtrtType As String
If OptCtrtNew.Value = True Then
CtrtType = "New"
ElseIf OptCtrtRenewal.Value = True Then
CtrtType = "Renewal"
ElseIf OptCtrtAmendment.Value = True Then
CtrtType = "Amendment"
End If
End Sub

I am then trying to do and INSERT INTO sql string that captures "New," "Renewal," or "Amendment" depending on the button selected. I have tried about a bazillion different attempts at syntax (none of which work) with the lastest being:

Private Sub CmdSave_Click()
Dim INSERTStmt As String
Dim CtrtType As String
Set Con = New ADODB.Connection
    Set rs = New ADODB.Recordset
Con.Open strCon
Con.Execute "INSERT INTO Contract (. . .Contract_Type, . . .)"  & _
"VALUES (. . . '" & CtrtType.Text & "', . . .)" 
 
    Con.Close
    Set Con = Nothing
    
End Sub

Apparently "CtrtType" cannot be a String if it modiefies .text. I've tried Dimming it as an OptionButton and an Object as well, but get either a type mismatch or a With Block or variable not defined message. I don't see what to do to make this work. "Help" did not help. Any human help appreciated. --Ed

[RobDog888]

Option Explicit
 
Dim CtrtType As String
 
Private Sub OptCtrtNew_Click()
    'Dim CtrtType As String
    If OptCtrtNew.Value = True Then
        CtrtType = "New"
    ElseIf OptCtrtRenewal.Value = True Then
        CtrtType = "Renewal"
    ElseIf OptCtrtAmendment.Value = True Then
        CtrtType = "Amendment"
    End If
End Sub
 
Private Sub CmdSave_Click()
    Dim INSERTStmt As String
    'Dim CtrtType As String
    Set Con = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Con.Open strCon
    Con.Execute "INSERT INTO Contract (. . .Contract_Type, . . .)"  & _
"VALUES (. . . '" & CtrtType & "', . . .)" 
 
    Con.Close
    Set Con = Nothing
    
End Sub

You declaring the CtrtType in multiple places, thus your clearing out the value from the opt click event. Make it modular by declaring in the declarations section.

[StLMacMan]

Thanks, Rob. That did the trick on the Option Button value. Now I'm going to focus on the blank date problem. Thanks again--Ed.

[RobDog888]

No prob. Glad to help. It keeps me sharp

ps, dont forget to Resolve your thread