Remember last Value in ComboBox

U

Utopian

Hello!

I have a ComboBox, and I need that remember the last value chosen; remain this value ever by default.

The values are taken from a table with ID(autonumber) and Description(text)

Thanks!

Me.-
 
T

Tom Wickerath

Hi Utopian,

General plan of attack:
Try writing the ID value to a single-record table. Then read this value from
the table whenever you open the form. In the Form_Current event procedure,
set the selected record to the ID value.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello!

I have a ComboBox, and I need that remember the last value chosen; remain
this value ever by default.

The values are taken from a table with ID(autonumber) and Description(text)

Thanks!

Me
 
T

Tom Wickerath

Karl,

You don't construct a "single record table". You create a table with at
least one field. Use two fields if you have additional default values to
store: one that describes the setting, and one that saves the value. This
table only needs to use one record for each default value that one wishes to
save. When one wants to update the last saved value, they update the same
record rather than adding a new record. When they want to read the default
value, they read the same exact record.

Alternatively, you can use the GetSetting function and SaveSetting statement
to read and write these values to the registry. Or, you can create a text
file and use that to read and write the values to (default.ini).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

How do you construct a "single record table?"
Karl
__________________________________________

:

Hi Utopian,

General plan of attack:
Try writing the ID value to a single-record table. Then read this value from
the table whenever you open the form. In the Form_Current event procedure,
set the selected record to the ID value.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello!

I have a ComboBox, and I need that remember the last value chosen; remain
this value ever by default.

The values are taken from a table with ID(autonumber) and Description(text)

Thanks!

Me
 
K

Karl H

Hi Tom, Thanks for responding. I'm not quite getting it just yet. I created
a two field table for this option group to select center printing or
left-aligned printing. The table's first column is for center or left and
the corresponding values are 1 or 2.

I used the control for the option group as this table, then for the
"current form" event, I have:

'Sets print option to the last one selected by user.
Private Sub Form_Current()
Me.frmPrintOptions.Value = Tables.tblPrintOptions.Column(1)
End Sub

I get a bug that says "tables" not a defined variable.
Can you tell me what I'm doing wrong?

Thanks again,
Karl..Across the water from you in Poulsbo
 
T

Tom Wickerath

Hi Karl,

Small world. Poulsbo is a very beautiful place! I've been there a few times.
I get a bug that says "tables" not a defined variable.
Click to expand...
You cannot refer to the value in a table like that. The form you used is
similar to how one refers to the combo or list box in an open form, but it
doesn't work for tables.

For this example, I created the following table with two fields:

tblUserPreferences
pkDefaultDesc Text (set as Primary Key)
DefaultValue Text

I chose Text for the DefaultValue, so that one could store text values
easily. You can store numeric values that are saved as text as well. In table
preview, I entered the following:

pkDefaultDesc DefaultValue
PrintAlignment 1

I added an unbound frame control with two option buttons to a form. I named
the frame "fraPrintOptions" (without the quotes). The option button with
label "Center" has an option value of 1, and the option button with label
"Left" has an option value of 2.

I added the following two procedures to the code module associated with this
form. I used the Form_Open event procedure instead of Form_Current as you
indicated:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

' Read the last value chosen by the user
Me.fraPrintOptions.Value = CInt(Nz(DLookup("DefaultValue", _
"tblUserPreferences", "pkDefaultDesc = 'PrintAlignment'"), 1))
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Open event procedure..."
Resume ExitProc
End Sub

Private Sub Form_Close()
On Error GoTo ProcError

' Save the last value chosen by the user
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()
strSQL = "UPDATE tblUserPreferences SET DefaultValue = '" _
& CStr(Me.fraPrintOptions) & "' " _
& "WHERE pkDefaultDesc = 'PrintAlignment';"

db.Execute strSQL, dbFailOnError

ExitProc:
On Error Resume Next 'Cleanup
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Close..."
Resume ExitProc
End Sub


Notes:
If you are missing Option Explicit from new code modules, I recommend that
you make a change to your VBA editor settings so that you will get this most
important line in all new modules. Here is a gem tip that discusses this more
fully:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

2.) The tblUserPreferences table can be used to add any number of settings
that you wish to save for the user. Just use unique pkDefaultDesc values. For
example, you could use "NameOfFormPrintAlignment" instead of just
"PrintAlignment" if you want to store default values for a number of
different forms, where NameOfForm is equal to the name of the form. You'd
need to make a similar adjustment in the DLookup function to refer to the new
value.

3.) The method I used to retrieve the value uses a domain aggregrate
function named DLookup. This is a fairly easy method to implement, but it may
not be the most efficient, especially if your database is a split multi-user
database. An alternate method of reading the value from the table can be
written using either DAO or ADO code.

