N
Niklas Östergren
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
=============================================
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
=============================================