Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to open new form to same record as old form

Form Basics

How to open new form to same record as old form

by  chdavisjr  Posted    (Edited  )
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:
<<You must install Microsoft DAO 3.6 Object Library:
Open the database;
Open Microsoft Visual Basic (Press Alt-F11);
Click Tools;
Select References...;
scroll and select Microsoft DAO 3.6 Object Library;
click OK to save.>>
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 tried this in the "Open form2" button's On Click event
create by the wizard:
Code: 
   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "Form2"
   DoCmd.OpenForm stDocName, , , stLinkCriteria
but it opened to the first record in form2, not the
matching record.

I tried this:
Code: 
   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "Form2"
   stLinkCriteria = "[mainSSN]=" & "'" & Me![SSn] & "'"
   DoCmd.OpenForm stDocName, , , stLinkCriteria
But formed opens with only the 1 filtered record (1 of 1)


The Solution:
Create the new button and add this to the On Click event.
Code: 
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

chdavisjr@aol.com (Chuck Davis)
wrote in message news:<728c3657.0206050742.fd89d2e@posting.google.com>...
Chuck
cdavis@uaex.edu
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top