Opening a Form from two places...

[Nightowl4933]

Hi,

I have a Form which I open from two different forms, but which would use the same code on the Click event to set the configuration of that form.

As I'm getting fed up changing the Click code on one form and then updating the code on the Click event of the second form, wouldn't it be easier for me to use the same code in a Function and call it from the Command button's Click event?

If I can do this, could it just be a case of writing it like this:

Public Function OpeningTheForm
...
...
End Function

The reason I'm asking is that I've written some code on the Click event of one form, but it doesn't work when I copy/paste it to the second. I get the error:

'Run-time error '424':
Object required

Thanks

 

[Nightowl4933]

I think I know what's causing the problem prompting me to ask this question, but I really don't know why the code doesn't work the same way from both forms.

What I'm trying to do is confirgure the second form to highlight fields that can be edited, using a series of With, End With instructions, on the Click event from each of them.

This works with both forms:

If Granted > 1 Then
.boxGranted1.Visible = True
.boxGranted1.BorderColor = vbBlue
.boxGranted.Visible = False
.boxRejected.Visible = False
.Granted.BorderColor = vbBlue
.txtDateRejected.Visible = False
End If

...but this only works from 1 of the forms:

If optExpiryDate = 1 Then
.Expiry.Visible = False
.Limit.Visible = False
.ExpDate.Visible = False
.lblTimeLimitBox.Visible = True
ElseIf optExpiryDate = 2 Then
.Limit.Visible = True
.Expiry.Visible = False
.ExpDate.Visible = True
.lblTimeLimitBox.Visible = False
ElseIf optExpiryDate = 3 Then
.Limit.Visible = False
.Expiry.Visible = True
.Expiry.Enabled = True
.Expiry.Locked = False
.ExpDate.Visible = False
.lblTimeLimitBox.Visible = False
End If

When I step through the second bit of code, the value of 'optExpiryDate' isn't picked up from the new form, and therefore it's value isn't 1, 2 or 3, which explains the reason it doesn't work, but not why because it does using the same code on another form.

I'm confused...

Pete

 

[sneuberg]

I think we need to see more than this. Could you upload your database or if you can't do that post all of the code and a screen shots of the forms in design view?

 

[Nightowl4933]

Do you want the code from both the cmdButtons? They're 150 lines each and mostly (except the DIM lines) exactly the same.

I can't u/l the database as it has data in it, but I can do screen shots of the form being opened in design view. Do you need screenshots of the 'source' forms, or just their code?

Thanks

 

[Nightowl4933]

Here's the code to start with...

Private Sub cmdEdit_Click()

'=================================================================================
'NOTE: Changes to this form must be replicated in frmSearchResults, cmdOpenRecord
'=================================================================================

Dim strAuthority As String
Dim strReference As String
Dim strTown As String
Dim strSearchString As String

strAuthority = txtAuthority.Value
strReference = txtInitPlanApp.Value
strTown = txtTown.Value
strSearchString = "[Initial Planning Application Reference] = '" & strReference & "' AND [Town] = '" & _
strTown & "' AND [Initial Planning Application Reference] = '" & strReference & "'"

DoCmd.OpenForm "frmApplicationUpdateResult", , , strSearchString, , , OpenArgs:=Me.Name

With Me
.Visible = False
.Modal = False
End With

'Setting up the initial view of the frmApplicationUpdateResult form...
With Forms!frmApplicationUpdateResult
.boxOtherReferences.Visible = True
.SubPlanApp.BorderColor = vbRed
.SubPlanApp.Enabled = True
.SubPlanApp.Locked = False
.boxComments.Visible = True
.Comments.BorderColor = vbRed
.Comments.Enabled = True
.Comments.Locked = False

'Setting up the Hydrant consultation area, if Planning Condition/Obligation paragraph applied...
If chkConsulted = False Then
.boxConsulted.Visible = True
.chkConsulted.Visible = True
.chkConsulted.Enabled = True
.chkConsulted.Locked = False
.boxConsultDate.Visible = True
.ConsultDate.Visible = True
.ConsultDate.Enabled = True
.ConsultDate.Locked = False
.ConsultDate.BorderColor = vbRed
.lblConsulted.BorderStyle = 1
.lblConsulted.BorderColor = vbRed
Else
.boxConsulted.Visible = False
.chkConsulted.Visible = True
.ConsultDate.Visible = True
.ConsultDate.Enabled = True
.ConsultDate.Locked = False
.ConsultDate.BorderColor = vbBlue
End If

'Setting up the Planning decision outcome area...
If Granted > 1 Then
.boxGranted1.Visible = True
.boxGranted1.BorderColor = vbBlue
.boxGranted.Visible = False
.boxRejected.Visible = False
.Granted.BorderColor = vbBlue
.txtDateRejected.Visible = False
End If

If txtDateRejected > 1 Then
.boxRejected1.Visible = True
.boxRejected1.BorderColor = vbBlue
.boxRejected.Visible = False
.boxGranted.Visible = False
.Granted.Visible = False
.txtDateRejected.Visible = True
.txtDateRejected.BorderColor = vbBlue
End If

