Data validation

[namliam]

have you tried doing a dcount ???

What is your current code??

fyi, "AND IS NOT NULL" is not really needed in the where statement

 

[brucey54]

How do I amend this code to do the following;

If date entered AND hospital name selected from the dropdown list is not within the query Then

Do this

Else

Do this

Code

If (DCount("*", "QryPrintDataVBA", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & _
" AND [Hospital] = '" & Me.ComboSelectHospital & "'") <> 0) Then

 

[namliam]

That IS what the dcount does, in the test database's case
?DCount("*", "QryPrintDataVBA", "[MealDate] = #11/19/2014# AND [Hospital] = 'Hospital2'")
Should return 0

 

[namliam]

Your problem isnt in your dcount, it is in the logic of your if.... What is your current code?

 

[brucey54]

Do you think I have if else statement mixed up?



If (DCount("*", "QryPrintDataVBA", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & _
" AND [Hospital] = '" & Me.ComboSelectHospital & "'") <> 0) Then


'A date provided exists in the table
DoCmd.SetWarnings False
stDocName = "McrPrint_Customise_Labels"
DoCmd.RunMacro stDocName

Else

DoCmd.SetWarnings False
stDocName1 = "McrReOrderDiets_PrintLabels"
DoCmd.RunMacro stDocName1


End If


End Sub

 

[gemma-the-husky]

Brucey.

As I said before, I would add a nz, in case you are getting null, as below

I would test your query "QryPrintDataVBA" directly, testing different values directly, so you are 100% sure what should happen
I would add a breakpoint, or a few msgbox's to show what was happening.
I would double check what the macro is doing.
I would actually replace the macro with code
I would temporarily remove the setwarnings false, so you can see exactly what the query is trying to do

You are stuck on thinking that the dcount is not working correctly. It is working correctly. dcounts don't stop working correctly. You may have the syntax/detail wrong for what you are trying to do, though.


If nz((DCount("*", "QryPrintDataVBA", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & _
" AND [Hospital] = '" & Me.ComboSelectHospital & "'"),0) <> 0) Then


'A date provided exists in the table
DoCmd.SetWarnings False
stDocName = "McrPrint_Customise_Labels"
DoCmd.RunMacro stDocName

Else

DoCmd.SetWarnings False
stDocName1 = "McrReOrderDiets_PrintLabels"
DoCmd.RunMacro stDocName1

End If


End Sub

 

[brucey54]

If I swap the if else statement round this would work, but if someone from one of the kitchens hits the print button twice, the code will re-order the diets twice.

Producing double the labels for that kitchen, this would also affect the stocktake, is there a way to stop this from happen?

 

[brucey54]

Hi gemma-the-husky, I'm receiving error next to the "),0) of your code,

If I swap the if else statement round it works ok, need to figure a way round to stop the re-order of the diets if someone hits the print button twice...

 

[namliam]

well it seems to me like you would if the DCount <> 0 you would want a REorder
where currently you do a reorder if it is 0

 

[brucey54]

Hi Namliam, my understanding is;

if DCount not = 0, this would mean that a date and a hospital name exists already within the query! So no need to Reorder diets for that kitchen, just print the labels!

Is my understanding wrong?

 

[gemma-the-husky]

Hi gemma-the-husky, I'm receiving error next to the "),0) of your code,

If I swap the if else statement round it works ok, need to figure a way round to stop the re-order of the diets if someone hits the print button twice...

I think there is an extra bracket at the front. I just tried to wrap the dcount with nz

nz(your dcount statement,0)

 

[brucey54]

Hi gemma-the-husky, your code is working ok now, but still receiving the same issue....

 

[namliam]

re-order sounds to me like re-do, i.e. it has been done before....

From your desciption it sounds like your logic is different