Checking empty fields based on another textbox value (1 Viewer)

Mackbear · Jul 22, 2019

Hello, starting another thread again, thank you everyone for your patience on a beginner like me I really appreciate it. so I need to be able to make sure that there will be no required fields that will be left blank. I attached an image of how it looks like.
* Ext date, InVoice count, LF Assessed, waiver approved, request method should not be blank.
* If the waiver is approved "Yes", the credit amount, credit method and exp date to credit should not be blank.
* If the waiver is "No" the declined reason should not be blank.

Here's my code:

If IsNull(Me.extdatevreachout) Or IsNull(Me.lfassessedvreachout) Or IsNull(waiverapprv) Or IsNull(reqmethod) Then

MsgBox "Please check empty fields", _
vbCritical, _
"Canceling Update"
Me.extdatevreachout.SetFocus
Cancel = True

If Me.waiverapprv = "Yes" And IsNull(Me.creditamnt) And IsNull(Me.creditmethod) And IsNull(expinvdatecredit) Then

MsgBox "Please check empty fields", _
vbCritical, _
"Canceling Update"
Me.extdatevreachout.SetFocus
Cancel = True

ElseIf Me.waiverapprv = "No" And IsNull(Me.declinedreason) Then

MsgBox "Please check empty fields", _
vbCritical, _
"Canceling Update"
Me.extdatevreachout.SetFocus
Cancel = True

Else

"Some action"

End if

Thank you!

theDBguy · Jul 22, 2019

Hi. So, what's happening with your code? Is it not working? If not, are you getting an error message?

Micron · Jul 22, 2019

you didn't say if there is an issue, much less what it is.
I'm guessing that if the first IF block presents a problem message, code is just marching on because you don't exit? Just guessing...

Code:

MsgBox "Please check empty fields", _
vbCritical, _
"Canceling Update"
Me.extdatevreachout.SetFocus
Cancel = True
Exit Sub

I haven't studied the rest because the issue is unknown.

Mackbear · Jul 22, 2019

Oh I'm sorry yes I did not say the problem, it just does not work, and another weird thing happening is that VBA does not tell me anymore where the error is unlike before, not sure if I turned off something, I just know that it does not work because when I click on the button that is supposed to be a trigger. nothing happens....

The_Doc_Man · Jul 22, 2019

First and foremost, you might wish to consider that IsNull isn't the best function for you. It is possible for a field to be empty but not null, and it can even have a zero-length string which is neither case. (Empty is a possible value of a variant.)

Instead of IsNull(Me.extdatevreachout), try NZ(Me.extdatevreachout, "")="" which will give you something if the given field is null or if it has a zero-length string. Of course, do that for all of the fields you are testing with IsNull.

Another possibility is LEN(Me.extdatevreachout)=0 which works for nulls and ZLS cases and I believe also works for Empty cases.

As to "nothing happens" you need to learn how to set a breakpoint and single-step through your code so that you can see exactly what it does one step at a time. A pain in the toches because it can be tedious, but indispensable for debugging and seeing what is wrong.

Further hint: If you correctly set the breakpoint in the code, you can hover the mouse over a variable or object name and find out things about it. If it is a textbox, you will see the contents. If it is a variable, you will see its value.

Micron · Jul 23, 2019

If it is a variable, you will see its value.

Only if the value has been assigned, yes? When you pause on

strText = DLookup("field", "table","criteria"), strText will be "" if not assigned previously. You have to execute (move off of) such lines/assingments.

Mackbear · Jul 23, 2019

The_Doc_Man said:
First and foremost, you might wish to consider that IsNull isn't the best function for you. It is possible for a field to be empty but not null, and it can even have a zero-length string which is neither case. (Empty is a possible value of a variant.)

Instead of IsNull(Me.extdatevreachout), try NZ(Me.extdatevreachout, "")="" which will give you something if the given field is null or if it has a zero-length string. Of course, do that for all of the fields you are testing with IsNull.

Another possibility is LEN(Me.extdatevreachout)=0 which works for nulls and ZLS cases and I believe also works for Empty cases.

As to "nothing happens" you need to learn how to set a breakpoint and single-step through your code so that you can see exactly what it does one step at a time. A pain in the toches because it can be tedious, but indispensable for debugging and seeing what is wrong.

Further hint: If you correctly set the breakpoint in the code, you can hover the mouse over a variable or object name and find out things about it. If it is a textbox, you will see the contents. If it is a variable, you will see its value.

F8 does not work, tried to singlestep....

theDBguy · Jul 23, 2019

Mackbear said:
F8 does not work, tried to singlestep....

Was the code stopped?

Mackbear · Jul 23, 2019

theDBguy said:
Was the code stopped?

I just presses f8 and nothing happens... i have to be at the start of the code right? The private sub that is where the cursor is... then i pressef f8 and nothing happens

theDBguy · Jul 23, 2019

Mackbear said:
I just presses f8 and nothing happens... i have to be at the start of the code right? The private sub that is where the cursor is... then i pressef f8 and nothing happens

Sorry, no. You might be thinking of F5. F8 is only used to step through a running but suspended code. F5 is used to start it. However, I am not sure the exact times when F5 works and when it doesn't. Either way, I know F8 only works when the code is already running and stopped, usually while in Debug mode.

Mackbear · Jul 23, 2019

