Data validation

[namliam]

Atleast provide us with a sample content of Kitchen and of the lookups it does...

 

[brucey54]

Hi a folk, the database is split, and my boss won’t allow me to upload a copy of the database argggggggggggg

is there no way I can just modify the code so that if the name of the kitchen
and date are in the table Then

Print labels

Else

Re-order_diets
Print labels

At the current moment the code is not functioning the way it should and I’m still learning VBA any help much appreciated ...

 

[jdraw]

Brucey,

Your responses are not very helpful. Forget the Access and queries etc.
What is the database about?
What exactly would be put on label? Give us a sample of what you want the label to look like.
Is this a hospital or cafeteria setup or similar?
Is there only 1 menu per meal?
You've got to have more than 2 tables (in my view).

Here's a sample of a description of a business.

From RogersAccessLibrary
Narrative
ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

Can you work from it and tell us about your database/business in plain English?

 

[brucey54]

Hi jdraw,

I think we have our wires crossed, the labels are not the problem and the database design/relationship is not the problem.

Regardless if the database is for a hospital, cafeteria setup or 1 menu per meal or 2 menu per meals or 1 table, 2 tables 1000 tables...

This information is total irrelevant and does not focus on the main problem.

The problem is the code is not working correctly, if a date already exists within the table regardless of the kitchen name, the code will not run the second part of the else statement

i.e. to re-order the meals again.

When the other kitchens print their labels, the labels are blank this is because the query that provides this information to the reports have the following criteria;

· Current date
· Name of the kitchen

As the code has not run the second part of the else statement there is no current dates within the table, this is why the labels are blank.

I hope this make senses.

 

[jdraw]

Sorry Brucey, but to me I'd like to know WHAT the purpose of the database is and to see a poster developed data model to see the tables and relationships. It helps clarify the situation, entities involved and gives some idea of the poster's Access/database skills.

This information is total irrelevant and does not focus on the main problem.

Perhaps not, but it helps readers put the problem/opportunity into context.

The problem is the code is not working correctly, if a date already exists within the table regardless of the kitchen name, the code will not run the second part of the else statement

I'm sure that's what you are seeing and feeling, but for readers - we're asking "What code?"

Show us - code; the desired label with sample data; and a jpg of your tables and relationships; and/or a copy of your database with no private info and just enough data to highlight the problem.

You're going to have to give readers something to work with if we are to help you.

We shouldn't be at this level after 20+ posts.

 

[gemma-the-husky]

brucey

put a breakpoint in your code
step through it

I would surround the dcount with a nz(,0) just in case it is returning a null. (which is <>0 by definition)

I expect you are missing something obvious, but you need to methodical about it. I think you are fixated on the date thing, but it isn't that.

 

[jdraw]

I read through the thread again did more guessing and this is what I came up with as possible tables.

I agree with Dave that stepping through your code is an option, if you aren't going to provide more info.

Anyway, good luck with your project.

 

[gemma-the-husky]

if it is the null, then you can simply turn it the other way round

if dcount()>0 then
.....
else
.....
end if

 

[brucey54]

Hi folks, some additional information,

I have removed all meal dates and client details.

I have then added two new Clients that have their diets prepared in separate kitchens.


Testing outcome

• Print labels for kitchen 1 with today’s date – works ok
• Print labels for kitchen 2 with today’s date – works ok

• Print labels for kitchen 1 with tomorrows date – works ok
• Print labels for kitchen 2 with tomorrows date – no ( labels are blank)

But if I delete kitchen 1 tomorrow date from the diet table and then print kitchen 2 with tomorrow date it works ok,

Surely this must be because the code is not action the And clause?

I’m think the code looks at the query QryPrintDataVBA , this query pulls

· (SELECT MAX(MealDate) from TblDietPlan)
· kitchen name

I think the code sees the date has been entered is already in the database and does not action the AND clause for the kitchen name therefore does not re-order the diets.


Code


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 = "McrReOrderDiets"
stDocName = "McrPrint_Customise_Labels"
DoCmd.RunMacro stDocName1

End If

 

[brucey54]

Thanks Jdraw and everyone for your help much appreciated, this is just so annoying

 

[gemma-the-husky]

another thought then. Let's assume you are correct.

is there a print flag you are setting - so kitchen A prints labels for kitchen A, but then incorrectly updates ALL the meals as having been printed

so what is in this query?

QryPrintDataVBA

and what does this macro do - just print the labels, or something more?

DoCmd.RunMacro stDocName

 

[jdraw]

It would be less annoying if you would post a copy of your database with confidential info removed.

 

[brucey54]

The code should stop this from happening i.e.

• Does date and kitchen name exists within the database, if not re-order meals and print labels.
  
