Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2018, 07:14 PM   #1
euphonium01
Newly Registered User
 
euphonium01's Avatar
 
Join Date: Nov 2018
Location: Stockton-on-Tees - UK
Posts: 14
Thanks: 8
Thanked 0 Times in 0 Posts
euphonium01 is on a distinguished road
VBA Code to check a Form for empty/Null Controls

I am using Access 2016. I have a Form with Controls that need to be ALL completed, ie, no Null values. I cheated a little and copied some code from an internet video where it Tagged each control I wanted to check, if there were any then the vba code opened the Form with the appropriate Control's background colored Red. When I ran the code for the first time it worked perfectly?

After saving the Form, I opened it again to check it worked and as soon as I tried to open the Form I got Run-Time Error 438: Object doesn't support this property or method when I click debug I get ctrl.BackColor = vbRed highlighted?

I checked the source where I got the code, the video was made in 2012, could this be the reason as I am using Access 2016, how do I fix this? I am unsure if I need to list all the code I used to get an answer, or if anyone has a better solution? The data is on a Page within the Form called ShippingInfo_Page, the main Form is called Orders.

To print an accurate shipping label I need to be sure no Controls are empty/Null, I don't really want to set the 'Required' option for each Control in the underlying Table to Yes. I'm certain there must be a 2016 version of the code I need, or another 'global' checking solution? Hope this all makes sense?
Paul.

euphonium01 is offline   Reply With Quote
Old 11-16-2018, 08:37 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,166
Thanks: 10
Thanked 3,887 Times in 3,830 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Code to check a Form for empty/Null Controls

What's the code? What control was it working on? From the error, I'd guess it's a control without that property.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-17-2018, 06:23 AM   #3
euphonium01
Newly Registered User
 
euphonium01's Avatar
 
Join Date: Nov 2018
Location: Stockton-on-Tees - UK
Posts: 14
Thanks: 8
Thanked 0 Times in 0 Posts
euphonium01 is on a distinguished road
Re: VBA Code to check a Form for empty/Null Controls

Hi Paul, many thanks for such a quick reply. I'm sorry about this long post but, it's hard to explain one's difficulty when asking experts for their advice, so I've posted all the codes used for this process just in case, I hope you understand?

I triple checked that every Control I needed checking had the correct Tag (as you will see below, the word 'Complete' is used for the Tag) and on the first test run it all worked great, after saving (and making a 'copy of' the form to be sure) it didn't work, and neither did the copy.

The code was in 4 parts, this triggers the first bit-

Private Sub cmdShipOrder_Click()
Dim ShipOrder

If StatusID = 0 Then
Beep
MsgBox "The Order is not Invoiced! Invoice it then try again", vbInformation, "White Rose"
Exit Sub
End If
ShipOrder = MsgBox("Are ALL Shipping details completed?", vbQuestion + vbYesNo, "White Rose")
If ShipOrder = vbNo Then
Me.ShippingInformationPage.SetFocus
CheckForEmpty
End If
Exit Sub
If ShipOrder = vbYes Then
MsgBox "The Order will be marked as Shipped", , "White Rose"
StatusID = 2
End If

End Sub


I believe the problem(s) may be here?-

Function CheckForEmpty() As Boolean

CheckForEmpty = True
ClearControlFormatting

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.Tag = "Complete" Then
If IsNull(ctrl) Then
ctrl.BackColor = vbRed
CheckForEmpty = False
End If
End If
Next

End Function


Or here-

Private Sub Form_OnCurrent()
ClearControlFormatting
End Sub


Or here-

Sub ClearControlFormatting()

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.Tag = "Complete" Then
ctrl.BackColor = vbWhite
End If
Next

End Sub


It's the ctrl.BackColor = vbWhite That is highlighted when I click 'debug' in the error window. The reason I believe the problem to be in the 2nd, 3rd or 4th codes is only due to elimination. The error occurs as soon as I try to open the Form, if I disable the OnCurrent - ClearControlFormatting' the Form opens OK, but then the error occurs when I click 'Ship Order' then 'No' to check for Null Controls, clicking 'Yes' to the question marks the Order as Shipped - as it should. But if I disable CheckForEmpty - ClearControlFormatting I don't get any errors, however, empty/null Controls are left alone and aren't highlighted in Red as requested?

The only item I noticed today that may also be a problem is that I have a 'SetFormState' code on this Form, one that disables the Invoice Control once clicked, then the Ship button once clicked etc, not sure if that could impact on the code(s) above?

I hope I'm making sense here, I am very new to vba and it's not easy. I also apologise for all the code being set left on here, it's set out logically and indented in my coding screen?
Paul
Since writing all the above, I've considered creating a very simple database with one Table and a simple Form, then apply the above codes and see what happens? It wouldn't answer why the main database has errors but, it might prove the coding is sound?

euphonium01 is offline   Reply With Quote
Old 11-17-2018, 06:44 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,350
Thanks: 92
Thanked 1,811 Times in 1,686 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: VBA Code to check a Form for empty/Null Controls

