Open Form New/Existing Record 2

[sucoyant]

Good day!

I have a main form and I need to open a second form by clicking on a button in the main form.
Sounds easy, but here is the catch...

When the user click the button, I need to check to see if that account number already exists, and if it does, open the second form to that record. Otherwise if the account number does not exist, we will need to create a new record and place that account number in the correct field in the second form.




________________________________________

Buddha. Dharma. Sangha.

 

[TheAceMan1]

How are ya sucoyant . . . .

I pretty much understand what your looking for. Without specifics, below is an example of what will do the job, or at least come close to it. If you have any questions let me know . . .

Private Sub YourButtonName_Click()
   Dim db As DAO.Database, rst As DAO.Recordset, frm As Form
   
   DoCmd.OpenForm "Form2Name"
   Set frm = Forms!Form2Name
   Set db = CurrentDb()
   Set rst = frm.RecordsetClone
   rst.FindFirst "AcctNum = " & Me!AcctNum
   
   If rst.NoMatch Then
      rst.AddNew
      rst!AcctNum = Me!AcctNum
      rst.Update
      frm.Requery
      Set rst = frm.RecordsetClone
      rst.FindFirst "AcctNum = " & Me!AcctNum
      Me.Bookmark = rst.Bookmark
   Else
      Me.Bookmark = rst.Bookmark
   End If
   
   Set rst = Nothing
   Set db = Nothing
   Set frm = Nothing
   
End Sub

TheAceMan

 

[Rolliee]

As long as I coded this, I will post it. Looks like the AceMan got there first.

rollie@ bwsys.net

Private Sub cmdChk_Click()
Dim rs As DAO.Recordset, okay As Boolean
Set rs = Me.RecordsetClone
okay = False
If rs.EOF And rs.BOF Then
rs.AddNew
rs.Fields(1) = Me.txtNew1
rs.Close
Set rs = Nothing
End If
rs.MoveLast: rs.MoveFirst
Do While Not rs.EOF
If rs.Fields(1) = Me.txtNew1 Then
okay = True
Me.Bookmark = rs.Bookmark
Exit Do
End If
Loop
rs.Close
Set rs = Nothing

End Sub

 

[sucoyant]

Thank you for the replies!
I'm terribly sorry, I'm not sure if this will matter, but each forms record source is different.

Form1's recordsource is Table1
Form2's recordsource is Table2

I think the code that has been provided is close... I would really appreciate it if we could nail this thing. We are just about there.... thanks to all of the wonderful people who have replied!

________________________________________

Buddha. Dharma. Sangha.

 

[Rolliee]

sucoyant,

Just open the other recordset like this -

Private Sub cmdChk_Click()
Dim rs As DAO.Recordset, okay As Boolean
DIM r2 as DAO.recordset
Set rs = Me.RecordsetClone
set rs = currentdb.openrecordset("SecondTable")
okay = False
If rs.EOF And rs.BOF Then
r2.AddNew
rs2Fields(1) = Me.txtNew1
r2.Close
Set r2 = Nothing
End If
r2.MoveLast: r2.MoveFirst
Do While Not r2.EOF
If r2.Fields(1) = Me.txtNew1 Then
okay = True
Me.Bookmark = rs.Bookmark
Exit Do
End If
r2.movenext
Loop
rs.Close
Set rs = Nothing

End Sub

It will not be quite like this, not knowing the sources and targets of data. This should help though.

Rollie E

 

[sucoyant]

Rolliee: I'm a bit confused. Where exactly does the code go to open the new form?

When a user clicks on a button in Form1(Whos recordsource is Table1), Form2(Whos recordsource is Table2) needs to open, and search for a match in the AcctNum field (from Form1/Table1), which is in both tables and a text field on both forms. If there is a match within both tables, the new form will display all of the Table2's data. Otherwise, if there is no match, the data in the AcctNum field on Form1 needs to be transfered to Form2, and create a new record.

Hope this helps..

Thanks in advance!

________________________________________

Buddha. Dharma. Sangha.

 

[Rolliee]

The cmd button named say, 'checkr' opens r1 as a clone of the form1. It also opens r2 as a recordset for table2 and if it finds the entry, perhaps shows the data tho it need not. Then if the data is not there, do the r2.addnew and enter the 'me.' fields from form1. If form1 is not the current form, you have to use the longer protocol:

forms![Form1]![FieldName1] etc

Does that answer?

rollie@bwsys.net

 

[sucoyant]

Thanks to everyones help I was able to find a solution!

Here is the code:

Dim rs As DAO.Recordset
Set rs = Forms![Real Estate Secured Lines of Credit Equiline].RecordsetClone

whatToLookFor = Forms![Customer/Checkbacks]![txtAcct]
criterion = "[Acct #]=" & Chr(34) & whatToLookFor & Chr(34)

With rs
    .MoveLast
    .FindFirst criterion

    If .NoMatch Then
        .AddNew
        ![Acct #] = whatToLookFor
        .Update
        .Bookmark = .LastModified
        Forms![Real Estate Secured Lines of Credit Equiline].Bookmark = rs.Bookmark
    Else
        Forms![Real Estate Secured Lines of Credit Equiline].Bookmark = rs.Bookmark
    End If
End With

I placed this in the "On Load" evend of the called form. The calling form just has a button that opens the "called" form.

________________________________________

Buddha. Dharma. Sangha.

 

[Rai12]

I am designing a button which takes you to the new record so that the user can enter the new data. The only problem is if I enter the new company name in the form and i click the button to enter the record the new company name is not displayed in the combox box. How can i requery the combobox. THE MAIN PURPOSE OF THIS IS IF COMPANY NAME DOESN'T EXIST IN THE RECORD THAN A PERSON CAN ENTER THE NEW COMPANY NAME AND IF COMPNAY DOES EXISTA USER DOESN'T HAVE TO ENTER IT. THE USER CAN SELECT FROM THE COMBOBOX.

Any help will be appreciated.

Thanks
Rishabh