Results 1 to 8 of 8
  1. #1
    asita is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Location
    Connecticut USA
    Posts
    4

    Data form to insert data referencing multiple tables data

    Hello Good Evening,


    I Did development in Access 7 years back, now In my project I need to develop few forms

    Can somebody please advise me or guide me to do this request
    1) basically we have 2 tables in SQL server 2010(we are planning to move them to ACcess) for example first table is Customer (CustID, CustType, Age) second table is CustomerContact (CustID, Email1, EMail2, City)

    I need to create a access for to insert data to Second table (but on form I need to show first table values in combobox)



    so in my form I need to have Combobox (for table1 values to display) CustID, Email1, Email2, City text boxes
    the combination of first table 3 fields (Custid, Custtype, Age) need to be populated as a single value per row in combobox when a user selects value from combobox then custid text box
    will have the selected value Custid and user manually enters remain text box values(Email1, Email2,City) then when they click on insert button it should insert data to second table.

    please guide me on any materials etc... Please please

    Thank you very much in Advance

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Hi!

    Instead of several contacts in single row of table CustomerContact, you better have several rows there - one for every contact info. And then you design a single form based on table Customer with linked list-type subform based on table CustomerContact with CustID as Link Master Field and Link Child Field.

    You can add a ContactType field to CustomerContacts table, so you can in same table store p.e. phone numbers too. When you want to keep addresses in same table too, you need some additional fields, like ZIP, Country, City (in case you need some statistics or search based on these values).

  3. #3
    asita is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Location
    Connecticut USA
    Posts
    4
    Hi Thank you for your input

    FYI tables will be in sql server only no moving to MS access

    so we need to add records to sql server table through ms access forms

    please tell me what is best way to do this using VBA? or macros etc... please

    Thanks in advance
    Asita

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can't you set links to SQLServer tables? Bind forms to the linked tables.
    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
    asita is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Location
    Connecticut USA
    Posts
    4
    Create
    TABLE dbo.TestCustomerContact(CID numeric, CName varchar(20), Email1 varchar(50),Email2 varchar(50),Email3 varchar(50),Email4 varchar(50))
    Create
    TABLE dbo.TestCustomer(CID numeric, CName varchar(20), City varchar(50))
    Insert
    Into TestCustomer Values (100,'Chase','Ashburn')
    Insert
    Into TestCustomer Values (200,'Wachovia','Edison')
    Insert
    Into TestCustomer Values (300,'Barclays','NYC')
    Insert
    Into TestCustomer Values (400,'PNB','Miami')
    Insert
    Into TestCustomer Values (500,'Bofa','Tampa')
    select
    *from TestCustomer
    Insert
    Into TestCustomerContact Values (100,'Chase','Chaseceo@chase.com','ChaseDM@chase.com','ChasePM@chase.com',null)
    Insert
    Into TestCustomerContact Values (200,'Wachovia','sales@Wachovia.com','promos@Wachovia.com',Null,null)
    select
    *from TestCustomerContact
    select
    *from TestCustomer
    /*
    CID CName City
    100 Chase Ashburn
    200 Wachovia Edison
    300 Barclays NYC
    400 PNB Miami
    500 Bofa Tampa
    */
    select
    *from TestCustomerContact
    /*
    CID CName Email1 Email2 Email3 Email4
    100 Chase Chaseceo@chase.com ChaseDM@chase.com ChasePM@chase.com NULL
    200 Wachovia sales@Wachovia.com promos@Wachovia.com NULL NULL
    */
    SELECT
    Cast( CID asvarchar(20))+' - '+ CName +' - '+ City FROM TestCustomer -- Combo Box (only selections happens)
    /*
    100 - Chase - Ashburn
    200 - Wachovia - Edison
    300 - Barclays - NYC
    400 - PNB - Miami
    500 - Bofa - Tampa
    */
    Access
    from should have the fields(textboxes or something like)
    1
    ) COmbobox
    2
    ) Cname
    3
    ) Email1
    4
    ) Email2
    5
    ) Email3
    6
    ) Email4
    7
    ) CID
    user
    selects a value from dropdown box,thensecondtext box, seventh text box will be filled with Cname(secondvaluesfrom combo boxes), CID(textbox 7) automatically ,thenuser manually enters the data for email1,2,3,4
    when
    user hit add button then the row will be added to TestCustomerContact.
    please note here we
    use TestCustomer only for retrival purpose(combo box) also use the valuestoadd data to TestCustomerContact table.
    please advisse your thoughts
    ...

  6. #6
    asita is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Location
    Connecticut USA
    Posts
    4
    Thank you for your input, I can try that but have no idea where to start
    Please see above details in case if that helps

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What is all that in post 5? What is it supposed to tell us? Why are you creating tables with SQL action?

    Have you done any research on how to link to SQLServer tables?
    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.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    You have got here several times an advice to link your forms to SQL server database. Here is more detailed explanation:

    There is no need to move your data to Access. You can create an Access front-end, which is connected to database(s) on SQL Server (you link SQL server database(s) tables and views into Access front-end). User works with Access front-end, but all operations in database(s) (inserting rows into tables, editing data, deleting rows from tables, etc.) are done on SQL Server.

    There are some advantages for such design. P.e. you can use more advanced SQL query syntax of SQL Server in SQL Server views compared to Access stored queries, when using SQL Server views the speed of your application depends less on user's computer's capacity (this applies when you have the SQL database(s) on separate SQL Server of-course), you can call SQL Server stored procedures from your front-end (on SQL Server you often can do more complex calculations with much simpler syntax), you can run scheduled jobs on SQL server (p.e. on every night the integrity of your data is checked, or some info is periodically requeried from other databases), you can use domain groups to limit user's access to your database instead of Access clumsy security system, etc.

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

Similar Threads

  1. Insert data from multiple tables into one table
    By mohanmoni in forum Queries
    Replies: 3
    Last Post: 02-05-2015, 01:31 AM
  2. Replies: 6
    Last Post: 08-17-2014, 06:14 PM
  3. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  4. Replies: 1
    Last Post: 07-16-2012, 02:10 PM
  5. Replies: 4
    Last Post: 02-27-2012, 10:29 AM

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