Basic "Form" Help, Creating a checklist

[aumana]

Hi, I am new in access and I am trying to create a basic checklist in access. I created a form with 4 columns, one for month (as a search column), one for task, one for responsible, and one as a yes/no column where the user checks if the task was completed or not. Evrythinig works, but when the user changes the month on the first column, I want to yes/no box to get unchecked.
Ex: If I already did task XYZ in May, and it is June, I will change the month to June but the check box is still checked, therefore the user cannot do the task and the box will stay as if it was done.
How can I fix this?
Thank you very much!

 

[PNGBill]

Is your Form's Data Source the Table ? that has the check box on it?

You will need to sort out your Data Source. eg, Where does Month Come From, If it is a different Table from the the Check box then Have Month on the Main Form or a Sub Form and The Check Box on a sub form or different Sub Form from Month. Link the two by a field and when you change the Month Text Box Control the check box that relate to the New Month should display in the other Sub Form.

 

[missinglinq]

...but when the user changes the month on the first column, I want to yes/no box to get unchecked.

In addition to PNGBill's question, what exactly do you mean by changes the month? Because of the checkbox staying checked, it sounds an awful lot like you're simply changing the value for month in the existing record, rather than creating a new record for the new month.

Also, is the checkbox bound to the underlying table? Unbound controls for all records hold the value last entered in that control on any record.

 

[aumana]

Thank you for your help. To your questions, I do have a table where I have a search column which is month, and the information here comes from a table called "mes" where the months are listed. Then I have the other information and one yes/no column. With this table I created a tabular Form, that's all I did.
I am attaching the document, there is three different checklists.
Thank you!

 

[PNGBill]

Nothing Attached.

Seems like you have some basic thinking to do about your Database, Tables and Forms.

Your Form is Tabular and from One Table Only.
This implies it is just like viewing the raw data of your table.

You see a month, May, and the next field is ticked.
You change May to June and of course the next field stays ticked because why would it change??
Imagine your Shopping List.. You put Peas in the Trolley and tick them and look down to Beans. Beans are unticked - Great.
But what it appears you are doing is ticking the Peas and then Changing Peas to beans and Walla - Beans are already ticked because that is the tick for Peas.
If I am wrong, sorry for playing with your grocery list

If I am correct, you need to research and get the basics working.

What Tables do you have that relate to what you want to do.

Normally, a Table has basic Data, Say CustomerTable - Customer Name etc
ProductTable - Product details and QuoteheaderTable, Quote Basic Details, Cust Name, Date etc and QuoteDetailsTable, the actual Line Items on all quotes.

Of the Tables above, The QuoteHeaderTable will have One New Record for each new quote and QuoteDetailTable will have as many new records for however items there are on each quote.

Your Form will then show all the QuoteHeaderTable records, in Tabular form if you wish and you will Scroll down the form and tick as you wish BUT not Change (in this context).

You will have Forms to ADD a Quote, To Edit a Quote to Just View a Quote. - One Form will do all of these with a little "Magic"

Mostly you will have Forms and Subforms where the Mainform has the Quote Header Details and the Subform the Quote Details.
The two Forms will link by Master Child fields so the data Matches.

 

[aumana]

Thank you PNGBill. I do understand what you said. I am going to play with my file and see if I can figure out something. I need to learn this stuff.

 

[aumana]

I think now it is attached. I am reading a manual I found online. And yes, I am excatly doing what you sai, using the Form to see the same thing I hve in the Table.

 

[PNGBill]

Sorry can't open the file - doesn't recognize accdb. I use access 2000. What version do you use?

Did you compact and Repair??

One way is to open a new Database and name it ProblemoDatabase.
Import into the New database only the few tables, queries and forms that relate to your Issue.

Delete most of the records and only leave Non Sensitive or Example Data.

Compact and Repair and also zip if, still large but shouldn't be by then and Attach this new database.

Sorry, change to 2000 format if using newer version - should be an option somewhere for this.

 

[aumana]

Here it is, this might work. Thsnk you so much for your help.

 

[PNGBill]

Your Months Table shouldn't have a primary Key named Id. Change to MesID.

 

[PNGBill]

Also your Year Table has Id. This means two tables have the same name for their Primary Key
Change to AnoID

 

[PNGBill]

Do you want the different tasks to be completed for each month and a record kept of the different months completed tasks?

 

[aumana]

Yes, that is exactly what I want. And actually not sure if the Year column is even needed, I can omit that one.

 

[PNGBill]

year "field" - no columns in a database. You can have columns in Reports.

Will have a "play" with your db and get back to you.

 

[PNGBill]

Have a look at what I have done with your database.

I have changed some field names and the tables.

Lookup for Month is no longer done on the table, just the form and just uses the Name of the month from a qry.

I have added a command button to one form that will append any records that are completed and then reset the month to Completed so you can start again.

The appended Records are stored in a new table so you can view the history.

If what I have done is what you are looking for, then you will need to replicate the Command Button on the other two forms - change the query names. Queries are already made.

 

[aumana]

Great! I am going to take a look at it, thank you soooo much! I will let you know how it goes. Love it because I really want to learn more than just dealing with databases in tables.

 

[aumana]

Thank you very mucho for your help, I have had a busy day today but tonight I am going to work on it and understand what you did. One question that I had, is, in the Command Button you put on the Form, what you do is that you create a Macro on that button?

 

[PNGBill]

You could create a macro to run when you click the button but I did not, I just listed tasks to be done.

You have 3 tables which I understand to be Tasks that should be done Monthly.
There really should just be one table and add a field to show which type of task it is.
The new table will have 40 records but when you query for one type you will just see the 14 or so records for that type.

This will mean you can have just one Form and a way to either show all the 40 tasks or just the tasks for the Group you want to see.

I made two queries x 3 tables = 6 queries but if one table, then 2 queries only needed.
One query will add any records to the Completed Task Table when The Month, Date and Tick are all present.
The 2nd Query then changes the Task Table Record back to Month = Completed and removes the Date and Tick so you can start off the task for the next month. Maybe the date should remain so you see "Completed" and the date will show when completed. - easy to change.

What you need to do is look at the changes I made to the tables, the new table added, the new queries added.
In Form Design View, right click on the Command Button and see the On Click event to see what happens when you click the button.

I repeated some tasks as they don't always complete on the first attempt . Maybe one of the Experts can suggest a better method but essentially I am just running the two queries and then refreshing the form so you see the current data.

You will need to make your own form to display the data in the Completed table and you can have a Commend Button on your current forms to open the new form and view completed tasks.

If you are unable to create the other two Command Buttons, let me know.

Good luck

 

[Uncle Gizmo]

I noticed this thread on checklist creation in a Microsoft Access database and thought the set of videos on my website here:- https://sites.google.com/site/msaccess457966vmfjg/advanced-techniques/check-lists/add-a-check-list-to-your-db/videos---add-a-check-list-to-your-db might be of interest to anyone who happens upon this thread