Clear a List Box on Next New Record?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I'm (slowly) getting to grips with cascading combo boxes & have a quick
question.

In my scenario I have a Company combo box, the chosen value in which
controls which values are displayed in the Location list box. I have
managed to get the List Box contents to 'refresh' on a value being chosen in
the combobox with the following:

Private Sub cboCompany_AfterUpdate()
Me!lstLocation = Null
Me!lstLocation.Requery
End Sub

This works fine. But....the list values remain on the form into the next
empty New record (even when the company combo is empty & not yet selected).
The List refeshes OK when a company is chosen but I want the form totally
blank for new records.

How can I (simply) get the List Box to appear empty when a new record is
selected.

Gratefully yours,

-Jay-
 
This will involve redoing some of your code. To get the list box completely
empty, you have to destroy it's rowsource property. Try putting this in the
Current event of your form:

If Me.NewRecord Then
Me!lstLocation.Rowsource = ""
End If

Now, the trick is, you will have to reestablish the rowsource to use the
list box again again.
 
Cheers K.

I suppose I'd have to re-establish the rowsource in code as well. Or what
about having the Me.NewRecord detailed below change the listbox font to
white (same as the box background) and then have the After_update event of
the combo box change it to black? So it only *looks* as though the box is
empty and no need to change the rowsource. Would that work or is that bad
design?

And d'you mind my asking - What exactly does the current event of the form
mean/signify.

Many thanks,

-Jay-
 
The current event fires whenever you change to a different record, whether it
is a new or existing record. It also happens when you delete a record,
because when that occurs, you will have a new record.

Now, as to your font changing idea. NO.

Reestablishg the row source would actually be much easier than kludging up
your form. In your original post, you requery the list box once a selection
has been made, so instead of this

Private Sub cboCompany_AfterUpdate()
Me!lstLocation = Null
Me!lstLocation.Requery
End Sub

Do This

Private Sub cboCompany_AfterUpdate()
Me!lstLocation.RowSource = SomeQuery
End Sub

One more suggestion, if I may. You and anyone who has to come behind you
and read your code will benefit from proper indention. It make the code so
much easier to read.

Private Sub cboCompany_AfterUpdate()
Me!lstLocation = Null
Me!lstLocation.Requery
End Sub

Not so much as above, but notice the difference below

Private Sub DoSomething
Dim intCnt As Integer
Dim strLorp As String
If IsNull(Me.txtLorp) Then
strLorp = "Missing"
Else
strLorp = Me.txtLorp
End If
intCnt = InStr(strLorp,"Q")
If intCnt = 0 Then
strLorp = ""
Else
Mid(strLorp, intCnt, 1) = "Z"
End If

Private Sub DoSomething

Dim intCnt As Integer
Dim strLorp As String

If IsNull(Me.txtLorp) Then
strLorp = "Missing"
Else
strLorp = Me.txtLorp
End If

intCnt = InStr(strLorp,"Q")

If intCnt = 0 Then
strLorp = ""
Else
Mid(strLorp, intCnt, 1) = "Z"
End If

See the difference?
 
The current event fires whenever you change to a different record, whether it
is a new or existing record. It also happens when you delete a record,
because when that occurs, you will have a new record.
Click to expand...

Aaah, I see.
Now, as to your font changing idea. NO.
Click to expand...


Thought not :-)
Reestablishg the row source would actually be much easier than kludging up
your form. In your original post, you requery the list box once a selection
has been made, so instead of this

Private Sub cboCompany_AfterUpdate()
Me!lstLocation = Null
Me!lstLocation.Requery
End Sub

Do This

Private Sub cboCompany_AfterUpdate()
Me!lstLocation.RowSource = SomeQuery
End Sub
Click to expand...

Cheers, I'll try that.
One more suggestion, if I may. You and anyone who has to come behind you
and read your code will benefit from proper indention. It make the code so
much easier to read.

Private Sub cboCompany_AfterUpdate()
Me!lstLocation = Null
Me!lstLocation.Requery
End Sub

Not so much as above, but notice the difference below

Private Sub DoSomething
Dim intCnt As Integer
Dim strLorp As String
If IsNull(Me.txtLorp) Then
strLorp = "Missing"
Else
strLorp = Me.txtLorp
End If
intCnt = InStr(strLorp,"Q")
If intCnt = 0 Then
strLorp = ""
Else
Mid(strLorp, intCnt, 1) = "Z"
End If

Private Sub DoSomething

Dim intCnt As Integer
Dim strLorp As String

If IsNull(Me.txtLorp) Then
strLorp = "Missing"
Else
strLorp = Me.txtLorp
End If

intCnt = InStr(strLorp,"Q")

If intCnt = 0 Then
strLorp = ""
Else
Mid(strLorp, intCnt, 1) = "Z"
End If

See the difference?
Click to expand...

Yeah, I see exactly what you mean. I've jusy been copying the layout of the
code I've come across in stuff I've been tinkering with. I've never actually
*learnt* any code, so wasn't aware of what was allowed re the
indenting/layout etc. Although I do intend buying a book I've seen - "Visual
Basic in Easy Steps" which seems like a nice gentle idiot's guide to get me
started:-)

Many thanks

Jay.


<snip>
 
Back
Top