Open A reprt with Conditions from a subform

A

Alvin

I have the following code in the On Open Event of a Report. I am new to VB
Code but am learning. I can get one condition to work when I use it Like this.

----------- It Works Like This---------------

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
End If
End Sub
---------------------------------------------------------------------------------------

but I don't know how to add the second or third. Here are the conditions
and actual names I want to use something Like the following where I can Make
sure needed field have been entered before viewing the Report.

----------------This way I can't get it to work---------------------Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
If IsNull(Forms!Breeders![Breeders subform].Form![BreadFemale]) Then
MsgBox "You Must Choose A Female To Be Bred! " & Chr(13) &
Chr(10) & Chr(10) & "You Have Chosen ( " & [Breeders
subform].Form![BreadFemale].Column(1) & " ) To Breed With ( " &
[Form_Breeders]![Name] & " ) You Must Now Enter a Breed Date! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
End If
End If
End Sub
 
S

Steve Schapel

Alvin,

The general concept you are aiming at is *almost* there. I think it
would work like this...

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
ElseIf IsNull(Forms!Breeders![Breeders subform].Form![BreadFemale]) Then
MsgBox "You Must Choose A Female To Be Bred! " & Chr(13) &
Chr(10) & Chr(10) & "You Have Chosen ( " & [Breeders
subform].Form![BreadFemale].Column(1) & " ) To Breed With ( " &
[Form_Breeders]![Name] & " ) You Must Now Enter a Breed Date! ",
vbInformation, "Breed Manager"
Cancel = -1
End If
End Sub

However, a few comments that are hpefully helpful...

- I am surprised that this section of your code works:
..To Breed With ( " & [Form_Breeders]![Name] & " )
I would have expected you to need Forms![Form_Breeders]![Name]

- In any case, Name is a Reserved Word (i.e. has a special meaning) in
Access, and it is strongly recommended not to use it as the name of a
field or control or database object.

- In code, I think it is preferable to use vbCrLf in the place of
Chr(13) & Chr(10)

- Instead of waiting for the Open event of the report, I would do this
validation on the event that triggers the opening of the report. For
example, from what you have told us so far, I would imagine there is a
command button that you click on the Breeders form to run the report, or
some other form-based event to run the report... am I right? If so, you
could do like this...

