Private Sub Problem.

[Spark021]

Hi guys.

Okay I have a Private Sub on form PlotF:

Private Sub SetCheck212()
    If Me.[Check161] And Me.[Check169] And _
       Me.[Check167] And Me.[Check181] And _
       Me.[Check261] And Me.[Check189] And _
       Me.[Check187] And Me.[Check195] And _
       Me.[Check203] And Me.[Check201] Then
        Me.Check212 = True
    Else
        Me.Check212 = False
    End If
End Sub

Private Sub Check161_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check169_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check167_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check181_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check261_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check189_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check187_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check195_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check203_AfterUpdate()
    Call SetCheck212
End Sub

Private Sub Check201_AfterUpdate()
    Call SetCheck212
End Sub

Some of the checkboxes are locked as I want to force the user to check them on another form (InvoicedF) but I want them to be displayed PlotF as well.

So I want to call the Private Sub from another form. So that the Check212 still automatically checks to true with out PlotF open on the screen.

Is this possible, Do I have to make this private sub a public sub?
If so how do I do this?

I wouldn't consider myself experienced with Access I only know the basics. So if you could please dumb it down for me that would be great

Thanks in advance :banghead:

 

[marlan]

Yup! If you want to run the Sub from another form (actually: module), you must make it public.

NOTE: If the form is not loaded (=opened), checking the value of a control will result an error!
You can:
either - handle this error (google "MS ACCESS VBA Error handling")
or - check if the form is loaded, before running the code: http://www.techonthenet.com/access/forms/form_loaded.php

All the best!

 

[Spark021]

Yup! If you want to run the Sub from another form (actually: module), you must make it public.

NOTE: If the form is not loaded (=opened), checking the value of a control will result an error!
You can:
either - handle this error (google "MS ACCESS VBA Error handling")
or - check if the form is loaded, before running the code: http://www.techonthenet.com/access/forms/form_loaded.php

All the best!

Thank you for the reply.

If I was to change Private Sub SetCheck212() to Public Sub SetCheck212()

Would I have to change all the other private subs that are calling the public sub to public as well?

Then I assume on my other form modual I would call the public sub in exactly the same way?

 

[JHB]

Actually place the code in a module, not in a form's module.
The you need also a reference to which form you're a doing the test, use a parameter when you call the test. The reference is also used when you want to set a checkbox to true/false.
Then also use mean full names for your control - do you thing you can remember what Check167, Check161, ... is in a month!

 

[marlan]

If I was to change Private Sub SetCheck212() to Public Sub SetCheck212()

Would I have to change all the other private subs that are calling the public sub to public as well?

No, make public only what only what you want to be called publicly.

Then I assume on my other form modual I would call the public sub in exactly the same way?

Probably... but even if so, I must note this is very pore practice, you should follow JHB's suggestion to have forms communicate via a global module, and have meaning full names for your controls.

You can read this for better understanding of variables and scope: http://www.excelfunctions.net/VBA-Variables-And-Constants.html

ATB

 

[Spark021]

Thank you for the advice and help guys.

I have put the code into a separate module (CheckPlotCompleteM) as suggested.
I am now getting an error which was expected as previously stated.

I am getting the following error:
Runtime error:2450

So the access is saying the form must be open for me to call a control on the form?
Is there any way around this?

Here is my code:

Public Sub SetCheck212()
If [Forms]![PlotF]![Check161] And [Forms]![PlotF]![Check169] And [Forms]![PlotF]![Check167] And [Forms]![PlotF]![Check181] And [Forms]![PlotF]![Check261] And [Forms]![PlotF]![Check189] And [Forms]![PlotF]![Check187] And [Forms]![PlotF]![Check195] And [Forms]![PlotF]![Check203] And [Forms]![PlotF]![Check201] Then
[Forms]![PlotF]![Check212] = True
Else
[Forms]![PlotF]![Check212] = False
End If
End Sub

I am calling the sub with this code:

Private Sub FirstFixInvoiced_AfterUpdate()
Call SetCheck212
End Sub


As previously suggested, I am going to go through and change all the control names to more relevant names. As I understand this looks really messy at the moment. I would just like to get it working before I change the control names. So I don't confuse myself any further.

