locked
How to enter data in related fields Access 2010 RRS feed

  • Question

  • I'm transitioning from another relational database (4th Dimension) to Access 2010, and trying to understand how entering new records in related fields works.  To make things simple, my database has three tables:

    1. Aircraft

    2. Events

    3. Liveries

    Table 1 & 2 are related through a construction number field one to many

    Table 2 & 3 are related through a liverycode field many to one

    In my previous database when the user entered data into one of the related fields from the Table 2 side one of two things happened:

    1. if the entered data matched a record in the related table(s) other data from the related table would appear automatically in the form (which is a nice confirmation that the relation is actually working).

    2. if the entered data did not match a record in the related table, a dialog would come up warning the user that no record exists and if they wanted to create a new record in the related table.  This would prevent orphans or allow the user to fix a typo.

    I've been trying to replicate this behavior in Access 2010 with little success. I have been able to create a form that displays related data from another table, but none of those fields can be edited from that form (I would have to load another form to modify or add to the related table.

    Although I haven't delved into VBA, I assume I could create an elaborate form that used variables to collect all the data with code to manipulate the data into the right tables.  This does seem a bit extreme.  Isn't there an easier way to deal with this scenario?  I appologize if I'm asking a really basic question, but after a week of almost finishing an Access book, I still haven't come across clues on how this scenario actually should work.  TIA for any clues....

    Wednesday, September 5, 2012 3:29 PM

Answers

  • Although I haven't delved into VBA, I assume I could create an elaborate form that used variables to collect all the data with code to manipulate the data into the right tables.  This does seem a bit extreme.  Isn't there an easier way to deal with this scenario?  I appologize if I'm asking a really basic question, but after a week of almost finishing an Access book, I still haven't come across clues on how this scenario actually should work.  TIA for any clues....

    Hi TFLeonard,

    I am still using A2003, but for this discussion it is not so important.

    Access is good in subforms, lookup tables and comboboxes, but in my opinion poor in handling relations. That is why I worked on automating relations, together with generalisation of forms.

    I think the result is about what you need, but I have to verify that first.

    So I will try to sketch my way of working.

    You have a 1:n relation Aircraft – Events over construction number, and a m:1 relation Events – Liveries over liverycode. That means that the Events contains a FK to Aircraft and a FK to Liveries, and eventually some other fields to characterize that event record, let us say a date field. In an Events form I would see the date, some information on the specific Aircraft and some information on the specific Liveries.

    On adding a new record to Events opens a New-form with 3 controls: one for the Aircraft, one for the Liveries and one for the Date. The Date can be predifined, typed in or picked from a build-in calender. On typing in the Aircraft control, a form is opened with all the “available” Aircrafts (depending on certain selection rules), using a kind clairvoyance principle, from which you can choose one. If you choose one, the construction number is filled in an invisible control on the previous form and a readable description of the Aircraft in the Aircraft control. If the wanted Aircraft is not yet available, you can – depending on a.o. authorization – add a new one, which then after storing automatically behaves as the selected record and so filling the Aircraft control. In the selection form you can do any sorting on any column, searching, or whatever to quickly find the right record.

    Exactly the same happens with the Liveries control. And in fact this always occurs in any FK relation. The adding of new records is a recursive process that goes a couple of levels deep.

    Double clicking on the Aircraft control gives all the information about that Aircraft, and double clicking on the Liveries control gives all the information about that Liveries.

    A New_form is an unbound form. After the “Store” click a new record is written in Events with its own PK, the entered Date and the entered FK’s for Aircraft and Liveries,

    If you start from the Aircraft (continuous) form, you can click on a Events-button. This opens the Events form, but preselected the Events belonging to the Aircraft (as with a subform). Then adding a new Events record gives already the Aircraft filled in. It works in exactly the same way if you start with the Liveries form, and then going to Events.

    Any relation can be traversed bidirectional.

    If this is the way you want to achieve your goal, well, then roll your arm sleeves, learn VBA, and keep discussing.

    Imb.


    • Proposed as answer by Dummy yoyo Monday, September 17, 2012 2:16 AM
    • Marked as answer by Dummy yoyo Wednesday, September 19, 2012 2:46 AM
    Wednesday, September 5, 2012 10:25 PM

