Results 1 to 8 of 8
  1. #1
    Sweet16 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    9

    Multioccurence/Multievent Form

    I have two tables : Customer and Purchase. I have a query that connects these two tables and identifies what a customer purchases. Also a customer can make more than one purchase, each purchase is a new activity - or row in the table.

    The Customer table has the following fields: Last Name, First Name, Unique Customer ID.

    The Purchase table hast the following fields: Unique Purchase ID, Purchase Date, Payment Type, Purchase From, Ship To, and Unique Customer ID (looked up from the Customer table).



    The Customer Purchase query combines all the data from the two tables and is joined where fields from both tables are equal. The tables and queries work fine. For the Customer table I can see all the customers that exist. For the Purchase table I can see all the purchases that have been made them (based on the unique purchase ID) and who made them (based on the unique customer ID). For the query I can see all the purchase information in addition to the actual names of the customers. There are several customers that have made multiple purchases and I can view each one in the table – as each as a unique purchase ID.

    The problem I have experienced is with the form. I want to view those customers that have made purchases. Instead of utilizing the Customer ID from the field list and selected – find a record on my form based on the value I selected in my combo box- I then selected Customer ID from the Customer Purchase query.

    In the form view of the form I can see all the Customer ID’s in the combo box. Those customers with more than one purchase have their Customer ID listed more than once. However, for those customers, the form is only populated with the purchase information from one purchase. I can select a different instance but the purchase information does not change. I’m not sure why this is happening.

    Help is greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    Cannot use a bound combobox as search criteria. Selecting from list merely changes the value in the field, it will not find a record. Accomplishing this will require code and an unbound combobox. Check out this tutorial http://datapigtechnologies.com/flash...tomfilter.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Sweet16 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    9
    Thank you so much for the tutorial. I am still having difficulty veiwing cusomters with more than one event.

    For instance I have customers with the following:

    Customer ID Purchase From
    1011 Indy
    1011 Illinois
    1011 Kentucky
    1234 Illinois
    4563 Missouri
    4231 New York
    4231 Indy

    For customers 1011 and 4231 I can only see one of their purchases i.e. though 1011 has purchases from Indy, Illinois, and Kentuck the form is only updated with the one from Indy. 1011 is only listed one time in the list and I want it to appear as many times as that person has an order. The way I had it set up before then listed them as often as they appeared on the list but the associated info did not change.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    What is the form setup? One form or a form/subform? Is the form displaying purchases set for SingleForm or Continuous or Datasheet view? Show the RecordSource for the form.

    Could post project for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Sweet16 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    9
    I can't upload the database due to a security token?....

    It is one form. It is in single form view but when i tested continuous view nothging changes. I have also noticed that the purchaase ID is not chaging with the different purchases in the form, though they are different in the query table.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    Your network won't allow upload of file?

    This should work. I just tested two tables set up like this and all records show in the Detail section. Is there filter criteria set in the form? You set up the search combo as described in the tutorial?

    Not clear on what you want the combobox to search for. You want it to find a specific purchase record or select customer and the form filters to show all purchases for that customer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Sweet16 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    9
    I'm at work so I'm guessing it has something to do with the privacy of the computer.

    I would like for the combo box to dispaly the custoemer ID of all the purchases. If a customer has made more than one purachase their ID will appear more than once. The combo box accurately displays this but when I got to click on an ID with multiple purchases access only chooses one record to display irregardles of which of those customer purchases is selected.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    If you want the form to show all purchases for a specific customer there is no need to repeat the customer in the combobox list.

    Did you try the method demonstrated in tutorial?

    We could go in circles on this forever. I suspect there is something wrong with the combobox and the VBA code. You need to either post the project (can you do it from another computer, maybe home) or more details. Combobox properties (RowSource, ControlSource, ColumnCount, ColumnWidths), form properties (RecordSource, DefaultView), the VBA code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums