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]

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?

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]

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

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]

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!

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]

Yes, the table have many rows..

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",[SomeOtherColumn Field]="")

where SomeOtherColumn Field 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]

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",[SomeOtherColumn Field]="")

where SomeOtherColumn Field 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.

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]![frmDateRangeSto re]![BeginDate] & "# And [ReportDate] <= #" & [Forms]![frmDateRangeSto re]![EndDate] & "#")

Thanks for any Help.