Extra On Click Code for Command Button

J

JohnB

Hi. I wonder if anyone can help with this. I have a
Command button on a Subform which currently opens a
linked form. See below for the On Click Event code. I
want to modify this to do two things. First to decide
whether to open a form at all, based on the contents of a
field in the Subform. Second, to decide which of two
forms to open, based on the contents of a field in the
main form.

The first decision would be based on the content of field
txtStage in the Subform. If this reads either ONE or TWO
then the process would stop and a Message Box would be
displayed. For any other content in this field, the
process would move on to step two. This would depend on
the content of field txtSubject, in the main form. If
this reads ECS then form frmGrades1 is opened. For any
other content, form frmGrades2 is opened. For each case,
the link would be as below, PlacementID.

Is this possible and if so, can anyone help with the
additional code needed? Thanks, JohnB


Private Sub CmdGrades_Click()
On Error GoTo Err_CmdGrades_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGrades1"

stLinkCriteria = "[PlacementID]=" & Me![PlacementID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdGrades_Click:
Exit Sub

Err_CmdGrades_Click:
MsgBox Err.Description
Resume Exit_CmdGrades_Click

End Sub
 
W

Wayne Morgan

Changes are in-line. You need a couple of If statements to check the values
you mentioned.

'-------------------------------------------------------
Private Sub CmdGrades_Click()
On Error GoTo Err_CmdGrades_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.txtStage = "ONE" Or Me.txtStage = "TWO" Then
If Me.Parent.txtSubject = "ECS" Then
stDocName = "frmGrades1"
Else
stDocName = "frmGrades2"
End If
stLinkCriteria = "[PlacementID]=" & Me![PlacementID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Your message here", vbOkOnly + vbInformation, "Check
Selection"
End If

Exit_CmdGrades_Click:
Exit Sub

Err_CmdGrades_Click:
MsgBox Err.Description
Resume Exit_CmdGrades_Click

End Sub
'-------------------------------------------------------

I have used the values you've given. Is the ONE and TWO actually text or are
they the numbers 1 and 2? If so, you will need to change the first IF to

If Me.txtStage = 1 Or Me.txtStage = 2 Then

Note that there are no quotes around numeric values. Quotes are for text
values (including numbers stored as text). The basic structure of this type
of statement is

If (something is true) Then
Do this
Else
Do this instead
End If

Then can be "nested" as above to do more than one check when the subsequent
checks rely of the value of the first check.
 
J

JohnB

Thanks for this Wayne. Yes the ONE and TWO fields are
text. I wont be able to test your code out till next
Monday but it looks as if it would do exactly what I want.
I see now how you can identify the subform and main forms
fields. Also I appreciate your explanation - I should
really have a try at building some of this myself. Thanks
again for taking the time over this. Cheers, JohnB
-----Original Message-----
Changes are in-line. You need a couple of If statements to check the values
you mentioned.

'-------------------------------------------------------
Private Sub CmdGrades_Click()
On Error GoTo Err_CmdGrades_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.txtStage = "ONE" Or Me.txtStage = "TWO" Then
If Me.Parent.txtSubject = "ECS" Then
stDocName = "frmGrades1"
Else
stDocName = "frmGrades2"
End If
stLinkCriteria = "[PlacementID]=" & Me! [PlacementID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Your message here", vbOkOnly + vbInformation, "Check
Selection"
End If

Exit_CmdGrades_Click:
Exit Sub

Err_CmdGrades_Click:
MsgBox Err.Description
Resume Exit_CmdGrades_Click

End Sub
'-------------------------------------------------------

I have used the values you've given. Is the ONE and TWO actually text or are
they the numbers 1 and 2? If so, you will need to change the first IF to

If Me.txtStage = 1 Or Me.txtStage = 2 Then

Note that there are no quotes around numeric values. Quotes are for text
values (including numbers stored as text). The basic structure of this type
of statement is

If (something is true) Then
Do this
Else
Do this instead
End If

Then can be "nested" as above to do more than one check when the subsequent
checks rely of the value of the first check.

--
Wayne Morgan
MS Access MVP


Hi. I wonder if anyone can help with this. I have a
Command button on a Subform which currently opens a
linked form. See below for the On Click Event code. I
want to modify this to do two things. First to decide
whether to open a form at all, based on the contents of a
field in the Subform. Second, to decide which of two
forms to open, based on the contents of a field in the
main form.

The first decision would be based on the content of field
txtStage in the Subform. If this reads either ONE or TWO
then the process would stop and a Message Box would be
displayed. For any other content in this field, the
process would move on to step two. This would depend on
the content of field txtSubject, in the main form. If
this reads ECS then form frmGrades1 is opened. For any
other content, form frmGrades2 is opened. For each case,
the link would be as below, PlacementID.

Is this possible and if so, can anyone help with the
additional code needed? Thanks, JohnB


Private Sub CmdGrades_Click()
On Error GoTo Err_CmdGrades_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGrades1"

stLinkCriteria = "[PlacementID]=" & Me![PlacementID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdGrades_Click:
Exit Sub

Err_CmdGrades_Click:
MsgBox Err.Description
Resume Exit_CmdGrades_Click

End Sub
Click to expand...


.
Click to expand...
 
W

Wayne Morgan

I see now how you can identify the subform and main forms
fields.
Click to expand...

Well, yes and no. Since you said the button was on the subform, I went that
way. "Me" simply refers to the form or report that the code is running on.
"Parent" is the correct designator to get to the main form from the subform.
However, going the other way is a little trickier. To refer to a control on
the subform from the main form it would be

Me!ctlNameOfSubformControl.Form!ctlNameOfControl

The subform is actually held in a control on the main form called a subform
control. This refers to the name of that control. Referring to subreports is
done the same way. To refer to a form from another, separate form or from a
query, you need to full path to the form which would replace the Me part,
after that it is the same. It would look like

Forms!frmNameOfForm!ctlNameOfControl

The Forms!frmNameOfForm is what would replace Me.
 
J

JohnB

Thanks for this additional info Wayne.I appreciate the
time you have taken to explain this. A very clear
explanation too. Thanks again, JohnB
 
J

JohnB

Hi again Wayne. I wonder if you still monitor this old
thread. If you dont reply to this Ill repost and keep
looking at both.

Your code works great. Just one slight problem. When the
Message Box does open and I click on its OK button, I get
a second message saying The action or method requires a
Form Name argument. Does this indicate a problem with the
code? In any case, how do I stop it appearing. Thanks
again for the help, JohnB
 
W

Wayne Morgan

John,

I just took a look at the code in the previous message and I don't see the
problem. Will you copy and paste the code you're using into a message?
 
J

JohnB

Hi Wayne. Heres the full code. As you see, Ive now used
actual field names and Ive added an AND function. It does
work perfectly, apart from the additional message. Thanks
for any help you can give. Cheers, JohnB

Private Sub CmdGrades_Click()
On Error GoTo Err_CmdGrades_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.cboPlacementStage = "PGCE Final" Or
Me.cboPlacementStage = "Yr3" Or Me.cboPlacementStage
= "Yr4" Then
If Me.Parent.cboSubject = "ECS" And
Me.cboPlacementStage = "Yr4" Then
stDocName = "frmGrades2"
Else
stDocName = "frmGrades1"
End If

stLinkCriteria = "[PlacementID]=" & Me!
[PlacementID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "You cannot enter Grades for this
Placement Stage", vbOKOnly + vbInformation, "Check
Selection """
End If


stLinkCriteria = "[PlacementID]=" & Me![PlacementID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdGrades_Click:
Exit Sub

Err_CmdGrades_Click:
MsgBox Err.Description
Resume Exit_CmdGrades_Click

End Sub
 
J

JohnB

Hi Wayne. My fault. I somehow managed to produce two
identical sets of the following code

stLinkCriteria = "[PlacementID]=" & Me![PlacementID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Ive taken the lower set out and alls well. Sorry to waste
your time. Thanks again for all the help. Regards, JohnB
 

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

Help with If, Then, Else strings please 4
Link criteria on Open Form Command Button 5
Open form and filter form & subform using combo-boxes 2
Command Button linked to SubForm 5
Run-time error '3218': could not update; currently locked 1
Re-Post - Search For Record 2
Open with Link Criteria and Columns in Datasheet 1
Need code fix for searching 2

Top