Can you check my code?

[Steven811]

Hi

I have a form with 4 combo boxes.

The code that I use works correctly when I first open the form. There after if the form remains open and I select new records the next cbo filters correctly but the subsequent cbo retains the previous record set until it's parent is selected.

The combos should be locked and cleared.

They do clear but don't lock (I think this is because there is a list from the previous record set).

All of the combo boxes are sourced through queries.

This is the code that I am using.

[Private Sub cboRep_AfterUpdate()
Me.cboCust.Requery
Me.cboCust.Locked = Me.cboCust.ListCount < 1
Me.cboCust.Enabled = Me.cboCust.ListCount > 0

Me.cboSite = ""
Me.cboSite.Requery
Me.cboSite.Locked = Me.cboSite.ListCount < 1
Me.cboSite.Enabled = Me.cboSite.ListCount > 0

Me.cboCont.Requery
Me.cboCont = ""
Me.cboCont.Locked = Me.cboCont.ListCount < 1
Me.cboCont.Enabled = Me.cboCont.ListCount > 0

End Sub]

Steven811

 

[lewds]

Hi....

I have never seen your particular code of comparing a boolean value to a numeric expression.

Will your code work if you go the long way around?

If M.cboCust.ListCount < 1 Then
Me.cboCust.Locked = True
Else
Me.cboCust.Locked = False
End If

Follow this logic and replace each part of your code and test.

 

[Steven811]

Hi

I've tried to make your code work and I can't. I swapped the [>] plus the False and True and on the second selection it greys out the subsequent cbo. Doesn't seem to matter which way round they go.

In spite of my best (although still limited) efforts I have reverted to my previous code. This almost works and I'm sure that it's something simple, just don't know what it is.

Steven811

 

[mordja]

Me.cboCont.Locked = Me.cboCont.ListCount < 1 is perfectly valid syntax. Me.cboCont.ListCount < 1 is a boolean value, not a numeric expression. What is the relation of the four combo boxes, you want the first to trigger a result in the next three ? If it works when you first open the maybe you should think about the event type ? If it is changing underlying record data perhaps OnCurrent may help.

Mordja

 

[Steven811]

Hi

You are correct, the combo boxes act as a filter for the subsequent cbo. The record is the criteria used in a SELECT query, this all works fine.

The problem that I have is that once I have used the form the records don't clear in the 2nd level combo boxes until the parent combo is selected.

If combo 1 is selected, cbo 3 retains the previous record set, although not visible. If cbo 2 is selected, cbo 4 retains the previous record set. If the controls were not locked they would be editable.

The parent and the immediate cbo work correctly.

To help the user I have a counter to display the number of records in their current cbo selection. This becomes confusing when the higher level record set suggests that there are no records to show. yet the lower level (next plus one) cbo shows the record count from the previous selction.

I can clear the visible fields and lock the controls until the parent combo is correctly selected. I don't understand why the requery code is not clearing the record set.

Am I using the wrong instruction perhaps?

Steven811

 

[lewds]

Hi..

MORDJA:
You are absolutely correct about the syntax, I must have been brain dead when I read the post (not uncommon).

Steven811:

After I woke up.. I put together a database of 4 tables, 1 form with 4 comboboxes.

tblRep 1 column Rep
tblCust 1 column Cust and 1 column Rep
tblSite 1 column Site and 1 column Cust
tblCont 1 column Cont and 1 column Site

you may have all your info in one table, this just makes for easy clarification in the following

Create a standard Module.. placed the following code

Start of Module Code...

Global strRep, strCust, strSite, strCont As String

Public Function FnRep()
FnRep = strRep
End Function

Public Function FnCust()
FnCust = strCust
End Function

Public Function FnSite()
FnSite = strSite
End Function

Public Function FnCont()
FnCont = strCont
End Function

End of Module code....


Created a form with 4 comboboxes
Here are the Row Source statements for each combobox
cboRep...SELECT [tblRep].[Rep] FROM tblRep;
cboCust...SELECT [tblCust].[Cust], [tblCust].[Rep] FROM tblCust WHERE ((([tblCust].[Rep])=FnRep()));
cboSite...SELECT [tblSite].[Site], [tblSite].[Cust] FROM tblSite WHERE ((([tblSite].[Cust])=FnCust()));
cboCont...SELECT [tblCont].[Cont], [tblCont].[Site] FROM tblCont WHERE ((([tblCont].[Site])=FnSite()))

Start of Form code...

Private Sub Form_Load()
Me.cboCust.Enabled = False
Me.cboSite.Enabled = False
Me.cboCont.Enabled = False

Me.cboRep.Locked = False
Me.cboCust.Locked = False
Me.cboSite.Locked = False
End Sub

Private Sub cboRep_Change()
strRep = Me.cboRep.Text
Me.cboCust.Enabled = True
Refresh
End Sub

Private Sub cboCust_Change()
strCust = Me.cboCust
Me.cboSite.Enabled = True
Refresh
Me.cboRep.Locked = True
End Sub

Private Sub cboSite_Change()
strSite = Me.cboSite.Text
Me.cboCont.Enabled = True
Refresh
Me.cboCust.Locked = True
End Sub

Private Sub cboCont_Change()
strCont = Me.cboCont.Text
Me.cboSite.Locked = True
End Sub

End of Form Code.....

When the form loads the pattern is set to only have cboRep enabled allowing you select a value.

After selecting a value in cboRep, cboCust become enabled(allowing the underlying SELECT to run)and so on..

They maybe many other ways to do this, but this works.

I Hope this Helps..

 

[Steven811]

Thanks, I'll work on this tomorrow am and let you know.

Regards

Steven811