Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-20-2019, 05:55 PM   #1
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 622
Thanks: 465
Thanked 7 Times in 7 Posts
Zydeceltico is on a distinguished road
Make form control value required with VBA

Hi All -

I have a form with some controls. The nature of this form is such that it is always opened twice. Once to record an initial Start time (=Now) and later to record the Stop time.

I have another control called ScrapProduced that takes a number.

I don't want the user to leave that field blank. I made it required at the table level but there is no scrap produced when the user visits the form the first time.
Only on the second visit can the user know how much scrap was produced and be able to enter a number in that control.

Is there a way to enable/disable the Required property of a field contingent on a value being placed in a different control on the form? In this case, it would be when the user enters a Stop time.

Thanks as always,

Tim

Zydeceltico is offline   Reply With Quote
Old 05-20-2019, 06:15 PM   #2
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 856
Thanks: 10
Thanked 177 Times in 168 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Make form control value required with VBA

AfterUpdate of stop time - make scrap field visible. When to trap no scrap entry depends.
Form unload event, check if null or "", cancel form unload if it is, present message?
Button click that saves record?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Zydeceltico (05-20-2019)
Old 05-20-2019, 06:18 PM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Make form control value required with VBA

Hi Tim. If it's possible to enter partial records, then the field is not really required, or at least it shouldn't be. You can always force an entry in said field in the BeforeUpdate of the form that goes something like, if Stop time is not empty, then Scrap Produced must not be empty also.

__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Zydeceltico (05-20-2019)
Old 05-20-2019, 06:31 PM   #4
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 622
Thanks: 465
Thanked 7 Times in 7 Posts
Zydeceltico is on a distinguished road
Re: Make form control value required with VBA

Quote:
Originally Posted by theDBguy View Post
Hi Tim. If it's possible to enter partial records, then the field is not really required, or at least it shouldn't be.
That....and the rest of your response......makes sense.

Thx!
Zydeceltico is offline   Reply With Quote
Old 05-20-2019, 06:43 PM   #5
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 622
Thanks: 465
Thanked 7 Times in 7 Posts
Zydeceltico is on a distinguished road
Re: Make form control value required with VBA

Quote:
Originally Posted by Micron View Post
Form unload event, check if null or "", cancel form unload if it is, present
Button click that saves record?
For my needs I think this is a good way to go.

THX
Zydeceltico is offline   Reply With Quote
Old 05-20-2019, 07:07 PM   #6
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 622
Thanks: 465
Thanked 7 Times in 7 Posts
Zydeceltico is on a distinguished road
Re: Make form control value required with VBA

Quote:
Originally Posted by theDBguy View Post
Hi Tim. If it's possible to enter partial records, then the field is not really required, or at least it shouldn't be. You can always force an entry in said field in the BeforeUpdate of the form that goes something like, if Stop time is not empty, then Scrap Produced must not be empty also.
What is the difference between using the Nz function versus testing for Null?

What is the functional difference between:

If Me.txtBox Is Not Null Then...... (this appears to compile btw)

or

If Me.txtBox Not IsNull Then......

or

If Nz(Me.txtBox,"") &""<>"" Then
Zydeceltico is offline   Reply With Quote
Old 05-20-2019, 07:17 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,219
Thanks: 86
Thanked 1,620 Times in 1,503 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Make form control value required with VBA

The function difference between a test for Null and using NZ is that NZ allows you to "kill two birds with one stone."

Ask yourself this question: If you test for something using If xxx Is Null, what is the next thing you would do?

If you next would do something complex involving message boxes or GoTo statements or some other sequence of logic, then your use of Is Null was probably the right choice.

BUT if your next action would merely be to substitute a zero or a blank (or any other simple value) in place of the null, NZ() does exactly that in one quick call.

Watch out for "IsNull" (one word) because that is a function. Whereas "Is Null" (two words) is a special-syntax test for something being null.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Zydeceltico (05-20-2019)
Old 05-20-2019, 07:19 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Make form control value required with VBA

Hi Tim. What I would say is you'll need to know there are two kinds of "empty" data (for Text data anyway). Null means "unknown." Data is either missing or intentionally left blank. For example, if you have a Middle Name field and a person has a blank MI column, does it mean he/she doesn't have one or the data entry person forgot to put it in? The other type is called a "Zero Length String" or ZLS for short. A ZLS is not null but it doesn't have a length either (or perhaps more accurately, it has a length of zero). So, if you are checking a Text field if it's empty, you'll have to be aware it may be Null or it may be ZLS. The IsNull(), Nz(), and Is Null checks handle Nulls. They don't work with ZLS. ZLS can be checked using an empty string ("") or the Len() function.
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Zydeceltico (05-20-2019)
Old 05-20-2019, 07:23 PM   #9
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 622
Thanks: 465
Thanked 7 Times in 7 Posts
Zydeceltico is on a distinguished road
Re: Make form control value required with VBA

Thank you all. Signing off for the night. I've been trying variations of Is Null on the BeforeUpdate and Unload events - - -with some degree of success and also some unexpected errors. I'll post more tomorrow after sleeping on it. :-)

Thank You - as always!

Tim
Zydeceltico is offline   Reply With Quote
Old 05-20-2019, 07:48 PM   #10
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 856
Thanks: 10
Thanked 177 Times in 168 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Make form control value required with VBA

Quote:
What is the difference between using the Nz function versus testing for Null?
Nz is a conversion function. You could use it to convert Null and do some sort of test on the result (or not) but I wouldn't call it a test for Null in of itself - at least not the way I think you mean. Sure, it's a kind of test in that if Null, a conversion takes place but by itself reports nothing.

The basic difference between Is Null and IsNull is that the former is query syntax, the latter is vba. Both are a test for Null but I'm pretty sure you can't interchange them.
Quote:
If Me.txtBox Is Not Null Then...... (this appears to compile btw)
There should be something wrong with your statement.
If Is Null(Forms!frmAvgData.ID) Then MsgBox "is Null" is red text for me. So is
If Is Null Forms!frmAvgData.ID Then MsgBox "is Null"

If IsNull(Forms!frmAvgData.ID) Then MsgBox "is Null" is not red. Red, of course, meaning it won't compile.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 05-20-2019 at 07:50 PM. Reason: clarification
Micron is offline   Reply With Quote
Old 05-20-2019, 09:37 PM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,055
Thanks: 110
Thanked 2,728 Times in 2,491 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Make form control value required with VBA

Hi Tim
Micron and the DBG have discussed the different methods of handling nulls. If you are interested, I ran some tests comparing the efficiency of three methods: Nz, Len and Trim. See http://www.mendipdatasystems.co.uk/s...sts/4594424200.
For info, Nz was marginally the best approach is my tests.

Back in post #1, you mentioned that the form is opened twice. Once to populate the start time and again for a stop time. Why do you close the form? If not needed in the interim, why not leave it open but hidden? Then you can return to the same record on the form at any time.
Or why not have the timing done on the same form as the other code to simplify matters further?

__________________
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
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
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Make a form command button override required fields focus? JamesWB Forms 20 09-22-2014 02:24 AM
Unbound form - make a required field Matizo Forms 3 12-18-2009 08:54 AM
Yes/No Question on a form and make it required field Sed Forms 7 01-05-2009 01:46 PM
Enable or make control available when required field has filled in Thinh Forms 1 01-30-2007 04:12 PM
make the subform required in the master form CSCS Forms 1 03-07-2006 02:05 PM




All times are GMT -8. The time now is 05:08 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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World