Solved Null Value Verification

[Emma35]

Hi All,
I have a main form called frm_AddNewShift and a related subform called SubRecords. I want to be able to verify that the four controls on the main form have been populated before i allow access to the subform. I added the following code to the GotFocus event of the first control on the subform which is called SampleTime. I'm getting a syntax error but i don't know how to correct it ?. Is there a better way of doing this ?

Thanks

Private Sub SampleTime_GotFocus()
If IsNull [Forms]![frm_AddNewShift]![Block]
  MsgBox "You Must Enter a Block", vbCritical, "You forgot something..."
  Cancel = True
  Me.Parent.Block.SetFocus
  Exit Sub
End Sub

 

[sonic8]

• IsNull is a function. You must pass the value to check as argument to it: IsNull([Forms]![frm_AddNewShift]![Block])
• Your If block is missing a Then and an End If
• The token Cancel is not defined in your code. The GotFocus event happens after the control got (past tense!) the focus. So, it cannot be canceled because it happened already.
• Exit Sub immediately before End Sub makes little sense but doesn't do any harm.

 

[bob fitz]

Hi All,
I have a main form called frm_AddNewShift and a related subform called SubRecords. I want to be able to verify that the four controls on the main form have been populated before i allow access to the subform. I added the following code to the GotFocus event of the first control on the subform which is called SampleTime. I'm getting a syntax error but i don't know how to correct it ?. Is there a better way of doing this ?

Thanks

Private Sub SampleTime_GotFocus()
If IsNull [Forms]![frm_AddNewShift]![Block]
  MsgBox "You Must Enter a Block", vbCritical, "You forgot something..."
  Cancel = True
  Me.Parent.Block.SetFocus
  Exit Sub
End Sub

Perhaps you could set the sub form as Not enabled. Then put your validation code in the Main forms Before Update event and in the Main forms On Current event

 

[Pat Hartman]

I added the following code to the GotFocus event of the first control on the subform which is called SampleTime. I'm getting a syntax error but i don't know how to correct it ?. Is there a better way of doing this ?

Therein lies the problem. Validation code belongs in the Form's BeforeUpdate event NOT EVER in the GotFocus event. The BeforeUpdate event is the last event that runs before the record gets saved and you get to cancel the save - that's what the Cancel = True is for but it only works in Events with a Cancel argument, if there are any errors found.

When you put your validation code in the correct event, you don't need work arounds.

In newer versions of Access, Access will not allow you to add records from the subform if the main form has not been populated and saved. If you want to add additional code because your version doesn't block this action, then the correct event to use is the subform's BeforeInsert event. This event runs as soon as any character is typed into the subform. Your code checks the value of the PK in the main form. If it is populated, then you can allow the event to continue. Otherwise, you cancel the event AND undo the typing.

Here's an example from one of my subforms:

Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.Parent!PersonID & "" = "" Then
        Cancel = True
        MsgBox "Please choose a person or create a new person record first.", vbOKOnly
        Me.Undo
        Me.Parent!cboFindPerson.SetFocus
        Exit Sub
    End If
End Sub

 

[Emma35]

Therein lies the problem. Validation code belongs in the Form's BeforeUpdate event NOT EVER in the GotFocus event. The BeforeUpdate event is the last event that runs before the record gets saved and you get to cancel the save - that's what the Cancel = True is for but it only works in Events with a Cancel argument, if there are any errors found.

When you put your validation code in the correct event, you don't need work arounds.

In newer versions of Access, Access will not allow you to add records from the subform if the main form has not been populated and saved. If you want to add additional code because your version doesn't block this action, then the correct event to use is the subform's BeforeInsert event. This event runs as soon as any character is typed into the subform. Your code checks the value of the PK in the main form. If it is populated, then you can allow the event to continue. Otherwise, you cancel the event AND undo the typing.

Here's an example from one of my subforms:

Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.Parent!PersonID & "" = "" Then
        Cancel = True
        MsgBox "Please choose a person or create a new person record first.", vbOKOnly
        Me.Undo
        Me.Parent!cboFindPerson.SetFocus
        Exit Sub
    End If
End Sub

Thanks for the help Pat...the code worked perfectly. My knowledge of VBA is atrocious as you can see !
Do you know a good resource or YouTube channel where i could learn the basics even ?

Thanks everyone for the help and suggestions
Em x

 

[Gasman]

PMFJI, Plenty of videos on Youtube for most subjects of Access.
Just try searching.

https://www.youtube.com/results?search_query=access+vba

 

[Emma35]

Thanks Gasman....i think i'll give the Programming Made EZ one a try

 

[Pat Hartman]

Do you know a good resource or YouTube channel where i could learn the basics even ?

I made two videos about validation. And I posted the database I used in the videos. You can use it to learn which events run when.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[GPGeorge]

Thanks for the help Pat...the code worked perfectly. My knowledge of VBA is atrocious as you can see !
Do you know a good resource or YouTube channel where i could learn the basics even ?

Thanks everyone for the help and suggestions
Em x

Here's a good presentation on data validation.

 

[Pat Hartman]

Thanks @GPGeorge But, here's a better link. There are two videos plus the database I used in the videos so you can download it and play with it. Rename your unzipped version and then modify the forms or create new ones. As long as you use the logging code the same way the forms I made do, your forms will create the same logging records in the display form that sits as the background.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[Emma35]

Pat/George thank you both for the links. Someday i might surprise everyone and actually answer a question in the forum with my new VBA knowledge

 

[Pat Hartman]

We look forward to it Most of my early VBA knowledge came from reading threads and trying to work out the solution. And then there was "the Access Cookbook" by Litwin. Look for the second edition. Even though it is old, VBA hasn't changed much and the book is a good way to learn VBA since it solves little problems. Some of the problems is solves have been incorporated into newer versions of Access.

 

[MajP]

Someday i might surprise everyone and actually answer a question in the forum with my new VBA knowledge

If you really want to do it then buy a used copy of this book and read it. Some of the stuff is outdated and that may be a challenge to firgure out what not to bother with. But 90% is still relevant and will take you from novice to expert. That is what I did. Then answered a lot of posts. You learn by answering.
Here is a copy for $7 dollars.

Access 2002 Desktop Developer's Handbook by Gunderloy, Mike, Litwin, Paul, Getz, Ken: Good (2001) Pap/Cdr. | Better World Books

Free Shipping - ISBN: 9780782140095 - Pap/Cdr. - Soft cover - Wiley & Sons, Incorporated, John - 2001 - Condition: Good - Used book that is in clean, average condition without any missing pages. - Access 2002 Desktop Developer's Handbook

www.abebooks.com

 

[jdraw]

Emma,
Late to the thread. I agree with advice you have been given. In addition, I have a list of articles and videos that I have "discovered" over a few years. You can access the list using the Database Planning and Design link in my signature.