Multiple checks for Multiple Users - one app ver.

[xaos]

Hello, I have a few databases i maintain and develop for a university. One is for prospective students. it is split into a backend (mdb) and a front-end which i maintain as an mde file for security reasons.
in this database users can check a box for each student out of some 4000 that they need to print out a mailing label for. For the mailing labels i use the label report based on a query that looks for the true value of the checkboxes they have checked in the table.
the table the query is based on is called "userchecks", is separate from the main info table, and is related via the main table's primary key of course so each record in the main table has a corresponding record in the userchecks table and the userchecks table is updated when the main one is.
The userchecks table has fields for each of the 20 users on that database to enter their checks. and when they click the check label it runs an update query that clears all the checks after they print the labels for those students.

This is my problem: in order to give each user their own user checks i need to maintain 20 different versions of the database with each form and query bound to each different username field in the userchecks table. as you may know, this can be a time consuming process when updating the front end.

i've tried creating a nonlinked table in the frontend for the checks so that it would be unique for each user but that presents two problems. one, you can't make regular tables relate to linked tables so that they cascade update and delete, which causes problems when many new records are added each day, and , two, when i copy a new version of the database to their desktop and overwrite the old it will lose the former settings (not as big a problem as they can just print what they need and then switch to the new version).

what i need to do is figure out either how to use the currentuser() object to insert the checks into that particular user's field in the userchecks table and set the query to clear the checks of that user, or:
figure out how, if i use individual non-linked tables in each mde, to update said non-linked userchecks table so that every new record also had a corresponding userchecks record.

ideas? thanks in advance.

 

[LittleSmudge]

It strikes me that you have the fundimental problem of not properly doing the Normalisation of your data structure.


How about this.

Junk the second table ( Which, from your description sounds very much like it should never have been a second table, but have been incorporated into the first table from the start.)

Replace the second table with:-

tblPrintList
MainTableRef - ForeignKey to Main Table
UserId

Make both fields a joint Primary Key for this table.

Then, populate the table with
MainTableRef ( The studentId ) from the Main table
UserId = CurrentUser()


You then need one form for everyone and one report that has it's Record Source =
"SELECT tblMainTable.* " _
& "FROM tblMainTable INNER JOIN tblPrintList " _
& "ON tblMainTable.MainTableId = tblPrintList.MainTableRef " _
& "WHERE UserId = '" & CurrentUser() & "'"



'ope-that-'elps.

G LS

 

[xaos]

Thanks. I used a variation on what you posted. couldn't use those two as primary keys as primary keys cannot be empty and wehn the users cleared their selections it would empty them. but it worked without those as keys nonetheless. just created the form, edited my report queries, created the delete queries, and voila! worked like a charm. Thanks again for the direction.

 

[LittleSmudge]

Okay xaos - one correction to your design.

DO NOT "Clear" the user's selections - DELETE Them.

Otherwise the table will eventually fill up with thousands of empty records.

& by deleting, you get rid of the problem that means you can't set the two fields as Primary Keys.

For data integrity and reliability reasons you DO need to do this.


G LS

 

[xaos]

Yeha you know, and don't I feel stupid! i realized early on that clearing them wasn't the answer cause of the sheer number of records that would accumulate, so i Created the delete queries to kill off the old records, but wasn't thinking when i posted about the primary keys.

 

[BigBlock468]

I have a question concerning checkboxes. I have a table with instances of the same ID_number and I want the checkbox to signify only one of the ID_number's as select.

I dont/cant use group checkboxes because there could be hundreds of instance of a ID_number.

GOAL 1: only one checkbox can be true for a specific ID-number.

Can you guys help me with this one.