Problem clearing listbox

  • Thread starter Thread starter Fred Thebeau
  • Start date Start date
F

Fred Thebeau

I have a combobox that lists several different types of surgery. When you
click on the particular type of surgery it populates a listbox with the
surgeons associated with that type of surgery. That functionality works
fine. The problem is that when I click on the command button to add the
record and clear the form for the next record to be put in, it does not
clear the contents of the listbox. I am able to clear everything else on my
form but the listbox. Any help would be appreciated. Below is my code for
the combobox and the command button.

Private Sub cboSurgicalSpecialty_AfterUpdate()

Me!lstSurgeon.Requery

End Sub

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

Anesthesiologist = ""
DateOfService = ""
cboSurgicalSpecialty = ""
lstSurgeon = ""
DelayCode1 = ""
DelayCode2 = ""
DelayCode3 = ""
DelayCode4 = ""
DelayCode5 = ""
Comments = ""

End Sub


Thank you,
Fred
 
Hi Fred,

How about:

Me.lstSurgeon.RowSource = ""

Hope that helps,
Jeff Conrad
Bend, Oregon
 
Thanks for the help Jeff, but unfortunately that didn't solve the problem.
Any other ideas?
 
Hi Fred,

That's strange. Works in my simple test.
Does anything happen? Any errors?

Taking a closer look at the code you posted I might see a problem. You
posted this code for your command button:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

Anesthesiologist = ""
DateOfService = ""
cboSurgicalSpecialty = ""
lstSurgeon = ""
DelayCode1 = ""
DelayCode2 = ""
DelayCode3 = ""
DelayCode4 = ""
DelayCode5 = ""
Comments = ""

End Sub

Is that EXACTLY how you have it???!
Do you realize your code will never progress down to this area?:

Anesthesiologist = ""
DateOfService = ""
cboSurgicalSpecialty = ""
lstSurgeon = ""
DelayCode1 = ""
DelayCode2 = ""
DelayCode3 = ""
DelayCode4 = ""
DelayCode5 = ""
Comments = ""

Your code will exit the sub here if no errors are raised:

Exit_cmdAddRecord_Click:
Exit Sub

If there is an error your code will STILL exit there based on this line:

Resume Exit_cmdAddRecord_Click

Try changing your code to this:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

DoCmd.GoToRecord , , acNewRec
Anesthesiologist = ""
DateOfService = ""
cboSurgicalSpecialty = ""
Me.lstSurgeon.RowSource = ""
Me.lstSurgeon.Requery
DelayCode1 = ""
DelayCode2 = ""
DelayCode3 = ""
DelayCode4 = ""
DelayCode5 = ""
Comments = ""

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

Hope that helps,
Jeff Conrad
Bend, Oregon
 
Jeff Conrad said:
Hi Fred,

That's strange. Works in my simple test.
Does anything happen? Any errors?

Taking a closer look at the code you posted I might see a problem. You
posted this code for your command button:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

Anesthesiologist = ""
DateOfService = ""
cboSurgicalSpecialty = ""
lstSurgeon = ""
DelayCode1 = ""
DelayCode2 = ""
DelayCode3 = ""
DelayCode4 = ""
DelayCode5 = ""
Comments = ""

End Sub

Is that EXACTLY how you have it???!
Do you realize your code will never progress down to this area?:

Anesthesiologist = ""
DateOfService = ""
cboSurgicalSpecialty = ""
lstSurgeon = ""
DelayCode1 = ""
DelayCode2 = ""
DelayCode3 = ""
DelayCode4 = ""
DelayCode5 = ""
Comments = ""

Your code will exit the sub here if no errors are raised:

Exit_cmdAddRecord_Click:
Exit Sub

If there is an error your code will STILL exit there based on this
line:

Resume Exit_cmdAddRecord_Click

Try changing your code to this:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

DoCmd.GoToRecord , , acNewRec
Anesthesiologist = ""
DateOfService = ""
cboSurgicalSpecialty = ""
Me.lstSurgeon.RowSource = ""
Me.lstSurgeon.Requery
DelayCode1 = ""
DelayCode2 = ""
DelayCode3 = ""
DelayCode4 = ""
DelayCode5 = ""
Comments = ""

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

Hope that helps,
Jeff Conrad
Bend, Oregon
Click to expand...

Fred -

I think Jeff is on the right track here -- you definitely need to move
your "clearing" code up to the location he suggests. However, I think
it's probably a mistake to clear the the rowsource of your list box,
since you are simply requerying it in cboSurgicalSpecialty_AfterUpdate.
I'd suggest that instead of these lines:
cboSurgicalSpecialty = ""
Me.lstSurgeon.RowSource = ""
Me.lstSurgeon.Requery
Click to expand...

you have these:

cboSurgicalSpecialty = Null '*** may not need this
Me.lstSurgeon = Null '*** may not need this
Me.lstSurgeon.Requery

The reason I have certain lines marked "*** may not need this" is that I
don't know if these are bound controls or unbound controls. If they are
bound controls, then when you go to a new record the values in them (as
opposed to their rowsource lists) should automatically be cleared and
set to either Null or a default value, if you supplied one. Therefore
you shouldn't have to explicitly set them to Null in that case. If
they're unbound controls, though, you *do* have to explicitly clear the
values in these controls, and so you need those lines I marked.

Incidentally, the same considerations apply to the other control you are
explicitly clearing -- you should only be doing that for unbound
controls, since bound controls are cleared automatically. Further, when
you do clear them you should probably be setting them to Null, not to ""
(a zero-length string), unless a zero-length string is an actual valid
value for these fields. Null is the "non-value" value used in Access
and most other database systems.
 
Back
Top