Last post was moderated.
Posing this to trigger email notifications
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-17-2018, 06:50 AM   #5
euphonium01
Newly Registered User
 
euphonium01's Avatar
 
Join Date: Nov 2018
Location: Stockton-on-Tees - UK
Posts: 14
Thanks: 8
Thanked 0 Times in 0 Posts
euphonium01 is on a distinguished road
Re: VBA Code to check a Form for empty/Null Controls

Oh, again forgive me for any ignorance, I am running Windows 10 64bit build 1803, and Office 2016 64bit, not sure if this is of any relevance what-so-ever? Sorry...
Paul
euphonium01 is offline   Reply With Quote
Old 11-17-2018, 07:28 AM   #6
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,701
Thanks: 317
Thanked 425 Times in 410 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: VBA Code to check a Form for empty/Null Controls

I think you need to test for the type of control or have you inadvertently put "Complete" in such a control without that property?

Debug.Print the ctrl.name and see what the control is?
HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 11-17-2018, 08:20 AM   #7
euphonium01
Newly Registered User
 
euphonium01's Avatar
 
Join Date: Nov 2018
Location: Stockton-on-Tees - UK
Posts: 14
Thanks: 8
Thanked 0 Times in 0 Posts
euphonium01 is on a distinguished road
Re: VBA Code to check a Form for empty/Null Controls

OMG: WOW: I can't believe it... I fixed it!!!!!!

It's a bit complicated (for me anyway) but here's what I did:
Following the comment from Gasman I checked all Controls that I wanted checking were capable of BackColor changes, they are, I tried it manually and they all went red.

I looked at some code I had in BeforeUpdate and there it was, an instruction telling the Form to SetFormState and in that code were statements that set action buttons to Enable/Not Enable Controls, basically this contradicted the ClearConrolFormatting set in OnCurrent, they were basically fighting against each other, at least this is my logic?

So I moved the code from BeforeUpdate to OnCurrent, then put ClearControlFormatting to only action when CheckForEmpty was triggered by the ShipOrder button, and bingo... it worked! I jumped and frightened the wife... she's not pleased!

It just goes to show that when you ask for advice, you look at all suggestions and can sometimes find the problem. Is it a regular 'thing' where a problem can suddenly become obvious, or am I just stupid for taking the fuel out and wondering why the engine wouldn't start? Thanks to everyone... on to the next problem... how do I mark this resolved?
Paul

euphonium01 is offline   Reply With Quote
Old 11-17-2018, 08:33 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,350
Thanks: 92
Thanked 1,811 Times in 1,686 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: VBA Code to check a Form for empty/Null Controls

Just approved the last post as it was also moderated
Congratulations by the way ....
Solutions are almost always obvious ...but only with hindsight

To mark the thread SOLVED, click the down arrow next to Thread Tools then click the 5th item
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
euphonium01 (11-17-2018)
Old 11-17-2018, 08:50 AM   #9
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,701
Thanks: 317
Thanked 425 Times in 410 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: VBA Code to check a Form for empty/Null Controls

Well I would not have thought that would fix it.

My impression/thoughts were exactly what the error was saying?

You were trying to set the backcolor on a control that did not have that property.?

If it does come back, check the ctrl.name.

Good luck though.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
euphonium01 (11-18-2018)
Old 11-17-2018, 09:57 AM   #10
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,546
Thanks: 368
Thanked 784 Times in 749 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: VBA Code to check a Form for empty/Null Controls

Quote:
Originally Posted by euphonium01 View Post
Is it a regular 'thing' where a problem can suddenly become obvious
Yes!

And also, you usually create the problem yourself!

Just the other day I was testing some code which opened a pop-up form and passed through a reference to a control on the form that was opening it.

In normal operation, both forms would stay open.

For testing I decided to use a form which was basically a splash screen. Unfortunately this form was designed to close after I pressed the button to open the next form. In other words there was nothing there, the control that was linked to disappeared from memory so to speak, so I get a puzzling error message.

This threw up an error which I posted about in another thread. I was convinced that ms-access was corrupt!

This should be a red flag to you, whenever you start blaming MS Access or somebody else's code, something else except yourself, then that's the red flag to tell you, look at your code you've done something!

I find the solution is in many cases is to recreate a very simple model of what you are trying to do in a separate test database. Use the least, the minimum amount of stuff from your development database, just enough to check and see if you can get it working there. This then proves your process, your code, and you then know to look for something else.

The other solution is to post a question in the Forum and let someone else to look at it. However, from experience I can tell you this is the most embarrassing way of solving a problem!

When somebody points out something so obvious, usually a minor error you've made. "The Shame" I rarely resort to posting in the Forum to solve a problem, unless I'm very tired, frustrated, and tearing my hair out..

Sent from my SM-G925F using Tapatalk
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 11-18-2018, 06:52 AM   #11
euphonium01
Newly Registered User
 
