Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2012
    Location
    Bolton, UK
    Posts
    8

    Question Unanswered: Pre-populating data into existing table?!

    On my database, i have two tables - one called tblOrders and one called tblAddresses.

    What i need to do is:

    When i type in the corresponding customer number (from the tblAdresses) into my TblOrders, i would like it to pre-populate the customer name and customer address (separate fields, both in the tblAddresses) into my tblOrders.

    I am thinking it has something to do with building some sort of Expression - but i have no idea what expression to use?!

    Does any one know how i'd go about doing this, any help would be very much appreciated!!

    Any questions, please feel free to ask me.

    Sophie

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Customer name and address should not be duplicated in both tables, it's a bad design. All the orders table needs is customer number and the specifics of the order. Why do you want to do this?

    Steve

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In a form you can do this:

    Autofill

    As Steve said, you generally don't want to store fields other than the key in related tables. I have seen it done in this type of situation, if you need to keep the address the shipment went to, just in case the address in the customer table changes later.
    Paul

  4. #4
    Join Date
    Aug 2012
    Location
    Bolton, UK
    Posts
    8

    Exclamation

    Thanks for your replies. I really appreciate any help i can get!! I'm trying to set this database up from scratch for a big company!!

    Ahh.. Well i didn't quite explain the database situation properly from the beginning..

    The reason that I have a separate table with all my customer names and addresses in, is because i have imported all these over from an old Lotus Approach database. I don't want ALL the customer names and addresses to be transferred into my new Orders table (because it's a brand new database for next year so there is no data in there yet...
    I only want the customer name & address to be pre-populated in the new table if me or someone else in the company types in the corresponding customer account number... I know it's possible to do because this used to be the way they had it in the old Lotus database....
    Hope it makes sense!
    I'm not sure if i'm making things too complicated for myself - there must be a simple solution but struggling to do it alone ... any help would be much appreciated!!

    Sophie

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd transfer the exisiting customer database to a customer table
    define a relationship between the customers and orders table.

    when you then do a join between the two tables, carefull design will show which customers haven't placed orders yet, or customers who have....
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    If you want to move the data on a case by case basis, then use a form and in the events of the text box where you are entering the Customer Number have it run an update query. This will add the appropriate customer name and customer address from your address table into the newly created record in the orders table.

    As mentioned earlier, it would safer and easier if you are choosing the customer number from a combo box or list. You can have the user select the appropriate customer, click an 'add order' button which will create a record in the orders table with the related data from the address table.

    Steve

  7. #7
    Join Date
    Aug 2012
    Location
    Bolton, UK
    Posts
    8

    Cool

    Thanks Steve, that sounds a bit clearer.. what's an 'add order' button though?

    Sophie

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Sophisticat2012 View Post
    Thanks Steve, that sounds a bit clearer.. what's an 'add order' button though?
    Just a standard button with 'Add Order' as the caption and an on click event where the processing logic is executed.

    It's all about how you want to manage the process flow. From my understanding of your process it would make sense to allow the user to select a customer number from a list (or search for one based on name). Then, rather than typing this into the order table they would click the 'add order' button. This triggers the on click event which follows some logic like:

    - get selected customer number
    - get related values from address table for this customer
    - insert a new record in the order table with the customer number and related record.

    This is a simplification of course, and how involved you want to make the solution depends on many factors, but it should get you started.

    Steve

  9. #9
    Join Date
    Aug 2012
    Location
    Bolton, UK
    Posts
    8
    Thanks guys,

    I've now created a combo box for the customer account numbers but I still need the names and addresses to then pre-populate when the user clicks on the corresponding account number??

    If it's a query i need to use, I'm not sure what criteria i need to put in...do any of you know what criteria i'd need for a query or the standard format of the criteria for the on click 'event procedure' (in the command button Steve was talking about) which will be able to create a record in the orders table with the related data from the address table?

    Maybe the standard format/wording...?

    Thanks for any of your help! I seem to be slowly making some progress

    Sophie
    Last edited by Sophisticat2012; 08-16-12 at 07:30.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have your combo box, populated by which table.
    thats the table that should have the customer details in.

    what you need to do is place some code behind the in the appropriate events of the combobox, i forget which, but suspect it may be the on click event..

    that code shoudl then fill the appropriate text boxes on the form.

    where the addesses come form depedns on you and your design
    you could stuff the address details as additional columns in the combo box
    or
    you could issue a SQL request to retrieve the details from the relevant table.

    given that addresses are soemtimes long I'd suggest the latter so you don't clog the network retrieving masses of data you don't actually need.

    you need to open a recordset (use ADO or DAO doesnt' matter)
    issue the SQL.. it will be something like
    SELECT line1,line2,line3,line4,postcode FROM mycustomerstable
    WHERE customerID = 8970987098

    or

    strSQL = "SELECT line1,line2,line3,line4,postcode FROM mycustomerstable
    WHERE customerID =" & mycombobox.value

    execute the SQL

    process the results
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2012
    Location
    Bolton, UK
    Posts
    8
    Thanks. I have tried that and got a bit further along. But i'm unsure of which fields from both tables to include in my select query..

    At the moment, my SQL data looks like this:

    SELECT [BOWL ORDER RECORD 2013 (VIEW ALL)].[Customer Account Number], [BOWL ORDER RECORD 2013 (VIEW ALL)].[Customer Name], [BOWL ORDER RECORD 2013 (VIEW ALL)].[Delivery Address]
    FROM address1 INNER JOIN [BOWL ORDER RECORD 2013 (VIEW ALL)] ON address1.[Record Number] = [BOWL ORDER RECORD 2013 (VIEW ALL)].[Customer Account Number]
    WHERE (((address1.[CUSTOMER NUMBER])=[Combo134].[value]) AND (([BOWL ORDER RECORD 2013 (VIEW ALL)].[Customer Account Number])=[Combo134].[value]));

    And i don't understand which order all this is meant to be in.!!

    Sophie

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by Sophisticat2012 View Post
    Thanks guys,

    I've now created a combo box for the customer account numbers but I still need the names and addresses to then pre-populate when the user clicks on the corresponding account number??

    If it's a query i need to use, I'm not sure what criteria i need to put in...do any of you know what criteria i'd need for a query or the standard format of the criteria for the on click 'event procedure' (in the command button Steve was talking about) which will be able to create a record in the orders table with the related data from the address table?

    Maybe the standard format/wording...?

    Thanks for any of your help! I seem to be slowly making some progress

    Sophie
    Did you notice the link in post 3?
    Paul

  13. #13
    Join Date
    Aug 2012
    Location
    Bolton, UK
    Posts
    8
    Quote Originally Posted by pbaldy View Post
    Did you notice the link in post 3?

    Yes i did, i tried to do it that way but I got very confused & couldn't do it correctly..

    I think i'm doing something wrong!!

    Thanks, Sophie

  14. #14
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I only want the customer name & address to be pre-populated in the new table if me or someone else in the company types in the corresponding customer account number...
    so, essentially you want to pull customer name & address from the address table for this customre number and insert it along with the customer number into the order table

    I'm not sure what you are doing with the [BOWL ORDER RECORD 2013 (VIEW ALL)] table (query?), but for the sake of argument I'll use the tables you originally mentioned (tblAddress and tblOrder) and cboCustomer

    Then use something like this for when the button is pressed

    Code:
    Private Sub cmdAddOrder_Click()
        Dim sql As String
        sql = "INSERT INTO tblOrder ( Customer_Number, Customer_Name,    Customer_Address ) SELECT tblAddress.CustomerNumber,  tblAddress.CustomerName, tblAddress.CustomerAddress FROM tblAddress  WHERE tblAddress.CustomerNumber = " & cboCustomer.Value
        CurrentDb.Execute (sql)
    End Sub
    do a query on INSERT INTO sql statements to understand this better, but essentially you declare which fields to insert the data to then in the values list you are selecting these fields from the other table.

    Steve

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by Sophisticat2012 View Post
    I think i'm doing something wrong!!
    If it looked conceptually like what you need, post what you're doing. Hard to figure out what you're doing wrong without seeing what you're doing.
    Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •