Clear List Boxes on New Record

[Jay]

I have a form with 3 cascading list boxes based on 'PickList' Queries.

The form is set for data entry, and when first opened only box 1
displays its list, with boxes 2 & 3 being populated when a datum is
selected in boxes 1 & 2.

However, when I navigate to the next new record, the boxes still display
the lists at the points from the previous record.

How can I get it so that when the next new record is moved to the form
looks like it does when first opened, with box 1's list showing from the
beginning and boxes 2 & 3 not showing anything until data chosen in
boxes 1 & 2.

I hope I've made myself clear.

Many thanks for any help offered.

Regards

Jason

 

[Guest]

Hi Jay

You need to reset the 1st combo if it is a new record

If Me.NewRecord Then
Me.WhatEver=""
Else
Me.WhatEver 'Set the criteria here'
End If

 

[Jay]

Forgive my ignorance Wayne (I'm a total novice when it comes to vba). Where
do I put the code you've suggested. I'm used to coding the events of
command buttons & list boxes etc. for their onclick or afterupdat events
(for example) , but I'm not really sure where to put your code. (And when I
say 'used to coding' I mean copying & pasting other people's code

Where do I put the code. ANd am I right in thinking 'Whatever' is my
list/combobox name?

I'd appreciate your help.

Thanks,

Jason

 

[j_beverly]

On way to do this is to use the On Current event to set your Combo
Boxes to Null and then refresh your form:

Private Sub Form_Current()
Combo1 = Null
Combo2 = Null
Combo3 = Null
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

=============
Jeff Beverly

 

[Jay]

That works a treat Jeff, thanks a lot. I wasn't sure where to put the code
(total vba novice) but must have fluked it.

Just one question though. My boxes are actually list boxes and not combos.
Boxes 2 & 3 in the cascade are cleared by your code (when a new record is
started) which is just what I wanted, but the first box retains the list
displayed at the same point from the previous record.

The size of the box means 15 values are visible (there are about 45 alpha
sorted company names in the table on which the box is based) and ideally I'd
like it to default back to the top of the list and display the first 15
(A,B,C...) names on moving to a new record.

Can you advise if and how to do this.

Many thanks for your help.

Jay