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

 

[IfButOnly]

Steven

I don't think you are clearing the combo boxes correctly. Try -

me.cboSite.recordsource = ""
me.cboSite.requery
me.cboSite.locked = true
me.cboSite.enabled = false

Luck ... Peter

 

[Steven811]

Code

Hi Peter

Thanks for helping, this problem has bugged me for days.

Question from a novice.

When I run the compiler I receive the error message 'method or data member not found'. This error occurs at the line [RecordSource]

[ 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.RecordSource = ""
Me.cboCont.Requery
Me.cboCont.Locked = True
Me.cboCont.Enabled = False

My row source is a query.

Not sure why it doesn't like that bit or what to do now.

Any suggestions?

Steven811

 

[smercer]

try
Me.cboCont.Form.RecordSource = ""

 

[Steven811]

Work around

Hi

I've managed to lock the cbo boxes and clear the visible fields so that a record set cannot be selected until the combo supplying the selection criterion for the query is requeried.

However, I still can't clear the previous record set from the combo. This is evident from a counter that is linked to the combo to help the user

I've tried to requery on the following: [OnUpdate] [Click] and [Change], none of which clear the previous slection, although the visible fields are cleared with [Me.mycbo = ""]

Any suggestions would result in the renaming of my first born after you (if successful or my wife would complain).

 

[smercer]

I've tried to requery on the following: [OnUpdate] [Click] and [Change], none of which clear the previous slection, although the visible fields are cleared with [Me.mycbo = ""]

Try:

Me.mycbo = Null

 

[smercer]

All of the combo boxes are sourced through queries.

Instead copy the query SQL and paste it into the RowSource of the combo box that references the query.

Then you can:

me.MyCombo_Box.requery

you wont need the queries after doing this

 

[IfButOnly]

Sorry, realised around midnight last night, that I should have said:

me.cboSite.RowSource = "" ' ( not .recordsource)

 

[Steven811]

Code

Hi smercer

I've tried your suggestion, thanks.

It supplies the data just like the queries, but I still can't clear the record set.

Very odd. I have included a copy of the db if that helps anyone.

Steven811

 

[Mile-O]

Put the code in the form's Current event?