If Statement for Sub-Form

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

I have a main form named TimeCards with a sub-form on it named
FTimeBillingSub
On the Main form is a memo field named Job Description, its default is set
to '"None"
On the sub-form is a Label with code attached to it.
I want on the on click event of the Label on the sub-form for the Invoice
not to open, (i.e. the code to not run) if the memo field on the main form
says None.
I tried the below code, but it did not work???



If ("Forms!TimeCards!Job Description") = None Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
 
Try this:

If Forms!TimeCards!Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If


Or, because you're running the code in the subform, you can use a more
generic example:

If Me.Parent.Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
 
You could use:
If Forms!TimeCards![Job Description] = "None" Then

or:
If Me.Parent![Job Description] = "None" Then
 
Trying different approach, cold not make code work off form, trying code on
Open Event of Report
It gives messagebox but still opens the Report. What can I change?

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
End If
DoCmd.Close , acReport
 
To stop the opening of a report in the report's Open event, just use
Cancel = False

instead of using
DoCmd.Close acReport, Me.Name

(Note that you have a syntax error in your DoCmd step in your post).

Also, when you do this, your form will get an Error 2501 ("You cancelled the
action...") and you'll need to trap for that error in the form's code if you
don't want the user to see that error.

I'm sure it's possible to make the code work in your form, which is a better
place to put it (not efficient to begin opening a report and then decide to
cancel it -- better to not open the report in the first place). Can you give
us more info about how you want to use it in the form?

--

Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Trying different approach, cold not make code work off form, trying code on
Open Event of Report
It gives messagebox but still opens the Report. What can I change?

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
End If
DoCmd.Close , acReport






Ken Snell said:
Try this:

If Forms!TimeCards!Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If


Or, because you're running the code in the subform, you can use a more
generic example:

If Me.Parent.Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
 
Better description I hope. The memo filed, i.e. Text field on the main form
is set to IsNotNull it has to have a value.
So I put the default value of None in it. ="None"
If a person tries to Invoice a customer, i.e. open the Report
InvoiceReport via the sub-form on the main form with the on click event
it should give a message box that a job description is needed and not open
the report.
The sub-form has a label on it that runs the code to open the Report, it has
a lot of criteria already.
The None value on the main form is necessary for a lot of reasons.
Here is all the code below, unsure of where to put the Job Description Code:

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Cancel = True

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings False

If IsNull(DSum("BillAmt", "TTimeBilling", "TimeID = Parent!TimeID")) Then
MsgBox "Enter an amount in the the Percent Column"


Else:

DoCmd.OpenReport "InvoiceReport", acPreview, "",
"[TimeID]=[Forms]![TimeCards]![TimeID]"
If [Forms]![TimeCards]![BidDiscAmt] < DSum("Payment", "TPaymentSub",
"TimeID = [Forms]![TimeCards]!TimeID") Then
Reports!InvoiceReport!QuoteInvLabel.Caption = "Credit Invoice"
Else: Reports!InvoiceReport!QuoteInvLabel.Caption = "Invoice"
End If

Reports!InvoiceReport!RTimeBillingSub.Report!SumExpBillAmt.Visible =
True 'Moved here 5/5/04.
Reports!InvoiceReport!RTimeBillingSub.Report!SumBillAmt.Visible = False
'Moved here 5/5/04.

Reports!InvoiceReport!LaborAndOverhead.Visible = True
Reports!InvoiceReport!RTimeBillingSubA.Visible = True
Reports!InvoiceReport!RTimeBillingSub.Visible = True
End If
DoCmd.SetWarnings True







Ken Snell said:
To stop the opening of a report in the report's Open event, just use
Cancel = False

instead of using
DoCmd.Close acReport, Me.Name

(Note that you have a syntax error in your DoCmd step in your post).

Also, when you do this, your form will get an Error 2501 ("You cancelled
the
action...") and you'll need to trap for that error in the form's code if
you
don't want the user to see that error.

I'm sure it's possible to make the code work in your form, which is a
better
place to put it (not efficient to begin opening a report and then decide
to
cancel it -- better to not open the report in the first place). Can you
give
us more info about how you want to use it in the form?

--

Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Trying different approach, cold not make code work off form, trying code on
Open Event of Report
It gives messagebox but still opens the Report. What can I change?

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
End If
DoCmd.Close , acReport






Ken Snell said:
Try this:

If Forms!TimeCards!Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If


Or, because you're running the code in the subform, you can use a more
generic example:

If Me.Parent.Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
--

Ken Snell
<MS ACCESS MVP>

I have a main form named TimeCards with a sub-form on it named
FTimeBillingSub
On the Main form is a memo field named Job Description, its default is
set
to '"None"
On the sub-form is a Label with code attached to it.
I want on the on click event of the Label on the sub-form for the Invoice
not to open, (i.e. the code to not run) if the memo field on the main
form
says None.
I tried the below code, but it did not work???



If ("Forms!TimeCards!Job Description") = None Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
 
You're using the Click event of the label to run the code, right? This
generic code should get you started:

Private Sub LabelName_Click()
If Me.Parent.[Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Else
' put here the code to open the report and whatever
'
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Better description I hope. The memo filed, i.e. Text field on the main form
is set to IsNotNull it has to have a value.
So I put the default value of None in it. ="None"
If a person tries to Invoice a customer, i.e. open the Report
InvoiceReport via the sub-form on the main form with the on click event
it should give a message box that a job description is needed and not open
the report.
The sub-form has a label on it that runs the code to open the Report, it has
a lot of criteria already.
The None value on the main form is necessary for a lot of reasons.
Here is all the code below, unsure of where to put the Job Description Code:

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Cancel = True

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings False

If IsNull(DSum("BillAmt", "TTimeBilling", "TimeID = Parent!TimeID")) Then
MsgBox "Enter an amount in the the Percent Column"


Else:

DoCmd.OpenReport "InvoiceReport", acPreview, "",
"[TimeID]=[Forms]![TimeCards]![TimeID]"
If [Forms]![TimeCards]![BidDiscAmt] < DSum("Payment", "TPaymentSub",
"TimeID = [Forms]![TimeCards]!TimeID") Then
Reports!InvoiceReport!QuoteInvLabel.Caption = "Credit Invoice"
Else: Reports!InvoiceReport!QuoteInvLabel.Caption = "Invoice"
End If

Reports!InvoiceReport!RTimeBillingSub.Report!SumExpBillAmt.Visible =
True 'Moved here 5/5/04.
Reports!InvoiceReport!RTimeBillingSub.Report!SumBillAmt.Visible = False
'Moved here 5/5/04.

Reports!InvoiceReport!LaborAndOverhead.Visible = True
Reports!InvoiceReport!RTimeBillingSubA.Visible = True
Reports!InvoiceReport!RTimeBillingSub.Visible = True
End If
DoCmd.SetWarnings True







Ken Snell said:
To stop the opening of a report in the report's Open event, just use
Cancel = False

instead of using
DoCmd.Close acReport, Me.Name

(Note that you have a syntax error in your DoCmd step in your post).

Also, when you do this, your form will get an Error 2501 ("You cancelled
the
action...") and you'll need to trap for that error in the form's code if
you
don't want the user to see that error.

I'm sure it's possible to make the code work in your form, which is a
better
place to put it (not efficient to begin opening a report and then decide
to
cancel it -- better to not open the report in the first place). Can you
give
us more info about how you want to use it in the form?

--

Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Trying different approach, cold not make code work off form, trying
code
on
Open Event of Report
It gives messagebox but still opens the Report. What can I change?

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
End If
DoCmd.Close , acReport






Try this:

If Forms!TimeCards!Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If


Or, because you're running the code in the subform, you can use a more
generic example:

If Me.Parent.Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
--

Ken Snell
<MS ACCESS MVP>

I have a main form named TimeCards with a sub-form on it named
FTimeBillingSub
On the Main form is a memo field named Job Description, its default is
set
to '"None"
On the sub-form is a Label with code attached to it.
I want on the on click event of the Label on the sub-form for the Invoice
not to open, (i.e. the code to not run) if the memo field on the main
form
says None.
I tried the below code, but it did not work???



If ("Forms!TimeCards!Job Description") = None Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
 
Yeah, That worked, Really Appreciate it. Making sure users cant and have to
do things is a whole other Database.

Ken Snell said:
You're using the Click event of the label to run the code, right? This
generic code should get you started:

Private Sub LabelName_Click()
If Me.Parent.[Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Else
' put here the code to open the report and whatever
'
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>

Dave Elliott said:
Better description I hope. The memo filed, i.e. Text field on the main form
is set to IsNotNull it has to have a value.
So I put the default value of None in it. ="None"
If a person tries to Invoice a customer, i.e. open the Report
InvoiceReport via the sub-form on the main form with the on click event
it should give a message box that a job description is needed and not
open
the report.
The sub-form has a label on it that runs the code to open the Report, it has
a lot of criteria already.
The None value on the main form is necessary for a lot of reasons.
Here is all the code below, unsure of where to put the Job Description Code:

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Cancel = True

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings False

If IsNull(DSum("BillAmt", "TTimeBilling", "TimeID = Parent!TimeID")) Then
MsgBox "Enter an amount in the the Percent Column"


Else:

DoCmd.OpenReport "InvoiceReport", acPreview, "",
"[TimeID]=[Forms]![TimeCards]![TimeID]"
If [Forms]![TimeCards]![BidDiscAmt] < DSum("Payment", "TPaymentSub",
"TimeID = [Forms]![TimeCards]!TimeID") Then
Reports!InvoiceReport!QuoteInvLabel.Caption = "Credit
Invoice"
Else: Reports!InvoiceReport!QuoteInvLabel.Caption = "Invoice"
End If

Reports!InvoiceReport!RTimeBillingSub.Report!SumExpBillAmt.Visible =
True 'Moved here 5/5/04.
Reports!InvoiceReport!RTimeBillingSub.Report!SumBillAmt.Visible = False
'Moved here 5/5/04.

Reports!InvoiceReport!LaborAndOverhead.Visible = True
Reports!InvoiceReport!RTimeBillingSubA.Visible = True
Reports!InvoiceReport!RTimeBillingSub.Visible = True
End If
DoCmd.SetWarnings True







Ken Snell said:
To stop the opening of a report in the report's Open event, just use
Cancel = False

instead of using
DoCmd.Close acReport, Me.Name

(Note that you have a syntax error in your DoCmd step in your post).

Also, when you do this, your form will get an Error 2501 ("You
cancelled
the
action...") and you'll need to trap for that error in the form's code
if
you
don't want the user to see that error.

I'm sure it's possible to make the code work in your form, which is a
better
place to put it (not efficient to begin opening a report and then
decide
to
cancel it -- better to not open the report in the first place). Can you
give
us more info about how you want to use it in the form?

--

Ken Snell
<MS ACCESS MVP>

Trying different approach, cold not make code work off form, trying code
on
Open Event of Report
It gives messagebox but still opens the Report. What can I change?

If Forms!TimeCards![Job Description] = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
End If
DoCmd.Close , acReport






Try this:

If Forms!TimeCards!Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If


Or, because you're running the code in the subform, you can use a more
generic example:

If Me.Parent.Job Description = "None" Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
--

Ken Snell
<MS ACCESS MVP>

I have a main form named TimeCards with a sub-form on it named
FTimeBillingSub
On the Main form is a memo field named Job Description, its default is
set
to '"None"
On the sub-form is a Label with code attached to it.
I want on the on click event of the Label on the sub-form for the
Invoice
not to open, (i.e. the code to not run) if the memo field on the main
form
says None.
I tried the below code, but it did not work???



If ("Forms!TimeCards!Job Description") = None Then
MsgBox "You Cannot Invoice Without a Job Description"
Exit Sub
End If
 
Back
Top