attachment image update based on combo box selection

[kjohn1210]

hello all,

I have two different tables (A_contracts, B_contracts). on A_contracts table - name field, attachment field. on B_contracts table - name field, attachment field.
on my form i have 2 combo boxes:
1st combo box displays 2 options (A_contracts or B_contracts)
2nd combo box displays a list of names from either A_contracts table or B_contracts table, based on your selection from 1st combo box.
What i need to do is find a way to display an image field (preferably an attachment cntrl) that allows a picture to change as 2nd combo box names are selected in order to display their individual contract form.(each person has a separate different contract, attached in the table).


There will be over 1,000 attachments so i need to find a solution that does not includes writing a code for each individual.

Thanks in advance

[June7]

Why do you have two tables for similar data?

Have one table with another field for Category (A or B).

Embedding images in db can chew up Access 2gb file size limit.

Are you storing multiple images in attachment field?

[kjohn1210]

I have two different tables because they contain two different sets of data. For the purposes of how i need to store my data, I have to keep both types of data separated into entirely different tables. they will not be used in conjuntion with each other. im just trying to create a database that allows you access to both types of my stored data.

[June7]

If you want the images embedded in table, life will be much simpler if you use one table with category field and filter as needed for each category. The difficulty is because the images are in separate tables and this means changing the form RecordSource property to pull records from table determined by the 1st combobox.

Otherwise, store the images external and use code to dynamically set the ControlSource property of Image control to the image file path.

Are you aware embedded attachments can chew up Access 2gb file size limit? Will there be multiple files in the attachment field?

[kjohn1210]

There is just one attached image per record. I may have to come up with a different method for this if i will have issues with size limits.

I already have my first combo box set up to change the records source of the second combo box so i figured that the attachment field would be a similar process.
If I am able to, would it be possible to bypass the problem of having two tables with building a query that merges the data together some how?
Otherwise I am open to other solutions. I would just like to get this working in any manner....

(ive also thought of maybe doing a switch board where i use 2 separate forms. each form having only one combo box on it and having the corresponding image show. so i would only be using 1 table for each form. But even in this instance i can not get the attached images to work properly)

[June7]

I see options as:

1. One form and one RecordSource. A UNION query can combine the two tables into a structure that would be the same as if there was only one table to begin with (so why not just have one table?). However, I don't know if the attachment field will still work from a UNION dataset - never tried. UNION queries are not editable (no data entry/edit).

2. One form and code that changes the RecordSource property.

3. Two forms and code to open the appropriate form. Maybe a switchboard will work - never used one, don't like.