Open Attachment from listbox of 19 items

[tmcrouse]

I have a listbox that displays 19 items when I click the search button. Item 1 is my primary key with item 19 being an attachment. So that would be column(0) and then column(18)

I have a right click option set up for sorting and an edit button so once the user finds the record they want to edit they click the edit button. The entire 1st row in the listbox is highlighted upon open of form.

What I want is if they see an attachment, which will be .pdf or .doc and they are very small and yes I know attachments in DB is not advisable, however this is how the upper folks want it for now until we get our server and of course they all say if Access did not mean for it to be used then why include it in the software programming options........ .....I am not going to argue this point with those that pay me..........wha tever they want I do............. .

So, my problem is, I want an event, maybe a double click would be ok so I can have some sort of query that says ok when the user double clicks and it is on row 8 which would have primary key of 8 and the attachment is there, open that attachment for viewing. Here is what I tried coding for it but it does not do anything other than clear out everything in my listbox.

Private Sub ListQuality_DblClick(Cancel As Integer)
DoCmd.OpenTable "main.attachment", acViewNormal, acReadOnly
End Sub

and tried

Private Sub ListQuality_DblClick(Cancel As Integer)
On Error Resume Next
listbox.RowSource = "select main.attachment" & _
"from main " & _
"where main.mstrID = " & listbox.Value * "'";"
End Sub

[twinnyfo]

tmcrouse,

Welcome to Bytes! Hope we can help you with this.

Attachments in Access are strange and I've come across some irregularities there, too.

Why not have a control on your Form for the Attachment? Then, double-clicking that control will open the attachemnt dialog. At this stage in my knowledge, I know of no way to "open" an attachment directly from Access. Some other giant brains on this site may know. But, from what I understand, you have to open the attachment dialog box first, because there may be multiple attachments.

Please let me know if this gets you pointed in the right direction.

[zmbd]

There are some slight differences in how attacments are handled.
Please provide the version of Access you are using.

[tmcrouse]

Access 2010 is my version

[tmcrouse]

Well here is the thing about the control. Right now if they see an item in the listbox and it has an attachment, they can click the edit button and it takes them to edit form where the attachment can be viewed because I used the attachment paperclip in design. But the mgmnt does not want them to have to do that to view an attachment. I said ok they click the row item anyway and then a button and then can open. 3 steps.......... ........if you want me to add something to open, they click the row item, doubleclick, attachment opens........2 steps.......I know it seems lame but saving that 1 step is like crucial for some odd reason. So, if I place a control on the form, that would take it back to 3 steps, however that might suffice because then they don't have to go to the update......... ..now.......for a control, I am not sure about coding that. sure I could do a button but that button would have to correlate to the row item they select from the list box. so when they click say view attachment button, how would I say ok they have selected listbox row 15, now open the attachment for that primary key 15. The row will always coorelate with my primary key in the database because I have that as part of the query for the viewing of listbox items.

[twinnyfo]

Please explain how you have your form and list box set up and associated with each other. If anything, you could create a subform that had a Parent Child relationship to the Primary Key and the only thing on the subform was the attachment control.

However, it just seems strange that a list box would be 19 items wide, and that is the only thing on the form.

If you provide additional informaiton, we can work through this as best as we can.

BTW, I understand your predicament. I see no difference between selecting an item and then double clicking an attachment icon vs. what they want. But, we'll do what we can to hepp!

[tmcrouse]

The header of the form has 10 menu item buttons. They take users to other forms in the db. The header also has a search function and a textbox so they can type whatever in, click the binoculars and it will filter down to what rows in the db they are looking for. I know this many items is a lot to look at and believe me playing with those col widths was a pain but this is how mgmnt wants the layout The row source is a query that grabs them all and bound column is the PK. I was thinking that since in this listbox I put in attachment and you cannot double click it because it is an attachment, perhaps I could get rid of that from list box and when the form opens, have a function where the attachment box I place next to the listbox populates automatically. Then when the user see's an attachment and they highlight the row they are interested in editing, it will see they select say row 5 and it will automatically highlight row 5 of the attachment box. I can query the row source there again to include the PK and just set its width to zero. But how do I get the listbox to work with the attachment box? This might be a complete dumb way to do this........I really hate attachments in DB's. It is so much easier to SharePoint them and point to hyperlinks but again mgmnt wants it this way until we switch to a SQL Server and then I will set it up so all attachments go to file in SQL Server and point to that file for them to open.....But that is much further down the road.

[twinnyfo]

Make a tiny subform with just an attachment control on it. Embed that in the main form. Master child fields will be the PK. When someone clicks on the list box record, the attachment will be shown in the subform. Not sure if I am making sense, but what your bosses are asking for may be how they want it, but is senseless from a db perspective. Why can't they have a display of all the same items in the list box, but in a neat and tidy format?!

[tmcrouse]

Well they are in the listbox. The attachment too. But the listbox does not allow a person to click on the attachment because it is not an attachment field. Maybe I need to change my listbox to datasheet view? Let me chk that.

[tmcrouse]

Wait, guess I cannot do that, but I bet I can do a subform for this instead of placing unbound for list in the detail section and the subform I can do the listbox and since subform I could say datasheet view?

[tmcrouse]

Nope that did not work. The listbox still displays as a listbox

[twinnyfo]

It sounds like your entire record is in the list box. Is this correct?

If so, then you could use a datasheet view, for the entire record. Then the attachment field would indicate the attachment and the user would double-click that to see the attachment(s).

This would give an almost identical view to a list box, and unless there is some driving need to use a list box, there is no advantage.

[tmcrouse]

I will try that..........

[tmcrouse]

UGH. Now I know why I have a listbox, cuz my code which is this is the sort and filter and search is all listbox related........ ..........that would me 100% recoding......U GH....I hate projects with required fast turn arounds. I am not a VBA Access person. I am SAS and thought that was what I was hired for but then they said oh no after the fact they want cheap.

[tmcrouse]

I am about to tell them it cannot be done and just click the update. I am not about to spend another weekend recoding everything