Two Forms; One Field (1 Viewer)

Novice1

Registered User
Joined
Mar 9, 2004
Messages
378
I have two forms that have a unique field (SSN).

As I'm working in one form, I want to be able to pull up the other form (which isn't a problem). On the second form pulled up, I want the record to match the first form (SSN) - this is the problem.

I believe SetFocus is what I need to use but I don't know how to capture the SSN from the first form to use as the focus for the second form.

Any help would be appreciated.
 

SOS

Registered Lunatic
Joined
Aug 27, 2008
Messages
3,517
Just open the other form like this:

DoCmd.OpenForm "FormNameHere", WhereCondition:="[SSN]=" & Me.SSN
 

Novice1

Registered User
Joined
Mar 9, 2004
Messages
378
Thanks for responding ... still getting an error: Data type mismatch. Any ideas?
 

Novice1

Registered User
Joined
Mar 9, 2004
Messages
378
Here's where I'm at ... I'm getting the data mismatch. Both are text fields. Not sure what the problem is ...

============

Dim rs As Object
Dim lngBookmark As Long

'set a variable to the current record
lngBookmark = Me.SSN
'open the new form
DoCmd.OpenForm "frmAlphaRoster1"

'take it to the selected record
Set rs = Forms!frmAlphaRoster1.RecordsetClone
rs.FindFirst "SSN = " & lngBookmark
'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
If rs.NoMatch Then 'optional - if no match, go to first record
DoCmd.GoToRecord acForm, "frmAlphaRoster1", acFirst 'optional
Forms!frmAlphaRoster1.txtEmpID = Me.SSN 'optional - copy the employee ID from this form
Else 'optional
Forms!frmAlphaRoster1.Bookmark = rs.Bookmark
End If 'optional
Set rs = Nothing
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,416
Use the code that SOS provided. You don't need to manipulate the forms recordset.

PS -
1. SSN should never be used as a key.
2. SSN should be stored as text rather than long since many start with a leading zero.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top