Data validation (1 Viewer)

[brucey54]

Hi folks,

I need some help here; I have 4 kitchens that use the same database to print their labels.
Each label contains customer name, location and date of meal.

The kitchen prints their labels at different times of the day; sometimes they will accidental hit the print button more than once!

To print their labels they select kitchen name from a combo box and enter a date.

I need the code to do the following;

Data Entered

• Kitchen name entered = A
• Date entered = 17/11/2014

Code

Check date field for kitchen A, if date already exists in table for kitchen A Then

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

Else

DoCmd.SetWarnings False
stDocName1 = "McrReOrderDietCusDate"
stDocName = "McrPrint_Customise_Labels"
DoCmd.RunMacro stDocName1

End If

End Sub

This is the code I have so far;

Private Sub Command38_Click()


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

 

[CJ_London]

So what is the problem?

You aren't resetting warnings to true which may be hiding something and you shouldn't set it to false anyway until you have the routine working properly so you can expose and fix any errors

 

[brucey54]

The problem;

If kitchen A prints the labels first and then kitchen B tyres to print, no data will come up for kitchen B as the code will look for the most recent date.

This is 17/11/2014 as kitchen A has already printed their labels, labels for kitchen B will be blank hope this makes sense....

I think I need something like

If Kitchen A selected do this
else
If Kitchen B selected do this
esle
If kitchen C select do

 

[CJ_London]

as the code will look for the most recent date

Since your code is looking to match on kitchen and date your comment does not make sense.

Sounds like the issue is with either your QryPrintDataVBA query or the recordsource to your label report

 

[brucey54]

Hi CJ_London, this is my sql for QryPrintDataVBA

SELECT TblDietPlan.MealDate, TblKitchen.Name

FROM TblKitchen INNER JOIN TblDietPlan ON Tblkitchen.KitchenID = TblDietPlan.KitchenID;

The query displays all the dates and kitchen names...

 

[brucey54]

How do I modify the code to do the following?
It Kitchen name and date already exist within the table do this

Else

If Kitchen name and date not in table do this

What’s happen at the moment is;

If date already in table regardless of kitchen name, the code will not re-order the meals for the other kitchens as it has the most current date within the table already

i.e. the first kitchen that prints gets the labels the other kitchens labels are display blank because the recordsource for the reports are looking for the current date and the kitchen name

• Kitchen A print labels ok
• Kitchen B labels are blank
• Kitchen C labels are blank
• Kitchen D labels are blank

 

[CJ_London]

so what is in the TblDietPlan and TblKitchen tables and when do they get updated

And please answer my second question - what is the recordsource to your report?

 

[brucey54]

The Dietplans table holds the meal details and the kitchen table holds the clients details,

They are updated when the kitchen staff clicks on the print button;
i.e. a query copies the diets data that has the most recent date then appends the data with the current date to the Dietplans table.

This process takes place every day and each kitchen prints their labels are different times.

The recordsource is a query that displays diet details/Client details, depending on the kitchen name is selected from the combo box and the current date.

 

[CJ_London]

Sorry, I'm not being clear. I'm asking you for all the code you are using which updates the table and the code that prints the labels

 

[brucey54]

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


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

Else

DoCmd.SetWarnings False
stDocName1 = "McrReOrderDietCusDate"
stDocName = "McrPrint_Customise_Labels"
DoCmd.RunMacro stDocName1


End If

 

[CJ_London]

repeating your first post does not help

How do I modify the code to do the following?
It Kitchen name and date already exist within the table do this

Your dcount function does this

If you cannot provide me with the code I have asked for, I cannot help you any further

 

[brucey54]

I appreciate your help on this,

I know the code is searching for date entered and the kitchen name, however if it finds the date already exists within the table regardless of the kitchen name the code is not running the second part of the else statement because that date already exists in the table.

I hope this makes sense

 

[CJ_London]

if this is your query

SELECT TblDietPlan.MealDate, TblKitchen.Name

FROM TblKitchen INNER JOIN TblDietPlan ON Tblkitchen.KitchenID = TblDietPlan.KitchenID;

and this is your criteria

"[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & _
" AND [Kitchen] = '" & Me.ComboKitchenName & "'"

then the criteria is looking for a field called Kitchen, which is not in your query.

Also you seem to have a field called Name in your TblKitchen table. Name is a reserved word and causes unexpected errors -recommend you change it to something like KitchenName

 

[brucey54]

sorry the field name is called kitchen i.e.

SELECT TblDietPlan.MealDate, TblKitchen.Kitchen

FROM TblKitchen INNER JOIN TblDietPlan ON Tblkitchen.KitchenID = TblDietPlan.KitchenID;

 

[CJ_London]

I'm sorry, I can't help anymore. Despite repeated requests you are unable to provide the information required to resolve your problem and the information you have provided proved to be inaccurate. I will see if I can flag someone else to see if they can help.

 

[brucey54]

ok no worries, thanks for your help

 

[namliam]

My gutt feeling on this is that either the cbo box has a different (bound) value or the kitchen column is a lookup field and has a different bound value which will fail when lookup it up in the DCOunt

 

[brucey54]

Hi Namliam, yes you are spot on the cobo box for the kitchen is a lookup, any ideas how to resolve this as I have been pulling my hair out all day :/

 

[jdraw]

Brucey,
Namliam has made a guess, correctly, based on symptoms he has gleaned from your discussion with CJ. I'm still not to the guessing stage since I'm not certain of:
-What exactly is the purpose of the database in simple English?
- I see the need for labels, but what labels exactly?
-What are the table structures involved?
-What relationships exist?
-Can you post a jpg of your Relationships window with tables expanded?
-Can you post all code that is involved in the "problem area"? Or a copy of the database?(remove anything confidential first)

I'm not trying to be picky, but you didn't really answer CJ and Namliam is guessing. Until we understand the business need and the issue you are having with your database/application, it's hard to offer more than guesses.

 

[brucey54]

Hi Jdraw,

The database just print labels, each labels tells the kitchen staff what food to put into each tray.

I have included relationship picture and the code that's causing the problem.

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


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

Else

DoCmd.SetWarnings False
stDocName1 = "McrReOrderDietCusDate"
stDocName = "McrPrint_Customise_Labels"
DoCmd.RunMacro stDocName1

End If