Transfering Data Through a Form

[Guest]

I have two tables on for a box number and one for file information.
The tables are set up like so:
tblBox
BoxNumber
PickUpDate
tblFile
FileType
FileNumber
BoxNumber
There is a one to many relationship for Box number.

What I need to do is set up a form that allows the user to assign files to a
box number.
My thoughts ran something like this:
In a form obtain a box number by assigning a pick-up date.
Enter into an unbound text box “file type†and “file numberâ€.
Click a button called “Add to Boxâ€.
Ideally in a datasheet at the bottom all files added to the box would be
displayed.

I cannot figure out how to make this work, if it even can work. I may be way
off base.

Does anyone have any suggestions?

 

[Guest]

Hi, Jen.

The classic strategy for a one-to-many relationship is to use a main form
based on the one side, and an embedded subform for the many side, linked by
their common field, in this case, BoxNumber.

Its also a good practice to have a unique record identifier. I nearly
always use an AutoNumber for my primary keys. For your case, BoxNumber
sounds like it would be fine as the PK, but you should add a primary key for
the tblFile table.

Create the main form based on tblBox, including the BoxNumber field. If you
use an AutoNumber key, it's your call as to whether you include it or not on
your form. Create a continuous form for tblFile, leaving *out* the BoxNumber
field. You don't need it, since the BoxNumber will be displayed on the main
form.

Once both are created and saved, open the main form in Design view, and
resize the window so that you have access to the database window. Then
simply drag the subform from the Forms tabbed page and drop it into the main
form. If you have Wizards toggled on (recommended), it will make a guess as
to the linking fields. If not, set the subform's Link Master Field and Link
Child Field properties both to BoxNumber.

The form will display one BoxNumber at a time with all the associated files
displayed in the subform. Because of the link, whenever you add a new file,
the BoxNumber is automatically stored in the underlying table.

HTH
Sprinks

 

[Guest]

I would go about it differently. I would create a query; something like this:

SELECT tblBox.BoxNumber, tblBox.PickUpDate, tblFile.FileNumber,
tblFile.FileType, tblFile.BoxNumber
FROM tblBox INNER JOIN tblFile ON tblBox.BoxNumber = tblFile.BoxNumber;


(to try it, you can cut and paste into a new query if you like)

I would then create the form based on the query. You will be able to add to
add to both tables the information you wish. You can also scroll through the
records to see them all.

hth,
JMorrell

 

[Guest]

Thanks unfortunately neither of these solutions work in this particular case.

The thing I forgot to mention is that tblFile is already populated. So in
essence, I need to assign a box number to existing records. Basically the
user will have a box of files, will get a box number and needs to assign the
files in the box to that number.

That is were I am having my issue is how to assign existing records to the
new record in the easiest fashion for the end user. (Namely a temp so I have
no clue what there computer skills will be if at all)