Exit Button - check fields have value in record

[whhaatt]

Please can someone help

I am trying to add code where by when i click the exit button, i want the function to check all fields in record to see if there is a value present

if no value then msgbox needs to be displayed to show which field is missing data

eg

If User_ID = "" Then MsgBox "Please enter User ID"
if Date = "" then Msgbox "Please enter date
else
docmd.exit

please help -

 

[whhaatt]

i have tried .Value = False then msgbox "missing data"

no joy

 

[gemma-the-husky]

do you mean the form close button.

--------

what happens is that if you have a partially edited record, the form will attempt to save the record before closing the form. now to save the record it will run the beforeupdate event - you can put tests in this event, and stop the form closing


so you get

sub form_beforeupdate(cancel as integer)
end sub

and if in the code you set
cancel = vbcancel, this will stop the form closing

hence

[note also use of nz - if you dont know it, it converts a null value to something else - this is particularly useful as a null is not the same as a string with nothing in it (a zero-length-string - zls). using if nz(field1,"")="" then is equivalent to saying if isnull(field1) or field1="" then

sub form_beforeupdate(cancel as integer)
if nz(field1,"")="" then goto missing 'test a text value
if nz(field2,0)=0 then goto missing 'test a number
if nz(field3,"")="" then goto missing
if nz(field4,"")="" then goto missing
exit sub 'everything ok, so accept it

missing:
call msgbox("sorry: Not all fields completed.")
cancel = vbcancel 'prevents the form closing, because it aborts the record save
end sub

 

[whhaatt]

sorry i dont understand (nz?)

i have tried the below and it get error
ELSE WITHOUT IF

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click
If Cust_Name.Value = False Or User_ID.Value = False Or User_ID.Value = False Then MsgBox "Missing information Please fill in ALL FIELDS"

ElseIf Cust_Name.Value = True And User_ID.Value = True And User_ID.Value = True  Then
DoCmd.Close

End If
    Exit Sub

What is wrong?

 

[whhaatt]

now using below

when button is click get msg box saying missing object

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

If Cust_Name.Value = False Or User_ID.Value = False Or Account_Number.Value = False Then MsgBox "Missing information Please fill in ALL FIELDS"

If Cust_Name.Value = True And User_ID.Value = True And Account_Number.Value = True Then
DoCmd.Close
End If

Exit_Command23_Click
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

 

[whhaatt]

Sorry Msg Box States

object Required

 

[gemma-the-husky]

is it this line causing the problem

If Cust_Name.Value = False Or User_ID.Value = False Or Account_Number.Value = False Then MsgBox "Missing information Please fill in ALL FIELDS"

there doesnt look anything wrong with it but try

If Cust_Name.Value = False Or User_ID.Value = False Or Account_Number.Value = False Then 
    call MsgBox ("Missing information Please fill in ALL FIELDS")
end if

out of interest what does the test if ... = false do, or supposed to do

 

[whhaatt]

Thankyou gemma

your code works

however on of the fields is a list box and contains numbers and letters (text)
the list records are pulled from a table that contains the values

how can i get the code to work for this

as even if there is a entry or of field is blank i get the sorry: Not all fields completed msg

Thankyou

 

[whhaatt]

.value = false is supposed to read if field is blank or contains no value

 

[whhaatt]

srry gemma just to say your nz code works

 

[gemma-the-husky]

out of interest, heres an expanded expalnation

a yes/no field (technically a boolean) can only store values yes/no or true/false

so if myfield = false will test a boolean value

--------
now in vba, boolean values are actually stored as
false = 0
true = -1

(although i think anything non zero is treated as true)

so if you have a number, say 425
and you test (if mynumber = false), you are really testing whether mynumber is zero

and if you have a text, say "smith"
and you test (if mytext = false), you are really testing whether mytext is zero which is almost bound to return false, ie mytext is not equal to the value 0

as you can say, this is most certainly not what you are trynig to do

-------------
what you are trying to do is to test whether the field has something in it

so you can say

if mytext<>"" then

or you can test the length of the field with

if len(mytext)>0 then

-----------
BUT these tests wont work properly if the field is blank (null) - since mytext is not equal to "", but is in fact (technically) undefined. I am not sure but it may produce a run time error

now you can say as an extra test

if isnull(myfield)

but you may NOT be able to get away with

if isnull(myfield) or len(myfield)=0 (as i say iam not sure, but the second bit may generate a runtime error if the field IS null)

---------
what the nz function does is assigns avalue if andf only if the field IS null

so nz(mytext,"") sets mytext to be "" (ie a zero length string), if it is null, but leaves it alone otherwise

so you can say

either

if nz(mytext,"")=""

or

if len(nz(mytext,""))=0 then


hope this helps

 

[gemma-the-husky]

further to this, given this, you could refine it further, and force the cursor BACK to the first faulty field, using the setfocus method - it all gets as complex as you want it to be

sub form_beforeupdate(cancel as integer)
dim check as long

check=1
if nz(field1,"")="" then goto missing 'test a text value

check=2
if nz(field2,0)=0 then goto missing 'test a number

check=3
if nz(field3,"")="" then goto missing

check=4
if nz(field4,"")="" then goto missing
exit sub 'everything ok, so accept it

missing:
call msgbox("sorry: Not all fields completed.")
cancel = vbcancel 'prevents the form closing, because it aborts the record save
[COLOR="Red"]if check=1 then field1.setfocus
if check=2 then field2.setfocus
if check=3 then field3.setfocus
if check=4 then field4.setfocus[/COLOR]
end sub