If Granted < 1 And txtDateRejected < 1 Then
If Granted < 1 Then
.boxGranted.Visible = True
.Granted.Enabled = True
.Granted.Locked = False
ElseIf txtDateRejected < 1 Then
.boxRejected.Visible = True
.txtDateRejected.Enabled = True
.txtDateRejected.Locked = False
End If
End If

'Setting up the Grid Reference area for editing...
If Easting <= 0 Then
.Easting.Enabled = True
.Easting.Locked = False
.Easting.BorderColor = vbRed
End If

If Northing <= 0 Then
.Northing.Enabled = True
.Northing.Locked = False
.Northing.BorderColor = vbRed
End If

'Setting up the Funding area...
.boxFunding.Visible = True
.optS106.Enabled = True
.optS106.Locked = False
.optCIL.Enabled = True
.optCIL.Locked = False
.optOther.Enabled = True
.optOther.Locked = False
.Secured.Enabled = True
.Secured.Locked = False
.AmtHeld.Enabled = True
.AmtHeld.Locked = False
.AmtReceived.Enabled = True
.AmtReceived.Locked = False
.AmtSecured.Enabled = True
.AmtSecured.Locked = False

'Setting up the Expiry Date area...
If optExpiryDate = 1 Then
.Expiry.Visible = False
.Limit.Visible = False
.ExpDate.Visible = False
.lblTimeLimitBox.Visible = True
ElseIf optExpiryDate = 2 Then
.Limit.Visible = True
.Expiry.Visible = False
.ExpDate.Visible = True
.lblTimeLimitBox.Visible = False
ElseIf optExpiryDate = 3 Then
.Limit.Visible = False
.Expiry.Visible = True
.Expiry.Enabled = True
.Expiry.Locked = False
.ExpDate.Visible = False
.lblTimeLimitBox.Visible = False
End If

.cmdReturn.SetFocus

End With

End Sub

 

[Nightowl4933]

...and this is from the form that doesn't work...

Private Sub cmdOpenRecord_Click()

'=====================================================================================
'NOTE: Changes to this form must be replicated in frmApplicationDetails,cmdOpenRecord
'=====================================================================================

Dim strAuthority As String
Dim strReference As String
Dim strTown As String
Dim strSearchString As String

strAuthority = Authority.Value
strReference = Reference.Value
strTown = Town.Value
strSearchString = "[Initial Planning Application Reference] = '" & strReference & "' AND [Town] = '" & _
strTown & "' AND [Initial Planning Application Reference] = '" & strReference & "'"

DoCmd.OpenForm "frmApplicationUpdateResult", , , strSearchString, , , OpenArgs:=Me.Name

With Me
.Visible = False
.Modal = False
End With

'Setting up the initial view of the frmApplicationUpdateResult form...
With Forms!frmApplicationUpdateResult
.boxOtherReferences.Visible = True
.SubPlanApp.BorderColor = vbRed
.SubPlanApp.Enabled = True
.SubPlanApp.Locked = False
.boxComments.Visible = True
.Comments.BorderColor = vbRed
.Comments.Enabled = True
.Comments.Locked = False

'Setting up the Hydrant consultation area, if Planning Condition/Obligation paragraph applied...
If chkConsulted = False Then
.boxConsulted.Visible = True
.chkConsulted.Visible = True
.chkConsulted.Enabled = True
.chkConsulted.Locked = False
.boxConsultDate.Visible = True
.ConsultDate.Visible = True
.ConsultDate.Enabled = True
.ConsultDate.Locked = False
.ConsultDate.BorderColor = vbRed
.lblConsulted.BorderStyle = 1
.lblConsulted.BorderColor = vbRed
Else
.boxConsulted.Visible = False
.chkConsulted.Visible = True
.ConsultDate.Visible = True
.ConsultDate.Enabled = True
.ConsultDate.Locked = False
.ConsultDate.BorderColor = vbBlue
End If

'Setting up the Planning decision outcome area...
If Granted > 1 Then
.boxGranted1.Visible = True
.boxGranted1.BorderColor = vbBlue
.boxGranted.Visible = False
.boxRejected.Visible = False
.Granted.BorderColor = vbBlue
.txtDateRejected.Visible = False
End If

If txtDateRejected > 1 Then
.boxRejected1.Visible = True
.boxRejected1.BorderColor = vbBlue
.boxRejected.Visible = False
.boxGranted.Visible = False
.Granted.Visible = False
.txtDateRejected.Visible = True
.txtDateRejected.BorderColor = vbBlue
End If

If Granted < 1 And txtDateRejected < 1 Then
If Granted < 1 Then
.boxGranted.Visible = True
.Granted.Enabled = True
.Granted.Locked = False
ElseIf txtDateRejected < 1 Then
.boxRejected.Visible = True
.txtDateRejected.Enabled = True
.txtDateRejected.Locked = False
End If
End If

'Setting up the Grid Reference area for editing...
If Easting <= 0 Then
.Easting.Enabled = True
.Easting.Locked = False
.Easting.BorderColor = vbRed
End If

If Northing <= 0 Then
.Northing.Enabled = True
.Northing.Locked = False
.Northing.BorderColor = vbRed
End If

