Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

Form Basics

How to open new form to same record as old form by chdavisjr
Posted: 6 Jun 02 (Edited 9 Jul 02)

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:

   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:

   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.

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.OpenForm stDocName, , , , acFormEdit, , strSSN

'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>...

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close