468,283 Members | 1,982 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,283 developers. It's quick & easy.

Passing Parameters with Autonumber

imrosie
222 100+
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
4 2050
MMcCarthy
14,534 Expert Mod 8TB
What is the record source of the "Add or Delete Customer" form?
Jul 17 '07 #2
imrosie
222 100+
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
14,534 Expert Mod 8TB
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
222 100+
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.

Similar topics

3 posts views Thread by domeceo | last post: by
7 posts views Thread by Harolds | last post: by
17 posts views Thread by Charles Sullivan | last post: by
2 posts views Thread by csmith8933 | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.