Solved Can a form open an attachment field from another table

[chacham]

I was asked to add a button that opens another form based on the current record. No problem. I just copied the button and code from another form that did the same thing. The user came back to tell me that the entire purpose of opening that second form was to click a button that opens the attachment field and asked if i could have the button open that directly instead.

The first form's record source is a query. The second form is set to a table. The first form opens the second form by passing it an id to a specific record. The button on the second form opens the attachment field for that record.

Can the first form open the attachment field? If not, can the second form be opened and show the attachment field, either directly or by programmatically clicking that button?

 

[Pat Hartman]

1. you should not be holding the pictures/documents as attachments. That causes database bloat and your db will rapidly become unweildy. Just store the path to the image/document
2. Attachments are shown with a bound control. To show an attachment from a different table, you can change the RecordSource of the form to join to the second table and select THAT attachment field. You probably want to use a left join in case data doesn't exist in the right side table. Otherwise, you can open a form with just the attachment field. Don't forget to use the where argument so the form opens to the correct record.

 

[chacham]

1. you should not be holding the pictures/documents as attachments. That causes database bloat and your db will rapidly become unweildy. Just store the path to the image/document
2. Attachments are shown with a bound control. To show an attachment from a different table, you can change the RecordSource of the form to join to the second table and select THAT attachment field. You probably want to use a left join in case data doesn't exist in the right side table. Otherwise, you can open a form with just the attachment field. Don't forget to use the where argument so the form opens to the correct record.

1. Thank you for the advice. Unfortunately, its not my database. I'm just helping with some of the code. Fwiw, eventually, we should be moving to SQL Server.

2. Ooh. I'm checking that out right now.

 

[Pat Hartman]

SQL server can hold much larger tables. HOWEVER, SQL server does NOT support the attachment data type so you/he will have to rebuild the parts of the app that use attachments later rather than sooner.

A word of advice, SQL Server et al do NOT support all Access data types. The Attachment, Multi-Value field, Hyperlink, the add only notes (I don't remember what it is called) are the four that come to mind. So, if some RDBMS other than ACE is in your future DO NOT USE these data types.

 

[chacham]

Thank you. I already wrote a migration routine to transfer the data, and used a child table to hold the attachments. The routine works nicely, though it took a few tries to get the attachment field to be converted for transfer. Too bad that process was put on hold for the time being.

 

[chacham]

change the RecordSource of the form to join to the second table and select THAT attachment field.

Thank you! Works perfectly.

 

[Pat Hartman]

You're welcome.