theDBguy said:
Sorry, no. You might be thinking of F5. F8 is only used to step through a running but suspended code. F5 is used to start it. However, I am not sure the exact times when F5 works and when it doesn't. Either way, I know F8 only works when the code is already running and stopped, usually while in Debug mode.

I cannot get in the debug mode.... it seems like it is not running at all... when i debug it does not show any error...

theDBguy · Jul 23, 2019

Mackbear said:
I cannot get in the debug mode.... it seems like it is not running at all... when i debug it does not show any error...

Hi. You would get to debug mode by putting a break point in the code and then executing it.

Mackbear · Jul 24, 2019

Not sure if I'm doing it right, I set a breakpoint which was marked in red, when I try to run the code still nothing happens.

I just want it to check and not allow saving if the fields are blank based on the selection,... on the waiver approved field.

If the value is Yes, 3 of the fields should have value. If No, one of the fields should have value and the other 3 is not necessary. there are default fields that should have a value regardless if it is a yes or no....

theDBguy · Jul 24, 2019

Mackbear said:
Not sure if I'm doing it right, I set a breakpoint which was marked in red, when I try to run the code still nothing happens.

How exactly did you try to run the code? Can you please post the complete code? Which event is it tied to?

Mackbear · Jul 24, 2019

theDBguy said:
How exactly did you try to run the code? Can you please post the complete code? Which event is it tied to?

It is triggered by a save button. Here is the code:

Private Sub save_Click()

'check the always required fields
If IsNull(Me.extdatevreachout) Or IsNull(Me.lfassessedvreachout) Or IsNull(waiverapprv) Or IsNull(reqmethod) Then

MsgBox "Please check empty fields", _
vbCritical, _
"Canceling Update"
Me.extdatevreachout.SetFocus
Cancel = True
Exit Sub

End If

'Check the waiverapproved field to identify which fields are required or not
If Me.waiverapprv = "Yes" And IsNull(Me.creditamnt) And IsNull(Me.creditmethod) And IsNull(expinvdatecredit) Then

MsgBox "Please check empty fields", _
vbCritical, _
"Canceling Update"
Me.extdatevreachout.SetFocus
Cancel = True
Exit Sub

ElseIf Me.waiverapprv = "No" And IsNull(Me.declinedreason) Then

MsgBox "Please check empty fields", _
vbCritical, _
"Canceling Update"
Me.extdatevreachout.SetFocus
Cancel = True
Exit Sub

Else

'Save the records to a table
Dim LF As DAO.Recordset
Set LF = CurrentDb.OpenRecordset("tbl_LFrequests", dbOpenDynaset)
LF.AddNew
LF("entryid_vendreachout") = Me![enrtyid_vendreach].Value
LF("InvoiceCount") = Me![invcnt].Value
LF("WaiverApproved") = Me![waiverapprv].Value
LF("RequestMethod") = Me![reqmethod].Value
LF("DeclinedReason") = Me![declinedreason].Value
LF("CreditedAmount") = Me![creditamnt].Value
LF("CreditMethod") = Me![creditmethod].Value
LF("ExpDatetoCredit") = Me![expinvdatecredit].Value
LF("ReviewStatus") = "Open"
LF("ReqbyAP") = Me![apname].Value
LF("ReqDate") = Me![ddate].Value
LF("ReqDateTime") = Me![ddatetime].Value
LF("ReqWeek") = Me![wweek].Value
LF("ReqMonth") = Me![mmonth].Value

LF.Update
LF.close
Set LF = Nothing
DoCmd.close acTable, "tbl_LFrequests", acSaveYes

MsgBox "Saved"

DoCmd.close
End If

Thank you so much for taking the time to look.

theDBguy · Jul 24, 2019

Mackbear said:
It is triggered by a save button. Here is the code:

Okay, let's try this. At the top of your code, modify it like so:

Code:

Private Sub save_Click()
MsgBox "Hello"

Now, click on your Save button. Do you get a message box? If so, put a break point on it and then step through the code again.

Mackbear · Jul 24, 2019

theDBguy said:
Okay, let's try this. At the top of your code, modify it like so:

Code:

Private Sub save_Click() MsgBox "Hello"

Now, click on your Save button. Do you get a message box? If so, put a break point on it and then step through the code again.

There is a message. I put a breakpoint now it is marked red. Should I run it again by clicking save?

Micron · Jul 24, 2019

If you put a break at the first line, clicked and code didn't start then you must have lost the connection to the control. In design view select the event for the control (... on property sheet)
and see if that fixes it.

theDBguy · Jul 24, 2019

Mackbear said:
There is a message. I put a breakpoint now it is marked red. Should I run it again by clicking save?

Yes, click on the Save button again and when you see the yellow highlight on the code line where you placed the break point (red dot), you should be able to use the F8 key.

Mackbear · Jul 24, 2019

theDBguy said:
Yes, click on the Save button again and when you see the yellow highlight on the code line where you placed the break point (red dot), you should be able to use the F8 key.

It is not bringing me back to the code line when I click save...

Checking empty fields based on another textbox value (1 Viewer)

Registered User.

Attachments

I’m here to help

AWF VIP

Registered User.

Immoderate Moderator

AWF VIP

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

AWF VIP

I’m here to help

Registered User.

Similar threads

Users who are viewing this thread