After 'NoData' on report combobox won't dropdown

[Guest]

Hey all you smart people - using access03 I open a form with a combo box to
select a club and the afterupdate event of the combocox opens a report which
has a history of that clubs performance and sets the form to invisible. The
OnLoad of the form sets the focus to the combobox and dropsdown the list.
Then when the report closes it makes the form visible and drops down the
combobox again so the user can choose another from the list. Everything is
working fine except when I invoke the 'NoData' event in the report, I show a
msgbox indicating there are no records for that club. After I close the
msgbox the form reappears but the combobox is not dropped down. I would
like to have the list to dropdown again when I return to the form, but I
can't seem to put the code in the right place to make it happen. This is the
code I've got as it works, but the Club_combo won't dropdown when the form
reappears after the nodata event fires. Any ideas??

Form - PickAClub
Private Sub Form_Load()
Me!Club_combo.SetFocus
Me!Club_combo.Dropdown
End Sub

Private Sub Club_combo_AfterUpDate()
On Error Resume Next
GlblClubName = Club_combo.Text ' save name of club to use in msgbox
Me.Visible = False
Docmd.OpenReport "ClubInfo", acViewPreview, , "ClubID = " &
[Forms]![PickAClub]![Club_combo]
End Sub

Report - ClubInfo
Private Sub Report_NoData(Cancel as Integer)
Msgbox ("There is No Data for " & GlblClubName)
Cancel = True
End Sub
Private Sub Report_Close()
[Forms]![PickAClub].Visible = True
[Forms]![PickAClub]![Club_combo].SetFocus
[Forms]![PickAClub]![Club_combo].Dropdown
End Sub

Thanx,

 

[Marshall Barton]

Hey all you smart people - using access03 I open a form with a combo box to
select a club and the afterupdate event of the combocox opens a report which
has a history of that clubs performance and sets the form to invisible. The
OnLoad of the form sets the focus to the combobox and dropsdown the list.
Then when the report closes it makes the form visible and drops down the
combobox again so the user can choose another from the list. Everything is
working fine except when I invoke the 'NoData' event in the report, I show a
msgbox indicating there are no records for that club. After I close the
msgbox the form reappears but the combobox is not dropped down. I would
like to have the list to dropdown again when I return to the form, but I
can't seem to put the code in the right place to make it happen. This is the
code I've got as it works, but the Club_combo won't dropdown when the form
reappears after the nodata event fires. Any ideas??

Form - PickAClub
Private Sub Form_Load()
Me!Club_combo.SetFocus
Me!Club_combo.Dropdown
End Sub

Private Sub Club_combo_AfterUpDate()
On Error Resume Next
GlblClubName = Club_combo.Text ' save name of club to use in msgbox
Me.Visible = False
Docmd.OpenReport "ClubInfo", acViewPreview, , "ClubID = " &
[Forms]![PickAClub]![Club_combo]
End Sub

Report - ClubInfo
Private Sub Report_NoData(Cancel as Integer)
Msgbox ("There is No Data for " & GlblClubName)
Cancel = True
End Sub
Private Sub Report_Close()
[Forms]![PickAClub].Visible = True
[Forms]![PickAClub]![Club_combo].SetFocus
[Forms]![PickAClub]![Club_combo].Dropdown
End Sub

Your use of Resume Next is masking what is really happening.

The way this is usually done is something like:

Private Sub Club_combo_AfterUpDate()
On Error GoTo ErrHandler
GlblClubName = Club_combo.Text ' save name of club
Me.Visible = False
Docmd.OpenReport "ClubInfo", acViewPreview, , _
"ClubID = " & [Forms]![PickAClub]![Club_combo]
ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Me.Club_combo.SetFocus
Me.Club_combo.Dropdown
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume ExitHere
End Sub

 

[Guest]

Thanks Marshall!! I haven't tried it yet but it looks like it will work. I
figured cause there were no more lines after the openrport that I'd just
resume next. I'll have to be more consciencious in my error handling from
now on. Thanks again
--
Gil

Hey all you smart people - using access03 I open a form with a combo box to
select a club and the afterupdate event of the combocox opens a report which
has a history of that clubs performance and sets the form to invisible. The
OnLoad of the form sets the focus to the combobox and dropsdown the list.
Then when the report closes it makes the form visible and drops down the
combobox again so the user can choose another from the list. Everything is
working fine except when I invoke the 'NoData' event in the report, I show a
msgbox indicating there are no records for that club. After I close the
msgbox the form reappears but the combobox is not dropped down. I would
like to have the list to dropdown again when I return to the form, but I
can't seem to put the code in the right place to make it happen. This is the
code I've got as it works, but the Club_combo won't dropdown when the form
reappears after the nodata event fires. Any ideas??

Form - PickAClub
Private Sub Form_Load()
Me!Club_combo.SetFocus
Me!Club_combo.Dropdown
End Sub

Private Sub Club_combo_AfterUpDate()
On Error Resume Next
GlblClubName = Club_combo.Text ' save name of club to use in msgbox
Me.Visible = False
Docmd.OpenReport "ClubInfo", acViewPreview, , "ClubID = " &
[Forms]![PickAClub]![Club_combo]
End Sub

Report - ClubInfo
Private Sub Report_NoData(Cancel as Integer)
Msgbox ("There is No Data for " & GlblClubName)
Cancel = True
End Sub
Private Sub Report_Close()
[Forms]![PickAClub].Visible = True
[Forms]![PickAClub]![Club_combo].SetFocus
[Forms]![PickAClub]![Club_combo].Dropdown
End Sub

Your use of Resume Next is masking what is really happening.

The way this is usually done is something like:

Private Sub Club_combo_AfterUpDate()
On Error GoTo ErrHandler
GlblClubName = Club_combo.Text ' save name of club
Me.Visible = False
Docmd.OpenReport "ClubInfo", acViewPreview, , _
"ClubID = " & [Forms]![PickAClub]![Club_combo]
ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Me.Club_combo.SetFocus
Me.Club_combo.Dropdown
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume ExitHere
End Sub