Private Sub YourButton_Click()
If IsNull(Me![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value." &
vbCrLf & "You Must Select A Female to Breed With ( " & Me![TheName] & ")
before you can View This Report!", vbInformation, "Breed Manager"
ElseIf IsNull(Me![Breeders subform].Form![BreadFemale]) Then
MsgBox "You Must Choose A Female To Be Bred! " & vbCrLf & "You
Have Chosen ( " & Me![Breeders subform].Form![BreadFemale].Column(1) &
") To Breed With ( " & Me![TheName] & ") You Must Now Enter a Breed
Date!", vbInformation, "Breed Manager"
Else
DoCmd.OpenReport "YourReport"
End If
End Sub

--
Steve Schapel, Microsoft Access MVP

I have the following code in the On Open Event of a Report. I am new to VB
Code but am learning. I can get one condition to work when I use it Like this.

----------- It Works Like This---------------

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
End If
End Sub
---------------------------------------------------------------------------------------

but I don't know how to add the second or third. Here are the conditions
and actual names I want to use something Like the following where I can Make
sure needed field have been entered before viewing the Report.

----------------This way I can't get it to work---------------------

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
If IsNull(Forms!Breeders![Breeders subform].Form![BreadFemale]) Then
MsgBox "You Must Choose A Female To Be Bred! " & Chr(13) &
Chr(10) & Chr(10) & "You Have Chosen ( " & [Breeders
subform].Form![BreadFemale].Column(1) & " ) To Breed With ( " &
[Form_Breeders]![Name] & " ) You Must Now Enter a Breed Date! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
End If
End If
End Sub
Click to expand...
 
A

Alvin

Thank you steve, Someone else suggested changing Name to something else and I
will do that but I gotta be ready because I am afraid of loosing my links,
Everyone can't be wrong lol:)! Besides I am still learning.
By the way.
On my subform I have a checkBox and on the main form I have a CmdButton that
opens a Form called Notes. Lets say a user selects a record in the subform
and then clicks on the CmdButton to add notes about that record. How can I
have the checkBox automaticaly insert a check that way the user will know
there are notes pertaining to that record.
SHewwwww I hope I explained that correctly.
Thanks again Steve
Steve Schapel said:
Alvin,

The general concept you are aiming at is *almost* there. I think it
would work like this...

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
ElseIf IsNull(Forms!Breeders![Breeders subform].Form![BreadFemale]) Then
MsgBox "You Must Choose A Female To Be Bred! " & Chr(13) &
Chr(10) & Chr(10) & "You Have Chosen ( " & [Breeders
subform].Form![BreadFemale].Column(1) & " ) To Breed With ( " &
[Form_Breeders]![Name] & " ) You Must Now Enter a Breed Date! ",
vbInformation, "Breed Manager"
Cancel = -1
End If
End Sub

However, a few comments that are hpefully helpful...

- I am surprised that this section of your code works:
..To Breed With ( " & [Form_Breeders]![Name] & " )
I would have expected you to need Forms![Form_Breeders]![Name]

- In any case, Name is a Reserved Word (i.e. has a special meaning) in
Access, and it is strongly recommended not to use it as the name of a
field or control or database object.

- In code, I think it is preferable to use vbCrLf in the place of
Chr(13) & Chr(10)

- Instead of waiting for the Open event of the report, I would do this
validation on the event that triggers the opening of the report. For
example, from what you have told us so far, I would imagine there is a
command button that you click on the Breeders form to run the report, or
some other form-based event to run the report... am I right? If so, you
could do like this...

Private Sub YourButton_Click()
If IsNull(Me![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value." &
vbCrLf & "You Must Select A Female to Breed With ( " & Me![TheName] & ")
before you can View This Report!", vbInformation, "Breed Manager"
ElseIf IsNull(Me![Breeders subform].Form![BreadFemale]) Then
MsgBox "You Must Choose A Female To Be Bred! " & vbCrLf & "You
Have Chosen ( " & Me![Breeders subform].Form![BreadFemale].Column(1) &
") To Breed With ( " & Me![TheName] & ") You Must Now Enter a Breed
Date!", vbInformation, "Breed Manager"
Else
DoCmd.OpenReport "YourReport"
End If
End Sub

--
Steve Schapel, Microsoft Access MVP

I have the following code in the On Open Event of a Report. I am new to VB
Code but am learning. I can get one condition to work when I use it Like this.

----------- It Works Like This---------------

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
End If
End Sub
---------------------------------------------------------------------------------------

but I don't know how to add the second or third. Here are the conditions
and actual names I want to use something Like the following where I can Make
sure needed field have been entered before viewing the Report.

----------------This way I can't get it to work---------------------

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms!Breeders![Breeders subform].Form!MatingOrderID) Then
MsgBox "You Have Chosen a Mating Order That Has No Value. " &
Chr(13) & Chr(10) & Chr(10) & "You Must Select A Female to Breed With ( " &
[Form_Breeders]![Name] & " ) before you can View This Report! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
If IsNull(Forms!Breeders![Breeders subform].Form![BreadFemale]) Then
MsgBox "You Must Choose A Female To Be Bred! " & Chr(13) &
Chr(10) & Chr(10) & "You Have Chosen ( " & [Breeders
subform].Form![BreadFemale].Column(1) & " ) To Breed With ( " &
[Form_Breeders]![Name] & " ) You Must Now Enter a Breed Date! ",
vbInformation, "Breed Manager"
Cancel = -1
Else
End If
End If
End Sub
Click to expand...
Click to expand...
 
S

Steve Schapel

Alvin,

Me.MySubform.Form!MyCheckbox = -1

Might be better to have the command button on the subform, I would say.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Value from combo box in message box 3
filtering a form based on the values of a subform 0
Changing Report Record Source 4
Cancel = True "variable not defined" 3
Conditionaly Protect Form Fields 1
Set focus on a different Form 1
Alter Report Captions from a Subform 3
Changing the subject line of a message to a set format 3

Top