Solved Lost on IsEmpty? (1 Viewer)

[Samantha]

Hello!

I am stumped here. I am trying to incorporate the following code attached to a button merging information into Excel. JobStatus is a field on my form that would have a value between 1-4 if the PK is on the table at all. The form is based on a query tying my main table tblProposals with tblWOH. When a job is approved the JobNumber (PK) is added to tblWOH with the JobStatus field defaulted to 1. I realize thats why I am not getting the proper results returned - the PK is not in the table at this point however idk how to fix it. Maybe my whole approach is wrong - any advice would be greatly appreciated!

If Result = IsEmpty(JobStatus) Then
MsgBox "This job needs to be added to WOH prior to generating a project sheet.", vbOKOnly, "Error"
GoTo Exit_Procedure
Else

 

[theDBguy]

Hi. I think IsEmpty() is an Excel function. In Access, I think it is used for optional function parameters. If you want to check for Null values, you can use the IsNull() function.

 

[Samantha]

Hi. I think IsEmpty() is an Excel function. In Access, I think it is used for optional function parameters. If you want to check for Null values, you can use the IsNull() function.

I did also attempt IsNull and it is giving me the message box regardless if there is a value in JobStatus or not.

 

[theDBguy]

What is in Result?

 

[MajP]

Change from
If Result = IsEmpty(JobStatus)
to
If isnull(jobStatus) then

If result is an undeclared variable then it will be a variant and Null
Null = (true or false)
is always false. Get rid of Result =

 

[Samantha]

What is in Result?

I've looked as so many examples that I have tried to follow.. and have thoroughly confused myself. I declared that as a Boolean.
Deleted that and went back to:

If IsNull(JobStatus) Then
MsgBox "This job needs to be added to WOH prior to generating a project sheet.", vbOKOnly, "Error"
GoTo Exit_Procedure
End If

It partially works. If the value of JobStatus is between 1-4 it carries on as it should. If it has not been added to that table tblWOH.JobStatus it does not show me the message nor does it continue to create my excel file. But it wouldn't register as null if its not there right?

 

[Pat Hartman]

Your code is much clearer if you disambiguate references to form fields/controls. So, if JobStatus is in the recordSource or bound to a control, use Me.JobStatus to reference it so it is clear to everyone that it is not a variable.

Null and ZLS are NOT the same thing as they are treated in Excel. Even if JobStatus is a numeric data type, if you are referencing a control (a control is always a variant), then it might be a ZLS so you need to account for that.

If Me.JobStatus & "" = "" Then ''' takes care of both null and ZLS As does:
If Len(Me.JobStatus) = 0

 

[theDBguy]

It partially works. If the value of JobStatus is between 1-4 it carries on as it should. If it has not been added to that table tblWOH.JobStatus it does not show me the message nor does it continue to create my excel file. But it wouldn't register as null if its not there right?

What is the Data Type of tblWOH.JobStatus?

 

[Samantha]

What is the Data Type of tblWOH.JobStatus?

It's numeric.
I've resolved by sandwiching my if then around my preexisting code. So if value is between 1-4 then code will run and else msgbox. Probably not the most efficient way to go however it works.
Its apparent I need to go back and re-read my Wrox VBA book and search for some learning resources.
Thanks for your time, I truly appreciate it.

 

[theDBguy]

It's numeric.
I've resolved by sandwiching my if then around my preexisting code. So if value is between 1-4 then code will run and else msgbox. Probably not the most efficient way to go however it works.
Its apparent I need to go back and re-read my Wrox VBA book and search for some learning resources.
Thanks for your time, I truly appreciate it.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

you never responded with WHERE the variable is defined so you probably didn't determine if it might be ZLS rather than null.

 

[Samantha]

WHERE the variable is defined

Hi Pat,

Thanks for your response. Not exactly sure what your referencing, nevertheless...

I did try building IfThen using both of your suggestions today and it is still not exiting like I would expect - not that I know much.
If Me.JobStatus & "" = ""
If Len(Me.JobStatus) = 0

I just need to go back to square one I created this DB 10 years ago and do not use VBA consistently enough at this point. Thanks for your input didn't want it to seem like your advice has fallen on deaf ears.

Thanks,
Samantha

 

[Pat Hartman]

The code sample assumed that JobStatus is the name of a bound control. That is why I asked where it is defined. When you reference controls on a form, you reference them as Me.controlname. That makes it absolutely clear that you are referencing a control on the form or a column from the form's RowSource. Professionals usually give controls a different name than the field to which they are bound. So - Me.FieldName would refer to the field in the RecordSource but Me.txtFieldName would refer to the control that holds Me.FieldName. Not using "me", I would assume that the field was a variable defined with a Dim statement in the procedure.

Consistency is your friend.

If you are happy with your solution, we don't need to worry about this.