Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41

    Auto Complete Form from Two tables outputting to third table?

    I am creating a simple database for an estate agency which basically consists of two main tables.

    1. Purchasers

    2. Vendors

    I am trying to create a form which will auto-complete data from these two tables based on a single value (probably Name) and then output this data to a third table which would be "Sales", containing only the key details of each party, and also "new" details of the agreed sale which would be entered via this form (agreed sale price etc.).

    Can anyone guide me on how to create this?



    Thanks in advance!

  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 this what you're after?

    BaldyWeb - Autofill
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Possibly! I am trying to create the form, I have my combo box but keep getting a syntax error to address a subquery error error or en close subquery in parentheses.

    In the expression builder I have = SELECT ClientID, ClientName, ClientPhone, ClientE-Mail, ClientSource FROM Clients and have tried adding parantheses before clientID and affter Client e-mail to enclose the date but it's not happening...

    What am I doing wrong?

    Edit to add screens with example forms and date that I wish to auto-populate to the new table & form.

  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
    For starters, this field name would have to be bracketed due to the inadvisable symbol:

    [ClientE-Mail]

    Does that fix it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    I first tried your suggestion with no success and have now taken the - out of E-Mail so it is now just E Mail and have tried every variation of [ .... ] that occurs to me but I am still getting the syntax subquery error..?? I saved the database after changing the E-Mail field.

    Any other suggestions at all? I'm at a loss.

    I have now taken personal info out of the database and upload a copy to see if perhaps I have done something wrong in the tables?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What form/combo are you having trouble with?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Form1 is where I am trying to auto-complete based on the data in Clients & Vendors. Then I would add some other boxes which all together will output to a "Sales in Progress" table.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, this works as the row source:

    SELECT Clients.ID, Clients.[Client Name], Clients.Telephone, Clients.[E Mail], Clients.Source FROM Clients;

    The fields are different than what you posted above.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41

    Unhappy

    I don't understand. I have copied that exactly into the expression builder and am still getting the same error. I have attached a screenshot of how it looks just before I press ok and get the syntax error.


  10. #10
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Do I need to build a query?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You don't want the = sign in there, and you want it in the row source property, not the control source property. Hitting the ellipsis there will open the query builder, not the expression builder.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Ok, The syntax error is gone. Now when I go to from view and click the combo box, it pops up asking me for a parameter value. Why does it not just display the client name in a drop down menu?

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The parameter prompt is something Access can't find. Double check the spelling of your field names. What's the SQL now?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    yep. My fault. I had changed the Client Name column to just Client and forgotten to change it back.

    Works perfectly now thanks, except that the drop down is coming up as the Client ID rather than name?

    All I have to do then is figure out how to get the data to output to another table.

    Should be easy enough.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The column widths property determines which columns show. Presumably the form would be bound to the other table, so copying values to bound textboxes would save the values.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-04-2011, 01:50 PM
  2. Auto complete field on form
    By oam in forum Access
    Replies: 5
    Last Post: 09-23-2011, 12:45 PM
  3. Dlookup to auto complete in form
    By custhasno in forum Access
    Replies: 2
    Last Post: 09-08-2011, 12:53 PM
  4. Replies: 1
    Last Post: 03-31-2011, 02:51 AM
  5. Auto Complete Data
    By manicamaniac in forum Access
    Replies: 5
    Last Post: 09-14-2010, 03:38 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