VBA Code to check a Form for empty/Null Controls (1 Viewer)

euphonium01 · Nov 17, 2018

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.

pbaldy · Nov 17, 2018

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

euphonium01 · Nov 17, 2018

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?

isladogs · Nov 17, 2018

Last post was moderated.
Posing this to trigger email notifications

euphonium01 · Nov 17, 2018

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

Gasman · Nov 17, 2018

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

euphonium01 · Nov 17, 2018

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

isladogs · Nov 17, 2018

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

Gasman · Nov 17, 2018

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.

Uncle Gizmo · Nov 17, 2018

euphonium01 said:
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

euphonium01 · Nov 18, 2018

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

pbaldy · Nov 18, 2018

euphonium01 said:
if there was one way, and one way only, life might be a lot easier?

Where would be the fun in that??

Uncle Gizmo · Nov 18, 2018

euphonium01 said:
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-through-a-set-of-controls/

Sent from my SM-G925F using Tapatalk

euphonium01 · Nov 19, 2018

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

Gasman · Nov 19, 2018

Re: Helping Others

euphonium01 said:
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

Uncle Gizmo · Nov 19, 2018

Re: Helping Others

euphonium01 said:
Reading through various Threads, I've found a few people saying that copying other's work isn't a good thing

People do say and think like that. But there is a little problem with it, where does the code come from originally?

You wouldn't be able to write code for MS Access without Microsoft providing you with a manual.

So really, ALL Code originates from Microsoft.

Most code will consist of basic routines ... If Statements, Select Case Statements, various kinds of Loops, to name a few.

If you want to copy those and adapt them to your needs, then there's no real problem, although it's considered good practice to add a comment to your code with a link to where you found the code. Indeed, you will find this handy yourself, especially if you get an error, you might be able to look back to where you got it from.

If you are really worried about falling foul of someone saying you have copied code, then go to the Microsoft website something like this:-

https://docs.microsoft.com/en-us/of...s/getting-started/using-ifthenelse-statements

and copy the code directly from Microsoft, you should be able to find everything you need there. (Still add a link showing where you got it from)

Now where it becomes an issue if you see my code here:-

http://www.niftyaccess.com/nifty-date-picker/

This represents a considerable amount of my time and effort invested in creating this code, and also a degree of personal attachment! The expression "Alograsm" from a recent post springs to mind! https://access-programmers.co.uk/forums/showthread.php?t=302473

But there's nothing in my code itself which is unique, it just consists of If Statements, Case Statements and a Loop, plus a few other things.

The uniqueness comes from the way it is put together to perform the function it was created for.

If it's of interest you can download it here:-

https://gum.co/NiftyDatePicker

For Free Option Visit the "Nifty Access Website" Here:- http://www.niftyaccess.com/nifty-date-picker/

euphonium01 · Nov 20, 2018

Amazing! One has to ask should those people with that level be creating databases? We all start at the beginning (don't I know it.. I'm still at the beginning) but changing filenames etc. is an obvious and basic computer skill, heck, doesn't the guy have a smartphone that he texts from, it's almost the same? Never mind, onward and upwards as they say.

I have a general question: What is the main reason/advantage for splitting a database, if necessary you can still go in to the back-end and change the tables should it be required? I don't see why you would write a letter and store the name & address in a different place? I understand getting the Tables right is a fundamental and shouldn't need to be changed if they're created properly, but am I missing something obvious?
Paul

MajP · Nov 20, 2018

What is the main reason/advantage for splitting a database, if necessary you can still go in to the back-end and change the tables should it be required? I don't see why you would write a letter and store the name & address in a different place? I understand getting the Tables right is a fundamental and shouldn't need to be changed if they're created properly, but am I missing something obvious?

The biggest reason is speed and efficiency in a multi user database. All Data is stored on a network in a single location, and every user has a front end of forms, reports, queries, code on their local machine. Then you are not pulling all that information over the network only the data. You can just store a database on a shared drive and multi users can open it. That is really inefficient and not very stable.

euphonium01 · Nov 20, 2018

Thanks MajP, that makes a lot of sense. Not having done a database that requires multi-user functionality, it never occurred to me that one central point of data is available to all, and therefore everyone is 'singing from the same page'... literally!

Many thanks for your answer, if I ever have to write something where the Tables need to be accessed by more than 1 person, I'll know to split it.
Paul

euphonium01 · Nov 20, 2018

Hi Uncle Gizmo, I had a quick look at the code you did for dates, and my question is - how the h**l did you do that? It's so complicated, I couldn't contemplate how to even start such a project as that, that code is longer and more complicated than ALL my code put together... wow!

I don't believe I will have the time left to get to this level lol. I would like to ask, have you ever got to a point where you gave up with an issue because it simply wouldn't play ball? I have, and tend to kick the computer... spilling my cuppa over the keyboard, wrecking my (usually) well organised little office, and going into a bad mood for the day! When this happens I usually go for a drive, that always makes me feel better (have an old classic Jag that I've almost rebuilt over the last 4 years), if I ever disappear off the Forum for a while, I'm working on the car, or the wife has done me in... lol. BTW, my wife knows me well and usually has a laugh at my expense as I often have these funny tantrums, as you can probably tell by now, I'm always up for a laugh and forever the joker!
Paul.
I will learn vba, I will learn vba.... lol

VBA Code to check a Form for empty/Null Controls (1 Viewer)

Registered User.

Wino Moderator

Registered User.

MVP / VIP

Registered User.

Enthusiastic Amateur

Registered User.

MVP / VIP

Enthusiastic Amateur

Nifty Access Guy

Registered User.

Wino Moderator

Nifty Access Guy

Registered User.

Enthusiastic Amateur

Nifty Access Guy

Registered User.

You've got your good things, and you've got mine.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread