Populate Attatchment Field based on Combobox Selection

[ZLHysong]

Hi all,

I am creating a Database to track product details and inventory for items we purchase.

I have several Tables and several Forms already working properly, except for one thing.

I have a Form called "Lights", and it contains a ComboBox called Light_Name and 6 TextBoxes: Manufacturer, Model_Number, Cost, Weight, Light_Type, Notes. It also has an Attachment Field linked to the same table.

I am using the Code:

Me.TEXTBOX = Me.Light_Name.Column(#)

to populate the Textboxes with the relevant info from the Light Table.

This works for every Textbox I have.

However I cannot get the Attachment Box to change. And when I try to add code to it I get a debug error. The Attachments are PDFs and I need them to show based on the ComboBox Selection.



I have tried the same code as above, and as I said, it gives me an error.

Any Help would be appreciated.

[June7]

This sounds like duplication of data - poor design. Don't save all the values from the Lights table, just save the key. Then retrieve the related information by joining tables in query.

Programmatically saving an attachment requires complicated code. Would have to save the file to a folder outside the database then save into the other attachment field. Review http://blogs.office.com/b/microsoft-...cess-2007.aspx

However, duplicating the attachment data is just going to bloat your database. Embedding files in an attachment field causes database size to expand enormously.

[ZLHysong]

What do you mean? I clicked the internal tool for an attachment, and when viewing the table, all is well. What is poor about the design, this is one of the simplest tables I have ever worked with... What am I doing wrong?

[June7]

The 'poor' aspect is duplicating data. Unless I misunderstand the nature of the form. It sounds like you are selecting info from a table via combobox and saving that info to another table.

[ZLHysong]

In a way, yes, but not exactly lol... I have tables made for different Products, Lights, Floor Plate, Angle Steel, Etc. And I made a form to easily navigate and view the details for these products.

The Form is as follows:

ItemName - ComboBox
Size1 - TextBox
Size2 - TextBox
Thickness - TextBox
Weight - TextBox

And so on.

The ComboBox simply selects the Item Name and fills the TextBoxes with the Relevant Data. All it is intended to do, at the moment, is view a single item instead of the whole table.

Eventually we will be using it as a Selector to add these items to a Bill of Materials, but that is not implemented yet.

In the Table for Lights, I used the Field Selector and selected Attachments. Using the Code above, everything works flawlessly except this field.

Does that make things clearer?

[June7]

So you are not saving the values, just displaying them in unbound textboxes? You intend to use this combobox to select items for 'Bill of Materials' table?

You have products split into numerous tables? These tables vary in structure? This will surely complicate the selection of products for 'Bill of Materials'.

The Attachment field has only one file (an image type) for each record?

Instead of VBA code to set the value of these unbound textboxes, options:

1. Expression in ControlSource property
= Light_Name.Column(#)
However, this will not work any better for the attachment field than the VBA method because combobox can't 'hold' the image data, it only shows a count of how many files are in the attachment field.

2. Include the product table in form's RecordSource joined to 'Bill of Materials' table. JoinType 'Include all records from 'Bill of Materials' ...'
Bind combobox to field of 'Bill of Materials' table.
Bind textboxes to the fields of Lights and set them as Locked yes, TabStop no.
For attachment field, drag it from the FieldList to create an Image control that will display the image file (set Locked yes, TabStop no).

If you want to provide db for analysis, follow instructions at bottom of my post.

[ZLHysong]

I understand Step 1. That makes sense to me. However I do not have the Bill of Materials Tables set up yet. All this was supposed to do was: As I select a light or other item, click on the attachment to view the official Cut Sheet for the product.

I am attaching the DB now, but would a Hyperlink to a separate file work instead?

I could not upload directly, as the DB exceeds the maximum file size, but here is a direct MediaFire Link.

http://www.mediafire.com/?jrbnavym7xji8es

EDIT: I set the ControlSource as you said, but it does not repopulate the Textboxes... They are just blank. Can you go into more detail on this?

EDIT2: I got THAT part working now. But I still do not understand part 2.

[June7]

I can't download from fileshare until late tonight. Did you run Compact & Repair and zip db? Up to 2mb zip allowed as attachment.

I expect hyperlink will have same issue with combobox.

Need to bind the form to records.

Option 2 is just binding the form to a data source that is a query joining two tables so that all related info is available for display. Since you have not yet created the 'Bill Of Materials' table, this won't be possible. Building forms before a workable data structure is 'cart before the horse'.

[ZLHysong]

I tried that, the file is still 4 MB. The Bill of Materials is something that will be created by this form eventually, the Tables containing the Info are already created. If you can download it later I think you will understand better.

[June7]

Size is probably because of embedded files in attachment field. Could copy db and eliminate records, only need a few for testing. Otherwise, my download at home could be quite slow.

[ZLHysong]

StarFlite Systems Projects - Copy.zip

How's that? The Table that has the attachments is the table called Lights.

[June7]

Which form has the combobox involved in the issue?

[ZLHysong]

It is also called Lights, but I removed that field to show the form to my boss. It is a standard Attachment form.

[June7]

You have attached that same image to your other thread. Is this two threads on the same issue? Need to pick one and stick with it.

That form does nothing to address the original issue.

Trying to design forms without the data framework in place is frustrating and non-productive.

I suggest you build a 'Bill of Materials' table and experiment with Option 2, even if the table has only two fields (autonumber and LightRecID). It worked for me.

BOM form RecordSource:
SELECT BOM.ID, BOM.LightRecID, Lights.[Light Name], Lights.Cost, Lights.[Model Number], Lights.[Light Type], Lights.Notes, Lights.Weight, Lights.Manufacturer, Lights.Attachments FROM Lights RIGHT JOIN BOM ON Lights.ID=BOM.LightRecID;

Combobox RowSource:
SELECT Lights.ID, Lights.[Light Name] FROM Lights;

[ZLHysong]

These are two different issues, and I attached the Image to this thread on accident, I apologize.

We have decided to simply create a Hyperlink to the folder that contains the PDF Separate from the DB until I get further along in this project.

I appreciate all the help in this matter, but my hands are tied and my boss wants it that way...