Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms and linking tables (Access 2000)

    Hi Everyone,

    Ahh! If I have 3 tables, customer details, product consumption, and cooking frequency.
    The customers details table has a 1-1 reln with product consumption and 1-1 with cooking frequency.
    The customer details has a memberNo which is autonumber.
    I want to link the tables and create a one form with all the fields from each form, so the user can enter details into
    one form and it will put the data into 3 tables. This is my problem.
    I think I am creating the tables wrong.
    I have 3 table and am putting the memberNo from the customerdetails table into them.
    What I am hoping to do is:
    The user opens the form and adds a record--the memberNo is automatically generated and put into the 3 tables and the user fill out the rest of the forms.

    I keep getting the error --see attachment.

    Hope you can help.

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms and linking tables (Access 2000)

    Something isn't working properly. Your subform record has a MemberNo of 0 rather than 450, which is what appears on the main form. Did you set the Master and Child links between the parent form and the subforms?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms and linking tables (Access 2000)

    Hi Charlotte,

    I am had lots of problems with linking. So I recreated the database.
    I will try and attach a copy
    Attached Files Attached Files

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Forms and linking tables (Access 2000)

    Is sounds as if you are having a problem with the primary keys in the product consumption and cooking frequency tables. What are the primary keys in these tables? For example, if the cooking frequency is one and cooking frequency is set as the primary key, then you will only be able to enter one once even if many customers need to enter one.

    To get around this, set the autonumber as the primary key and then link to cooking frequency.

    HTH
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms and linking tables (Access 2000)

    If you have one-to-one relations between the three tables, you might as well create one table with all the fields from the three tables combined. I see no special advantage in having three tables. Having one table avoids the problem of creating the linking ID in the other tables.

    Whether you have one or three tables, it's useless to have a continuous or datasheet subform (as in the picture in your first post in this thread), because the subform can contain only one record corresponding to the record in the main form.

    Instead, I would use a tab control to arrange the fields in a clear way - you can put related fields together on a tab page.

    If you still want to use subforms, make them standard single-record forms and make sure that the master and child linking fields are set correctly. Then, the link should take care of entering the correct value for the ID in the subforms.

    If you want to attach a database to a post for others to look at, you must (compact it and) zip it. You can't attach .mdb files directly. The WinZip file must be less than 100 KB.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms and linking tables (Access 2000)

    Hi HansV,

    This is the one I was working on: before trying to recreate everything.
    The error message I get when I try and enter a new record.
    Firstly it says "You cant go to the specified record" and then when I try and close the form: I have attached a doc with details.
    Very Frustrating.......

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms and linking tables (Access 2000)

    Sorry to be critical, but this design is destined to cause trouble.
    You have 9 tables, each with an AutoNumber field as primary key that also functions as the link field between the tables. This is very vulnerable, as you have found. If you ever have a problem, the numbers will get out of step. And since you can't edit AutoNumber fields, you would have no way of repairing the values.

    As I remarked in my previous reply, I see no advantage in having all these separate tables. You've had to create a query that combines them all anyway, so why not use one table with all the fields instead of 9 tables and a query? You would avoid the problem of having to keep track of the keys in all the tables.
    Later on, you can create queries that select only the fields concerning a certain subject, for instance Product Consumption.

    On the other hand, if you want to keep the separate tables, you should do the following:
    <UL><LI>Use an AutoNumber field in the main table Personal_Details only.
    <LI>Delete the relations between the main table and the other 8 tables (otherwise you won't be able to take the next step).
    <LI>Change the primary key field in the other 8 tables to Numeric (Long Integer).
    <LI>Recreate the relations.
    <LI>Base the main form on the Personal_Details table.
    <LI>Remove all controls bound to other tables from the form.
    <LI>Create a subform for each of the 8 other tables.
    <LI>Place the subforms on the main form. They should be linked to the main form by the Personal_Details_ID field automatically. If not, set the master and child link fields yourself.[/list]Now, the links between the main form and subforms will take care of entering the correct key value in the subforms.

    I have attached a very simple zipped Access 97 database that demonstrates the subform approach. It is meant as an example only. I have paid no attention to aesthetics at all, and I have created only a few of the tables and fields.

    A final note: you have used field names with spaces and apostrophes in them. Although this is allowed in Access, it's better to avoid this - it'll save you work later on. When you're writing code, you must surround field and control names that contain spaces in square brackets, e.g.
    [Last Name].SetFocus
    whereas you can refer to a field or control name without spaces etc. directly:
    txtLastName.SetFocus

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms and linking tables (Access 2000)

    Thank you, Thank you HansV.
    It all made sense.
    Did you attach a zip file - because it didn't appear - I would be very interested in viewing.

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms and linking tables (Access 2000)

    Oops - probably fell into the trap of previewing and not adding the file name again. Sorry about that.

    Here is the promised attachment.

    Regards,
    Hans
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms and linking tables (Access 2000)

    Hi HansV,

    I have setup the database and form, everything is working except for one last thing, the tab order.
    I have a main form (personal details) with 8 subform on it.
    I have setup up the tab order in each form and they work.
    I have setup the tab order on the main form to go through some fields then a subform and then onto the next subform -
    The problem occurs here - it will not tab from subform to subform.
    It will tab throught the master form fields, and onto the first subform, tab through all the first subform fields, and then go back to the master form fields again.

    However If I click on each individual sub form section it will tab through its fields.

    Any ideas?


    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms and linking tables (Access 2000)

    Hello Kerrie

    As I noted a few replies ago, I don't see the need for subforms, but anyway...

    A workaround without any code is to use Ctrl+Tab to get out of a subform. Tab will cycle within a subform, but Ctrl+Tab will move you to the next control on the main form (the next control may be a subform).

    If you're willing to write a bit of code, you can use the OnExit event of the last control in each subform to set focus to the first control in the next subform.

    Example: let's say that the last control (in the tab order) on the first subform is txtYearChild6, that the second subform's control name is sbfGrandchildren and that the name of the first control (in the tab order) on this subform is chkGrandchildren.

    Create an event procedure for the OnExit event of txtYearChild6:

    Private Sub txtYearChild6_Exit(Cancel As Integer)
    Parent!sbfGrandchildren.SetFocus
    Parent!sbfGrandchildren!chkGrandchildren.SetFocus
    End Sub

    Note (you probably have done this already): you must set the Cycle property of the subforms to Current Record.

    HTH,
    Hans

Posting Permissions

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