By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,225 Members | 2,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,225 IT Pros & Developers. It's quick & easy.

Passing Parameters with Autonumber

imrosie
100+
P: 222
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub custname_DblClick(Cancel As Integer)
  2. Me.[custname] = ""
  3. DoCmd.OpenForm "Add or Delete Customer"
  4. End Sub
  5.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub custname_NotInList(NewData As String, Response As Integer)
  2. MsgBox "The Name you entered is not found" & _
  3. vbCrLf & "Double Click to add a new customer", _
  4. vbInformation, "The name is not found"
  5. Response = DataErrCont
  6. End Sub
  7.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub custname_AfterUpdate()
  2. Dim rst As Object
  3.  
  4. Set rst = Me.RecordsetClone
  5.  
  6. rst.FindFirst "Customers.[CustomerID]=" & Me![custname]
  7. If Not rst.NoMatch Then
  8.  
  9.   Me.Bookmark = rst.Bookmark
  10. Else
  11.   'Not found!
  12. End If
  13.  
  14. End Sub
  15.  
This is code used for the 'Edit Customer' button
Expand|Select|Wrap|Line Numbers
  1. Private Sub createcust_Click()
  2. DoCmd.OpenForm "Add or Delete Customer"
  3. 'The event to open the Order form with some controls filled in
  4. Forms![Add or Delete Customer]![fullcustomer].Value = Forms![Search a Customer]![custname]
  5. Forms![Add or Delete Customer]![companies].Value = Forms![Search a Customer]![compname]
  6. Forms![Add or Delete Customer]![BillingAddress].Value = Forms![Search a Customer]![abilladdress]
  7. Forms![Add or Delete Customer]![newstateorprov].Value = Forms![Search a Customer]![astateOrprovince]
  8. Forms![Add or Delete Customer]![City].Value = Forms![Search a Customer]![City]
  9. Forms![Add or Delete Customer]![Title].Value = Forms![Search a Customer]![thetitle]
  10. Forms![Add or Delete Customer]![thezippostal].Value = Forms![Search a Customer]![ZIPCode]
  11. Forms![Add or Delete Customer]![acountry].Value = Forms![Search a Customer]![acountry]
  12. DoCmd.Close acForm, "Search a Customer", acSaveNo
  13. End Sub
  14.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub BeginOrder_Click()
  2. DoCmd.OpenForm "Add an Order and Details"
  3. 'The event to open the Order form with some controls filled in
  4. Forms![Add an Order and Details]![thefullname].Value = Forms![Search a Customer]![FullName]
  5. Forms![Add an Order and Details]![TheCompany].Value = Forms![Search a Customer]![compname]
  6. Forms![Add an Order and Details]![custacct].Value = Forms![Search a Customer]![custaccts]
  7. Forms![Add an Order and Details]![ShipAddress].Value = Forms![Search a Customer]![ShipAddress]
  8. Forms![Add an Order and Details]![thecountry].Value = Forms![Search a Customer]![acountry]
  9. Forms![Add an Order and Details]![ShipCity].Value = Forms![Search a Customer]![ShipCity]
  10. Forms![Add an Order and Details]![BillingAddress].Value = Forms![Search a Customer]![BillingAddress]
  11. Forms![Add an Order and Details]![ShipStateOrProvince].Value = Forms![Search a Customer]![ShipStateOrProvince]
  12. Forms![Add an Order and Details]![ShipZIPCode].Value = Forms![Search a Customer]![ShipZIPCode]
  13. DoCmd.Close acForm, "Search a Customer", acSaveNo
  14. End Sub
  15.  
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
Jul 9 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
What is the record source of the "Add or Delete Customer" form?
Jul 17 '07 #2

imrosie
100+
P: 222
What is the record source of the "Add or Delete Customer" form?
Hi Mccarthy,

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

Rosie
Jul 19 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub createcust_Click()
  2. 'createcust is not a great name as this is for editing a customer already in existance
  3. Dim stDocName As String
  4. Dim stLinkCriteria As String
  5.  
  6.     stDocName = "Add or Delete Customer"
  7.     stLinkCriteria = "[CustomerID] = " &  Me.custname
  8.     ' This will only work if the CustomerID is the bound control on the custname combobox
  9.     DoCmd.Open Form stDocName, , , stLinkCriteria
  10.  
  11. End Sub
  12.  
Jul 19 '07 #4

imrosie
100+
P: 222
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub createcust_Click()
  2. 'createcust is not a great name as this is for editing a customer already in existance
  3. Dim stDocName As String
  4. Dim stLinkCriteria As String
  5.  
  6.     stDocName = "Add or Delete Customer"
  7.     stLinkCriteria = "[CustomerID] = " &  Me.custname
  8.     ' This will only work if the CustomerID is the bound control on the custname combobox
  9.     DoCmd.Open Form stDocName, , , stLinkCriteria
  10.  
  11. End Sub
  12.  
Thanks so much....it worked beautifully. take care
Rosie
Jul 19 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.