Passing values to get corresponding values in the same table

[Guest]

Hi, I need some help with this request. I am new to access and could use
some help getting the right VBA code for what I am trying to do. I have a
tblMain that has records that are identified by a users full firts and last
name. User log into the database using a login id which is their first
initial and last name.

I have a seperate tblOfficer that has their Full name a login id in the
table. I have a form on which I only want to show them their records based
on the click of a button. It should be that I click a button and the code
underneath should use the CurrentUser function to pass my loginid to the
tblOfficers to return my Name. The codes should then take your name and run
a macro passing your name as the criteria for which records to show.

This way I would only need one macro, versus have one for every use. Please
help I have trying this thing for way to long and im just not getting it.
Any and all help is greatly appreciated!!!

 

[Joan Wild]

You would find it easier to reverse the setup you have. I would put the
loginID in the tblMain (and any other tables where it matters) rather than
the full name. Then you can just use a criteria of CurrentUser() and
retrieve their records. The tblOfficer would contain the loginID and full
name, so that you can look that up if needed.

If you want to continue with what you have, you'd need to change the
recordsource of your form to a query based on tblMain and tblOfficer. Then
you can pull all the fields from tblMain and the loginID from tblOfficer,
with a criteria of CurrentUser() on the loginID.

You don't need a button on your form to do all this. Just make this query
the recordsource for your form.