All replies

  • It sounds like you are not using a main form containing the parent records and a sub form with the child records. That is all you really have to do. There is no VBA involved.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, September 5, 2012 3:36 PM
  • you can do all of this without vba

    create a form with your "1" table (aircraft)

    create a form with you "many" table (events)

    insert the events form into the aircraft form to make it a subform (the events form)

    with the subform selected, open properties, select the data tab and enter the link child and master fields

    that's all you need to do.  You cannot/should not edit the foreign key field in the child table because it is linked to the master

    when you add a new master record using the form, and then you add other data in the sub form, the foreign key is automatically created

    as for your many to 1 relation, I'm guessing that this is a look up table - this can be done in the subform using a combo box.  set up the key field in the events table that is related to the liveries table using a combo box.  set your combo box rowsource to the liveries table or query based on that table.  Bind the combo box to the liveries primary key, set the field width for that column to 0, and put the description column first and in alpha order.  This way the user can type in a livery description, the description will be displayed, but the key is the data that is saved to the events table


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Wednesday, September 5, 2012 3:47 PM
  • Thanks, the fog is clearing a little...  Your example is actually coming from the opposite side of the relation since in my database most of the data is event data entered into the Events table.  In respect to relations, its the Event table that is always the many side and relates to Aircraft and Livery.

    I tried adding subforms for Aircraft and Liveries to the Events form and aside from looking cumbersome. the links work.  The problem I have now is if the user enters a new record with, in this example, a new construction number, Access says "You cannot add or change a record because a related record is required in table 'Aircraft'".  At that point, would the use have to stop the editing of the Event record, go to the Aircraft table and create a new record with that construction number?  I was hoping the user could simply confirm that it was a new construction number and enter the aircraft info through the original form.  The problem will occur with the livery table also since if there is no matching livery the same message will come up.

    Regarding lookup tables and combo boxes.  This doesn't seem practicle due to the large number of records in the foreign tables.  Ideally, the user would start typing into a the linked field and access would automatically present data from the related field that matched (in some programs this is called clairvoyance) a little like outlook completing an e-mail address based on previously used addresses.

    Regarding my cumbersome comment above, currently using the subform approach requires the look of the parent form to have the subform data in a sequestered frame.  This is OK for reference, but doesn't help with data entry.  Is there a way to just add related fields directly on the parent form and access allowing new data entry as well?  I can display related fields but the user can't enter new related data (similar to the problem I mentioned earlier).

    I still feel a little in the dark about this and thank you very much for helping me understand this.

    Wednesday, September 5, 2012 5:22 PM
  • your parent table is the "1" and the child table is the "many"

    since you don't have a record in the aircraft table you cannot add a child in the events

    if the aircraft and livery as just used as "lookup" tables, then they should be set up as combo boxes (rather than subforms).  with access 2010 you can set up the form to open to add an aircraft if none exist in the list using the "list items edit form" property under the data tab.  This will allow you to add an aircraft that does not exist so that you can use it in the events table.


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.


    • Edited by -suzyQ Wednesday, September 5, 2012 5:49 PM
    Wednesday, September 5, 2012 5:48 PM
  • OK, I'll try it as a lookup table, but how should I display the rest of the data in the parent table when I'm using the child table's entry form?
    Wednesday, September 5, 2012 6:19 PM
  • You might find Crystal's Access Basics eBook helpful. It's free and concise. It can be downloaded from my site at http://www.thatlldoit.com/Pages/AccessBasics.aspx

    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, September 5, 2012 8:03 PM
  • OK, I'll try it as a lookup table, but how should I display the rest of the data in the parent table when I'm using the child table's entry form?

    in your combo box you can include all the columns you want to see on the form

    change the number of columns property accordingly

    in other text boxes you can refer to the combo box column to display the data

    EDITED: Whoops - we were doing this without vba so instead of

    me.txtBoxName = me.ComboBoxName.Column(0)

    column indexes start at 0 and continue to column count -1

    put =comboBoxName.column(0) - or whatever the appropiate index is in the textbox's data source property 

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.


    • Edited by -suzyQ Wednesday, September 5, 2012 8:14 PM
    Wednesday, September 5, 2012 8:11 PM
  • Another wrinkle.  In the case of the Liveries table, I have two picture fields that I want to display once the link is made through the common field "Livery Code".  If I'm not mistaken, the combo box will only display text in columns, whereas I'd like to show the logo and livery in different places on the Event Table's form.  Is this possible?

    As a side note, I converted major portions to webpages and all this relational stuff happens with links in the HTML code.  If Access can display the results of hyperlink addresses (I know you can save the hyperlink so that the user can click on it) rather than the actual address (the link to the logo .jpg file, then it might save some trouble.  Is this a better way to accomplish this, at least regarding pictures?

    Wednesday, September 5, 2012 8:33 PM
  • Although I haven't delved into VBA, I assume I could create an elaborate form that used variables to collect all the data with code to manipulate the data into the right tables.  This does seem a bit extreme.  Isn't there an easier way to deal with this scenario?  I appologize if I'm asking a really basic question, but after a week of almost finishing an Access book, I still haven't come across clues on how this scenario actually should work.  TIA for any clues....

    Hi TFLeonard,

    I am still using A2003, but for this discussion it is not so important.

    Access is good in subforms, lookup tables and comboboxes, but in my opinion poor in handling relations. That is why I worked on automating relations, together with generalisation of forms.

    I think the result is about what you need, but I have to verify that first.

    So I will try to sketch my way of working.

    You have a 1:n relation Aircraft – Events over construction number, and a m:1 relation Events – Liveries over liverycode. That means that the Events contains a FK to Aircraft and a FK to Liveries, and eventually some other fields to characterize that event record, let us say a date field. In an Events form I would see the date, some information on the specific Aircraft and some information on the specific Liveries.

    On adding a new record to Events opens a New-form with 3 controls: one for the Aircraft, one for the Liveries and one for the Date. The Date can be predifined, typed in or picked from a build-in calender. On typing in the Aircraft control, a form is opened with all the “available” Aircrafts (depending on certain selection rules), using a kind clairvoyance principle, from which you can choose one. If you choose one, the construction number is filled in an invisible control on the previous form and a readable description of the Aircraft in the Aircraft control. If the wanted Aircraft is not yet available, you can – depending on a.o. authorization – add a new one, which then after storing automatically behaves as the selected record and so filling the Aircraft control. In the selection form you can do any sorting on any column, searching, or whatever to quickly find the right record.

    Exactly the same happens with the Liveries control. And in fact this always occurs in any FK relation. The adding of new records is a recursive process that goes a couple of levels deep.

    Double clicking on the Aircraft control gives all the information about that Aircraft, and double clicking on the Liveries control gives all the information about that Liveries.

    A New_form is an unbound form. After the “Store” click a new record is written in Events with its own PK, the entered Date and the entered FK’s for Aircraft and Liveries,

    If you start from the Aircraft (continuous) form, you can click on a Events-button. This opens the Events form, but preselected the Events belonging to the Aircraft (as with a subform). Then adding a new Events record gives already the Aircraft filled in. It works in exactly the same way if you start with the Liveries form, and then going to Events.

    Any relation can be traversed bidirectional.

    If this is the way you want to achieve your goal, well, then roll your arm sleeves, learn VBA, and keep discussing.

    Imb.


    • Proposed as answer by Dummy yoyo Monday, September 17, 2012 2:16 AM
    • Marked as answer by Dummy yoyo Wednesday, September 19, 2012 2:46 AM
    Wednesday, September 5, 2012 10:25 PM
  • Yes, you've described the process well.  Therefore, if I understand you, these kinds of actions cannot be done without having VBA handle the data and saving of the record?  I'm willing to work my way up to this, since I haven't gotten to the programming chapters in the book I'm reading (Access 2010 Bible).

    Since learning by example is great, do you know of any example databases which demonstrate what you've described?

    Thursday, September 6, 2012 2:38 PM