By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Deal with more than 50 checkboxex on the same form

P: 68
I am fixing already existing database about coastal grants. I have several tabs for data entry on main form like Main, Contacts,Budget, Project Goals,Performance Reports..etc.Total 10 tabs on a main form.

On Project Goals tab I have a command button called "Open Project Deliverable". When you click on this command it will open you a form or rather say a subform which has more than 50 checkboxex and associated textboxes.

Right now I have created one table which has all those more than 100 fields one chekbox and associated textbox. All the data is stored there.

On another tab I want a button which on click shows the tabular format of what a user has selected and entered a value for.

Please help I am clueless. I have tried several ways around.

My main table is called MainDataEntry and Primary key is Grant Index.
One Grant Index can have multiple deliverables.
User can be able to edit all of them if he wants to.
These are several conditions on it.

Let me know if anybody have any question regarding description of problem.
Thanks for help in advance
Feb 11 '09 #1
Share this Question
Share on Google+
9 Replies

Expert 100+
P: 1,287
What is the structure of the table that holds the data about checkboxes and text fields? It seems to me that one record in this table should include a checkbox value and a textbox value, and there will be 50 of these entries all with the same ID or whatever it is they are a part of.

Say your table has:
CustomerID, chkField, txtField

Then you can base the tabular format you want on something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM MyTable WHERE chkField = -1 AND _
  2.  & CustomerID = 'Customer1'
Since -1 = True, and 0 = False, that will display only the values that are checked.

If that's not what you want, you'll have to explain a little more.
Feb 11 '09 #2

P: 68
I thought to do something like that and also tried to put it into effort. But than I confused how do I uniquely identify that set of 100 checkbox and textbox.

Here is what I did.
I created two tables


GrantIndex Number
DeliverableId AutoNumber


PerformanceMesure Text
CheckBoxValue Yes/No
TextBoxValue Number
GoalValue Number
PerformaceMeasure has 50 different kinds of data to be entered like Public Access, Boating Access, Fees, Management etc( like that 50 diff. types)

I have to connect these two tables to the main table that is called "Main Grant Information". Here, GrantIndex is a primary key and it is autonumber.

Please guide be little bit about how to make them realted. How should I establish relationship between them?
One "GrantIndex" can have many Project Deliverables..User requirement.

Thanks for helping
Feb 11 '09 #3

Expert 100+
P: 1,287
Each GrantIndex can have many Project Deliverables.

Ok, you just need to specify in your tblDeliverables which GrantIndex this PerformanceMeasure is for. Add the GrantIndex field to your tblDeliverables and delete the tblProjectDeliverables, that's how you make them related. You don't have to, but you can make a primary key including GrantIndex and PerformanceMeasure. I don't see any reason you would have the same PerformanceMeasure twice for one GrantIndex.

Now you can select records from tblDeliverables with criteria based on the GrantIndex, and there should be up to 50 records per GrantIndex, right? Make sure the GrantIndex field is indexed for perfomance reasons, if you don't use a primary key.
Feb 11 '09 #4

P: 68
Now you can select records from tblDeliverables with criteria based on the GrantIndex, and there should be up to 50 records per GrantIndex, right? Make sure the GrantIndex field is indexed for perfomance reasons, if you don't use a primary key.
Here little bit nore clarification is needed I think.

One GrantIndex can have many Project deliverables. That is right, but
One deliverable means 50 or less Performance measures.
Meaning, One granIndex has many Deliverables and One Deliverable has 50 or less performance measures values(Chkbox+Textbox)

Also, on other tab I have to provide the Progress report. So I want a tabular format something like this

GrantIndex DeliverableId
Performance Measure ChkBoxValue TextboxValue Goal

By deleting tblProjectDeliverables table I cannot establish link between deliverables and GrantIndex.

Thanks once again
Feb 11 '09 #5

Expert 100+
P: 1,287
Ah, I missed the concept of the deliverables and the relationship, sorry.

So, instead of GrantIndex in your tblDeliverables, you need the DeliverableID, and you keep the tblProjectDeliverables. So those 2 tables will be related by the DeliverableID, and if you wanted the tblDeliverables data on a form with the associated GrantIndex, you'd use a query that inner joins the two tables on the DeliverableID field.

It sounds like you are close to what you want already. I'm not exactly clear what you mean by the report format, but if you make that query that joins the data, you can set the report to group GrantIndex and DeliverableID, then put them in a header for their section, or put them in the detail and set them to not repeat duplicates.
Feb 11 '09 #6

Expert 2.5K+
P: 2,653
Hello, jinalpatel.

When you work out your tables you probably could use method described here to render those 50 options on subform.
Feb 11 '09 #7

P: 68
Still I am not able to fix it.
Now I have two tables like

GrantIndex Number
DeliverableId Autonumber

DeliverableId (Number)
PerformanceMeasure (text)
ChkboxValue (number)
TextboxValue (number)
GoalValue (number)

And my Main table which has GrantIndex as a primary key
Now what do you think about their relationship.How should I connect them, provided all the criterions in previous posts.

OR do you think I need third table called Performance measure, Which has following fields
PerformanceMeasureId (autonumber)

And with that I will need to change the tblDeliverables like the following
DeliverableId (Number)
PerformanceMeasureId (Nnumber)
Feb 11 '09 #8

Expert 100+
P: 1,287
I don't see any reason for a third table. What's the problem you're having with the two tables you have now? All the information is there, it's just a matter of how you want to present it.
If you want to show all the performance measures in a subform, make it's data source a query which is a join of the two tables on DeliverableID, and link its child fields GrantIndex and DeliverableID to master fields selected or filled in on the main form. Know what I mean?
Feb 11 '09 #9

P: 68
Ok, I have figured this thing. I am not 100% sure but I think it will work.

Now I have two tables

GrantIndex (number)
DeliverableId (number)
PerformanceMeasure (Text)
ChkboxValue (yes/no)
TextboxValue (number)
GoalValue (number)

2) tblProjDelUser (this is the table opened from my main data entry form to enter the performance measure data and their values)

PerformanceMeasure (Text) (PK)
ChkboxValue (yes/no)
TextboxValue (number)
GoalValue (Number)
I have made one Append query that appends all the data entered by user in the table "tblProjDelUser", to tblDeliverables.
That way I got all my data in the table called tblDeliverable.
So GrantIndex + DeliverableId togather uniquely identify the record on that table. I also can write a query to show it in a report format.

My questions:

1) My GrantIndex field is autonumber and it is automatically incremented when user starts to enter data on first tab.
How do I populate this field in "tblDeliverables"?
I need to show tblProjDelUser table for data entry on 6th tab of main form.

2) I would like same DeliverablId in "tblDeliverables" for bunch of Performance Mesure. How can I do that?
Meaning When user hit append records command button on 6th tab, all the records that are appened in tblDeliverables should have same DeliverableId.

Thanks for helping me and discussing with me Chip R.
Feb 12 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.