MS Access --> several attachments 1

[Dimst]

Hi,

I am using Ms Access 2000:

Within my mdb i have made it possible for users of the database to search for a file and attach it to the record. Though it works properly, i need to attach several files to the same record.

For example
Record 1
attachment 1
attachment 2
attachment 3

Record 2
attachment 6

Record 3
attachment 7
attachment 8
etc..

I know the code for putting and storing one attachment in the databse (like record 2 in the example); but i don't know how to make a code that makes it possible to store and save several attachments with one record (like the other records in my example).

Thanks in advance for your solution(s)

Dims-

 

[maxhugen]

Sounds like you need a separate table in which to store the attachments, linked to the table that has the 'records' you speak of.

Max Hugen
Australia

 

[Dimst]

Thanks Max,

I am not totally new in "Access-land", but i don't know how to make it happen.

The situation which i described is that the attachment which i already have in my form, is stored in a column attachment in the table. Still a couple of questions:

1) how do i make sure that if one text-object is filled (attachment1), access automaticly search for the next available text-object (attachment2)

2) and same with opening the file, how do i make sure access is about to open the right one?

Thanks for the help!



-Dims

 

[maxhugen]

It could help if you described your table (name, field names & types... at least the key ones). But let's say your table is something like:

[tt]table myRecords
recordID (primary key)
recordName
attachment[/tt]

You would create a second 'child' table, linked to the first one:

[tt]table myRecordAttachments
attachmentID (primary key)
recordID (foreign key)
attachment[/tt]

Next, you'd need to append the 'recordID' and 'attachment' for all records in table myRecords to table myRecordAttachments.

Now you can add as many attachments as you like via the child table.

You could design a form with the main record details from table myRecords, and use a linked subform for the child records, ie the attachments in table myRecordAttachments.

Does this sound like what you need?

Max Hugen
Australia

 

[Dimst]

Thanks Max,

Describing the tablenames would be rather difficult, because it's in dutch

However, the situation you described was exactly what i need. I had thought of something like that a couple of days ago, but couldn't remember it anymore (sometimes i think my brains are letting me down finding me searching to deeply in codes while it can be simple enough)

Thanks again!

-Dims

 

[Dimst]

Question regarding the building of the code for attaching several files to the db.

I have 5 attachment, corresponding with 5 fields:
TB_attachment1 has field attachment1
TB_attachment2 has field attachment2
TB_attachment1 has field attachment3
TB_attachment1 has field attachment4
TB_attachment1 has field attachment5

So far, So good.

attachment 1, has a set of code for attaching and opening the corresponding file
attachment 2, has a set of code for attaching and opening the corresponding file
attachment 3, has a set of code for attaching and opening the corresponding file
attachment 4, has a set of code for attaching and opening the corresponding file
attachment 5, has a set of code for attaching and opening the corresponding file

Is there some way to compress the code? in stead of 5 the same Private Sub...End Sub statements with each a different name?

Maybe the question is vague, but i hope it is clear enough for an answer.

Thanks in advance,

-Dims

"Wearing sunglasses won't help taking my brightness away

 

[maxhugen]

If you are using a second 'child' table now, and a mainform/subform, as suggested above, the subform should be set to Continuous Forms.

Since each record in this subform has only one attachment, you only need the code to add one attachment at a time. You just keep adding more records to add multiple attachments.

Unsure what you've done here:

TB_attachment1 has field attachment1
TB_attachment2 has field attachment2
TB_attachment1 has field attachment3
TB_attachment1 has field attachment4
TB_attachment1 has field attachment5

You haven't added 5 'child' tables, have you?

Max Hugen
Australia

 

[PHV]

Dims, have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dimst]

Thanks Max Hugen and PHV;

for the record:
I know about relational databases, i just couldn't think of a way to write the code. So the easiest way i could think of, was writing the code a couple of times for testing-purposes

Thanks for the answers!


-Dims

"Wearing sunglasses won't help taking my brightness away"

 

[Dimst]

To Max:
regarding the "quote"; what i meant was that the user gets a screen where he can attach 5 files to 1 record; and what i couldn't figure out was how to manage the several attachment, so what i made was that each and every field object (attachment 1 to 5) could be assigned and opened seperately (each field object has their own open and filesearch buttons).

This as explanation. Your posts are very helpfull thanks again.

-Dims

"Wearing sunglasses won't help taking my brightness away"