'Setting up the Funding area...
.boxFunding.Visible = True
.optS106.Enabled = True
.optS106.Locked = False
.optCIL.Enabled = True
.optCIL.Locked = False
.optOther.Enabled = True
.optOther.Locked = False
.Secured.Enabled = True
.Secured.Locked = False
.AmtHeld.Enabled = True
.AmtHeld.Locked = False
.AmtReceived.Enabled = True
.AmtReceived.Locked = False
.AmtSecured.Enabled = True
.AmtSecured.Locked = False

'Setting up the Expiry Date area...
If optExpiryDate = 1 Then
.Expiry.Visible = False
.Limit.Visible = False
.ExpDate.Visible = False
.lblTimeLimitBox.Visible = True
ElseIf optExpiryDate = 2 Then
.Limit.Visible = True
.Expiry.Visible = False
.ExpDate.Visible = True
.lblTimeLimitBox.Visible = False
ElseIf optExpiryDate = 3 Then
.Limit.Visible = False
.Expiry.Visible = True
.Expiry.Enabled = True
.Expiry.Locked = False
.ExpDate.Visible = False
.lblTimeLimitBox.Visible = False
End If

.cmdReturn.SetFocus

End With

End Sub

 

[Nightowl4933]

So, the form looks like Image1 with the first bit of code and image2 with the second bit.

image3 shows that area of the form in design mode.

Hope this helps,

Pete

 

[sneuberg]

When I step through the second bit of code, the value of 'optExpiryDate' isn't picked up from the new form, and therefore it's value isn't 1, 2 or 3, which explains the reason it doesn't work, but not why because it does using the same code on another form.

It appears from the code that optExpiryDate is something on the opening form and not the form being opened. So for the form that doesn't work I'd double check the name of that control. When you type Me. does IntelliSense show it on the form that doesn't work?

 

[Gasman]

As you state, trying to maintain the code from two forms is a little trying. Have you considered putting the code in destination form and passing the few arguments you test for to the destination form.?

 

[Nightowl4933]

When I type this...

"if optexpirydate=1 then "

and click Enter, it changes to...

"If optExpiryDate = 1 Then "

...so I assume it 'knows' it exists and, on the form itself, using Me.op shows the control in the list (image4)

 

[sneuberg]

Please put

Debug.Print "optExpiryDate =  " & optExpiryDate

before

If optExpiryDate = 1 Then

and tell what shows in the Immediate Window when you run the code.

Also please check the values that are assigned to the check boxes. I mean is "1" assigned to "None"?

 

[Nightowl4933]

I get the following in the Immediate window:

optExpiryDate =

1 is assigned to None
2 is assigned to Years
3 is assigned to Date

If I run it from the cmdButton that works, I get the following...

optExpiryDate = 2

Thank you,

Pete

 

[gemma-the-husky]

can you just not call with an "openargs" value, and then set all the controls in the form's open event depending on the openarg setting.

 

[sneuberg]

I get the following in the Immediate window:

optExpiryDate =

1 is assigned to None
2 is assigned to Years
3 is assigned to Date

If I run it from the cmdButton that works, I get the following...

optExpiryDate = 2

Thank you,

Pete

You're getting 2 when None is selected? That's weird. Maybe you just need to recreate the option group or try copying and pasting the one from the form that works correctly.

 

[Nightowl4933]

can you just not call with an "openargs" value, and then set all the controls in the form's open event depending on the openarg setting.

I don't know much avbout OpenArgs, but quite possibly!

I've used it to remember the name of the form that opened it, but I wasn't aware these configuration settings could use it.

This has all got a bit out of hand, really. I started by trying to stop users from unwittingly editing a record when it's being viewed, and relying on a definite action to edit it - I've just let it grow like Topsy!

Someone will probably tell me there's a single command that can achieve the same thing!

Pete

 

[Nightowl4933]

You're getting 2 when None is selected? That's weird. Maybe you just need to recreate the option group or try copying and pasting the one from the form that works correctly.

Oops. No, I get 2 when Years is the selected option. I'm using the command to set the visibility of several controls based on the option value.

Erm, should I use "If optExpiryDate.Value = 1 Then "?

 

[sneuberg]

can you just not call with an "openargs" value, and then set all the controls in the form's open event depending on the openarg setting.

If you look at the OP's code you see that that would be quite a bit of information to include in the OpenArgs. I suppose he could concatenate it together in a string, delimit it with commas, and then use the split function to sort it out in the form open.

 

[Nightowl4933]

I suppose he could concatenate it together in a string, delimit it with commas, and then use the split function to sort it out in the form open.

I love it when techies talk dirty

 

[sneuberg]

Oops. No, I get 2 when Years is the selected option. I'm using the command to set the visibility of several controls based on the option value.

Erm, should I use "If optExpiryDate.Value = 1 Then "?

I don't think that will make any difference. I think I need to see the database to figure this out.

 

[gemma-the-husky]

I meant more

docmd.openform formname openargs:="X"

if the openargs is X then do whatever is necessary from inside the form.

Then two forms could open the form with openargs "X" (or indeed with a different openargs)