Open form to a certain record, if not create a record

[Locopete99]

Hi Guys,

I have two tables that are related by a field [Blanket Agreement Number].

What I'm looking for, if possible, is as follows.

I have Form 1 for Table 1 which is a "Register." Information about [Blanket Agreement Number] is held here.

I want to then open Form 2 for table 2 where [Blanket Agreement Number] is the same. This is easy enough.

However, if a record doesn't exist in Table 2 for [Blanket Agreement Number], I want it to then create a record on Form 2 where [Blanket Agreement Number] is the same as the one that is currently open on Form 1.

Is there a way to do this and can you help?

 

[Ranman256]

in a form, in the header is a text box, txtFind, where user can enter agreement,
if it exists, the records open frmAgree on that 1 agreement.
if not , it can create a new one.

sub txtFind_Afterupdate()
dim vID

vID = Dlookup("[id]","tRegister","[Blanket Agreement Number]='" & txtFind & "'")
if isNull(vID) then
      'enter new data
   docmd.OpenForm "frmAgree",,,,acFormAdd 
else
     'edit existing record
   docmd.openForm "frmAgree",,,"[id]=" & vID
endif

 

[Locopete99]

Hi Ranman,

Your method wasn't working for me, and I didn't want to give another text box to open, so I have used the below

 DoCmd.OpenForm "Frm_Reports", acNormal, "", "[Blanket Agreement Number1]=" & [Blanket Agreement Number], , acNormal
    On Error Resume Next
    DoCmd.OpenForm "Frm_Reports", acNormal, "", "", acAdd, acNormal

This is working, but I when I raise a new form, I want Blanket Agreement Number1 to Autofill with the Blanket agreement number from the previous form.

I have tried the following, but this isn't working. Can someone error check and advise?

Private Sub Form_Load()
If Me.OpenArgs <> vbNullString Then

Forms!Frm_reports![Blanket_Agreement_number1] = Me.[Blanket_Agreement_Number]

End If

End Sub