Unanswered: VBA code for checking new records before inserting
I have a form A with a subform B linked to it and that subform has another subform C linked to it. (A subform linked to a subform linked to a form). Now, in the subform C, I want to enter records but those records need to be checked for certain conditions. The condition is that all the fields of the previous records on subform C should be filled, then only a new record will be allowed to be entered. So it is mandatory to supply all the values for a particular record before a new record can be started. Now I have not used VBA much before, so I was wondering if anyone of u Access Gurus can help me out with that and provide me with a code snippet for that?
I could have done the above thing easily with a macro but the problem is that because it is a subform embedded in another form (a subform in this case), so whenever the macro tries to run, Access doesnt allow it saying that the "form is closed and should be opened in another window". I guess Access requires any form to be open explicitly before it can execute any macros associated with that.
The above process is possible with VBA, but due to my lack of programing knowledge in VBA, I will not be able to come up with the required code in time and time is short for me here.
Just off the top...I would say that a For Each Control scenario would be the way to go but this would require a look at your database to test it. It would be difficult at best to try to explain the procedures required without falling into errors somewhere along the way.
If possible, perhaps attach a copy of your database (or very close similarity to it) so that we can apply and test code to accomplish the required task.
OK....This can be quite confusing, so please read carefully:
This is rather a unique but yet common problem when more than one SubForm is used within a Parent form. In order to carry out our task we must place our main conditional code within the Before Update event of SubForm 1 (Document Revisions) since this is the form that contains our main record selector and, we will indeed use the For Each Control scenario as mensioned earlier.
A initial problem arises from the fact that when focus is established onto SubForm 2 (SubForm Document Revisions) the Before Update event within SubForm 1 wants to fire thus prematurely issuing our established warnings of fields containing no data. This is not when we want the warnings to be issued since we simply want to enter the required data into SubForm 2. To get around this, we must create a public flag to inform our routine (again located within the Before Update event in SubForm 1) when we are actually on SubForm 1 and when we are on SubForm 2. We can do this by using the Mouse Move events located within both SubForms (In Form and Details sections of each form).
In the returned database (attached) you can see that I have created a module and in the module I have named our flag OnSForm which has been Publicly dim'ed as a Integer variable. Now, when the mouse passes over SubForm 1, the flag will indicate 0. In turn, when the mouse is passes over SubForm 2, the flag will indicate 1. By doing this, we can inform our conditional routine located within the Before Update event of SubForm 1 to fire only when we are no longer working on SubForm 2 (where our intent is to either modify fields in SubForm 1 or select a new record).
Now...we run into another problem. Since the Before Update event will only fire once (unless a field in SubForm 1 is modified) there is no way to check our conditions (re-fire the event) when we move focus back and forth between the two SubForms. To get around this a small Function has been placed into the new code module that fools the Before Update event in SubForm 1 to fire when we want it to. All the function does is copy the data held in the Project No field into a String variable, sets the field to Null, then places the data right back into the field again. This is carried out so rapidly that you can not tell it is happening. This simply simulates typing the data into the field which in turn reinisializes the Before Update event in SubForm 1 so that when a new record is selected, our conditions can be checked.
The code used to check conditions (have ALL fields in SubForm 2 been filled with data) is as follows:
If OnSForm = 0 Then ' Mouse is on SubForm 1
Dim Ctrl As Control
For Each Ctrl In [Form_SubForm: Document Revisions].Controls
If IsNull(Ctrl) Then
Cancel = 1
MsgBox "Please Note:" & vbNewLine & vbNewLine & "You have forgotten to enter " & _
"data into the " & Chr$(34) & Ctrl.NAME & Chr$(34) & " field." & _
vbNewLine & "You must do so before continuing.", vbExclamation, "Incomplete Data Entry"
The Code that is placed into the Mouse Move events for SubForms 1 (in both Form and Detail sections) is as follows:
OnSForm = 0
The Code that is placed into the Mouse Move events for SubForms 2 (in both Form and Detail sections) is as follows:
The Code (and Function) that is placed into the the new code Module (Module1, located within the Modules section of the database window) is as follows:
Public OnSForm As Integer
Public Sub ResetBeforeUpdate()
Dim Strg As String
Strg = [Form_Document Revisions].txtProjNo
OnSForm = 1
[Form_Document Revisions].txtProjNo = Null
[Form_Document Revisions].txtProjNo = Strg
Whew....I hope this makes sense, or at least helps a little.
Thanks a ton Pal. U r the Hero. That whole code and explanation was extremely helpful to me. I managed to successfully complete this module and get on with other phase. I will also try to see if the whole thing can be done differently, using the same code, just for increasing my understanding of this thing, and rather just cut-copy and paste. I hope at some point of time I can be of similar help to you or anybody else.