Check box for one record, form pulls from multiple tables

[chellebell1689]

Hello, I really hate to open a new thread on something I've already asked, but this is a different set up.

I have an attendance form that pulls member information (first & last name) from a members table and is suppose to save if the member attended to another table (attendance table). My problem is that if I click the check box it checks all boxes in that row, but when I uncheck it only unchecks that one (but will recheck all if I uncheck another). I tried adding the field from the table via "Add Existing Field", but then it won't pull the names from the member table. I tried bounding it to the field via the control source, but then it tells me I can't edit it.

Please help! Let me know if you need more information. Also, sorry for asking this again, but as you can tell, it's a bit different situation.

 

[vbaInet]

Can you show us a screenshot of this problem?

 

[chellebell1689]

Hello, sorry it took me so long to reply. I only work here a couple days a week (Mon-Wed) and yesterday was Memorial Day.

Here are the screen shots you asked for in regards to the check boxes; the ones marked in red are ones that are bound (won't let me change them because they're bound) the others are unbound but I use an append query to update the table. I included form view & design view, in case you wanted both.

 

[vbaInet]

The checkbox needs to be bound to a field. Unbound controls in a continuous form behave like this, they repeat themselves across all records.

 

[chellebell1689]

I tried that. If I bind it I'm not allowed to make changes.

 

[vbaInet]

Can you expand on that... i.e. what's the exact error message, what actions did you take prior to receiving this message?

 

[chellebell1689]

Sure, sorry about the vague. I've tried to bind it via the control source > build > then assign it to the appropriate field in the table. After doing this, I get "Control can't be edited; it's bound to the expression '[SS_Attend]![S1_Attend]'." I've also tried to add the field via > add existing field > other tables > the appropriate field. But when I do this it stops generating the members of the class. Then I go in and set the control source for the form to Members Table. After this I get the error "Control can't be edited; it's bound to the unknown field 'S1_Attend'." If I try making the control source for the form to select fname & lname from my members table and the attend from the attendance table, it still doesn't generate my members like it should.

I believe that's everything I've tried. Thank you for helping me with this.

 

[vbaInet]

You don't need to go to Build, just select the appropriate field from the drop-down menu in the Control Source.

 

[chellebell1689]

It's from a different table. Sorry I thought I mentioned that. But I do have a temp attend field in my members table that I tried assigning it to. That works (as far as the form goes, I haven't checked how it's saving), but I have an attend box for each Sunday of the month and the form doesn't like all 5 to be assigned to one field. I don't want to make a temp attend field for each Sun. Is there a better way to do this?

 

[vbaInet]

A control should be bound to the form's record source and you do so by selecting one of the fields in the drop down, so what you've done is not quite right.

If you want to include a field from another table then that table must have been included in the Record Source that is based on a query. Your query will join on both tables and from the relevant fields from both tables should be included in the query. It is this query that you will use as the Record Source of your form. From there you will be able to select the field that should be bound to the checkbox.

Does that make sense?

 

[chellebell1689]

I took a screen shot of what I think you're talking about. If so, I've done this and it's not generating the names of the class members like it should.

 

[vbaInet]

...it's not generating the names of the class members like it should.

Was hoping that you would expand on this as well I'm guessing you're getting lots of repeated data?

Do you know much about the term normalisation?

By the way, you only need one one criteria there, it's either you put Yes or you put False, with the former being my preference if it's a Yes/No field.

 

[chellebell1689]

I was just about to edit that post. lol When I say it's not generating like it's suppose to I really mean it's not generating any names at all. When it's set to just table it shows all the members, once I click on a filter button for a class it only shows the members who are on the roll & in that class. If I use the query to use both tables as the control source, it won't show any names, even when I click the class filter buttons.

I have been working on normalization. I have another database that I'm slowly setting up so that I can transfer (aka re accomplish) all the right steps to do the thing I want the database to do (like this form for example).

I know I only need one, it was a quick test setup and I forgot if it checks for Yes or for True (in the past it only did one for me on this form).

 

[vbaInet]

It's mainly down to how you've set-up the tables. Upload a cut-down version of your db, explain your project and I'll take a look and advise.

Ok, one rule-of-thumb you should always remember is, tables first and everything else later. Don't try to build your tables to accommodate the look and feel of your forms. First normalise your tables, I mean get it properly normalised before thinking about forms. A properly normalised table makes developing the table easy.

 

[chellebell1689]

I'm sorry I don't understand what a "cut-down version" is. Thank you so much for the help.

**Update**
I am heading home right now, but will be back here tm. If you let me know exactly what it is you want I will send that to you tomorrow.

Thank you again for all your help.

 

[vbaInet]

A copy of your db with no sensitive data.

 

[chellebell1689]

Oh well don't I feel dumb!? lol I'm trying to upload it, but it's taking forever. It stopped once and said a security cert was missing. Trying again hoping it won't say the same.

Thank you so very much for helping me!

**Update**
I keep getting an error about a missing security token. I sent an email to the recommended person requesting help. So it'll be a while before I can actually post it.

 

[vbaInet]

Follow these steps:

1. Debug > Compile: you'll find these menus in the VBA code side of Access). But if you don't have any code in your application then you can skip this step.
2. Compact and Repair
3. Zip the db
4. Upload

 

[chellebell1689]

Ok, I tried your steps, twice, and it's still not working. Tells me a security token is missing (not sure if I previously stated that). I'm going to try to remove all the other data and just leave what pertains to this question; I'm wondering if the real issue is it's too big? It's currently at 122,078 KB (zipped).

 

[vbaInet]

That's 122MB zipped... it's beyond huge

On the Attachments window it lists the maximum file size for each file type and I think accdb and mdb are 2MB each. You need to copy and paste only the relevant bits into a new db and follow the same steps as before.