Solved Directing a Public Function to a Different Form (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
I have a Public Function that I use to checks text boxes and combo boxes with a "*" in the tag.
If the no "*" in the tag, then it returns a True.
Works great in the BeforeUpdate event of the form and looks like this in the form:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'ValidationOfControl can be True or False.
'False - all tagged fields have data
'True - one or more tagged fields have missing data

Cancel = ValidationOfControl(Me)

End Sub

So my problem is, I would like to use this same public function in another form but having trouble with directing it there.
I have a form with a couple of subforms.
Namely, the parent form is F_Contract and the two subforms are SF_ContractInfo and SF_Payments.

In the SF_Payments BeforeUpdate I place the following:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = ValidationOfControl(Forms!F_Contract!SF_ContractInfo)

End Sub

But this give me a "Run-time error '13': Type mismatch" and highlights the light "Cancel = ValidationOfControl(Forms!F_Contract!SF_ContractInfo)" line.

Basically, I have one text box in the SF_ContractInfo subform, named txtAwardDate that has the "*" in the tag. I want the user to input an AwardDate prior to updating the Payment subform.

Am I not referring to the subform correctly?
Note both subforms are in separate tab controls, although I don't think that matters.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:24
Joined
Oct 29, 2018
Messages
21,357
Hi. No need to complicate it, I think. Simply using

Cancel=ValidationOfContro(Me)

should also work in subforms.
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
When I use Cancel=ValidationOfContro(Me), it gives me a "Run-time error '13': Type mismatch" and highlights
"If ctl.Tag = "*" And Len(ctl.Value & "") = 0 Then"
I assume it does this since it's not finding a "*" in the Tag. And since it Me. in argument, it's that directing it to the SF_Payment and not the SF_ContractInfo?
Here's a snip of the forms. The tab Contract has the SF_ContractInfo in it and the tab Payments has the SF_Payments in it.
Any other ideas?
1582517379869.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:24
Joined
Oct 29, 2018
Messages
21,357
No, I don't think that is the reason for the error. Some controls don't have a Value property. For example, Labels don't have it. So, you'll need to split those two checks or add a check for the control type.
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
Oh, ok. If I understand you correctly, since my public function checks both text boxes and combo boxes, and I only have a “*” on one text boxes tag and not on any combo box, it may be giving me this error. Is that correct?

Thanks DBguy, I’ll hit this first thing in the morning.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:24
Joined
Oct 29, 2018
Messages
21,357
Oh, ok. If I understand you correctly, since my public function checks both text boxes and combo boxes, and I only have a “*” on one text boxes tag and not on any combo box, it may be giving me this error. Is that correct?

Thanks DBguy, I’ll hit this first thing in the morning.
Hi. No, that's not what I was saying. If you post the actual code in your function, we can tell you where it may need to be modified. For one thing, you shared the following code earlier:
Code:
If ctl.Tag = "*" And Len(ctl.Value & "") = 0 Then
So, what I am saying is if ctl=Label Control, then the check for ...AND Len(ctl.Value & "") = 0 Then will cause an error because a Label does not have a Value property. So, the ctl.Value & "" part is probably giving you a Type Mismatch error. Just guessing...

But if you post your code, we can see if there's anything else that might be causing it, if not the above portion.
 

Dreamweaver

Well-known member
Local time
Today, 12:24
Joined
Nov 28, 2005
Messages
2,466
Sorry to butt in @theDBguy but this post may be of interest with what he/she is trying to do
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
DBguy,
Here's the code in the Public Function.
Do you see an issue in the code?

Code:
Public Function ValidationOfControl(frm As Access.Form) As Boolean
'This function requires the user to provide data to Combo and Text boxes that have a * in the Tag property.

If frm.Dirty = True Then

    Dim nl As String, ctl As Control
    Dim boolResult As Boolean
    nl = vbNewLine & vbNewLine
    
    For Each ctl In frm.Controls
        If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
            If ctl.Tag = "*" And Len(ctl.Value & "") = 0 Then
              msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
                    "You can't save this record until this data is provided." & nl & _
                    "Enter the data and try again . . . "
              Style = vbQuestion + vbOKOnly
              Title = "Required Data..."
              MsgBox msg, Style, Title
              ctl.SetFocus
              boolResult = True
              Exit For
            End If
        End If
        Next ctl
End If

ValidationOfControl = boolResult

End Function
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:24
Joined
May 21, 2018
Messages
8,463
Cancel = ValidationOfControl(Forms!F_Contract!SF_ContractInfo)
That returns a subform control and not a subform
Thus a type mismatch since you are passing a subform control and not a form
Cancel = ValidationOfControl(Forms!F_Contract!SF_ContractInfo.form)
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
Ok, I've tried a few things including "Cancel = ValidationOfControl(Forms!F_Contract!SF_ContractInfo.form)", but unfortunately I must be missing something here.
Add the MajP's suggesting stopped the error message but unfortunately, is not working.
Attached is a stripped down version of my db.
It's a bit messy after deleting a few things out but, it will demonstrate the problem.
When you generate a new Contract (new record in F_Contract), I do not want the user to be able create a record in the SF_Payment prior to input an Award Date in the SF_ContractInfo.
I am trying to do this via a Public Funcition in SF_Payment's Before Update event.

Can you take a look and see what I'm doing wrong?
 

Attachments

  • 0 DFM 11 Problem2.zip
    532.3 KB · Views: 213

cheekybuddha

AWF VIP
Local time
Today, 12:24
Joined
Jul 21, 2014
Messages
2,237
I think MajP is on the right track.

You need to check whether your subform control is called 'SF_ContractInfo' or something else - it's not always called the same as the form used as its SourceObject.

You need:
Code:
  Cancel = ValidationOfControl(Me.SubFormControlName.Form)

hth,

d
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
I will for sure double check that in the morning.
Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:24
Joined
Oct 29, 2018
Messages
21,357
Ok, I've tried a few things including "Cancel = ValidationOfControl(Forms!F_Contract!SF_ContractInfo.form)", but unfortunately I must be missing something here.
Add the MajP's suggesting stopped the error message but unfortunately, is not working.
Attached is a stripped down version of my db.
It's a bit messy after deleting a few things out but, it will demonstrate the problem.
When you generate a new Contract (new record in F_Contract), I do not want the user to be able create a record in the SF_Payment prior to input an Award Date in the SF_ContractInfo.
I am trying to do this via a Public Funcition in SF_Payment's Before Update event.

Can you take a look and see what I'm doing wrong?
Hi. Not trying to figure out what you're doing but just trying to fix the "type mismatch" error. See if you still get them now...
 

Attachments

  • 0 DFM 11 Problem2.zip
    554 KB · Views: 439

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
DBguy,
I'm still not getting this.
I create from scratch a new database to show the problem.
What I want it to do is:
Prior to updating anything in F_Payment, I want the controls in red to be inputted first.
I've tried the suggestions above but for some reason I cannot figure this out.
Can you please take a look at the attached db and edited to work or slap me around until I get it??
 

Attachments

  • Database3.zip
    30.6 KB · Views: 178

theDBguy

I’m here to help
Staff member
Local time
Today, 05:24
Joined
Oct 29, 2018
Messages
21,357
First of all, it looks like you didn't look at the sample database I posted earlier because I see you didn't change your code to avoid the Type Mismatch error, like I showed in that sample.

Second of all, maybe a little clarification is in order here. Generally, when you use a Public Function to validate a Form, you are validating a single form. However, in your case, you are trying to validate three (3) Forms at the same time (F_Contract, F_ContractInfo, and F_Payment). In theory, this rarely (if not never) works. For example, let's say you want to make sure before saving changes in Form1, that a certain value is entered first in Form2. However, at the same time, you don't want to save Form2 changes until some value is entered in Form3. Since the BeforeUpdate event fires for each individual Form, the above requirement, will not work because you will never get a chance to save any records that depend on other form - none of them gets saved. It's a Catch-22 situation.

One possible solution for this scenario is by using an Unbound Form to allow data entry in any Form and then providing a "Save" button to validate the data entry and only save the records to the appropriate tables after a successful data validation.
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
Thanks DBguy.
Your assumption that I did not look at your sample database is incorrect. I always dive into the help people send me but, in this case I did not catch the change you made to the public function. I only saw the change in the BeforeUpdate event of the SF_Payment form.

But related to the public function change, I do have a question.
The statement "Len(ctl.Value & "") = 0" Can you help me better understand this?
What I believe it is saying is:
The first part returns a Long containing the number of characters in a string. Which in this case is ctl.Value & "".
ctl.Value - this will be an integer 0 to some number, right? Is it possible for this to be null?
I just don't understand this yet. The ctl in this case, can be either a combo or text box.
What are the values of a text box and a combo box?
"" - Not sure why I need this? This represents a null string, right?
In general I understand that if Len(ctl.Value & "") = 0 then, we know the control does not have any data in it.

I see the catch 22, with the way you explained above.
I'll try the SAVE button approach.
I may try some enable = true between forms too.

Thanks for the help!
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
DBguy,
I did some more research and think I now understand.
I utilized the Immediate window in VBA editor to help me understand.

So
For a text box and it's related Value:
If the text box has nothing in it:
? Len(Forms!F_Contract.DateCreated.Value)
Null
If the text box has nothing in it but add the "" in the statement:
? Len(Forms!F_Contract.DateCreated.Value & "")
0
If the text box has "02/25/20" in it:
? Len(Forms!F_Contract.DateCreated.Value & "")
9
Lastly:
? Len("")
0

So I now see why this is needed in the code.

I'll keep working on my original problem.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:24
Joined
Oct 29, 2018
Messages
21,357
DBguy,
I did some more research and think I now understand.
I utilized the Immediate window in VBA editor to help me understand.

So
For a text box and it's related Value:
If the text box has nothing in it:
? Len(Forms!F_Contract.DateCreated.Value)
Null
If the text box has nothing in it but add the "" in the statement:
? Len(Forms!F_Contract.DateCreated.Value & "")
0
If the text box has "02/25/20" in it:
? Len(Forms!F_Contract.DateCreated.Value & "")
9
Lastly:
? Len("")
0

So I now see why this is needed in the code.

I'll keep working on my original problem.
Hi. Glad to hear you got that part sorted out. Sorry for the delay...
 

Weekleyba

Registered User.
Local time
Today, 07:24
Joined
Oct 10, 2013
Messages
586
DBguy,
I think I figured my problem out.
Can you take a quick look and tell me what you think?
 

Attachments

  • Database4.zip
    40.7 KB · Views: 268

theDBguy

I’m here to help
Staff member
Local time
Today, 05:24
Joined
Oct 29, 2018
Messages
21,357
DBguy,
I think I figured my problem out.
Can you take a quick look and tell me what you think?
Hi. Congratulations! Glad to hear you got it working. Good luck with your project.
 

Users who are viewing this thread

Top Bottom