general set-up & VBA: insert variables into a table

[WalterInOz]

I have a database which holds records of publications in journals, newspapers or other media. A minority of these publications are of interest to everyone in the company. Most are of interest to smaller audiences, i.e. groups of one or four individuals. I would be surprised if the total number of users will ever be more than 30. The number of publications will be in the thousands.

the database is not very user friendly, it needs an additional feature. What I would like to achieve is to add an individual component to the database so that a person can identify records that are of special interest to him/her. This would save a lot of scrolling through records and make the publication of interest much more accessible and user friendly to each individual.

The database is split in front-end and back-end. In general, people work from the same PC all the time but there are exceptions. If possible I’d like to cater for the exceptions too, if too hard the exceptions will have to restrict activities in relation to database work to their own PC.

One way I thought this could be approached is to have a table with a field Username (based on login of the user), a field with a publicationID and a field to identify if the user has selected if that publication is ‘of interest to me’ (checkbox).

Straight forward enough? Or are there better ways to do this?

If this set-up is OK I still have a problem. This set-up requires inserting the username and the publication number into the table when the user checks the box ‘of interest to me’. This checkbox is located on a continuous form showing all records. I suspect the table tblSelectedRecords must have a double key (Username and PublicationID). Correct? If this is a viable set-up, could someone give me a leg-up what the code would look like in the after-update event of the checkbox?

thanks

 

[pbaldy]

What you are describing is a typical many-to-many junction table. As you suspect, it would have fields for the user and publication. Some would make those 2 fields a compound key, others would have a separate autonumber field as the primary key.

 

[WalterInOz]

Thank you for you reply, I've continued down that path.

I have created a button with the following code behind it:

Private Sub cmdAddToMyList_Click()
With CurrentDb.OpenRecordset("tblSelect_MY_options")
     .AddNew
        !Loginnaam = Me.Loginnaam
        !RecordID = Me.txtRecordID
        !MyList.Value = -1
     .Update
End With
Me.Requery   
End Sub

This works fine, record is selected and each user can now query for his/her own selected records. Great.

But what if users later on decides this publication should be removed from their personal list? I have no idea how to delete a record form the junction table. I can't do a simple delete as that would remove the record entirely. i just want to remove the entry for that particular user and record removed. Like the code above, but in reverse.

Anyone please?

 

[pbaldy]

How are you presenting the data to the user? I would simply delete that record. I'm not sure what your concern is with deleting a record entirely, as that would be the normal course of action here.

 

[WalterInOz]

I'm not sure what your concern is with deleting a record entirely, as that would be the normal course of action here.

I can't delete the entire record. Remember there are multiple users, each of which should be able to individually see all records at all time. The selection option for each individual is just to make finding your particular interest more easy. If someone deletes an entire record it would be deleted from the entire database and that wouldn't be good at all.
The only data I want removed is from the junction table.

Or do I perhaps misunderstand you?

 

[pbaldy]

I think so, yes. I'm talking about deleting the record from the junction table.

 

[WalterInOz]

Yes, that's exactly what I'd like to do but I don't find it that simple to do.
I am looking for the exact opposite of the AddNew action used to create the record in junction table. The standard Delete button won't cut it.

 

[pbaldy]

That's why I asked how you're presenting the data to the user. If on a form bound to that table, it's simple. If not, it's not much harder; a delete query that uses the appropriate values in its criteria so it only deletes the appropriate record.

 

[WalterInOz]

The data are presented in several ways, the one that presents difficulties is the Continuous Form showing ALL records. That's the form most appropriate to view a summary of the record and decide if you want to add it or delete a selected record from the personal list.

I now have it working but not to my satisfaction.
Once the record is created in the junction table and users can select the record for 1 or 2 categories of personal lists it's there forever. If a user deselects a checkbox the record will remain with checkbox value 0. In a way that's OK, however, with eg 20 users and 1000 records you potentially end up with a table of 20,000 records, most if which may have a unchecked checkbox. It'd be much better if the entire record can be deleted and keep the number of records in the junction table under control.

A delete query sounds good. I've never used one, in fact I hesitate a little bit 'Delete record' is a bit of scary thing to me. Once it's gone it's gone. but I'm sure it's OK, just not familiar with them. I'll have a read and see where it gets me. Any tips for where to start?

 

[WalterInOz]

As already said above the solution I have found wasn't satisfactory. In fact, it doesn't do what I need it it to do. After reading up a bit on delete queries as suggested I see this is the way to go.

I've build one that does exactly what is needed, but only when I run it from the query builder. When I copy the code behind a button I get an error, the last line of code is red. i've experimented with adding or deleting " and ) but cannot get it right.

Could someone please have a look and correct it for me?

Private Sub cmdDeleteFromList_Click()

Delete tblSelect_MY_options.Loginname, tblSelect_MY_options.RecordID, tblSelect_MY_options.SelectID, tblSelect_MY_options.MyList, tblSelect_MY_options.PrintList, tblSelect_MY_options.PersonalNote
FROM tblSelect_MY_options
WHERE (((tblSelect_MY_options.Loginname)=[Forms].[frmRecords].[txtLoginnaam]) AND ((tblSelect_MY_options.RecordID)=[Forms].[frmRecords].[txtRecordID]) AND ((tblSelect_MY_options.SelectID) Like "*") AND ((tblSelect_MY_options.MyList) Like "*") AND ((tblSelect_MY_options.PrintList) Like "*") AND ((tblSelect_MY_options.PersonalNote) Like "*"));

End Sub

 

[pbaldy]

You can't just put SQL in code like that. The simplest way for you would be to keep that as a saved query, then run it:

DoCmd.OpenQuery "QueryNameHere"

To do it in code would take a little more work, as it would look like:

CurrentDb.Execute "DELETE ..."

but would also require the form references to be concatenated into the string:

"...WHERE Loginname=" & [Forms].[frmRecords].[txtLoginnaam] & " AND ..."

Since there's nothing dynamic about the query, executing the saved query would be more efficient.

 

[WalterInOz]

Excellent!! You've been a big help Paul. Thank you.