1

I have a form in an MS Access database which lists Orders with an Order Number with one order per page. At the bottom of the form there is a button which opens another form, to add an item for the order.

I am trying to use vb in MS Access to take the order number and automatically put it in a field in the details form for the new item. I have tried different ways but using OpenArgs seems to be recommended. But the detail form won't open and I get run-time errors. Here are the details of the problem - advice will be much appreciated:

The forms and field concerned are: Form with orders is frmPedidoAvifiFind Form with order-lines for one order is frmPedidoAvifi-dtlAdd (a separate form for adding details but not for viewing existing ones). Field on both forms for Order Number is PedidoAvifiNo. This is a numeric field in both tables which are linked by a one-to-many relation via this field.

Main form: Button bring up detail form, code as follows:

Code on main form button:

Sub AddDetails_Click()
  Dim strDocName As String

    strDocName = "frmPedidoAvifi-dtlAdd"

    ' Open frmPedidoAvifi-dtl form in data entry mode and store PedidoAvifiNo in  the form's OpenArgs property.

    DoCmd.OpenForm strDocName, , , , acFormAdd, , [frmPedidoAvifiFind]![PedidoAvifiNo]

End Sub

Detail form: On Open property 

Private Sub Form_Open()
If Me.OpenArgs <> vbNullString Then
   Me.PedidoAvifiNo = Me.OpenArgs
End If

End Sub

Test 1: select an order number on main form so that record shows. Press button to add orderline. - run-time error '2465' can't find the field "|" referred to. Debug highlights the DoCmd line.

Test 2: Change openform line to: DoCmd.OpenForm strDocName, , , , acFormAdd, , Me.PedidoAvifiNo result: - run-time error 2501 the openForm action was canceled.

Thank you, Mike Gunner Reus, Spain

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
user566577
  • 11
  • 1
  • 2
  • 2
    Subforms are a great way to handle this use case in MS Access. – mwolfe02 Jan 07 '11 at 14:38
  • Subforms are great, but not so great for adding records when you need a lot of control. – Fionnuala Jan 07 '11 at 16:18
  • @Remou: I'd want to see an explanation first of why a subform won't work before mucking about with a popup form. – David-W-Fenton Jan 08 '11 at 03:42
  • @David-W-Fenton I did not say it would not work, just that a subform is not always the best way to add data when you need a lot of control. I am all for subforms, but I do not always use them to add records. – Fionnuala Jan 08 '11 at 15:05
  • I'm just saying that in the case of master/child data relationships, a subform should be the first UI implementation that springs to mind, and you'd only go to a different approach (whether a popup, or a pair of linked list/detail subforms) if that didn't work. In other words, start with the way that Access makes easy, and complicate things only when the easy way isn't fully adequate (or becomes overcomplex). – David-W-Fenton Jan 09 '11 at 23:34

1 Answers1

1

The error you are getting means that you have misspelled the name of the control PedidoAvifiNo.

When you type Me. intellisense will give you a list of available fields, see what you have that is similar to PedidoAvifiNo, or check the properties. It can be very easy to switch one letter and not notice.

As for the second part, you should use the Load event, rather than the Open event on frmPedidoAvifi-dtlAdd, because the controls are not yet available in the Open event.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152