two combo box questions

[Guest]

I have two combo box questions.
a) On one of my forms, I want the comboboxes to always show a record, as
opposed to appearing blank until a selection is made. I'd like a record
always to show. Is this easy?
b) As well, I would like each comboboxes to show the same record it did
when the form was last closed (the application is still open). I know this
could involve storing the control's value in a Static variable but I'm not
sure how to code that.

grateful for any clues

 

[Bill]

Cinnie,
I've included below some code (untested) that attends to setting the
"un-bound" combo box to a pre-determined "selection". As you
probably already know, this is NOT necessary for bound combo
boxes.
Bill
------------------------------------------------------------------

Private Sub PreSelect(strNameOfControl, strWhatYouWantToDisplay As String)
'==============(for unbound combo boxes)=====================
' We have to go through a bit of trouble to pre-select the RowSource item
within the
' current combo box with what is passed here as strWhatYouWantToDisplay.
'======================================================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT field1,field2,,,,fieldx " & _
"FROM name of rowsource table _
& " WHERE Field = """ & strWhatYouWantToDisplay & """;"

'Where field1,field2,,,,fieldx are the fields of interest.
'and Field is the field in the rowsource containing the value you
'want displayed.

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
rs.MoveFirst
Me("strNameOfControl).DefaultValue = .Fields(0)
.MoveNext
End With

Set rs = Nothing
Set db = Nothing

End Sub

 

[Naeem Azizian]

Cinnie,
I've included below some code (untested) that attends to setting the
"un-bound" combo box to a pre-determined "selection". As you
probably already know, this is NOT necessary for bound combo
boxes.
Bill
------------------------------------------------------------------

Private Sub PreSelect(strNameOfControl, strWhatYouWantToDisplay As String)
'==============(for unbound combo boxes)=====================
' We have to go through a bit of trouble to pre-select the RowSource item
within the
' current combo box with what is passed here as strWhatYouWantToDisplay.
'======================================================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT field1,field2,,,,fieldx " & _
"FROM name of rowsource table _
& " WHERE Field = """ & strWhatYouWantToDisplay & """;"

'Where field1,field2,,,,fieldx are the fields of interest.
'and Field is the field in the rowsource containing the value you
'want displayed.

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
rs.MoveFirst
Me("strNameOfControl).DefaultValue = .Fields(0)
.MoveNext
End With

Set rs = Nothing
Set db = Nothing

End Sub

One of my clients also needed same thing. the best is to create a
table store the last values in that table and then load the last used
values out of the 'defaults' table and put them back where you want
them. This is for multi user environment in which each user works with
different types of data in the same form.
Trying to modify the form's default value for the combo box is slow
and i have tried it but gave up on it.