personalise record selections

[WalterInOz]

I am really struggling with what I expected would not be very difficult. I just don’t get it and need a bit of help/feedback to get me on the right track.
We have stacks and stacks of research papers that need to be organised so that everyone in our team of about 10-12 people can search the database, access the papers electronically and identify the ones that are of particular interest to them.

That shouldn’t be too hard should it? Well, the first bits I found easy, it’s the last bit that I need help with. I currently have a tblRecords with the general info on a paper (title, authors, journal etc). I have a second table with personal details including Logon (username for the network) and a thrird table in which a user can identify a record for the "print list" or "My selection list". This table has a double key (RecordID and Logon).

What I’d like to be able to do is that every user can identify a paper for their own personal list of interest by ticking a Yes/No checkbox on a form with all other details for that paper. That Yes/No selection should than be stored in the 3rd table with RecordID and Logon as combined key so that for each user a unique set of records can be identified.


The username (Logon) is retrieved through Nz(Environ("username").
I have a few questions and would be grateful for advise:
1. Is this a reasonable approach or is there a better way to do this?
2. How do I pass on the values to the middle table?
All data is stored on a server in the back-end.
I have thought about storing the selected values in a separate table in the front-end but that means everyone has to work from the same computer all the time which is not always the case.


Thanks for your advise.

 

[CameronM]

I assume your form is a continuous form showing all the research papers so that you have the RecordID field available to use. You can then have a 'Add to Favourites' button on each record, that when clicked calls some code to add that record into the third table, along with the Logon value.

Private Sub cmdAdd_Click()
 
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strUser As String
 
'set the current users name
strUser = Nz(Environ("username"))
 
'set the SQL to retrieve results from tblMyRecords
strSQL = "SELECT * FROM tblMyRecords WHERE RecordID=" & Me.txtRecordID & " AND Logon ='" & strUser & "'"
 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
'only add if the record does not exist otherwise ignore
If rst.EOF Then
    rst.AddNew
    rst!RecordID = Me.txtRecordID
    rst!Logon = strUser
    rst.Update
End If
 
'close the recordset
rst.Close
Set rst = Nothing
 
End Sub

 

[WalterInOz]

Thank you Cameron.
I indeed have a continuous form and I think I understand what your suggesting. I'll give it a try tomorrow morning when I'm back at the office.

 

[WalterInOz]

it's now almost good Cameron. I get a pop-up box asking me for txtrecordID so I guess it's simply a matter of playing with that a bit in either the VBA or the naming on the form. Unfortunately haven't had time for that today but will trying until it's either working or I give up and come back to this thread.

 

[CameronM]

Yes, I placed a textbox on the form called txtRecordID, with the Control Source set to the RecordID field.