Simple questions but can't figure it out

DougM

Registered User.
Local time
Today, 04:34
Joined
Apr 18, 2004
Messages
26
I have a table that one field is set for either N or Y (i.e. No or Yes) to tell if the person listed is a volunteer or not.

I have 2 forms I would like to put a check box on, where you could change this field in the table .

1 form is item sold entry form, the other is a form listing information about the person (info taken from a different table).

How do I setup a check box to do this on each form?

sorry for the dumb question (I am very new to access). Every thing I have tried to do just shows a -1 or 0 (which I know if computer talk for N, and Y..but would like to see the N, and Y. Is this possible?

Thanks!!!
Doug
 
I'm assuming that the data type of your Y/N field is text. If you change the data type of the field in your table to Yes/No your table will actually display a checkbox when you view it if that would suit your purposes. This would be the recommended way.

If you definitely want the Y/N, you'll have to first add a non-visible text box to your form, bound to the Y/N field. Then add an unbound check box. In the On Click event for that check box you need to put a piece of code:

Code:
If Me.chkBox = 0 Then Me.txtYesNo = "N"
If Me.chkBox = -1 Then Me.txtYesNo = "Y"
chkBox and txtYesNo are simply example names for your 2 controls, you'll have to name them or change the code accordingly.

Also, in the form On Current event you would need to insert a piece of code to fill the unbound check box based on the content of your Y/N field.

Code:
If Me.txtYesNo = "N" Then Me.chkYesNo = 0
If Me.txtYesNo = "Y" Then Me.chkYesNo = -1
 
working on it..thanks! one more question

RichO said:
I'm assuming that the data type of your Y/N field is text. If you change the data type of the field in your table to Yes/No your table will actually display a checkbox when you view it if that would suit your purposes. This would be the recommended way.

If you definitely want the Y/N, you'll have to first add a non-visible text box to your form, bound to the Y/N field. Then add an unbound check box. In the On Click event for that check box you need to put a piece of code:

Code:
If Me.chkBox = 0 Then Me.txtYesNo = "N"
If Me.chkBox = -1 Then Me.txtYesNo = "Y"
chkBox and txtYesNo are simply example names for your 2 controls, you'll have to name them or change the code accordingly.

Also, in the form On Current event you would need to insert a piece of code to fill the unbound check box based on the content of your Y/N field.

Code:
If Me.txtYesNo = "N" Then Me.chkYesNo = 0
If Me.txtYesNo = "Y" Then Me.chkYesNo = -1


I was able to set it up the way your 1st said on the form that reads directly from the table with the volunteer field. Now I have a problem with my other from, it reads all of it's information from a table called Consignor Information. What I would like to do is add the check box to this table also (have it linked to the volunteer field on the other table) so that they can change volunteer status here as well. The 2 table are linked with a 1M relationship w/ forced E and cascad delete. When I put the check box on the form I am not sure how to link it...any ideas?

THANKS for all your help!
Doug
 
Doug, you have other threads going that relate to this one. What is the point of having a table with only an autonumber and a yes/no field? You need to put the Volunteer field in the table that defines individuals.

When you want to show a yes/no field as a check box, change the control type. Go to Format/Change To.
 
Pat Hartman said:
Doug, you have other threads going that relate to this one. What is the point of having a table with only an autonumber and a yes/no field? You need to put the Volunteer field in the table that defines individuals.

When you want to show a yes/no field as a check box, change the control type. Go to Format/Change To.


Pat, sorry about the different threads, I have been trying to work on different problems with this database, and it seems as if they are all combined. The volunteer field is part of a table not by itself. It is located on a table called Items sold. What happens is that someone buys an item and it gets entered into the database, at this point the person inputing the data should be able to select if the consignor who sold the item is a volunteer or not (that is what I set up on the items sold form..thats for showing me how to do the check box). They also use a form called consignor information (which they keep open most of the time, and do most of their work in) that lists all of the consignors in the database and their information. It used to be that when some one was no longer a consignor they would delete their name (but this would still leave all of their items sold, etc still lingering in the items sold table) hence my asking about the cascade delete option (which I hope is what is needed, and will clear out all information related to the consignor's number??

I am trying to add a check box to the consignor information form so that they can check or update a consignor's volunteer status there also, but since that information is located on a different table I am not sure how to set it up.

I am sorry for all the confusion, I didn't not create this database, and have come in to help a friend with it, as you know sometimes trying to figure out a person's thought processes and logic for creating something is harder that fixing it.

The only table that is "floating" out there with very little info on it is called Volunteer Table. This table only has the fields AutoNumber, and Consignor Number in it.

Again Sorry for the different threads, and the confusion. I am just trying to tackle this only problem at a time, and hopefully get it all working.

Thanks for all the help and wonderful advice,
Doug
 
addition to previous message

In digging into why this floating table is out there (volunteer table) They have a Query set that reads from this table and also the Items sold table,

Question, If I have it set so that they have the check boxes on the items sold table saying if they are a volunteer or not, do you really need this other table?

Oh ok, now I see..what happens is that at the end of the month you input into the Volunteer table those people/consignors that have volunteer during the month, then it pulls their information from the items sold table and puts it together (however you have to go into the items sold table and manually change them from a N to a Y in the volunteer field (this is before I added the check box option). Would there be anyway to stream line this..even to me (a relative beginner at access) this seems very "clunky". I see that if I get rid of the volunteer table then they would have to recheck the consigors every month, but that seems easier...any ideas?

would there possible be a way to make a button on a form that each month at the beginning they click and it changes the volunteer field to N automatically, then during the month they can just use the check box to select who has volunteer or not? this would (i think) get rid of the "floating" table and also make it a bit easier. But how would I change the query to correctly get the information? (i.e. from the volunteer table [listing consigor number] to the items sold table [volunteer Y/N] option)

what a second...since all of this is basically to print a report of the current months volunteers and what was sold...you wouldn' need the query any longer since all information would be listed in one table..just need to reconfigur the report to pull the correct information (correct me if I am wrong here)...so any ideas on what I would need to change on the report?

Ok the report gets all of it's information from the query...so if I just change how the query works I won't need to change the report..(which is easier?)


Ok I think (hopfully) that is it...If I can get this part, then everything (should) be just fine and working (keep your fingures crossed).

I have gotten the autodate fill, the cascade delete (which should be easier even with the volunteer table gone), and several other little problems cleaned up..just this one left.

Thanks!
Doug
 
Any idea how I can create a report that just shows:

Those consignors who are volunteers (taken from table Items Sold, field Volunteer (Y/N).

Consignor number, First name, Last Name (taken from table Consignor Information).
 
Make a query that selects the required fields and base the report on that.
 
more questions

Ok, In looking at the query what I would like to do
is set it up so that it shows only those volunteers who have a Yes (checked check box) in the Items Sold table (this field is set to Yes/No for Data Type). how would I do this?


I see how I can set the query up to use those fields, but I don't know how to make it selective in one field (i.e. I can get it to use EVERYONE in the volunteers field (i.e. all those with N and Y)) but not how to make is show only the Y marked ppl.

Doug
 
Last edited:
Good New, and one more question

HEY!!!
I figured out most of the problems (who would have thought it) Just had to get rid of all the distractions, and sit down and start tearing it apart, and digging.

figured out the selecting certain items from a field set with the YES/No data source, had to reset the querys to <>0 and <>-1.

I just have one nagging problem left (that I can see so far)

that is putting a check box on my form (Consignor Information) the infor for this form is taken from the table Consignor Information. The check box I would like to get it's information from table (Items Sold) field Volunteer (with data source set to YES/No) This way if they are in this form they can change a consignor from a volunteer to a non-volunteer without having to leave the form.

I can put the check box there, but every way I think of to link it doesn't work, the boxes show up greyed out and won't work.

Any ideas on what I can do here?

Oh the 2 tables (consignor Information, and Items Sold) are linked with 1M relationship w/ Enforce Referential Integrity and cascade update/delete selected

Thanks for all your help guys, I wouldn't be able to have done anything without your advice.

Doug
 
Just one more question and I have it!

Hey all,

I fixed all the problems listed above. HOT DAMN!

I went and deleted the floating table, move the volunter field to the consignor information table (it just seemed to go there more) and got everything working.

One last problem remains (its a little one, so I hope it is an easy answer)
I have a report that "should" list the currently selected volunteers in order of consignor number. It should only list them 1 time as it is reading from the consignor information table which only lists them 1 time, but the report is listing the volunteers several times (one time for each time they are listed in the items sold table). Does anyone know of a way I can stop it from doing this? I think there must be somewhere are reference to the items sold table that this report is getting (or the query it is build from) but I have looked and don't see anything. Please tell me it is a simple fix.

Doug
 
I think I got it

Hot diggied I think I figured it out. In the query the items sold table was shown, but I didn't have anything refering to it in the query. I removed the table from showing and now everything seems to be working!


I can't tell you how much this forum and its members have helped me, and expanded my horizons about access. Thank you all very much!

Doug

(until the next problem arises) :rolleyes:
 

Users who are viewing this thread

Back
Top Bottom