Open Form from On Click event to add new record

[CliffACS]

Forgive me if this isn't detailed enough but I have a Suppliers form (Suppliers) in MS Access 2000 with an unbound combo box (SupplierNameCbo) that allows the user to select a supplier name. When a selection is made, several text boxes on the form get filled in with the related table data using the control source method of "=SupplierNameCbo.Column(0), etc.." Thus, when a user selects a supplier in the combo box, then the SupplierID textbox on this form shows the corresponding supplier's number.

I also have a command button called Add Products on this form. I want to use the OnClick event of this button to open up another form called "Add Items" in Add mode only. However, I would like the SupplierID field on the Add Items form to have the supplier number filled in with the SupplierID number produced by the selection made from the Suppliers form. I tried the code below found on this forum and, unfortunately, it gives me the 'Object doesn't support this property or method' error message. Does anyone have some suggestions to fix this? Thanks!

Private Sub Add_Products_Click()
On Error GoTo Err_Add_Products_Click

Dim strDocName As String

strDocName = "Add Items"
'Open Add Items form in Add mode and store SupplierID in
'the form's OpenArgs property.
DoCmd.OpenForm strDocName, , , , acFormAdd, , Me!SupplierID

'Give SupplierPartNo control focus.
Forms![Add Items]!SupplierPartNo.SetFocus

Exit_Add_Products_Click:
Exit Sub

Err_Add_Products_Click:
MsgBox Err.Description
Resume Exit_Add_Products_Click

End Sub

 

[Kryst51]

I'm not sure, but I don't think it can do that on click, because it's add new, the ID hasn't been assigned yet until the form opens and some information is entered. I am pretty sure it can be done on the ID update box, or on the "New Product" form close event.

 

[CEH]

Your actually just opening a linked form.........

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add Items"

stLinkCriteria = "[SupplierID]=" & Me![SupplierID]
DoCmd.OpenForm stDocName, acAddForm, , stLinkCriteria

Try something like that....... should work.

 

[CliffACS]

Thanks Curtis. I was able to get that part to work without the Object error message, however, now when my Add Items form opens up, it doesn't fill in the SupplierName field. I've tried the following code on the After Update event for the SupplierPartNo field on my Add Items form (this is an identical design to the Northwinds ProductName field event procedure in the Northwinds database) and it doesn't autofill in the Supplier Name after entering data in the SupplierPartNo field.

Private Sub SupplierPartNo_AfterUpdate()
'If OpenArgs property isn't null, set SupplierID to value of form's OpenArgs
'property. OpenArgs will have a value if AddItems form is opened by clicking
'Add Products command button on Suppliers form.
If IsNull(Forms!AddItems.OpenArgs) Then
Exit Sub
Else
Me!SupplierID = Forms!AddItems.OpenArgs
End If

End Sub

Unless I manually select the supplier name then it won't let me add new records. Any ideas?

 

[CEH]

I believe the code needs to be on the Onopen event of your AddItems form.