2 forms, Same record

[driver7408]

BORING PART:
First off, I wanna say thanks to the help I have had in the past. I heard that my first database is still in full use without a hitch, and has just passed its 2 year anniversary. This forum is a godsend, and without you guys' help it would not have been so successful.

I am still lacking at VBA. I plan to work with it more in my spare time, but currently, most of the macros do what I need. My databases rely more on clever, user friendly form creation than anything.

ISSUE:
I am currently building a database for personnel that will require 65 records (personnel) max. I am using Social Security Numbers as the PK. I made a popup form that lets them select their name from a combo box in order to login to the database. In this form, after selecting their name from the CB, they logging input their SSN(password), the recordset acknowledges their permissions based on pre-designated controls. Some are admin and will have admin access after logging in, so they will be taken to the appropriate form. This is all done within the login form.

My issue is that I need a way after they log in, for the new form to go to their (the same) record, while the log in form closes. This code has to fire when the new form opens. I want to open that new form, and that new form be the object that looks at the old form, and opens the recordset based on the SSN(PK) that they entered on the log in form. I want to open a new form that will go to the same record from the last form, but I want the code to fire on the form that opens, rather than the first form. I have scoured the internet and only found one item, and the VBA for that wasn't even close.

Does this make any sense? lol...

 

[boblarson]

I don't know where you are located but using SSN's as a PK is not really good. In fact, storing them in an Access database is not good as it is not secure and anyone who can get to that file can take it with them and have everyone's Social Security Number. If you are going to do this, you should really store the data in SQL Server or SQL Server Express, so you can have some controls set around them and someone can't just walk away with them.

I'll answer your question as soon as I can decipher exactly what it is saying (it's kind of confusing).

 

[boblarson]

If I deciphered it, you would use OpenArgs to accomplish it. But I think you should just do it this way:

DoCmd.OpenForm "FormNameHere", acNormal, , "[SSN]=" & Chr(34) & Me.SSN & Chr(34)

(that's assuming SSN is text)

and that way it will only open to that record and not be able to be navigated to any others.

So no code needed on the second form.


If you need to have it on the second form then something like thisl

DoCmd.OpenForm "FormNameHere", acNormal, OpenArgs:=Me.SSN

And in the On Load event of the new form

Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Set db = CurrentDb
 
Set rst = Me.RecordsetClone
 
rst.FindFirst "[SSN]=" & Chr(34) & Me.OpenArgs & Chr(34)
If rst.NoMatch Then
   Msgbox "No record exists for that SSN"
Else
   Me.Bookmark = rst.Bookmark
End if

 

[driver7408]

Thanks; I will do some research on OpenArgs. I work in a trusted organization where SSN's are the only common way people are identified; otherwise I wouldn't use them.

 

[Pat Hartman]

Your organization needs a culture change. SSNs should never be used as the PK to anything. You can add a unique index on them, you can search on them. That doesn't mean they have to be the PK.

 

[Isskint]

On a side note, if you are in the UK (or EU i guess), check up on Data Protection Act 1998. The storage, on paper or electronically, of any piece of data that is unique to an individual, is either prohibited or requires the persons permission and the most robust security to prevent data theft.