Add Attachment to form

[Catalyst159]

I have a linked table called "dbo_proble ms", and a form called "Problem Records Details". I have created a table called "Attachment s". The "Attachment s" table contains the following fields: ID, Problem_ID, and Attachment.

ID is Primary Key (DataType=AutoN umber)
Problem_ID is (DataType=Numbe r) (Indexed Yes No Duplicates)
Attachment is (DataType=Attac hment) (Required = No)

The form "Problem Records Details" has an Unbound object called Attachment. I would like the user to be able to insert an attachment from this form. I have tried the following as the control source:

SELECT [Attachment] FROM [Attachments] WHERE [Problem_ID]=Forms![Problem Records Details]![ID];

Only the attachments where Problem_ID of the attachments table equals the ID of the record displayed on the "Problem Records Details" form.

This does not seem to be working. Any help, ideas, or insight would be greatly appreciated.

Thank you in advance.

Regards,

Catalyst

[Rabbit]

If Problem_ID in the attachment table is unique, why did you put it in its own table? If it's going to be one to one anyways, just put it in the same table.

[Catalyst159]

The dbo_problems table is a linked table. So I can not create a new Attachments field.

I want to keep all attachments in a different local table called Attachments. The Problem_ID field in the Attachments table should reference the ID field of the dbo_problems table. I am not sure if I explained it ok. I just realized however now that you made the comment that I would probably need to dump the ID field data from dbo_problems into the Attachments table, right ? Let me know if I am still confusing.

[Rabbit]

It's not necessary to insert all the ids from the problem table. You could use an outer join query as the source for your form and see if that's updatable. If not, you'll just need to create a separate form that you can call when you want to attach a file.

[Catalyst159]

What do you mean, see if that's updateable? And how would you go about using an outer join query? What would the outer join query look like ?

[Rabbit]

When you use a join query, the fields are not always updatable. The only way to know for sure is to do it and see if you can update the fields you want. An outer join query looks like this

SELECT*
FROM Table1
LEFT JOIN Table2
ON Table1.UniqueID = Table2.ForeignKey

[Catalyst159]

So it would be like:

SELECT * FROM dbo_problems LEFT JOIN Attachments ON dbo_problems.ID = Attachments.Problem_ID

[Rabbit]

Yes, and if the attachment field is updatable, then you can just bind the control to that field. So you don't have to use code to try to update it.

[Catalyst159]

When I right click on the paper clip in the form everything is greyed out. Manage Attachments is greyed out. Any Ideas?

[Rabbit]

That means it's not updatable. What you'll need to do instead is create a separate form for the attachments and then include it on the main form as a subform.

[Catalyst159]

I am not sure how to include a separate form for the attachments on the Main form as an attachment. Could you explain how to go about doing this?

[Rabbit]

Create a new form using the attachment tables. Save it. Go to the main form. Put a subform control on there. Point it to the form you just created.

[Catalyst159]

Ok. Let me give it a try.