MS Access, Solution Found:Open new form to same record as old form

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

Author	Message
Chuck Dav #1 / 3	Solution Found:Open new form to same record as old form 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 this really terrific forum webpage: http://www.--*.com/ The problem: ++++++++++++++++++++++++++++++++++++++++++++++++++++ I have 2 tables and 2 forms based on the 2 tables: form1 (linked to table1) and 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) ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ [b]The Solution:[/b] 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 = "" newstrSSN = newstrSSN + Mid(strSSN, 1, 3) + "-" newstrSSN = newstrSSN + Mid(strSSN, 4, 2) + "-" 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 = "" newmainSSN = newmainSSN + Mid(strmainSSN, 1, 3) + "-" newmainSSN = newmainSSN + Mid(strmainSSN, 4, 2) + "-" 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 ++++++++++++++++++++++++++++++ Chuck
Mon, 22 Nov 2004 23:02:00 GMT

Jeff Adam #2 / 3	Solution Found:Open new form to same record as old form form 1 should be a subform of form 2. Then both datasheet and form view will allow users to see the data. But it looks like you found another solution. Remember the order that data should be input. Jeff Adams Quote: >-----Original Message----- >I posted a message several times on different forums and received a lot of >useful hints from several people here. Although each individual suggestion Quote: >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 this really terrific forum webpage: http://www.tek-tips.com/ Quote: >The problem: >++++++++++++++++++++++++++++++++++++++++++++++++++++ >I have 2 tables and 2 forms based on the 2 tables: >form1 (linked to table1) and 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.) Quote: >table2's fields: AutoNumber, ID_table1, Fee, Deposit, BalDue (and many others.) Quote: >table1 may have hundreds of unique records where as table2 has only a few Quote: >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 Quote: >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 Quote: >be so simple! >I need a button on form1 to open form2 to the first record matching table1's ID Quote: >(table2!ID_table1 = table1!ID). >The users want form2 to open (but with form1 also open) on the matching record Quote: >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 Quote: >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: Quote: > 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) >++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >[b]The Solution:[/b] >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!FNam{ w ) ?sE?xE""h B e > strLName = Me!LName >'If the strSSN is of form xxxxxxxxx then change to >' xxx-xx-xxxx > If Len(strSSN) < 11 Then > newstrSSN = "" > newstrSSN = newstrSSN + Mid(strSSN, 1, 3) + "-" > newstrSSN = newstrSSN + Mid(strSSN, 4, 2) + "-" > newstrSSN = newstrSSN + Mid(strSSN, 6, 4)
Tue, 23 Nov 2004 01:25:36 GMT

Chuck Dav #3 / 3	Solution Found:Open new form to same record as old form Thank you, Jeff. Yep, it should be, but the users insisted it not be a subform, and "the customer is always correct" forced me to come up with an alternative! Again, thanks for replying! Quote: > form 1 should be a subform of form 2. Then both datasheet > and form view will allow users to see the data. But it > looks like you found another solution. > Remember the order that data should be input. > Jeff Adams > >-----Original Message----- > >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 this really terrific forum webpage: > http://www.tek-tips.com/ > >The problem: > >++++++++++++++++++++++++++++++++++++++++++++++++++++ > >I have 2 tables and 2 forms based on the 2 tables: > >form1 (linked to table1) and 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) > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > >[b]The Solution:[/b] > >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!FNam??w?) ??E?xE""h ? e > > strLName = Me!LName > >'If the strSSN is of form xxxxxxxxx then change to > >' xxx-xx-xxxx > > If Len(strSSN) < 11 Then > > newstrSSN = "" > > newstrSSN = newstrSSN + Mid(strSSN, 1, 3) + "-" > > newstrSSN = newstrSSN + Mid(strSSN, 4, 2) + "-" > > newstrSSN = newstrSSN + Mid(strSSN, 6, 4)
Sat, 27 Nov 2004 03:40:14 GMT

Page 1 of 1

[ 3 post ]