473,320 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2323
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

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

Similar topics

2
by: zlatko | last post by:
There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures...
3
by: domeceo | last post by:
can anyone tell me why I cannot pass values in a setTimeout function whenever I use this function it says "menu is undefined" after th alert. function imgOff(menu, num) { if (document.images) {...
12
by: Joel | last post by:
Hi all, Forgive me if I've expressed the subject line ill. What I'm trying to do is to call a c++ function given the following: a. A function name. This would be used to fetch a list of...
7
by: Harolds | last post by:
The code below worked in VS 2003 & dotnet framework 1.1 but now in VS 2005 the pmID is evaluated to "" instead of what the value is set to: .... xmlItems.Document = pmXML // Add the pmID...
39
by: Mike MacSween | last post by:
Just spent a happy 10 mins trying to understand a function I wrote sometime ago. Then remembered that arguments are passed by reference, by default. Does the fact that this slowed me down...
17
by: Charles Sullivan | last post by:
The library function 'qsort' is declared thus: void qsort(void *base, size_t nmemb, size_t size, int(*compar)(const void *, const void *)); If in my code I write: int cmp_fcn(...); int...
1
by: Punker | last post by:
Hi guys, I am trying to create export specifications for one of my queries. Now when I run the query on its own, it works perfectly. However when I try to export the data I get back the error...
5
by: Swinky | last post by:
I have a form "AccountInfo" that contains company names. I have inserted a subform "Contacts" with contact names and have established parent/child relationships between the two forms. All works...
2
by: csmith8933 | last post by:
How do I write a function where the number of parameters it takes varies? This is what I have but it doesnt work. // function prototype void functionThree(int num1=1, int num2=2, int num3=3);...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.