4.) The Nz function is used to convert a null to a pre-defined value (1 in
my example above). The CInt function is used to convert the looked up string
into a number. (DLookup returns a string). The CStr function is used to
convert the number into a string.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Tom, Thanks for responding. I'm not quite getting it just yet. I created
a two field table for this option group to select center printing or
left-aligned printing. The table's first column is for center or left and
the corresponding values are 1 or 2.

I used the control for the option group as this table, then for the
"current form" event, I have:

'Sets print option to the last one selected by user.
Private Sub Form_Current()
Me.frmPrintOptions.Value = Tables.tblPrintOptions.Column(1)
End Sub

I get a bug that says "tables" not a defined variable.
Can you tell me what I'm doing wrong?

Thanks again,
Karl..Across the water from you in Poulsbo
 
T

Tom Wickerath

Hi Karl,

I forgot to mention that you will need to set a reference to the "Microsoft
DAO 3.6 Object Library" in order to get the code in Form_Close to compile. If
you are using Access 97, then the correct version is the Microsoft DAO 3.51
Object Library.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Karl,

Small world. Poulsbo is a very beautiful place! I've been there a few times.
I get a bug that says "tables" not a defined variable.
Click to expand...
You cannot refer to the value in a table like that. The form you used is
similar to how one refers to the combo or list box in an open form, but it
doesn't work for tables.

For this example, I created the following table with two fields:

tblUserPreferences
pkDefaultDesc Text (set as Primary Key)
DefaultValue Text

I chose Text for the DefaultValue, so that one could store text values
easily. You can store numeric values that are saved as text as well. In table
preview, I entered the following:

pkDefaultDesc DefaultValue
PrintAlignment 1

I added an unbound frame control with two option buttons to a form. I named
the frame "fraPrintOptions" (without the quotes). The option button with
label "Center" has an option value of 1, and the option button with label
"Left" has an option value of 2.

I added the following two procedures to the code module associated with this
form. I used the Form_Open event procedure instead of Form_Current as you
indicated:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

' Read the last value chosen by the user
Me.fraPrintOptions.Value = CInt(Nz(DLookup("DefaultValue", _
"tblUserPreferences", "pkDefaultDesc = 'PrintAlignment'"), 1))
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Open event procedure..."
Resume ExitProc
End Sub

Private Sub Form_Close()
On Error GoTo ProcError

' Save the last value chosen by the user
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()
strSQL = "UPDATE tblUserPreferences SET DefaultValue = '" _
& CStr(Me.fraPrintOptions) & "' " _
& "WHERE pkDefaultDesc = 'PrintAlignment';"

db.Execute strSQL, dbFailOnError

ExitProc:
On Error Resume Next 'Cleanup
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Close..."
Resume ExitProc
End Sub


Notes:
1.) If you are missing Option Explicit from new code modules, I recommend
that you make a change to your VBA editor settings so that you will get this
most important line in all new modules. Here is a gem tip that discusses this
more fully:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

2.) The tblUserPreferences table can be used to add any number of settings
that you wish to save for the user. Just use unique pkDefaultDesc values. For
example, you could use "NameOfFormPrintAlignment" instead of just
"PrintAlignment" if you want to store default values for a number of
different forms, where NameOfForm is equal to the name of the form. You'd
need to make a similar adjustment in the DLookup function to refer to the new
value.

3.) The method I used to retrieve the value uses a domain aggregrate
function named DLookup. This is a fairly easy method to implement, but it may
not be the most efficient, especially if your database is a split multi-user
database. An alternate method of reading the value from the table can be
written using either DAO or ADO code.

4.) The Nz function is used to convert a null to a pre-defined value (1 in
my example above). The CInt function is used to convert the looked up string
into an integer. (DLookup returns a string). The CStr function is used to
convert the number into a string.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Tom, Thanks for responding. I'm not quite getting it just yet. I created
a two field table for this option group to select center printing or
left-aligned printing. The table's first column is for center or left and
the corresponding values are 1 or 2.

I used the control for the option group as this table, then for the
"current form" event, I have:

'Sets print option to the last one selected by user.
Private Sub Form_Current()
Me.frmPrintOptions.Value = Tables.tblPrintOptions.Column(1)
End Sub

I get a bug that says "tables" not a defined variable.
Can you tell me what I'm doing wrong?

Thanks again,
Karl..Across the water from you in Poulsbo
 
K

Karl H

Hi Tom,
That worked perfectly. Thank you so much for your generous effort. I owe you
& your wife a dinner. If you're in this neck of the woods, please drop me a
line @ [email protected].
Thanks again!
Karl
 
T

Tom Wickerath

Hi Karl,

I'm glad to hear that it worked for you. Thanks for letting me know.

I recommend that you "munge" your e-mail address anytime in the future when
you want to post it to a newsgroup message. Otherwise, the spammers will have
a field day with you.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Tom,
That worked perfectly. Thank you so much for your generous effort. I owe you
& your wife a dinner. If you're in this neck of the woods, please drop me a
line @ <removed e-mail address>
Thanks again!
Karl
 
Top