How Do I make fields not appear on a form or report

[Lisa]

I have a report or form (I can use which ever one will work) that has
several combo boxes to choose appropriate criteria. If a particular
combo box has not been used, I do not want it to appear on the final
print out. I realize the can shrink can grow only applies to text
boxes not combo boxes. Can I use "is null" anywhere to make this
work? I cant change these to text boxes since I am storing the value
in the table not the text.

Thank you.

 

[John W. Vinson]

I have a report or form (I can use which ever one will work) that has
several combo boxes to choose appropriate criteria. If a particular
combo box has not been used, I do not want it to appear on the final
print out. I realize the can shrink can grow only applies to text
boxes not combo boxes. Can I use "is null" anywhere to make this
work? I cant change these to text boxes since I am storing the value
in the table not the text.

Thank you.

I'd suggest using a Report (since Can Grow/Can Shrink applies to forms only
when you're printing, not onscreen); rather than putting combo boxes on the
report, though, base the report on a Query joining your main table to all of
the combo box rowsource tables. If a joining field may be NULL use Left Outer
Joins (select the join line and choose the option "show all records in <main
table> and matching records in <lookup table>"). Then you can use the text
field from the lookup table as the control source of a report textbox, and Can
Grow/Can Shrink will work for you.

John W. Vinson [MVP]

 

[Lisa]

I'd suggest using a Report (since Can Grow/Can Shrink applies to forms only
when you're printing, not onscreen); rather than putting combo boxes on the
report, though, base the report on a Query joining your main table to all of
the combo box rowsource tables. If a joining field may be NULL use Left Outer
Joins (select the join line and choose the option "show all records in <main
table> and matching records in <lookup table>"). Then you can use the text
field from the lookup table as the control source of a report textbox, and Can
Grow/Can Shrink will work for you.

John W. Vinson [MVP]

My report is based off of a query. I went into the query and made the
lookup field text. My combo boxes are still showing up as values in
the report, not the text. Any idea what im doing wrong?

 

[John W. Vinson]

My report is based off of a query. I went into the query and made the
lookup field text. My combo boxes are still showing up as values in
the report, not the text. Any idea what im doing wrong?

No, because you have not told us what you're doing.

Please open the Query in SQL view and post the SQL text here. Indicate which
field is showing up wrong, and in what way it's wrong.

John W. Vinson [MVP]

 

[Lisa]

No, because you have not told us what you're doing.

Please open the Query in SQL view and post the SQL text here. Indicate which
field is showing up wrong, and in what way it's wrong.

John W. Vinson [MVP]

Okay here it is. The problem is the fields such as "eatltg2",
"swgl1", "swgl2", are all based off of table that is autonumber and
text. I am storing the auto number in the table to save space. But
now I need to show the text in my final report and when I try to
change the combo boxes to text fields the "autonumber" appears not the
text.

SELECT tbl_demographics.patientnumber, tbl_demographics.[patient last
name], tbl_demographics.[patient first name], tbl_demographics.[admit
date], tbl_demographics.[conference type], tbl_demographics.[case
manager], tbl_demographics.physician, tbl_demographics.[program
group], tbl_demographics.[discharge date], tbl_demographics.swgl1,
tbl_demographics.swgl2, tbl_goals_met.eatltg1, tbl_goals_met.eatltg2,
tbl_goals_met.eatltg3, tbl_goals_met.eatltg4, tbl_goals_met.nultg1,
tbl_goals_met.nultg2, tbl_goals_met.nultg3, tbl_goals_met.nultg4,
tbl_goals_met.swltg1, tbl_goals_met.swltg2, tbl_goals_met.swltg3,
tbl_goals_met.swltg4, tbl_demographics.swgl3, tbl_demographics.swgl4,
tbl_demographics.eagl1, tbl_demographics.eagl2,
tbl_demographics.eagl3, tbl_demographics.eagl4, [tbl_field indicators
group extra].nugl1, [tbl_field indicators group extra].nugl2,
[tbl_field indicators group extra].nugl3, [tbl_field indicators group
extra].nugl4, tbl_Fields_Indicators_group3.strat1swal,
tbl_Fields_Indicators_group3.strat2swal,
tbl_Fields_Indicators_group3.strat3swal,
tbl_Fields_Indicators_group3.goal1swa,
tbl_Fields_Indicators_group3.goal2swa,
tbl_Fields_Indicators_group3.goal3swa,
tbl_Fields_Indicators_group3.ed1swa,
tbl_Fields_Indicators_group3.ed2swa,
tbl_Fields_Indicators_group3.ed3swa,
tbl_Fields_Indicators_group3.swalcomm,
tbl_Fields_Indicators_group3.eatcomm,
tbl_Fields_Indicators_group3.strat1eat,
tbl_Fields_Indicators_group3.strat2eat,
tbl_Fields_Indicators_group3.goal1eat,
tbl_Fields_Indicators_group3.ed1eat,
tbl_Fields_Indicators_group3.ed2eat, [tbl_field indicators group
extra].eatgoal1, [tbl_field indicators group extra].eatgoal2,
[tbl_field indicators group extra].eatstrat1, [tbl_field indicators
group extra].eated1, tbl_Fields_Indicators_group4.strat1nutrition,
tbl_Fields_Indicators_group4.strat2nutrition,
tbl_Fields_Indicators_group4.strat3nutrition,
tbl_Fields_Indicators_group4.goal1nutrition,
tbl_Fields_Indicators_group4.goal2nutrition,
tbl_Fields_Indicators_group4.ed1nutrition,
tbl_Fields_Indicators_group4.ed2nutrition,
tbl_Fields_Indicators_group4.nutritioncomments,
tbl_Fields_Indicators_group4.nutritiongoalblank,
tbl_Fields_Indicators_group4.nutritionedblank
FROM (((tbl_demographics LEFT JOIN [tbl_field indicators group extra]
ON tbl_demographics.patientnumber=[tbl_field indicators group
extra].patientID) LEFT JOIN tbl_Fields_Indicators_group3 ON
tbl_demographics.patientnumber=tbl_Fields_Indicators_group3.patientID)
LEFT JOIN tbl_Fields_Indicators_group4 ON
tbl_demographics.patientnumber=tbl_Fields_Indicators_group4.patientID)
LEFT JOIN tbl_goals_met ON
tbl_demographics.patientnumber=tbl_goals_met.PtID
WHERE (((tbl_demographics.swgl1) Is Not Null) AND
((tbl_demographics.swgl2) Is Not Null));

 

[John W. Vinson]

Okay here it is. The problem is the fields such as "eatltg2",
"swgl1", "swgl2", are all based off of table that is autonumber and
text. I am storing the auto number in the table to save space

Open the Query in design view. Add the three (?) lookup tables to the query
grid, joining the Primary Key to the numeric foreign key in your table. Select
the text field from the lookup table to include in your report.

Based on the SQL, you have a LOT of repeating fields and improper
normalization! You may want to read up on normalization; there are some good
links in the "Database Design 101" links on Jeff's webpage:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]

 

[Lisa]

Open the Query in design view. Add the three (?) lookup tables to the query
grid, joining the Primary Key to the numeric foreign key in your table. Select
the text field from the lookup table to include in your report.

Based on the SQL, you have a LOT of repeating fields and improper
normalization! You may want to read up on normalization; there are some good
links in the "Database Design 101" links on Jeff's webpage:

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]

Yes I know about the normalization but for some reason I just can't
understand the correct way to do this. I am better at the "design"
portion and making my forms look "nice" which really doesn't help much
when the reports don't work right. I have tried to get help but I
cant seem to find someone to take the time to perhaps "show me" how my
tables should be. I don't know if you can do that or not. If I could
briefly explain one area of my database maybe you can explain it so I
can understand.

Lets say I have a page for staff to fill out on a patient. The first
page is the patient's goals. There are 5 drop down combo boxes that
pull from the same table because they can choose up to 5 different
goals for one patient. My goals are all in one table. However, the
main table I had to make 5 different fields for each possible goal
selection. Like goal1, goal2, goal3, etc. So that is three fields
with one lookup table. I dont understand how to not have 5 separate
fields if someone can choose 5 different responses. That is why I
don't consider them repeating fields. Is there somewhere I can get
more personal help with this? Is there another user group that
provides one on one immediate help?

Thanks for any input you can offer.

 

[John W. Vinson]

Yes I know about the normalization but for some reason I just can't
understand the correct way to do this. I am better at the "design"
portion and making my forms look "nice" which really doesn't help much
when the reports don't work right.

If you're building a house, you don't concentrate on the trim and siding or on
the windowframes until you have the foundation poured correctly.

The Tables are the foundation of your Access database. If your tables aren't
correctly normalized (and I fear yours aren't) your entire structure will be
at risk.

I have tried to get help but I
cant seem to find someone to take the time to perhaps "show me" how my
tables should be. I don't know if you can do that or not. If I could
briefly explain one area of my database maybe you can explain it so I
can understand.

Sounds like a good idea.

Lets say I have a page for staff to fill out on a patient. The first
page is the patient's goals. There are 5 drop down combo boxes that
pull from the same table because they can choose up to 5 different
goals for one patient. My goals are all in one table. However, the
main table I had to make 5 different fields for each possible goal
selection. Like goal1, goal2, goal3, etc. So that is three fields
with one lookup table. I dont understand how to not have 5 separate
fields if someone can choose 5 different responses. That is why I
don't consider them repeating fields. Is there somewhere I can get
more personal help with this? Is there another user group that
provides one on one immediate help?

"Fields are expensive, records are cheap". You need A THIRD TABLE:

Patients
PatientID
LastName
FirstName
<other biographical details>

Goals
GoalID
Goal
<maybe other fields about the goal itself>

PatientGoals
PatientID <<< who has this goal
GoalID <<< what goal do they have
<other fields about this patient/this goal, maybe a comment field, date
attained, satisfactory/unsatisfactory, etc.>

You would use a Form based on Patients, with Subform based on PatientGoals.
This subform would have a Combo Box based on the Goals table (on one row).

You would see (using some of my goals)...

FName: John LName: Vinson
DOB: 5/16/1946
....

Goal Comment
Maintain body wt 170 lb target
Control BP 3 AHP drugs
Regular exercise Walk 2 miles 4/wk


If a patient has four goals, you add four rows to this continuous subform. You
can see all four of them; you can check off "satisfactory"; you can enter
comments, you can enter the attainment date... whatever you need. If a patient
has six goals, you add six rows... *or you completely redesign your wide
table, all your queries, all your forms, all your reports* because you had to
take a jackhammer to your foundation to add the new wing to your house.

Does that make sense...?

John W. Vinson [MVP]