How to get cascading combo boxes to display last choice picked

[delph]

Hi All,

I wonder if anyone can help. I've managed, with the the help of searching this site & Google, to set up 2 cascading combo boxes on a subform. The subform is now on my main form linked by customer id & is working.
The 1st combo box is called cboCategory, the 2nd is cboProducts and I've used some VB to write the choices made to text boxes bound to a table. This is for each customer. All works fine.

What I want to do is:
When the user makes the choices from each combo box, for each combo box to retain/display that value until the next time that combo box's value is changed for that customer.
Currently, both combo boxes default to blank each time the form is opened.
I've tried the following code in the OnLoad of the subform but it doesn't change the combo box value to what is in the table (no error messages):

Private Sub Form_Load()
Me.cboCategories.Column(1) = Me.txtCat
Me.cboProducts.Column(1) = Me.txtProduct
End Sub

Grateful for any pointers, thanks. Sue

 

[Dairy Farmer]

Did you try the code where you only have one combo?

Private Sub Combo1_AfterUpdate()
 
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Field2] = '" & Me![ComboField1] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Field2 = Field in the form that "links" the combo to the form
ComboField1 = Field used in the combo

 

[John Big Booty]

If you are saying that you want your combos to default to the value last selected for a particular client then you will need to look at the DLookup() function.

If your sub form is in either continuous or datasheet mode then I suggest you follow the discussion in this thread.

 

[delph]

Hi,

Thanks for the swift responses.

Dairy Farmer, I tried your code but it keeps saying Access can't find the field strCategoryName. I've also tried using cboCategories.Column(1)
but it comes up with the same message

Option Compare Database
Private Sub cboCategories_AfterUpdate()
Me.txtCat = Me.cboCategories.Column(1)


Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NatNo] = '" & Me![strCategoryName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub cboProducts_AfterUpdate()
Me.txtProduct = Me.cboProducts.Column(1)
End Sub

John Big Booty, thanks for suggesting using DLookup. I know the DLookup function but where would I use it in relation to the combo box? If I put it in the data, control source in the properties, the combo box no longer works.

Many thanks for your help

 

[John Big Booty]

I'd put it in the Form's On Current event. I'd guess you probably need to use the DLookup() in conjunction with DMax(), to select the last of the matches from the DLookup(), assuming that you have some way of sorting the records for each client.

 

[delph]

Thanks John Big Booty, I'l give it a go.