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