Populating attachment field based on text box

[justinwright]

Thanks to pbaldy, I solved the first part of the problem here:



https://www.accessforums.net/forms/p...-box-8073.html

Lets say you have four text boxes and one attachment field on your form
One number data type (in table), we'll call it LookupNumber
Second is a number, ReturnNumber
Third is a date, ReturnDate
Fourth is text, ReturnText
Fifth is an attachment field

I am able to have the user specify the lookup number and look up the values with the following code:

Dim db As DAO.Database 
Dim rs As DAO.Recordset 

Set db = CurrentDb 
Set rs = db.OpenRecordset("Select * FROM TrackingTable WHERE [LookupNumber] = " & Me.[txtLookupNumber])

And then using code like:

Me.txtReturnNumber = Nz(rs!ReturnNumber, "")

My question is, how would I populate an attachment field like that?

[RuralGuy]

What sort of attachment are you trying to put "in" the attachment field?

[justinwright]

Most likely the attachment will be either a txt or PDF file (most of the time PDF file of a scanned document). I can require them to just use PDF if that would make it easier.

I have it adding to the attachment field (seperate input form), but I just need something to list said attachments on the look up form, and provide a way for them to double click and open it (the attachments are stored in the attachment field inside the same db).

[RuralGuy]

I'm afraid I am with PBaldy on this one in that I have never used these attachment fields before and so have no 1st hand knowledge to share. I also use the last link Paul supplied to let the user select the file. I guess a screen capture of your Lookup Form might be useful for me to get a better idea what you are up against. You have the attachment in the AttachmentField but don't have a description of it for your form? Is that right?

[justinwright]

Correct, I only have an attachment field on the form thats currently bound to the AttachmentField field in the db (and I know they have to be unbound for it to look up).

Attached is a SS of the form.

[RuralGuy]

What would you like to see on the form you've shown and in what control?

[justinwright]

In the Check # part I want them to be able to type in a number, click the lookup button, and it return the list of attachments (that control on the form is an Attachment control). It already looks up and populates everything else, that's just the last part that I have left.

I want them to be able to list the attachments and double click them to open them if desired.

[RuralGuy]

Are you saying that the Check # has a 1:m relationship with the attachments? That would put the Attachment field in another table with a ForeignKey.

[justinwright]

Uh, I didn't understand much of that ._.

The check number is a field in the DB (but the DB itself has no primary key, I thought about making the check # the primary key but idk what that would do to other things). It just looks up the rest of the fields by the check number (the check number is the first field in the DB).

The attachment field is in the same table, at the end.

[RuralGuy]

How many attachments can each record have?

[justinwright]

Ideally I'd like up to three, as many as 5 if I could

[RuralGuy]

I really think you should consider another table for the attachments with a ForeignKey field that points to the 1 side of the relationship. Then you would display these Attachments in a SubForm in Continuous Form view.

[justinwright]

Do you have a guide or anything handy? That's frankly all Greek to me (I'm still really new at this).

[RuralGuy]

Have you looked at any of the templates that come with Access for ideas and how thay did it? Most of them have a mainForm/SubForm for something that displays a 1 to many relationship.

[justinwright]

Oh, I didn't even think about that. I'll look at it and get back to you on things, thanks