Results 1 to 8 of 8
  1. #1
    KeepItSimple is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Location
    Spalding
    Posts
    4

    Access Web App How to auto fill fields based on linked table

    Hi,

    I've worked a little with Access desktop databases, but I'm brand new to Access Web Apps. I have 3 tables, Customers, Stock and Orders, Orders being the equivalent of my Master table. The idea is that Users would input an order via a form to the Orders table, selecting the customer and which stock items they want via lookup fields linked to the customer table and the stock table.



    I have linked the Customer table account number field via a lookup to the Orders table and in the form based on the orders table I want to select the Customer number from a dropdown and then have the rest of the customer address fields on the order fill automatically. I will need to do something similar for the stock items as well, pick the item code and fill the description field automatically.

    Normally I would just use a Dlookup in an after update event (Not sure whether that would be best practice, but it's always worked for me). But I can't use vba in Access Web App.

    I'm using Access 2010 as we don't have Access Services enabled for SharePoint 2013. Googling this is telling me to use Macros but there are limited tutorials/examples and I have no idea what I'm doing and have tried a few things and nothing is working.

    Can anyone help and give me any direction or tell me how I do this in an Access Web App please?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd use columns in the combo instead of DLookup():

    http://www.baldyweb.com/Autofill.htm

    in a web app you can use a macro to copy if needed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    KeepItSimple is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Location
    Spalding
    Posts
    4
    Thanks, that is showing me the address in the Combo, but It won't let me view the column data in a text box. If I try to reference a column from the combo as a control source it give me #Name? in the text box.

    Any Suggestions?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I haven't created a web app, but I'd be surprised if the column reference didn't work. What exactly do you have in the control source?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    KeepItSimple is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Location
    Spalding
    Posts
    4
    Hi,

    I have a 'Customer' combo field which is a lookup field to the Customers Table. Then I've added a text box as per your instructions and tried to put the control source for that text box as a column number in the combo, but it doesn't recognise it and just brings up #Name?.

    I can't seem to attach a pic but I've put: =[Customer].[Column2] in the control source of the text box. It recognises the 'Customer' field but doesn't recognise the column reference. The autofill options it gives are: [ColumnCount], [ColumnHeads], [ColumnWidths] and it will tell me how many columns or how wide they are in my combo, but will not let me reference the data in it.

    Unfortunately there seems to be considerable differences in Access Web Apps. The latter part of your suggestion is not possible at all as you can't use any VBA at all in Web Apps. But I was hoping that I could display the data referencing the combo column.

    Oh well, back to the drawing board.

    Thanks anyway

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The latter part should be possible with a macro, as I mentioned. Make sure the column count property of the combo is the number of fields in the row source. If you want the third column, try

    =Customer.Column(2)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    KeepItSimple is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Location
    Spalding
    Posts
    4
    Hi Paul,

    I've worked it out using Queries instead

    I thought it should be done with Macros, but they just don't seem to function properly. I'm not sure whether that's just a web app thing!

    Thank you for your help.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted. Macros were enhanced in part because of web apps, so they should function properly, but...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 11-23-2014, 02:20 PM
  2. Replies: 25
    Last Post: 10-15-2014, 11:01 AM
  3. auto fill fields based on another field
    By dbell in forum Forms
    Replies: 18
    Last Post: 05-26-2014, 01:46 PM
  4. Replies: 6
    Last Post: 07-12-2013, 01:07 AM
  5. Replies: 3
    Last Post: 03-15-2013, 03:27 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