Pause to find value, then execute

  • Thread starter Thread starter DubboPete
  • Start date Start date
D

DubboPete

Hi all,

I have a reports menu (form), where one of the command buttons opens up a
"Staff Profile" report.

I have put in a trap whereby this happens:

Private Sub butStaffDetails_Click()

If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
MsgBox "Select staff member before trying to view profile",
vbInformation
Me.StaffMember.SetFocus
Exit Sub
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
End If

End Sub


At present, and where (Me.StaffMember = ""), the code exits; a staff member
is selected, then the command button has to be clicked again to open the
report without errors....

I'm sure there would be a way to pause the code at some point, probably
before the 'exit sub' line, to set the focus back on the [staffmember]
field, wait until a selection is made, and then after that update, resume
the opening of the report.... without having to re-click the command button
a second time.

Am I right, and can someone help fill in the gaps in the code for me?

thanks in anticipation

DubboPete
 
DubboPete said:
Hi all,

I have a reports menu (form), where one of the command buttons opens up a
"Staff Profile" report.

I have put in a trap whereby this happens:
Click to expand...
Private Sub butStaffDetails_Click()

Do While True
If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
If MsgBox("Select staff member before trying to view profile", _
vbInformation + vbRetryCancel) = vbCancel Then
Exit Do
Else
Me.StaffMember.SetFocus
End If
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
Exit Do
End If
Loop
End Sub
At present, and where (Me.StaffMember = ""), the code exits; a staff member
is selected, then the command button has to be clicked again to open the
report without errors....

I'm sure there would be a way to pause the code at some point, probably
before the 'exit sub' line, to set the focus back on the [staffmember]
field, wait until a selection is made, and then after that update, resume
the opening of the report.... without having to re-click the command button
a second time.

Am I right, and can someone help fill in the gaps in the code for me?

thanks in anticipation

DubboPete
Click to expand...
 
Hi Klatuu,

