Passing Parameters with Autonumber

[imrosie]

Hello All,

(newbie)...I'm working on an Ordering app using the typical tables (customers, orders). I have a Search form to find existing customers prior to entering a new order. On the search form there's a combo control called 'custname' so that when a customer is located, the existing customer name is filled in and the account number goes into the Account control (from an autonumber field called CustomerID from Customer table). The rest of the data for street, city, etc go into the associated controls. So far no prob, I can click the cmd button (to begin new order), open the 'Order' form and pass the control data just fine, to the Order form.

On 'Search' form there's a 'NotInList' event with the 'custname' control, it opens another form called [Add or Delete Customer] by a double-click event. No problem there because on the the AutoNumber gets assigned to a new customer name and the rest of the data associated with the customer get stored appropriately.

On the Search form the problem comes in when I click the 'Edit Customer' cmd button to change information on an existing customer after a customer is located. the 'Edit Customer' button also opens the [Add or Delete Customer] form and passs the controls data. But something really wierd happens..... The CustomerID Account number passes into Customer Name control (on [Add or Delete Customer] form and the the account number (which is bound to CustomerID Autonumber) complains about duplicates. I suspect because the control is bound to CustomerID, which was fine when adding a new customer, but not for editing.

I have an 'AfterUpdate' event (recordset) associated as well with 'custname' but I don't think it's the problem.

I've been trying to figure this out for days and can't get it worked out. If anyone has encountered this before, please offer some suggestions,,,, ,thanks in advance,,,,,,I' m over my head. Here's the code:

Private Sub custname_DblClick(Cancel As Integer)
Me.[custname] = ""
DoCmd.OpenForm "Add or Delete Customer"
End Sub
 

Private Sub custname_NotInList(NewData As String, Response As Integer)
MsgBox "The Name you entered is not found" & _
vbCrLf & "Double Click to add a new customer", _
vbInformation, "The name is not found"
Response = DataErrCont
End Sub
 

Private Sub custname_AfterUpdate()
Dim rst As Object
 
Set rst = Me.RecordsetClone
 
rst.FindFirst "Customers.[CustomerID]=" & Me![custname]
If Not rst.NoMatch Then
 
  Me.Bookmark = rst.Bookmark
Else
  'Not found!
End If
 
End Sub
 

This is code used for the 'Edit Customer' button

Private Sub createcust_Click()
DoCmd.OpenForm "Add or Delete Customer"
'The event to open the Order form with some controls filled in
Forms![Add or Delete Customer]![fullcustomer].Value = Forms![Search a Customer]![custname]
Forms![Add or Delete Customer]![companies].Value = Forms![Search a Customer]![compname]
Forms![Add or Delete Customer]![BillingAddress].Value = Forms![Search a Customer]![abilladdress]
Forms![Add or Delete Customer]![newstateorprov].Value = Forms![Search a Customer]![astateOrprovince]
Forms![Add or Delete Customer]![City].Value = Forms![Search a Customer]![City]
Forms![Add or Delete Customer]![Title].Value = Forms![Search a Customer]![thetitle]
Forms![Add or Delete Customer]![thezippostal].Value = Forms![Search a Customer]![ZIPCode]
Forms![Add or Delete Customer]![acountry].Value = Forms![Search a Customer]![acountry]
DoCmd.Close acForm, "Search a Customer", acSaveNo
End Sub
 

Private Sub BeginOrder_Click()
DoCmd.OpenForm "Add an Order and Details"
'The event to open the Order form with some controls filled in
Forms![Add an Order and Details]![thefullname].Value = Forms![Search a Customer]![FullName]
Forms![Add an Order and Details]![TheCompany].Value = Forms![Search a Customer]![compname]
Forms![Add an Order and Details]![custacct].Value = Forms![Search a Customer]![custaccts]
Forms![Add an Order and Details]![ShipAddress].Value = Forms![Search a Customer]![ShipAddress]
Forms![Add an Order and Details]![thecountry].Value = Forms![Search a Customer]![acountry]
Forms![Add an Order and Details]![ShipCity].Value = Forms![Search a Customer]![ShipCity]
Forms![Add an Order and Details]![BillingAddress].Value = Forms![Search a Customer]![BillingAddress]
Forms![Add an Order and Details]![ShipStateOrProvince].Value = Forms![Search a Customer]![ShipStateOrProvince]
Forms![Add an Order and Details]![ShipZIPCode].Value = Forms![Search a Customer]![ShipZIPCode]
DoCmd.Close acForm, "Search a Customer", acSaveNo
End Sub
 

So sorry to bombard with information ,but I thought it might clarify what's going on. thanks.

Rosie,,,,,,I've got to get some sleep..thanks

[MMcCarthy]

What is the record source of the "Add or Delete Customer" form?

[imrosie]

What is the record source of the "Add or Delete Customer" form?

Hi Mccarthy,

The record source is 'qrySearchCusto mer' based off of the Customers table, which has all the fields required. thanks.

Rosie

[MMcCarthy]

Try this ...

Private Sub createcust_Click()
'createcust is not a great name as this is for editing a customer already in existance
Dim stDocName As String
Dim stLinkCriteria As String
 
    stDocName = "Add or Delete Customer"
    stLinkCriteria = "[CustomerID] = " &  Me.custname
    ' This will only work if the CustomerID is the bound control on the custname combobox
    DoCmd.Open Form stDocName, , , stLinkCriteria
 
End Sub
 

[imrosie]

Try this ...

Private Sub createcust_Click()
'createcust is not a great name as this is for editing a customer already in existance
Dim stDocName As String
Dim stLinkCriteria As String
 
    stDocName = "Add or Delete Customer"
    stLinkCriteria = "[CustomerID] = " &  Me.custname
    ' This will only work if the CustomerID is the bound control on the custname combobox
    DoCmd.Open Form stDocName, , , stLinkCriteria
 
End Sub
 

Thanks so much....it worked beautifully. take care
Rosie