Multiple input field or Subform?

[bhunji42]

Hi,

I'm fairly new to using Access but am hoping someone can point me in the right direction.

So right now I have one big table(this is an issue for some other time, I'm just glad we aren't using excel any more) but for what I need to do I'm pretty sure I'll need to have a second table linking it with a foreign key to the first.

I currently have a form that has an input field for each item in table one. I need to add a new optional field to the form that can accept more than one entry. Each entry into this new field will be mostly unique but may have some(very few) duplicates between records and there may be upto 20+ entries in the second table but still only referencing 1 record in the 1st table.

What is the best way to add an input field onto the form, that will allow multiple entries for 1 record?
Is this where a subform could be used? or is there another way I should do this?

Thanks for any advice you can give me.

 

[dhookom]

Use a continuous subform to enter the related/child records.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Subform based on a new second table with a column that relates back to the primary key in the first table. In the second table this is called a foreign key. Typically you then set the Link master and child fields properties for the subform to these primary key and foreign key values (if you use the builder or three little dots button next to the property, it is easy).

Now would be a good time to fix your table name if it is literally named "Table1"... Once you start using code that uses the table name, it becomes less and less trivial to change. I don't expect you are there yet but worth doing now.

 

[bhunji42]

Okay so I created a simple table for my second table with only 3 fields, PK_ID, FK_ID, and Data_Field (note these aren't the actual names just trying to keep it simple for clarity)
The FK_ID appears to be correctly linked to my table1's primary key, when I load a pre-populated entry the correct data shows up in the sub form when I load a record through the main form.

I'm not sure I set the subform up correctly though, as I am having the following issue.
I can successfully edit any pre-existing entry but I receive an error if I try to add a new entry and if a new entry does somehow get created it isn't attaching a FK_ID to match the Forms (table1's)primary key...

I tried setting up the subform through the wizard not sure if this was the best thing to do and modified it to be continous once it was created.
During the Subform creation I only selected the Data_Field from the table. so only it is displayed, should I be including the PK_ID and FK_IDs in the subform?
How can I have it auto populate the FK_ID to match table1's primary_key for the record I am adding? what if it is a completely new record does that change anything?

What am I doing wrong?

Also is there a way to make the subform popup in a new window or something rather than displaying it on the same form? there are a lot of fields on the main form and any space I can save would be great.

 

[lameid]

Your subform has two properties... "Link Master Fields" and "Link Child Fields". What are these set to (see my first post)?

No you do not need to have the controls to get this to work.

Also is there a way to make the subform popup in a new window or something rather than displaying it on the same form? there are a lot of fields on the main form and any space I can save would be great.

This is an entirely different method. In this case I would have a control for the FK, either visible or not visible and probably disabled with it's default value set to the 'main' form or the form for 'Table1' and have a query as the recordsource that has criteria from the mainform . Sometimes this is necessary with subforms but once you have a couple subforms under your belt, you'll know this situation when you see it. Generally the link master and child fields proerties simply work.

 

[bhunji42]

My Link Master Fields is set to the Primary ID in table 1 and
Link Child Fields is set to the FK_ID of table 2

The linking does appear to be working for prepopulated data (I added a couple records directly to table2 and typed in a FK_ID for each entry), I just seem to be having trouble when I want to create new entries through the subform.

When I try to type a new entry I get a simple "Cannot perform this operation" error message. Once I click ok to the message I can continue typing, but then I receive a "You must enter a value in the 'FK_ID' field." error message.

How do I get the subform to autopopulate the FK_ID field for new entries?

 

[lameid]

FK_ID should just be populated.

It sounds like you are going to a new record in the main form that does not have a Primary Key yet so there is nothing for the FK_ID to pick up.

Both the main form and sub form can have record navigators, record selectors and buttons for the same. The first two can be turned on and off by properties. Buttons, assuming you used the wizard and didn't do anything fancy, will execute against the form (main or sub) they are on.

I'm not sure how hard this nuance is to catch or if I explained it well enough?