On clicking Retry, it just comes back with the same MsgBox RetryCancel, over
and over again.... :-(

Pete

Klatuu said:
DubboPete said:
Hi all,

I have a reports menu (form), where one of the command buttons opens up a
"Staff Profile" report.

I have put in a trap whereby this happens:
Click to expand...
Private Sub butStaffDetails_Click()

Do While True
If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
If MsgBox("Select staff member before trying to view profile",
_
vbInformation + vbRetryCancel) = vbCancel Then
Exit Do
Else
Me.StaffMember.SetFocus
End If
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
Exit Do
End If
Loop
End Sub
At present, and where (Me.StaffMember = ""), the code exits; a staff
member
is selected, then the command button has to be clicked again to open the
report without errors....

I'm sure there would be a way to pause the code at some point, probably
before the 'exit sub' line, to set the focus back on the [staffmember]
field, wait until a selection is made, and then after that update, resume
the opening of the report.... without having to re-click the command
button
a second time.

Am I right, and can someone help fill in the gaps in the code for me?

thanks in anticipation

DubboPete
Click to expand...
Click to expand...
 
Hi Klatuu,

The focus returns to the combo box, but before anything is allowed to be
entered or selected, the msgbox pos back up to Retry/Cancel....

any thoughts?

Pete
 
D'oh! Of course it does. The coffee has not kicked in yet.

I can think of a way to do it, but it will take a bit of extra coding. What
you will need is a boolean (Yes/No) variable dimmed at the module level.
Lets call it blnReportRequested.
First, we do the report if we have a staff name. If not, we set
blnReportRequested to True.

Private Sub butStaffDetails_Click()

If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
If MsgBox("Select staff member before trying to view profile", _
vbInformation + vbRetryCancel) = vbCancel Then
blnReportRequested = False
Else
blnReportRequested = True
Me.StaffMember.SetFocus
End If
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
blnReportRequested = False
End If

End Sub

Now in the After Update event of StaffMember:

If blnReportRequested Then
Call butStaffDetails_Click
End If

So, if they did not previously click the report button, it will behave
normally, but if they clicked on the report button and had no staff member,
then when they put in a staff member, it will print the report without having
to click the button again.
DubboPete said:
Hi Klatuu,

On clicking Retry, it just comes back with the same MsgBox RetryCancel, over
and over again.... :-(

Pete

Klatuu said:
DubboPete said:
Hi all,

I have a reports menu (form), where one of the command buttons opens up a
"Staff Profile" report.

I have put in a trap whereby this happens:
Click to expand...
Private Sub butStaffDetails_Click()

Do While True
If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
If MsgBox("Select staff member before trying to view profile",
_
vbInformation + vbRetryCancel) = vbCancel Then
Exit Do
Else
Me.StaffMember.SetFocus
End If
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
Exit Do
End If
Loop
End Sub
At present, and where (Me.StaffMember = ""), the code exits; a staff
member
is selected, then the command button has to be clicked again to open the
report without errors....

I'm sure there would be a way to pause the code at some point, probably
before the 'exit sub' line, to set the focus back on the [staffmember]
field, wait until a selection is made, and then after that update, resume
the opening of the report.... without having to re-click the command
button
a second time.

Am I right, and can someone help fill in the gaps in the code for me?

thanks in anticipation

DubboPete
Click to expand...
Click to expand...
Click to expand...
 
Okay Klatuu,

More caffeine, or more concentration on my part.

Here's the code I have, following your help, for the Command Button with a
blank StaffMember field:

Private Sub butStaffDetails_Click()

Dim blnReportRequested As Boolean

If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
If MsgBox("Select staff member before trying to view profile", _
vbInformation + vbRetryCancel) = vbCancel Then
blnReportRequested = False
Else
blnReportRequested = True
Me.StaffMember.SetFocus
End If
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
blnReportRequested = False
End If

End Sub


This then rightly sets the focus back onto the [StaffMember] combo box,
awaiting an update, but after my update nothing happens. The focus sits
quietly on the combo box....

here's the code for the after update event on the [StaffMember] combo box

Private Sub StaffMember_AfterUpdate()

Dim blnReportRequested As Boolean

If blnReportRequested Then
Call butStaffDetails_Click
End If

End Sub

I think, on lack of knowledge at my end, that the problem lies in the after
update event. Should I have something different to

Dim blnReportRequested As Boolean


thanks for your help mate...
Pete

----- Original Message -----
From: "Klatuu" <[email protected]>
Newsgroups: microsoft.public.access.formscoding
Sent: Wednesday, September 28, 2005 11:31 PM
Subject: Re: Pause to find value, then execute
 
Did you put the code in the After Update event of the control where you enter
the staff member's Name?
The message box should not pop up if there is anything in StaffMember.
 
blnReportRequested needs to be dimmed at the module level, not in a sub.
Both subs need visibility to it.
This indicates you are not using Option Explicit. See what can happen? The
value of Option Explicit is that is keeps you from referencing a variable
that does not exist in context. So, if you don't use Option Explicit, an
undefined and uninitialboolean variable will always evalute to false.

17th Century GoatHerder said:
Okay Klatuu,

More caffeine, or more concentration on my part.

Here's the code I have, following your help, for the Command Button with a
blank StaffMember field:

Private Sub butStaffDetails_Click()

Dim blnReportRequested As Boolean

If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
If MsgBox("Select staff member before trying to view profile", _
vbInformation + vbRetryCancel) = vbCancel Then
blnReportRequested = False
Else
blnReportRequested = True
Me.StaffMember.SetFocus
End If
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
blnReportRequested = False
End If

End Sub


This then rightly sets the focus back onto the [StaffMember] combo box,
awaiting an update, but after my update nothing happens. The focus sits
quietly on the combo box....

here's the code for the after update event on the [StaffMember] combo box

Private Sub StaffMember_AfterUpdate()

Dim blnReportRequested As Boolean

If blnReportRequested Then
Call butStaffDetails_Click
End If

End Sub

I think, on lack of knowledge at my end, that the problem lies in the after
update event. Should I have something different to

Dim blnReportRequested As Boolean


thanks for your help mate...
Pete

----- Original Message -----
From: "Klatuu" <[email protected]>
Newsgroups: microsoft.public.access.formscoding
Sent: Wednesday, September 28, 2005 11:31 PM
Subject: Re: Pause to find value, then execute

D'oh! Of course it does. The coffee has not kicked in yet.

I can think of a way to do it, but it will take a bit of extra coding.
What
you will need is a boolean (Yes/No) variable dimmed at the module level.
Lets call it blnReportRequested.
First, we do the report if we have a staff name. If not, we set
blnReportRequested to True.

Private Sub butStaffDetails_Click()

If (Me.StaffMember = "") Then
' field [staffmember] is a combo box and is text
If MsgBox("Select staff member before trying to view profile", _
vbInformation + vbRetryCancel) = vbCancel Then
blnReportRequested = False
Else
blnReportRequested = True
Me.StaffMember.SetFocus
End If
Else
DoCmd.OpenReport "rptReportStaffDetails", acViewPreview
blnReportRequested = False
End If

End Sub

Now in the After Update event of StaffMember:

If blnReportRequested Then
Call butStaffDetails_Click
End If

So, if they did not previously click the report button, it will behave
normally, but if they clicked on the report button and had no staff
member,
then when they put in a staff member, it will print the report without
having
to click the button again.
Click to expand...
Click to expand...
 
Hi Klatuu,

Logic made me find an easier way to do this.... I took out the command
button, and forced people to select from the combo box, and then prompted
them to answer whether they wanted to open the report ([yes]/[no]) - works
like a charm!

thanks for your help mate!

Pete
 
Good, I considered suggesting just that approach, but decided you probably
had a reason for doing it with a command button.

17th Century GoatHerder said:
Hi Klatuu,

Logic made me find an easier way to do this.... I took out the command
button, and forced people to select from the combo box, and then prompted
them to answer whether they wanted to open the report ([yes]/[no]) - works
like a charm!

thanks for your help mate!

Pete

Klatuu said:
Did you put the code in the After Update event of the control where you
enter
the staff member's Name?
The message box should not pop up if there is anything in StaffMember.
Click to expand...
Click to expand...
 
Back
Top