Empty Field Check

[navi95]

Hi guys,

I need a VBA which would check if one of the fields is empty before proceeding with the rest of the code.
Ive tried using something like:
if [telefonnummer] = "" then etc etc but no luck.

Ive tried setting validation rules on the form itself too, no luck. They dont seem to actually kick in until you write something in the field, so if you tab through without inputting anything, the validation rule seems to get ignored.
Thats why I want to try with VBA code.

Thanks

 

[bob fitz]

Try the forms Before Update event for validation.
Maybe: if [telefonnummer] = Null then

 

[Galaxiom]

Try the forms Before Update event for validation.
Maybe: if [telefonnummer] = Null then

I sure Bob knows that expression will always return False since the Equals operator cannot compare with a Null.

This one is pretty common.

 If Len([telefonnummer] & vbNullString) = 0 Then

 

[bob fitz]

I sure Bob knows that expression will always return False since the Equals operator cannot compare with a Null.

This one is pretty common.

 If Len([telefonnummer] & vbNullString) = 0 Then

Sorry folks. That's what becomes of trying to do this and hold a conversation with my wife at the same time. Thanks to Galaxiom for the correction.

 

[missinglinq]

FYI, the reasoning behind Galaxiom suggestion is that an 'empty' Control/Field, in Access, is usually Null, not a Zero-Length String ("") which is what your code is checking for.

A Zero-Length String (ZLS) can, occasionally, sneak in, usually when data is imported into Access from another program, so to cover both possibilities it is wise to use code like Galaxiom posted:

If Len([telefonnummer] & vbNullString) = 0 Then

Another popular hack to check for both Nulls and ZLSs is

If Nz(telefonnummer, "") = "" Then

Linq ;0)>

 

[navi95]

Thanks for all of the replies!

I ended up using:
If Len([telefonnummer] & vbNullString) = 0 Then I used this on my button whichs takes you to then next step of the form, works like a charm. Thanks!

 

[navi95]

How would I apply this same validation check to a field in the subform?
Ive tried using code like
if len([Forms!bestellung!Kunden.form!Name.value] & vbnullstring = 0

No joy though. So the form itself is called "bestellung" and the subform is called "kunden"

Help?

 

[bob fitz]

Thanks for all of the replies!

I ended up using:
If Len([telefonnummer] & vbNullString) = 0 Then I used this on my button whichs takes you to then next step of the form, works like a charm. Thanks!

What happens if the form is closed by some other means

 

[navi95]

What happens if the form is closed by some other means

There is no other way to close the form, this database will be used on a PC with just access runtime installed.

 

[bob fitz]

No white cross in a red square in the top right corner to close the app?

 

[navi95]

Nope not even that, I have the database exporting data when the app is closed so the user needs to the exit button ive coded with VBA to close it.

any idea regarding on how I referring to the control on the subform? Im googling around, trying stuff out but nada. Access cant seem to find it, so i must be referencing it wrong. I did read someone mention I have to refer to the control itself?

 

[bob fitz]

Nope not even that, I have the database exporting data when the app is closed so the user needs to the exit button ive coded with VBA to close it.

any idea regarding on how I referring to the control on the subform? Im googling around, trying stuff out but nada. Access cant seem to find it, so i must be referencing it wrong. I did read someone mention I have to refer to the control itself?

I think you'll find that the red button will close the form and save whatever data is entered. That's why I always use the forms Before Update event for validation

Perhaps this will help with your reference to a control on subform: http://allenbrowne.com/casu-04.html

 

[navi95]

Thats pretty much the same as the sites ive read up so far, I end up with the following code:

If Len([Forms.Bestellung.kunden.form.Name.value] & vbNullString) = 0 Then

I still think Ive named something wrong in the code as the error message states it find find the field.

What am I doing wrong?:banghead:

 

[bob fitz]

Perhaps try without the "value".
Also I would try changing the name of the control to something other than "Name" which is a reserved word in Access

 

[navi95]

hmm Ok Ive tried without "value" and Ive also changed it to Name2. No Luck

 

[bob fitz]

Check that the actual name of the subform control on the main form is correct

 

[navi95]

I finally figured it out, I didnt need the [] when referencing the subform. So my working code is as follows:

If Len(Forms!bestellung!Kunden.Form.Name2 & vbNullString) = 0 Then

Thanks for all your input guys

 

[bob fitz]

Thanks for letting us know what worked for you.

Good luck with your project

 

[Minty]

This page is very useful when dealing with the form / sub form items, http://access.mvps.org/access/forms/frm0031.htm - Bookmark it for future use. I still refer to it on a weekly basis, as my head just won't remember the correct syntax.