• If date and kitchen name exists within the database print labels no re-order

 

[jdraw]

I don't use macros, so can not help there.
What happened to Kitchens.
As suspected, your data model is not helping.

What exactly are you trying to do? I've looked at some of your posts since September. Many people have offered advice. You seem to have moved from Restaurants and Kitchens to Hospitals and Snacks.

People can not answer you with focused comments/advice when you have a moving, and poorly defined requirement.

Start with a clear statement of requirements. Build some test data and scenarios.
Working from your statement of requirements build a model. Test the model with your sample scenarios and data. Adjust as necessary until your model satisfies your test scenarios. Then start building the database -- tables and relations.

Good luck with your project. It sounds interesting.

 

[brucey54]

Thanks jdraw back to the drawing board I think, was kind of hoping for a quick fix...

 

[namliam]

I strongly advice against the use of macro's they are restrictive and anything they can do VBA can do better. Now you are deviding work between VBA and Macro's which makes things worse

In your Macro McrReOrderDietCusDate, you are first creating a temp table, creating temp tables for the sake of creating a report is bad bad bad practice
Next you are updating the temp table, again not the best thing

In your query is hardcoded hospital = "Ailsa Hospital", which is not good since my hospital is "Hospital 1", worse yet, you have a tekst field in your tblpatient to store this... Instead of having a Primary key in your tblHospitals and a Foreign key in your tblPatient. I am sure there is a host of other things I could comment on...

To your immediate problem....

Testing outcome

Print labels for kitchen 1 with today’s date – works ok
Print labels for kitchen 2 with today’s date – works ok
Print labels for kitchen 1 with tomorrows date – works ok
Print labels for kitchen 2 with tomorrows date – no ( labels are blank)
But if I delete kitchen 1 tomorrow date from the diet table and then print kitchen 2 with tomorrow date it works ok,

This is INDEED due to your (SELECT MAX(MealDate) from TblDietPlan) in the criteria of your qryPrintDataVBA since it will force the query to only select the latest date PERIOD, no dependancy on hospital or kitchen at all
On top of this your query doesnt have a Kitchen field, how would you expect the DCount to work on kitchen?

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

Worse yet, I dont even see a Kitchen field in any of the tables? so what are we

Did you inherit this database or did you start this project from scratch?
If this is something (relatively) new, I am sorry but the best way forward indeed may be to do a full redesign with lessons learned from this one.
Starting with a proper definition of your needs and a proper normalization process from that definition.

 

[brucey54]

Hi Namliam,

The (SELECT MAX(MealDate) from TblDietPlan) is not the problem, I can see why you would think this.

I have removed this from the query and I am still receiving the same issue.

I’m thinking I really only need a if statement like so;

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

Do this

Else

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

Do this

Else

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

Do this

Else

I need some help with the VBA code, I don’t have time at the moment for a full redesign, over all the database works well.

 

[namliam]

I need some help with the VBA code, I don’t have time at the moment for a full redesign, over all the database works well.

It works... but definatelly disagree on the "well" part... guess as long as you are satisfied with it

Note how you just "changed" from kitchen to hospital in your DCount

With your current test data in your current test database there are NO patients for hospital 2 to find....

lets try this...
Press CTRL + G when inside your database, this brings up the immediate window...
Type here

?DCount("*", "QryPrintDataVBA", "[MealDate] = #11/19/2014# AND [Hospital] = 'Hospital1'")

Change what ever you want/need in that DCount, what does it return that you do not expect based on the test database?

 

[brucey54]

Lol, thanks Namliam,

I think I have figured it out!

The code is working correctly, yes you read correctly lol

When the code looks at the query QryPrintDataVBA

It is looking to see if the date exists within the date field and if the hospital name exists within the hospital field i.e.

The labels will print ok, because no date or hospital name exists within the query.

Prints labels ok...

DATE Hospital
17/11/2014 Hospital1
17/11/2014 Hospital2

However when I print tomorrows date

DATE Hospital
17/11/2014 Hospital1
17/11/2014 Hospital2
18/11/2014 Hospital1

The labels will print ok for hospital1 but not for hospital2, this is because the code will not action the second part of the else statement.

Why!

Because the date 18/11/2014 already exists within the Date field and the hospital name hospital2 already exists within the hospital field.

Now how do I address this, 1 step forward 2steps back
code

 

[brucey54]

Disregard last post it’s not that, I have added the following criteria into the query for date field

(SELECT MAX(MealDate) from TblDietPlan) And Is Not Null

This will only pull the most recent date which should eliminate all the hospital names apart from the most updated one.

Therefore the code should action the second part of the else statement, as current date exists but hospital name does not...

Straight forward AND statement, but its it not working the way I’m thinking it should arrrggggg