euphonium01's Avatar
 
Join Date: Nov 2018
Location: Stockton-on-Tees - UK
Posts: 14
Thanks: 8
Thanked 0 Times in 0 Posts
euphonium01 is on a distinguished road
Re: VBA Code to check a Form for empty/Null Controls

Thanks Uncle Gizmo, that's sound advice. I had posted that I was going to try a simple Table & Form to see if the code worked, but I came across the problem after taking that closer look. Knowing my level of knowledge on vba it surprised me that I found it, fortunately, the low level of vba knowledge also prevents me from getting embarrassed. However, I haven't posted a question to which I should know the answer....yet!

I'm certain embarrassment will come to me soon enough though lol. The other thing I'm finding fascinating is the many different ways an objective can be coded, if there was one way, and one way only, life might be a lot easier?
Paul
euphonium01 is offline   Reply With Quote
Old 11-18-2018, 07:08 AM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,166
Thanks: 10
Thanked 3,887 Times in 3,830 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Code to check a Form for empty/Null Controls

Quote:
Originally Posted by euphonium01 View Post
if there was one way, and one way only, life might be a lot easier?
Where would be the fun in that??
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Uncle Gizmo (11-18-2018)
Old 11-18-2018, 08:14 AM   #13
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,546
Thanks: 368
Thanked 784 Times in 749 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: VBA Code to check a Form for empty/Null Controls

Quote:
Originally Posted by euphonium01 View Post
if there was one way, and one way only, life might be a lot easier?
I always say that there are at least three ways to do the same thing.

It's an interesting point you make, I've never considered it before.

My guess is some methods are for Legacy code, in other words, older methods are still in existence to support old code. A new, better way of doing the same is available.

I tend to use what I know, so I am probably most guilty of using older code when there is newer code around to do the same thing.

However there is an important distinction between the methods which you will come to understand as you progress.

In the beginning, the best way to learn is to see things as individual items, a textbox, a combobox, a list box, a command button and write your code accordingly, write it to access the individual control(s) individually.

However Access (VBA) also handles controls within collections, that is collections of controls, forms, records, just about anything really.

So instead of writing your code to work on an individual object, you write your code to operate on all the controls in a collection.

Operating on a collection sounds more complicated than necessary, however the technique is very powerful, and once understood very easy to implement.

There's a couple of examples here:-

http://www.niftyaccess.com/loop-thro...t-of-controls/

Sent from my SM-G925F using Tapatalk
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 11-18-2018 at 08:35 AM.
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
euphonium01 (11-19-2018)
Old 11-19-2018, 09:16 AM   #14
euphonium01
Newly Registered User
 
euphonium01's Avatar
 
Join Date: Nov 2018
Location: Stockton-on-Tees - UK
Posts: 14
Thanks: 8
Thanked 0 Times in 0 Posts
euphonium01 is on a distinguished road
Re: Helping Others

pbaldy exactly, where would the fun be if it were that easy lol?

Uncle Gizmo Thanks so much for the link, there's some very useful stuff on there.

Reading through various Threads, I've found a few people saying that copying other's work isn't a good thing, but isn't that what this Forum is all about, helping others? I find that when I copy down something that someone else has written or advised, I tend to learn/understand what it is I'm writing much faster, it also helps you remember how you did it? Similarly, I always try to thank someone when they send me advice, if someone takes the time to help by replying to a question, the least anyone can do is say thank you, whether the advice works or not, they took the time to try, and that's worth something!

If there was one way to do things, and I had the knowledge, I would write the definitive guide to doing absolutely everything and publish it... that's my pitch at making millions lol... this is a great Forum
Paul
euphonium01 is offline   Reply With Quote
Old 11-19-2018, 09:37 AM   #15
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,701
Thanks: 317
Thanked 425 Times in 410 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Helping Others

Quote:
Originally Posted by euphonium01 View Post
Reading through various Threads, I've found a few people saying that copying other's work isn't a good thing
Paul
I hope I was not one of them?

I have commented on some members literally copying the code as is and not amending for their situation. Even the code I offer up, and that is purely to show one of the ways to do what they are asking, a way that works/worked for me at the time.

There are some generous people on here who will literally write the code for people, but I feel those people then do not learn anything, in just using the code written for them.?

These people are then generally back almost immediately asking 'How do I change the filename' etc

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Check for empty/null fields in subform datasheet recordset ccondran08 Forms 5 05-30-2018 08:39 PM
[SOLVED] Improving code to check that certain controls on a form have values brharrii Modules & VBA 1 09-15-2014 08:23 AM
check for Null or Empty smig Modules & VBA 33 02-01-2011 09:56 PM
check for empty/NULL value in controls jguscs Modules & VBA 5 08-04-2003 11:10 AM
IF vs. SELECT - Check if Date Textbox is empty / null Cosmos75 Modules & VBA 19 05-19-2003 06:44 AM




All times are GMT -8. The time now is 08:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World