Results 1 to 12 of 12
  1. #1
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42

    Showing Record Key

    Hi Everyone. I'm fairly new to Access and i am trying to build some better tracking for work. Currently they are using an Excel sheet, but Access will give them better management of the data.

    So here is my question, after the user is done entering all the customer information on the form, i would like it to show the Primary key for that entry as they will need that for the quote tracking. A Drop Down will not work as there will quickly become hundreds to thousands of customers. I know they can just hit the forward button then the back button, but I'm trying to simplify this for the users. I was thinking a button with a VBA script that will do that, however I'm not sure what that script would look like or whether or not his is even the right approach.

    My second question is setting up a search query for the users to find a customer. I know in the query I can add "*" before and/or after to return more results, is there a way to do that automatically when the user types in the search field when running the report. I want to be able to do this so the users can look up the customer ID's without having to remember how to spell their name.

    I'm putting together basically a CRM that will track customers, quotes, and orders. Any other tips for this if anyone has any would be appreciated.

    Thanks in advance and I'm really looking forward to getting better with databases.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the primary key an autonumber? If so, with an Access backend, it should appear as soon as you start typing into a bound textbox on a form. If you're creating it, show it to the user after you've done it.

    If you use a form to gather user input, as most of us would, a query can have a criteria of

    Like "*" & Forms!FormName.TextboxName & "*"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    The primary key is an autonumber with a "CUS" prefix. The users (mostly sales people who have no idea how a database works) will be using the forms to enter in the information. I will have the forms set to data entry only with separate forms for editing if needed. So if i set a textbox linked to the primary ID field on the form, it should give me the primary ID as soon as they fill in the first text box?

    Is it possible to set up a form to search for the customer ID by name or company? I thought that was something that would have to be done in reports.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Re: "So if i set a textbox linked to the primary ID field on the form, it should give me the primary ID as soon as they fill in the first text box?", yes.

    No, forms can be set up for searching too. It can be done any number of ways, so you envision what you want the flow to be and we can probably get it to work. Simplest with a form bound to the table is the combo box wizard, choosing the third item "Find a record...".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I should clarify that the autonumber will appear immediately. The prefix should be separate, or it's not an autonumber.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    Thank you. Do you know of any good tutorials for creating a search form?

  7. #7
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    One more quick question. If I set the format mask for the autonumber for the customer table as "CUS"00, will it only allow me 99 customers, or will it roll over to CUS100?

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by MaxQTime View Post
    The primary key is an autonumber with a "CUS" prefix.
    I think you are doing something like this http://www.databasedev.co.uk/add_prefix.html

    However let me say I think this is a very bad thing to do. Autonumbers are NOT meant to have any real world meaning.
    See this link https://www.accessforums.net/showthr...288#post389288
    Read the linked sites.


    Quote Originally Posted by MaxQTime View Post
    One more quick question. If I set the format mask for the autonumber for the customer table as "CUS"00, will it only allow me 99 customers, or will it roll over to CUS100?
    The number of zeros has to do with how many leading zeros are displayed.
    If you have a Format of "OH-117-"00000 for an autonumber, it will display as "OH-117-"00001, "OH-117-"00002, etc
    If you have a Format of "OH-117-"0 for an autonumber, it will display as "OH-117-"1, "OH-117-"2, etc


    I just added 15000 rows to a table with the PK field format set to "OH-117-"0

    The first record displayed as OH-117-1.
    The last record displayed as OH-117-14714.


    If you then change the format property of the PK field to "OH-117-"000, the first record PK will display as OH-117-001.

  9. #9
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    So maybe I'm going about the design wrong. Here is what it needs to do. It needs to track the quotes and orders going to customers. Most customers will have more than 1 quote, and some customers will have more than 1 order. Every order needs a quote before it can go to order. I need this simple enough that the sales people are able to enter the information in (I can create the forms for them to use). I've created tables for Sales People, Customers, Quotes, and Orders. I was going to link the order to the customer through the customer ID key, as the sales person will have to do this. The reason I am not going with a dropdown is because we have a fairly large customer base. Is there a better way to set up the form for entering the quotes that will not require the primary key to link to the customers table?

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    1. It looks for me, that you can consider to have both orders and quotes in same table, as they generally have similar data structure (maybe there are a couple of fields filled only for quotes or only for orders, but when the number of such fields is small, you can ignore this). And the table has status field, where e.g. 1 is for quotes and 2 for orders.

    2) In case you want to see later the history of order (with possibly several quotes before final order was made), then you also can keep all quotes and orders in same table, but you need an additional table (let's name it Projects). With 1st quote you create a project, and attach this quote to this project (add project ID into Quotes/Orders/Project details/ whatever you name it table). Whenever a new quote is made instead of old one, it will have same project id, and the same with Order made as final step.

    I like 2nd solution better, so I continue with it. And as I have no clue what is sold, I leave this part also out.
    tblCustomers: CustomerID, CustomerName, ...;
    tblProjects: ProjectID, CustomerID, ProjectName, ...;
    tblProjectDetails: ProjectDetailID, ProjectID, ..., DetailStatus; (Detail status is 1 for quotes, 2 for Orders, ..., 99 for closed)

    You need a single (bound or unbound) form where you can select a customer (fCustomer).

    The form fCustomer has subform (sfCustomerProjects) with another single form based on table tblProjects (fCustomerProjects) as source. Form fCustomer and subform sfCustomerProjects are linked through CustomerID (In case you use unbound fCustomer, through form control in fCustomer where you select customer, and CustomerID in tblProjects). This quarantees, that in subform user can navigate only between projects linked to active customer, and creating a new project in subform links it automatically with active customer.

    The form fCustomerProjects has his own subform sfCustomerProjectDetails with form fCustomerProjectDetails based on tblProjectDetails as source. Form fCustomerProjects and subform sfCustomerProjectDetails ar linked through ProjectID. Depending on amount of information you need to display for quote or for order, fCustomerProjectDetails may be single form (the form is filled with data of single quote/order), or continuous one (every quote/order has a single row of data).

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    MaxQTime,

    Based on your posts, I recommend that you gather all of your "business rules/facts" and also identify what the expected outputs of your proposed database should be. Using pencil and paper create a data model (entities and relationships) from the business rules. These rules will identify/determine the relationships. Relationships are not arbitrary lines drawn on a diagram. Create some test data to "put values in some of the Tables in the paper based model" and some sample scenarios to see if you can access the data with sufficient info to "get" your proper outputs.
    Adjust the model as necessary as facts and the evolving model are "tested" with your sample scenarios.
    It is much easier to test and adjust your basic design with a paper based model than with a physical database.
    The vetted model will serve as a blueprint for your physical database (tables and relationships). Getting your tables and relationships to match your business requirements is key to a useful, maintainable database.

    Do not be too quick to have a physical database -- do the analysis and design.

    The Database Planning and Design link in my signature has many reference materials that you may find useful.

    Good luck with your project

  12. #12
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    The company I work for is a large cabinet company. The problem is, they are still using the same excel sheet for tracking all this information as they were using when the company wasn't so big. I've been trying to update some of their systems as I find this stuff fun and fascinating.

    I wasn't hired for this role, but I've sort of volunteered for it as the contract IT they use are too busy for stuff like this.

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

Similar Threads

  1. Replies: 18
    Last Post: 03-26-2019, 11:09 AM
  2. record not showing on report
    By chriswrcg in forum Access
    Replies: 8
    Last Post: 11-21-2018, 01:55 PM
  3. Replies: 1
    Last Post: 02-28-2018, 01:02 PM
  4. Record not showing in form
    By Lough in forum Forms
    Replies: 10
    Last Post: 04-18-2012, 11:56 AM
  5. Replies: 3
    Last Post: 06-27-2009, 03:53 PM

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