Thanks for all the help so far

 

[JHB]

Hmm - does post disappear, I think I've this morning made a replay, (or maybe I hit the wrong button), but then I'll write it again!

I don't think you understand all what I wrote, therefore I've made an example for you in the attached database, (open the two forms in it an click the checkboxes):
I've used some of your control names, so it is easier for you to follow.

Below is the code in the (public) module, remark I'm using a parameter for pointing back to the form who called the procedure.

Sub SetCheck212(TheCallingForm As Form)
    If TheCallingForm.[Check2] And TheCallingForm.[Check4] And _
       TheCallingForm.[Check6] And TheCallingForm.[Check8] Then
       TheCallingForm.Check212 = True
    Else
        TheCallingForm.Check212 = False
    End If
End Sub

Here is the code in the one of the forms.

Private Sub Check2_AfterUpdate()
  Call SetCheck212(Me)
End Sub

Private Sub Check4_AfterUpdate()
  Call SetCheck212(Me)
End Sub

Private Sub Check6_AfterUpdate()
  Call SetCheck212(Me)
End Sub

Private Sub Check8_AfterUpdate()
  Call SetCheck212(Me)
End Sub

 

[Spark021]

Hmm - does post disappear, I think I've this morning made a replay, (or maybe I hit the wrong button), but then I'll write it again!

I don't think you understand all what I wrote, therefore I've made an example for you in the attached database, (open the two forms in it an click the checkboxes):
I've used some of your control names, so it is easier for you to follow.

Below is the code in the (public) module, remark I'm using a parameter for pointing back to the form who called the procedure.

Sub SetCheck212(TheCallingForm As Form)
    If TheCallingForm.[Check2] And TheCallingForm.[Check4] And _
       TheCallingForm.[Check6] And TheCallingForm.[Check8] Then
       TheCallingForm.Check212 = True
    Else
        TheCallingForm.Check212 = False
    End If
End Sub

Here is the code in the one of the forms.

Private Sub Check2_AfterUpdate()
  Call SetCheck212(Me)
End Sub

Private Sub Check4_AfterUpdate()
  Call SetCheck212(Me)
End Sub

Private Sub Check6_AfterUpdate()
  Call SetCheck212(Me)
End Sub

Private Sub Check8_AfterUpdate()
  Call SetCheck212(Me)
End Sub

Thank you for the help again.
I still cant get it to work. Here is a link for my database ) it is too big to attach:

https://www.dropbox.com/s/b2qhm9vjscz6zva/Database V1.accdb?dl=0

(It is a working progress) for you to take a look at if you don't mind

Here is some navigation tips for my database. So you can see what I mean

From MainMenuF click Site Records
Builder - Test1
Development - TestDev1
Click Plot 1 (Runswick)
I have First Fix, Second Fix, Final Fix.

I want to make the Plot Complete checkbox tick automatically which it does when all other checkboxes are true. I also want to to show up on the Continuous Form (BDPSearchF). All this works

But If I don't have any of these forms open and from the main menu I click invoicing:
Builder - Test1
Development - TestDev1
I want to check the invoiced buttons for 1st, 2nd and Final here. (The others will be locked on PlotF)
But I can not get the Plot Completed (On PlotF) to Check From InvoiceContF when all three check boxes are ticked on InvoiceContF.

I get the following error Run-Time error '2465'.

I have gone wrong somewhere

I appreciate all your help, thanks again :banghead:

 

[JHB]

..
But I can not get the Plot Completed (On PlotF) to Check From InvoiceContF when all three check boxes are ticked on InvoiceContF.

I get the following error Run-Time error '2465'.

I have gone wrong somewhere

No ofcause not, then the calling form is InvoiceContF!
You get the error because InvoiceContF doesn't have the checkboxes you're testing for in the procedure/sub in the SetCheck212!

..
I have gone wrong somewhere

I think it is more a communication problem, I've read you question as if you've some (two) forms with the same checkboxes in and you want to set "Plot Completed" at the form you're working at.
But that isn't actually the case, so you've to do it in another way, you need to run an update query where you are checking if all fields which you're testing for in SetCheck212 is set to true, (here meaningful names could have done it easy to copy and paste into a query, but ....)!