Cancel Send Objects Command while Null Values exists in Table

[ElTipo]

Ok,

I have 3 stores to send a one report on click command botton. I need to write in the VBA if the store 1 is Null in the table then proceed with other.

Dim look As String
 
look = DLookup("[StoreNum]","tblStore")
 
IF look = "1" then
DoCmd.SendObject acReport, "1", acFormatSNP, "[EmployeeName]", "[SupervisorName]", "[Subject]","[email]",
Else
IF look = "2" then
DoCmd.SendObject acReport, "2", acFormatSNP, "[EmployeeName]", "[SupervisorName]", "[Subject]","[email]",
Else
IF look = "3" then
DoCmd.SendObject acReport, "3", acFormatSNP, "[EmployeeName]", "[SupervisorName]", "[Subject]","[email]",
End If

How I write a correct sintax for Null value store number if any store is Null then pass to the other line instruction.

Any Help?

[Jim Doherty]

@ElTipo
You have a choice from to types see below

Dim look As String
'convert any null to zero and test for 0 or zero length string
look = Nz(DLookup("[StoreNum]", "tblStore"), 0)
If look = 0 Or look = "" Then
    MsgBox "The store num field is null or blank", vbExclamation, "Store Num Required"
Exit Sub
End If
 
Select Case look
Case Is = "1"
    'Your DoCmd.SendObject acReport, "1" command goes here...
Case Is = "2"
    'Your DoCmd.SendObject acReport, "2" command goes here...
Case Is = "3"
    'Your DoCmd.SendObject acReport, "3" command goes here...
Case Else
    MsgBox "Nothing fits the either i,2 or 3", vbInformation, "System Message"
End Select
 
'Choose either the above code block or the one below
'whichever is your flavour
 
If look = "1" Then
    'Your DoCmd.SendObject acReport, "1" command goes here...
ElseIf look = "2" Then
    'Your DoCmd.SendObject acReport, "2" command goes here...
ElseIf look = "3" Then
    'Your DoCmd.SendObject acReport, "3" command goes here...
Else
    MsgBox "Nothing fits the either i,2 or 3", vbInformation, "System Message"
End If

[ElTipo]

@Jim Doherty
Sorry for my late to response & my English Jim,

I try the two steps with case an if and not work really. The function goes to the else and show me only the message box and skip the do command send object. I proceed to check the data in the table and the stores numbers exists in the table.

Thanks for your time Jim,

If any idea for this please write thansk again!

[Jim Doherty]

@ElTipo
if you are looking up the value of StoreNum from a single row table called tblStore then it should work.

Does tblStore have many rows? if so you need some criteria to identify and return return the actual row you are looking up

[ElTipo]

Yes, the table have many rows..

[Jim Doherty]

@ElTipo
Well if tblStore has many rows and you are wanting to lookup and return a value from a specific row and column then the current code is very likely to fail because it is only looking for the first occurrence of any value stored in the storenum column and obviously if that value is anything other than 1 or 2 or 3 then it will fail the test .

There is nothing presented in the current code to control the WHERE aspect the CRITERIA argument of the Dlookup functionality. ie:

DLookup("[StoreNum]", "tblStore",[SomeOtherColumnField]="")

where SomeOtherColumnField is another column in your table that you pass as criteria to focus the lookup routine to targetting the row you need to examine and hence return the storenum value.

I hope I am making some sense here.

[ElTipo]

@Jim Doherty
Hi Jim,

I Made this sintax for DLookup to include the criteria. If I select range in the form then provide the Stores Numbers in that range but this no working.

This is the sintax I made for DLookup:

Dim look As String

look = DLookup("[StoreNum]", "tblStore", "[ReportDate]>=#" & [Forms]![frmDateRangeStore]![BeginDate] & "# And [ReportDate] <= #" & [Forms]![frmDateRangeStore]![EndDate] & "#")

Thanks for any Help.