How to open new form to same record as old form 2

[SMHSleepy]

Hello, I found the following code to do this in the faq section. I tried contacting the original poster but didn't get a response, so I thought I'd ask you all.

This should work in theory but instead of finding the matching record on Form2 and showing it, it always tries to create a new record with the matching fields filled in. In my case, the SSn field is the primary key so it won't allow duplicates, and thus I get an error. Anyone have a fix? Thanks.

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

'Open form2, goto the matching SSN field, and set the
'focus to it. NOTE: the strSSn 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, , strSSN
Forms!form2!mainSSN.SetFocus

'Assign form2's mainSSN to a temp variable
strmainSSN = Forms!form2!mainSSN

'Find the first record in table2 (form2) that matches
'the SSN
DoCmd.FindRecord strSSN, , 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 strmainSSN <> strSSN Then
DoCmd.GoToRecord , , acNewRec
Forms!form2!FName = strFname
Forms!form2!LName = strLName
Forms!form2!mainSSN = strSSN
End If
End Sub

 

[dhookom]

Why don't you just open the form2 with a where condition that matches the SSN? I would generally just use DCount() on the table and if necessary add a record. I'm not sure what else the form might be used for.

I would try something like:

Private Sub openForm2_Click()
  Dim stDocName As String
  Dim newstrSSN, newmainSSN As String
  Dim strSSN As String, strFname As String
  Dim strLName As String, strmainSSN As String
  Dim strWhere 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
  strWhere = "SSN = '" & strSSN & "'"

  'Open form2, goto the matching SSN field, and set the
  'focus to it. NOTE: the strSSn 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, , ,strWhere , acFormEdit, , strSSN
  'Forms!form2!mainSSN.SetFocus

  'Assign form2's mainSSN to a temp variable
  'strmainSSN = Forms!form2!mainSSN
            
  'Find the first record in table2 (form2) that matches 
  'the SSN
  'DoCmd.FindRecord strSSN, , 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 IsNull(Forms!form2!mainSSN) Then
   Forms!form2!FName = strFname
   Forms!form2!LName = strLName
   Forms!form2!mainSSN = strSSN
  End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

SWEET! This code works perfectly, thank you so much dhookom. I think this should replace the other code in the faq section.

 

[SMHSleepy]

One more question on this: I tried modifying this code for another main table. The only difference is that the 'SSN' for Table 1 is the primary key. Now I get a Run-Time error '3464' Data type mismatch in criteria expression. Can you help with this?

 

[dhookom]

Your field might be numeric vs text. You need the basic understanding of the differences between data types.

strWhere = "[NumericField] = " & NumericVariable

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Thank you, that is exactly the problem. The field is numeric this time. How do I correct this though? I don't have to change the field to Text in my table do I?

 

[dhookom]

How do I correct this though?

Isn't that what I suggested in my code? Did you try it?

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Duane gave you the syntax for the field being defined as a Number, but to repeat

strWhere = "[NumericField] = " & NumericVariable

works where NumericVariable is defined as a Number.

strWhere = "[NumericField] = '" & NumericVariable & "'"

is correct when NumericVariable is defined as Text.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SMHSleepy]

Thank you for the clarification, Missinglinq.
Sorry, dhookom, I didn't notice the difference between the code you gave and the one I already had - duh!

All works fine now, you guys are awesome!