Solved Lost on IsEmpty?

[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?

 

[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.

 

[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