Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Two Tables in Form

    Hi all I have a question. I inherited a database where there two tables linked together in the design view of the form. Now what happens is when the use enters types in the Parent ID iin the Parent ID field of the form all of the information pertaining to that Parent ID populates all the fields. Such as Last name First name..etc. Now, so they look up records through typing in the values in the the fieds and it pops up in the form. We are now working off of an ADP and I was wondering how do I recreate that. How would do I make it so that users can do a search off the fields. Like the First name in the First name field and the data populates. How can I do that?? Usually I use a command button but I guess they want that feature also. Can anyone hlep pls

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Can you not access the code behind the form?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    there is no code, at first I thought there was code also but there isnt. I think she created it from Access 2000.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "enters types in the Parent ID"

    Is this a combo-box? (drop down) - maybe it's bound back to the ID field / combo?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Hi Garethdart, there is no combo box I looked and no code. Let me show a picture of what it looks like in the design view
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You've got some "nasty" look field names in your tables (ie. Telephone<space>#).

    Regardless, here's how I would design your form:
    1. Have a main type of data form where the recordsource is only based off the Parent table.
    2. Have a subform where the recordsource is only based off the Contact table. Use criteria in the recordsource for the ProductID field such as =Forms!MyMainFormName!ProductID
    3. Make sure the ProductID field is on the subform and set it's defaultValue property =Forms!MyMainFormName!ProductID (thus, allowing adding new records.)
    4. Change any values which I may need to set (on the form or subform) in code using the appropriate reference to the field either on the form or subform.

    This prevents the least amount of problems. Trying to update fields in 2 different tables on 1 form (using a recordset linking the tables) is not a good idea and open to problems - especially if you're going to an ADP (which I personally would NOT recommend (see my comments below).) It may be a tad more work designing 2 forms but it's the best route to go.

    Additionally, it's best to also open the main data form using criteria so it only returns 1 record. Opening all the records on the main data form can mean delays in form loading time for large recordsets.

    If you get further into revising this app (especially for ADP), I'd seriously look at removing spaces and other odd characters (ie. #) in the field names. It'll make your life much easier in the long run.

    Regarding your search field, it's then a simple matter of putting an unbound combobox field on the main data form. Set it up so it's rowsource is based off the Product table (ie. ProductID is the first column and whatever you want the user to type in is the 2nd column). Then set it's columncount property to 2 and the columnwidths to 0";1".
    Then in the AfterUpdate event of this combobox, you can manipulate the main data form's recordset or simply use the docmd.findrecord command. ie.

    Private sub MyComboboxSearchField_AfterUpdate()
    if not isnull(me!MyComboboxSearchField) then
    me.ProductID.setfocus
    docmd.findrecord me!MyComboboxSearchField
    me.MySubformName.requery
    end if

    Note the me.MySubformName.requery command above to requery the recordsource for the subform. Also note, to use the docmd.findrecord command above, you want to make sure your main data form is opening with all the records (ie. you can't search through records on a form when the form opens with criteria to only open 1 record.)

    Otherwise, there are some good examples of "search" type forms in the code bank. I will often have a button on the main data form called "Find Person" or "Find Product" which opens up a separate search form which allows searching on multiple fields and then opens the main data form using criteria. Again, see the code bank for examples on how to do this (there are many.) This would be the ideal route to go for large recordsets or external users with slow connections as it is the fastest.
    Last edited by pkstormy; 09-25-09 at 21:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding ADP projects,

    There's really no big significant difference with ADP versus an mdb which is designed efficiently (and uses SQL Server linked tables). There is actually more of a drawback to designing an ADP versus an mdb!

    1. In ADP, you lose the ability to create queries in your application.
    2. In ADP, you have to write 3 times as much code and it takes longer designing simple reports and forms.
    3. There's really very little speed or other difference in using an ADP versus an mdb which has the forms designed in an unbound fashion.
    4. Overall, ADP is more time-consuming developing and takes longer to modify or troubleshoot.

    I was working with records in the 5 million recordset sizes. I designed my application in both an ADP and mdb. Using unbound forms (and linked SQL Server tables) in the mdb, it was far more developer-time efficient using an mdb file versus an ADP. If needed, stored procedures were designed for the mdb to utilize but again, there wasn't a significant speed difference versus linked SQL Server tables and recordset coding (ie. functions to retrieve, update, delete records to/from the form.) Again, there was essentially very little speed difference if any at all. The ADP was much more difficult to maintain and code. The mdb could be troubleshot and modified in less than 1/2 the time. Losing the ability to design queries in the ADP required more development time for something which could easily be done with a query or queries in the mdb. In the ADP, other developers struggled and one developer deleted all the tables in the ADP (which also deleted them on SQL Server!) There was essentially no control separating the Developer and DBA to make sure tables weren't inadvertently modified or deleted (note: this has pros and cons but I'd still recommend avoiding ADP!) Also note, you can setup certain privaleges but there's more control using an mdb with linked tables.

    I'm not sure why you chose ADP but I'd seriously reconsider it. I've never found the need to design any more ADP's after designing several and dealing with longer development/modification/troubleshooting times. I actually redesigned all those apps to use mdbs versus ADPs (compiling my mdb files into mde files for the users.) It was much more efficient all around.
    Last edited by pkstormy; 09-25-09 at 21:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok Pk thank you, can make it so that the tables are linked instead of making it into and ADP. Its a database that I inherited. No normalization, need to redo the whole thing I am aware of that. thank you for the adive pk will change to linked tables
    Last edited by desireemm; 09-26-09 at 00:08.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Those are usually the funnest. Are you upsizing the tables to SQL Server or was the db already setup in SQL Server?

    I found that the normalization rules can sometimes get bent a little bit for certain structures. For example, the databases with our scanning software and other research data structures don't always fit into the normalization rules.

    I try to judge any databases I inherit by the skill level of the developer who developed them. If someone knew how to create db's on SQL Server, I'd like to think that knew how to normalize and to what extent if any.

    If you're creating new tables for SQL Server, the MSAccess Upsizing Wizard is a great wizard which upsizes your MSAccess tables to SQL Server. I will often create my table structure (relationships and joins, primary keys, indexes, record identifier), in MSAccess and then use the upsizing wizard to transfer it all to SQL Server. I name my MSAccess tables dbo_tblSomething so that when I link the tables from SQL Server, I don't need to change the name of the table (or in code) since ODBC always adds a dbo_ in front of the table name. The only thing is to make sure that you rename them before upsizing in MSAccess or after them in SQL Server.
    Last edited by pkstormy; 09-26-09 at 00:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Ok I linked the tables and tested one of the users permissions, the thing is on sql didnt give her permission to create anything but when the tables are link that rule doesnt apply. she can create..As an Adp she didnt have those permssions, how do I restore that. We have SQL authentication in Mixed Mode and the users dont log into a domain on a server. They sql client tools installed on their pc and they connect to sql server throught access.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. SQL Server permissions are still applicable in the mdb. In an ADP, you may have connected to the tables supplying the user/password or an "sa" type user/password (or other group type user/password) or SQL Server permissions applied directly. Using an mdb with linked tables, she may (or may not) have permissions to edit certain tables depending on how you configured the ODBC Dsn connection on her computer. The ODBC Dsn that you configure (on the user's computer) tells your MSAccess app how you want the linked tables treated for that user's connection. I sometimes set it to Windows Authentication Mode which then connects to SQL Server through ODBC using the user's windows loginID. I also sometimes use SQL authentication and supply the group/sa login/password while creating the ODBC Dsn to have all user's connect as one user/password. While you were creating the ODBC Dsn on her computer, if you used SQL Server Authentication and put in the "sa" user/password, she would be connecting to SQL Server as "sa" and thus, have "sa" type permissions. I might recommend using Windows Authentication mode if it's the first time your working with ODBC. How you create the ODBC Dsn for her machine, is how you're telling SQL Server what user/password you want her to connect as. Also, while linking the tables into an mdb from SQL Server, you typically want to check the "Save Password" checkbox that is on the 1st or 2nd popup in the wizard. If you want (I typically don't do this), you can also leave this unchecked and then configure your ODBC Dsn on the user's computer so when that user opens the mdb, they get a prompt to connect to SQL Server (for the linked tables.)

    You may want to check out this utility which takes care of automatically creating/refreshing the ODBC Dsn when the mdb opens:
    http://www.dbforums.com/6282465-post37.html

    I'll typically import the modules from the mdb in the example and then add the 1 line of code in my startup form as shown. This takes care of automatically creating my ODBC Dsn names and prevents mis-naming problems (but note: the user must have the ability to create ODBC Dsn's on their computer - some network admins will disable this on user's computers.)

    For users, accessing the linking tables in an mdb is dependant upon the ODBC Dsn and each user will need to have an ODBC Dsn (with the ODBC name itself being the EXACT same name when created on each user's computer) to "bridge" the connection so to speak between SQL Server and MSAccess. This is how you "bridge" the gap for most backend servers -> frontend (unless like your ADP project, you're writing code to the SQL Server directly.) I once used a special "Progressive" driver to bridge the gap between a Unix data server so I could link the tables into MSAccess. I can't recall if ADP's used ODBC or not. ODBC is typically non-problematic, installed with Windows, and works fine on normal setups unless again, you start creating different ODBC Dsn connection NAMES to the same SQL Server db. This is important to know otherwise it will drive you crazy on why user's get an ODBC error one day and then not on the next day, then again on the next day, etc...Otherwise, it's just a matter of creating the ODBC Dsn name on the user's computer (which is accessed via the Control Panel -> Administrative Tools -> ODBC Manager) to configure the connection.

    User's don't need to have the SQL Server Client installed (if that's what you mean by SQL Client) on their computer just to link to the tables. You install the SQL Server Client only when you want to give users the ability to edit/modify tables on SQL Server (but they would need to know how to use SQL Server also.) Unless you mean the SQL Server driver for ODBC. Typically, the SQL Server driver for ODBC is installed with MS Office so you really shouldn't need to install anything from the SQL Server disks on the user's computer.

    All you should need to do is again, just configure the ODBC Dsn connection (through Control Panel) on the user's machine to have them use your mdb with the linked SQL Server tables. Using the example in the link, you don't even need to do that.

    Just as you would if you were dealing with MSAccess tables, when you link the tables from SQL Server to MSAccess, you want to control how user's see/open the data via the forms (ie. you don't want user's editing data in the tables directly). But SQL Server permissions to edit data in the tables are also again, based on how you configured the ODBC Dsn on that user's computer.
    Last edited by pkstormy; 09-27-09 at 21:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    They already have sql client on their pcs. the client that I dont want touching anything is the one that works on the database from home. and I dont want her messing around with anything

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What exactly do you mean by "sql client"?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Client Tools Only

    Installs only the client relational database management tools. Included in this option are the client tools for administering SQL Server and the client connectivity components. In addition, this option allows you to select other components to install. For more information, see How to install client tools only (Setup).

    Server and Client Tools

    Installs both server and client tools to create a relational database server with administrative capabilities. Selecting Server and Client Tools presents the full range of additional setup options.

    For more information about performing a typical installation of a default instance of the database engine, including all client and connectivity components, see How to install SQL Server 2000 (Setup).


    Note This option is not available if you are installing client tools using a compact disc for an edition of SQL Server that is not supported by your computer's operating system.

    Connectivity Only

    Installs only the relational database client connectivity components, including MDAC 2.6 (Microsoft Data Access Components), a requirement for connecting to SQL Server 2000 named instances. This option provides connectivity tools only, with no choice of client tools or other components.

Posting Permissions

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