Solution Found:Open new form to same record as old form 1

[chdavisjr]

I posted a message several times on different forums and
received a lot of useful hints from several people here.
Although each individual suggestion didn't do what I
needed, I finally came up with one that works based on
several. I guess when you have a deadline looking over
you, it helps, huh?
My solution follows:
Thanks to all that replied!
Please let me know if you find this of use!
PS: also try searching this really terrific newsgroup
search site:

http://groups.google.com/

The problem:

I have 2 tables: table1, table2.
I have 2 forms based on the 2 tables:
form1 (linked to table1)
form2 (linked to table2)

Table2 has a foreign field (ID_table1) that is table1's
key (ID), related 1 (table1) to many (table2)based on these
fields.

table1's fields: ID, FName, LName, Street, City, 
         State, Zip (and a few others.)
table2's fields: AutoNumber, ID_table1, Fee, Deposit,
         BalDue (and many others.)

table1 may have hundreds of unique records where as table2
has only a few records, but will grow with use as users
enroll in classes.

NOTE: Making form2 a subform of form1 is NOT an option as
it doesn't do exactly what the users want. (The reason:
they want the option to be able to switch form2's view
between datatable view and form view.) Should the solution
be so simple!

I need a button on form1 to open form2 to the first record
matching table1's ID

(table2!ID_table1 = table1!ID)

.

The users want form2 to open (but with form1 also open) on
the matching record from form1.

They want to be able to use form2's navagation button to
move thru all the records (first to last) from table2.

If the record is not found in form2, then form2 will append
a new record for data-entry matching the record from form1.

The ID_form1 field should be filled in with the calling
form's ID number.

I have this in the "Open form2" button's On Click event
create by the wizard:

   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "Form2"
   DoCmd.OpenForm stDocName, , , stLinkCriteria

but it opens to the first record in form2, not the matching
record.

I tried this:

   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "Form2"
   stLinkCriteria = "[mainSSN]=" & "'" & Me![SSn] & "'"
   DoCmd.OpenForm stDocName, , , stLinkCriteria

But form2 opens with only the 1 filtered record (1 of 1)


The Solution:
Create the new button and add this to the On Click event.

Private Sub openForm2_Click()
 Dim stDocName As String
 Dim newstrSSN, newmainSSN As String
 Dim strSSN, strFname, strLName, strmainSSN As String
 stDocName = "Form2"
    
'Store the calling form's (form1) SSN, FName, and LName
'to add to new record in form2, if needed.
 strSSN = Me!SSn
 strFname = Me!FName
 strLName = Me!LName

'If the strSSN is of form xxxxxxxxx then change to
' xxx-xx-xxxx
 If Len(strSSN) < 11 Then
  newstrSSN = &quot;&quot;
  newstrSSN = newstrSSN + Mid(strSSN, 1, 3) + &quot;-&quot;
  newstrSSN = newstrSSN + Mid(strSSN, 4, 2) + &quot;-&quot;
  newstrSSN = newstrSSN + Mid(strSSN, 6, 4)
 End If
    
'Open form2, goto the matching SSN field, and set the
'focus to it. NOTE: the newstrSSn at the end of the
'following line is the OpenArgs property. It is the 
'SSN I wish to locate in Form2, and is by the
'DoCmd.FindRecord
   DoCmd.OpenForm stDocName, , , , acFormEdit, , newstrSSN
   Forms!form2!mainSSN.SetFocus

'Assign form2's mainSSN to a temp variable
 strmainSSN = Forms!form2!mainSSN
            
'If the stored ssn is of form xxxxxxxxx then change 
'to xxx-xx-xxxx
 If Len(strmainSSN) < 11 Then
  newmainSSN = &quot;&quot;
  newmainSSN = newmainSSN + Mid(strmainSSN, 1, 3) + &quot;-&quot;
  newmainSSN = newmainSSN + Mid(strmainSSN, 4, 2) + &quot;-&quot;
  newmainSSN = newmainSSN + Mid(strmainSSN, 6, 4)
 End If

'Find the first record in table2 (form2) that matches 
'the SSN
 DoCmd.FindRecord newstrSSN, , True, , True, , True
        
'If the SSN's do not match (not found in table2, then 
'this must be a new record so add a new record and 
'populate the listed fields of Form2

 If newmainSSN <> newstrSSN Then
  DoCmd.GoToRecord , , acNewRec
  Forms!form2!FName = strFname
  Forms!form2!LName = strLName
  Forms!form2!mainSSN = strSSN
 End If
        
End Sub

[code]
chdavisjr@aol.com (Chuck Davis) 
wrote in message news:<728c3657.0206050742.fd89d2e@posting.google.com>...
Chuck
cdavis@uaex.edu