Validating a Subform has a Record (1 Viewer)

Weekleyba

Registered User.
Local time
Yesterday, 19:53
Joined
Oct 10, 2013
Messages
586
I have a form, F_Project, that I want to force the user to fill in certain fields before the form record can be saved. Namely, Project Name, Engineer Name, & Project Phase. I have a Public Function that checks to see if there is anything in these, and that function is in the BeforeUpdate event of the form. This all works good. See attached snip.
I would like to add to the Public Function, that the Location is filled in as well. The problem is the Location combo is in a subform on F_Project.
Here's my Public Function:
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.
    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
              ctl.SetFocus
              boolResult = True
              Exit For
            End If
        End If
        Next ctl
ValidationOfControl = boolResult
   
End Function

Is there a way to add this by adding to the Public Function line something like this?

Code:
        If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Or ctl.ControlType = acForm Then

Or perhaps I'm not thinking of this correctly. Is a subform considered a control or just an object on the parent form?

I do have another way this is accomplished but I am trying to get it all taken care of with the Public Function in order to be a cleaner solution enabling it to all be within the BeforeUpdate event.

Thanks for any help.
 

Attachments

  • Form 1.png
    Form 1.png
    34.1 KB · Views: 183

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
Hi. This is a catch-22 situation. If the subform is linked to the main form, then you cannot create a new record in the subform without first saving a record in the main form. However, you now don't want to save the record in the main form unless there's a record in the subform, which is not possible without first saving the record on the main form.


I think the closest you can get to this is let the main form save the record, if there are no records in the subform. But, don't let it get changed/updated, if there's already a record in the subform and the location is empty. Of course, you can also put something in the BeforeUpdate event of the subform to make sure location is never empty.
 

vba_php

Forum Troll
Local time
Yesterday, 19:53
Joined
Oct 6, 2019
Messages
2,884
If the subform is linked to the main form, then you cannot create a new record in the subform without first saving a record in the main form. However, you now don't want to save the record in the main form unless there's a record in the subform, which is not possible without first saving the record on the main form.
That one made me laugh. I had to quote that...sounds like the great unsolvable problem....the irresistable force meets the immovable object. :p Just like this quote from the great Eric Dane.
Is a subform considered a control or just an object on the parent form?
when you drag and drop a form object from the db window in access to another form object when it's in design view, 2 things happen:

  1. a subform control is created
  2. a container is created
I believe both are essentially the same thing. check with the other guys about that though. the actual subform *object* that sits nested inside the main form *is* actually an object itself because it appears as a stand alone object in the db window, but because its sitting on the main form, when you look at it it's technically a control, just like a textbox or combo box. pointing to controls like textboxes and combos that are inside subforms is an extremely convoluted process and it doesn't have an answer really. I wrote a faq on the syntax required for many different scenarios years ago, but I don't think it's 100% accurate. I think questions in that regard are better suited to be taken on a case by case basis.
 

Weekleyba

Registered User.
Local time
Yesterday, 19:53
Joined
Oct 10, 2013
Messages
586
Understood. I was hoping there might be another way but, perhaps there is not.
Thanks for the help.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
Understood. I was hoping there might be another way but, perhaps there is not.
Thanks for the help.
Hi. The only other way is to use an unbound form and then use code to save the data entered on the form to their corresponding tables.
 

Weekleyba

Registered User.
Local time
Yesterday, 19:53
Joined
Oct 10, 2013
Messages
586
If you disabled the SF_Location until the three required fields were filled, could you then use the modified Public Function with the ctl.ControlType = acform?
This would ensure the main forms record is created first.
But would the modified Public Function actually work?
Just a thought
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
If you disabled the SF_Location until the three required fields were filled, could you then use the modified Public Function with the ctl.ControlType = acform?
This would ensure the main forms record is created first.
But would the modified Public Function actually work?
Just a thought
Hi. Just curious, who are you asking?
 

Weekleyba

Registered User.
Local time
Yesterday, 19:53
Joined
Oct 10, 2013
Messages
586
Not really asking anyone specifically, just the forum.
Perhaps I should have used ‘I’ instead of ‘You’ but does it really matter?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
Not really asking anyone specifically, just the forum.
Perhaps I should have used ‘I’ instead of ‘You’ but does it really matter?
Hi. It just confused me a little. Thanks for clarifying.
 

vba_php

Forum Troll
Local time
Yesterday, 19:53
Joined
Oct 6, 2019
Messages
2,884
Weekley,

Is this thread solved? There's been a little bit of a stoppage here.....
could you then use the modified Public Function with the ctl.ControlType = acform?
the ctl.ControlType collection does not return a form object in any part of the enumerations that would be returned by your loop in the code, not that I know of anyway. it returns things like:
Code:
accheckbox
actextbox
accombobox
aslistbox
etc...
so not really sure what you mean by that comment.....
 

Weekleyba

Registered User.
Local time
Yesterday, 19:53
Joined
Oct 10, 2013
Messages
586
Thanks VBA_PHP.
That is what I needed to know.
I thought it might work since VBA recognized it and compiled it but alas, it will not.
 

Micron

AWF VIP
Local time
Yesterday, 20:53
Joined
Oct 20, 2018
Messages
3,476
I use tables for temp records for something like this. I'd call them temp tables, but that has a more negative connotation. Anyway, you can bind the main and subform to tables that aren't linked and do whatever is required. This eliminates the need for lots of code to deal with unbound controls, plus provides properties that would not otherwise be available, such as OldValue. When done, you validate whatever needs to be validated, then just run append queries back to the primary tables, then flush the temp data.
 

Users who are viewing this thread

Top Bottom