Boolean value stored in a combobox!

From: Niklas Östergren (niklas.ostergren_at_spray.se)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 15:47:55 +0200

Hi!

I´ll try to explain this as good as possible!

I have a QBF which I let the user fill with values. The result is then
placed in a local table (tblResult). The selected values is also placed in a
local table (tblStorage).

I use the stored values in tblStorage to set some kind of default value next
time the QBF is opened. So it´s possible to see what was selected last time.
In the form (QBF) I use checkboxes to activate (Visible = True/False) the
comboboxes and texboxes where the user can select or type in the valus
needed.

If a checkbox, for a value (lets say chkMemberCard), i NOT checked when the
form is closed then I erase the value for MemberCard in tblStorage. So when
the form is opened next time combobox membercard (cboMemberCard) don´t have
any value.

This is only done if some other value was selected. If NO value at all was
selected I DON´t erase the value in tblStorage.

Since I have several different data types stored in tblStorage I have one
field for each type of data (intValue for integer/long, StringValue for
string data, CurrencyValue for currency and BooleanValue for ...right
boolean values ;-) ).

OK! Not to my Q:
Is it possible to set a boolean field in some sort of Null-state?

The problem I have is that when I reset the value for a boolean value in
tblStorage it becomes 0 (No). So when the form is opend "No" is written in
cboMemberCard instead of an empty string (nothing). Which is not right.
because I want to show the user the last selected criteras and if MemberCard
wasn´t selected I don´t want it to be "No" since "No" actually is a
selection.

The code I have, which doesn´t work the way I want is:
================================================================
Private Sub Form_Unload(Cancel As Integer)
Dim db As Database, rst As Recordset
Dim intNewValue As Integer
Dim curNewValue As Currency
Dim strNewValue As String
Dim strNewDescription As String

' If NO criteria have been selected then don´t do anything.
If Not chkMemberShipType And Not chkValidMemberShipYear And Not
chkMemberShipFee And Not chkRecivedMemberCard _
And Not chkSMCMember And Not chkGender And Not chkSelectAgeOnmember Then
Exit Sub

Set db = Currentdb
Set rst = db.OpenRecordset("tblStorage")
rst.Index = "PrimaryKey"

'***********************************************************************
' If membership card selected then store value in tblStorage
If chkRecivedMemberCard And Not Me.cboRecivedMemberCard = "" Then
    rst.Seek "=", "CriteriaMemberShipCard"

    ' If not found, create the entry.
    If rst.NoMatch Then
        rst.AddNew
        rst![Variable] = "CriteriaMemberShipCard"
        rst![BooleanValue] = Me![cboRecivedMemberCard]
        rst![Description] = "Selected membership card, " &
Me.cboRecivedMemberCard & "."
        rst.Update ' Update the recordset.

    Else

        rst.Edit
        rst![BooleanValue] = Me![cboRecivedMemberCard]
        rst![Description] = "Selected membership card, " &
Me.cboRecivedMemberCard & "."
        rst.Update ' Update the recordset.
    End If
Else
    rst.Seek "=", "CriteriaMemberShipCard"

    ' If NOT selected set value to "" in tblStorage.
    ' If not found, create the entry.
    If rst.NoMatch Then
        rst.AddNew
        rst![Variable] = "CriteriaMemberShipCard"
        rst![BooleanValue] = Null
        rst![Description] = "Selected membership card, " & "" & "."
        rst.Update ' Update the recordset.

    Else

        rst.Edit
        rst![BooleanValue] = Null
        rst![Description] = "Selected membership card, " & "" & "."
        rst.Update ' Update the recordset.
    End If
End If

rst.Close ' Close the recordset.

End Sub
===============================================================
And in the form´s Load event:
=============================================================
Private Sub Form_Load()

'****************************************
' MEMBERSHIP CARD
'****************************************
' Get value for membership card from tblStorage
If SeekStoredCriteria("tblStorage", "BooleanValue",
"CriteriaMemberShipCard") = 0 Or SeekStoredCriteria("tblStorage",
"BooleanValue", "CriteriaMemberShipCard") = -1 Then
    Me.cboRecivedMemberCard = SeekStoredCriteria("tblStorage",
"BooleanValue", "CriteriaMemberShipCard")
End If

End Sub

==============================================================
Code for function SeekStoredCriteria:

=============================================================
Function SeekStoredCriteria(strSQL As String, strField As String,
strSearchString As String) As Integer

'***************************************************************************
****
' Description: To seek stored INTEGER/LONG criteria
'
' Author: Niklas Östergren
' Date: 2004-04-13
' Returns: Value from tblStorage if successfull finding matching
record.
'***************************************************************************
****

Dim db As Database
Dim rec As Recordset
Dim strMsg As String

'Check validity of passed parameters.
' Exit sub presenting Msg if not valid
If strSQL = "" Or strSearchString = "" Then
    strMsg = "SQL-sträng och/eller sträng att söka efter saknas."
    MsgBox strMsg
    Exit Function
Else
    Set db = Currentdb()
    Set rec = db.OpenRecordset(strSQL, dbOpenTable)

    With rec
        rec.Index = "PrimaryKey"

        ' Look for search string
        .Seek "=", strSearchString

        ' If found, get value
        If Not .NoMatch Then
            ' If value <> 0 send value to calling sub else exit function
            If .Fields(strField) <> 0 Then
                SeekStoredCriteria = .Fields(strField)
            End If
        End If

        ' Close the recordset
        .Close
    End With
End If

Set rec = Nothing
Set db = Nothing

End Function
=============================================