Hide Field if Another Field is Null

[MaBear]

I'm a wannabe developer & programmer. I have a very simple inventory database that keeps track of how many packages of chocolate chip cookies are in the refrigerator and how many are empty. The FIELD NAMES and Data Fields look something like this:

MIX MADE MX1 MX2 MX3 MX4 EMPTY REMAIN
Choco Chips, 4 packages, Yes, No, No, No, 3 Empty, 1 Left
Apple Pie , 2 packages, No , No, 2 Empty, 2 Left
Fruit Pie , 4 packages, Yes, Yes, Yes, Yes, 0 Empty, 4 Left

What I want is, if there are only 2 packages (see Apple Pie), then I don't want to see the last 2 yes/no fields. I hope this makes sense.

Can I hide the emply value fields in a query or do I have to do it in a form? I would prefer to hide the fields in 1) table 2) query 3) form - in that order.

Thanks for helping me solve this & thanks for the excellent site.

 

[alvechurchdata]

At first sight it seems that you're storing too much information here. It's a general rule that you don't store any item of information that can be calculated from other items. If you do, you're just storing up trouble for the day when you get an inconsistent row like this:

[TT]
Smoothie, 3 packages, No, Yes, No, No, 0 Empty, 7 Left
[/TT]

It will happen.

The other problem is that you've restricted yourself to four slots. It's always a danger sign when there are fields labelled Thing1, Thing2, Thing3 etc. The design needs to be normalised. This involves splitting the table into two with the product information in one table and the location information in another:
[TT]
Mix table

Name
----
Choco chips
Apple Pie
Fruit Pie

Location table

Name MX State
---- -- -----
Choco chips 1 Yes
Choco chips 2 No
Choco chips 3 No
Choco chips 4 No
Apple Pie 1 No
Apple Pie 2 No
Fruit Pie 1 Yes
Fruit Pie 2 Yes
Fruit Pie 3 Yes
Fruit Pie 4 Yes
[/TT]

I know that this looks a lot more complicated but it will work out easier in the long run.




Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MaBear]

Thanks alvechurchdata for your suggestion. Agreed. I will create and link the Mix table later. The database isn't that large - at the most it only has about 25 records so I'm not that worried about it getting too big or cumbersome. I have one table, one query, and plan to create a form in order for my children to enter the data.

What I don't understand is how to hide a field if there are only 2 packages of Apple Pie. I looked up Visible and a few others things. Got some examples to read but I don't understand the VBA code when I try to relate it to my problem. Or, should I use an Expression? on the query or form?

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

What I don't understand is how to hide a field if there are only 2 packages of Apple Pie

If there are only 2 packages, there will be only 2 related records in the normalized location table as described by alvechurchdata. There is nothing to hide. It's like the OrderDetails table in the sample Northwind mdb. If an order has only 2 products, there are 2 related records in the order details. If there are 4 products in the order, there will be 4 related records.

If I were you, I wouldn't go any further prior to reading the link that PH provided.

Duane
Hook'D on Access
MS Access MVP

 

[MaBear]

Thanks PHV and dhookom and especially for the link PHV - it was excellent. Some of the terms are a bit above my knowledge or memory.

Everyone seems to be directing me to change the structure so I'll look at that as I can't get my brain wrapped around what alvechurchdata suggested I do without seeing it. So give me a day or so to set it up and then I'll update you if I am still after what I think I want. Its been awhile since I've done any database work so I'm VERY rusty, so thanks for your patience...

Cheers!

 

[MaBear]

Hi everyone! I believe I did what has been suggested. I created a table of packages and linked it to the mixes. Then I developed a query as alvechurchdata suggested but it isn't quite what I want. I also created a crosstab query to show the data as I want to see it but that didn't allow me to edit the Mx1/2/3/4 fields - for that, I would have to go to another area in the database.

I knew this was going to be hard to explain (sorry). I'll refer to the fields that I would eventually like to see on a form, from my first post.

These fields:
Won't change: MIX, MADE
Bag full or empty with Yes/No field value: MX1, MX2, MX3, MX4
Will be Calculated: EMPTY, REMAIN based on values in the Mx1/2/3/4 fields

What I am trying to do is limit the number of MX fields that can be seen on the form based on the value in the MADE field so that someone can't make up more packages. For example, if the value in MADE was 2 then you wouldn't see MX3 and MX4. If the value in MADE was 3, then I don't want to see the MX4 field. The MADE field could contain a number from 1-4 depending on how many packages I want for one food dish.

Think of the form as a quick automated checklist and nothing more. It will hold no more than 30 different Mixes at one time and no more than 120 packages if every Mix had 4 packages.

This is the same table that I refer to in the first post except I think I had one calculation wrong (corrected below) + one new record. I hope this is a correct statement, if I say the MX1/2/3/4 fields are a one to one relationship with the MIX field.

MIX MADE MX1 MX2 MX3 MX4 EMPTY REMAIN
Choc Cookie, 4 packages, Yes, No, No, No, 3 Empty, 1 Left
Apple Pie , 2 packages, No , No, 2 Empty, 0 Left
Fruit Pie , 1 packages, Yes, 0 Empty, 1 Left
Bread , 4 packages, Yes, Yes, Yes, No, 1 Empty, 3 Left

If I've still lost you or you don't agree with my database structure, then can you just explain how (or which Function I have to learn about) to hide a MX3 and MX4 field on a form if the value in the MADE field is 2. Or, if this is easier for you, can you just relate the VBA code to my example as I might be able to figure out the rest.

Thanks everyone, I really do appreciate the help.

 

[MaBear]

Hi everyone, this is similar to what I want I just don't know how to get it to work for my problem. Here is the thread:

thread702-953540

I have the form working well for everything else except this one problem. Thanks.

 

[MaBear]

Hi everyone, okay I've changed the structure radically from where I started and even from the suggestions made. I now have a one to many relationship which works great and I think its kind of normalized, albeit different from what everyone recommended or probably expected.

I've put the one relationship on the Mx1/2/3/4 and the many on the MIX. The form still looks the same as before and now I can enter as many different types of packages as I want and I'm still limited to only 4 Mxs per MIX (perfect).

But.... I still want to hide Mx 2/3/4 if the MADE is only 1 or Mx 3/4 if the MADE is 2. If anyone can help me with this I would appreciate it a lot. I am praying that this new format will work for you programmers.

BTW - thanks for your patience. I'm getting there...

 

[dhookom]

What is your current table structure?

Duane
Hook'D on Access
MS Access MVP

 

[MaBear]

Thanks Duane, I have 2 tables, 1 query, 1 form and 1 report. I'm happy except for the hide aspect.

I believe everyone wanted the one on the Mixes table and the many on the packages table but when I went to set it up it didn't work the way I want it to. Another aspect that I questioned before I set it up, what if I have 8 packages of 1 mix (very rare if ever), then I would set up 2 Mix records.

tMixes (Many)
MixID - Autonumber
Mix - Text
Made - Number

tPackages (One)
PkgID - Autonumber
Small - Yes/No
Mx1 - Yes/No
Mx2 - Yes/No
Mx3 - Yes/No
Mx4 - Yes/No
MixID - Number

Thanks.

 

[dhookom]

I would not continue with your current structure. I think all of the experienced developers in this thread have suggested a more normalized structure. Your table structure should not be determined by what you want to see on a form.

I don't understand your business so it's difficult to tell you how to build your form(s) to implement your business.

Duane
Hook'D on Access
MS Access MVP