Open a form from form

[gg80]

I am opening form B with a button on form A. Form B opens to a record with matching field on form A. If the form B’s source doesn’t have the matching value, then I would like to go to a new record on form B and insert the value. I used the standard “open B where B.fieldName =A.fieldName etc. to open form B.
If B.fieldName doesn’t exist, the B form usually opens to a new record which I detect with “if isnull(B.fieldname), then B.fieldname=A.fieldname.” It is not consistent. Sometimes I get an error message and Form B opens with errors that indicate the source query is not correct (#NAME everywhere) and there is no value in B.fieldname Is this instability my computer or is it caused by lmy procedure? I am using A2007 and W7.

[NTC]

Doubtful an instability issue.

One way to avoid the dilemma you have would be to use a subform approach - rather than a separate form. You could just toggle the visibility of the subform rather than open a separate form.

But if you wish to maintain the separate form approach; your key issue is knowing in advance whether the B form has a record to open to or not - in advance.

You might preceed the opening of the B form with a DLookUp to first determine if there is a record - if so open the form; if not - open the form with an argument to go to a new record. Presuming in this case the user is then going to enter info in at least 1 field you could autocomplete your A data into the B form during that event.

Hope this helps a little.

[ssanfu]

Your process is a little confusing.... Are you changing the record source of Form B each time the button is clicked? (I never have liked doing that)

Try this:

On FormA, add a button with this code in the click event:

Private Sub OpenFormB_Click()
   On Error GoTo Err_OpenFormB_Click
   Dim stDocName As String
   Dim stLinkCriteria As String
   Dim strOpenArgs As String
 
   stDocName = "FormB"
 
   'change [EmpId] and [ID] to your field names
   ' [EmpID] is in FormB
   '[ID} is in FormA
   stLinkCriteria = "[EmpID]= " & Me![ID]
   strOpenArgs = Me![ID]
 
   DoCmd.OpenForm stDocName, , , stLinkCriteria, , , strOpenArgs
 
Exit_OpenFormB_Click:
   Exit Sub
Err_OpenFormB_Click:
   MsgBox Err.Description
   Resume Exit_OpenFormB_Click
End Sub

In FormB, put this code in the load event:

Private Sub Form_Load()
   Dim tmp As Boolean
   tmp = Me.NewRecord
   If tmp Then
      'change [EmpID] to your field name
      Me.EmpID = Me.OpenArgs
   End If
 
End Sub

When the button on FormA is clicked, FormB is opened with a filter. If there are no matching records, the current record will be a new record. The Load event checks if it is at the new record and adds the open argument data to the matching field.

[gg80]

Thanks-I tried it and works, . The forms are just to have too much going on to be on screen at same time. I am also going to try the other solution proposed. This one I understand a little better-very direct..

[gg80]

Thanks very much. This works. However, I think that this is what I was doing before (with much less efficiency.) i.e. open form with filter, then, if the target field is null in form B, indicating new record, insert the value from form A. It worked most of the time but occasionaly blew up.

Your code works whether or not I have the on load event code for form B. Does the on-load code help stabilize things? I haven't use the OpenArgs property before-does this help stabilize? I obviously need to do more reading on code. Thanks again.