Excellent video! Thank you so much for explaining this concept! I sent 5 hours reading to try and understand it, but learned nothing. Your 35 minute video was what I needed!
I want to Thank You, Thank You, Thank You for this tutorial! I spend half a day trying to figure it out by myself and I just wasn't getting very far. But now, it all works the way I wanted to. Thanks and God Bless. :-)
My name is Brandon Wilson and I just watched your video on 'VBA for Working with Access Attachments', but unfortunately I could not find the code so that I could try it out; is there some way that you could post it again, because it was really good and clearly explained what you were doing?
This is Brandon again, and I was just wondering if you might attach a link directly with the 'VBA for Working with Access Attachments' video itself, because it will make it easier to find and copy.
Thank you for this BUT, I'm new to access and I am desperately trying to figure out how to create a document database such as this. My frustration with your tutorial is that you don't show step by step how to create this subform and link it etc... In viewing I have to try to guess how to recreate what you did and since I'm new to this, I can't follow exactly what you did. Can you please show this step by step? Or maybe if you have a template to download? PLEASE?
Hi great video..I have a query if i want to export access report with attachment images..if i do that as pdf the images are showing but when i export as excel images column is removed..can you make a video or guide how this can be achieved!!
Thanks for a very helpful video. I created the second form with add/delete/open buttons. On the add button everything seems to be working, file attaches but the subform does not requery and I'm getting Error Number: 424 = Object Required message. If I change records and go back the added files shows. I have triple check the coding and everything seems correct. Any suggestions?
This is a great video. I would just like to ask a few questions. - Would it be possible to add a date to each individual document and a comments field and have it sort the documents based on the date? 1 record from the main table would have several documents and comments for each document. If yes, where would I store that data as apparently the attachment type does not let me select each file individually in the table. Thanks in advance for all your advice.
+Joao Kharis Unfortunately, what Microsoft has built in at the document level is just name, and file type. Storing a date and comments would need to be a custom solution built by you. If I needed to do that, I would make a rule for myself that I would only allow storage of one document per database row. That would allow you to have a column for date and comments in the 'outer' table, with a one to one match. If you really like the idea of multiple documents per row, then you could create a second table with a column that would match up to the primary key of your attachment table. Then a second column in that table to match up to the file name, then you could have your date and comments in that table.
Hello. This video is great and I have had loads of fun learning from it. Could you please provide a link to the video in which you describe the use of "fDialog"? Also, I am interested in saving photos outside of access (in a shared network folder) rather than using attachments. Is it possible to map the location (rather than creating an attachment) each time a photo is selected using a dialogue box like in this video? Thanks.
Hey Ethan, sorry for the huge delay. Yes, I have a video here: th-cam.com/video/DTtDchA4XQ4/w-d-xo.html, on the file dialog. Yes, you can use the file dialog to get the full path to an image file, and save the path as a string in a database column. One thing I have done, is use the file dialog to find the image, or file you want to save, and then use VBA to copy the file to a folder structure used by your database, and save the new path as a string in a database column.
Great video. Thanks for sharing! I do have a question... I have something very similar to the form in the video, and I'm almost there. I never saw the control sources for the text box controls in the subform. I have the subform built with the control source for two of the attachment field subtypes (Filename and file type). They both show the correct information when I have the subform open as a stand alone. However, when I bring that subform into the main form and view the data, both text control boxes return the value of ?Name. Any ideas as to what I'm doing wrong?
Hello, Thanks for this great video! I have chosen for the no code option, but actually I want to add a bit of code for the refresh button you mentioned to update the FileName en FileType of the Attachment. Since I am a beginner in vba and access, could you give me an example of the code needed to do this? Thanks in advance for your answer.
+Frank van Rooij Two methods to try: If you put the button on the main (outer) form: subformName.Requery If you put the button in the header of the subform: me.requery
Hey, is there an easy way to allow users to view and add attachments but not delete attachments? I have an attachment field in one of my forms that I need this feature, any help would be appreciated
I would like to thank you for your time and reply. So if I understand your 2nd suggestion correctly, I could have 2 tables: 1) The table with my records (I want 1 record to be able to have more than 1 file) with a column (AttachDocID-FK) linked to the PK of the attachment table; 2) an AttachDocTbl with 4 columns AttachDocID (PK) - autonumber, "attachment", "date" and "comments". Would this work? I still don't understand how I would be able to browse or force that files are stored in different rows in the attachment table, instead of all of them being saved in 1 row.
+Joao Kharis We might be saying the same thing. If you want to store more than one document on a row in your attachment table, then you'll need a second table to hold your date and comments for each document. The reason being, when we add an attachment column to a table, Microsoft controls the columns that are "inside' that attachment column: we only get filedata, filename and filetype. So you don't have a way of saving a date and comments at the document level there. You would need two tables, your attachment table, with a primary key, maybe AttachID, and then your comments table. Your comments table would need two foreign keys: one to point to the primary key of the attachment table (AttachID), which gets you to the correct row, and then another foreign key to point to the appropriate document stored on that row - and I think your only choice to use here is the FileName sub-column. This plan will break down if Microsoft lets us store two documents on the same row with the same name. If you restrict yourself to only saving one document per attachment table row, then you can add a date and comments column to your attachment table, and be done. How to restrict yourself to only one document per attachment table row? Not sure how to answer that, this will depend on your interface, and implementing code, etc that only allows you to add one document per row.
Hi. I have watched your wonderful videos, but i think my situation is somewhat unique. I have a table referred to as: tblAgenda I have an unbound form referred to as: frmAgendaReqst. The form is for users to enter data. The "Save and Submit" button when clicked will refer to the code below allowing for data to be entered into specific fields in the tblAgenda. One of my fields in the form is for attachments. Not sure how to use an unbound control for attachments. When i use the bound control and specify the control source field in the table, it creates a new record for just the attachment instead of placing the attachment in the new record for the item. I need the attachment field in the form to add the attachments to the attachment field (Files_Attached) in the table showing as a part of the whole new record not as a new record by itself. Code: Private Sub btnSaveSubmit_Click() Dim rs As DAO.Recordset, db As DAO.Database Dim txt, msgReply, Filter_Name Dim recent_Agenda 'Last_Item = DMax("[Agenda_ID]", "tblAgenda") 'SQLtext = "SELECT * FROM [tblAgenda] WHERE [AGENDA_ID]=" & Last_Item Set db = CurrentDb Set rs = db.OpenRecordset("tblAgenda", dbOpenDynaset, dbSeeChanges)
With rs .AddNew '!Files_Attached.Fields("FileData").LoadFromFile Me.attFiles
Else ' unique to ad hoc !SubTopic_ID = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocSubID !AdHocDescription = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.AdHocDescription !AdHocTimeDecimal = CDbl(Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtTimeNeeded.Column(0)) !AdHocTimeNeeded = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocTimeTXT End If
'End Select
.Update
End With Agenda_ID = DMax("[Agenda_ID]", "tblAgenda") Filter_Name = "[Agenda_ID] = " & Me.Agenda_ID rs.Close db.Close
DoCmd.OpenReport "rptsubmitteditem", acViewReport, , Filter_Name DoCmd.Close acForm, "frmAgendaReqst" 'msgReply = MsgBox("Do you want to print?", vbYesNo, "Print Report") ' If msgReply = 6 Then 'Yes ' DoCmd.PrintOut ' End If 'DoCmd.Close acReport, "rptsubmitteditem" End Sub
If your table with attachments has two fields: documentid and document (this is the attachment field), this SQL will list all of the attachments on each row: "SELECT documentid, document.filename from attachments". In my video example table it yields: documentid document.filename First HospitalWide32.bmp First TempXcel.xlsx First DollarSign32.bmp Second ImportPDF32.bmp Second TestDoc.docx Second InfoCircle32.bmp Second TempXcel.xlsx AppIcon InfoCircle32.bmp One solution would be to loop through that recordset in VBA and count.
Wow, the formatting really got messed up with I posted that. The word at the top of each pair is the documentID, the word on the bottom of each pair is the attachment's filename. So you can see there are 3 attachments on the first and 4 attachments on the second.
Hi, do yo have any videos on how to use the first option that you said, this program worked great for me but I have a big volume of images and it keeps increasing, I want to select the object in the file dialog, save it to another folder, and save its route. Thanks!!
I don't have a video on that topic. If that is the approach you want to take, I would suggest forgetting about the attachment data type, and simply save the file path as a string in a regular table column.
the form that lists all of the attachments on a single record is showing the actual image, not an icon. I sized the picture box to fit the size of images I was using in the video. It will display larger images, based on how you set the properties of the picture box (clip, stretch, zoom).
Hello, I would like instructions on how to create your sub form inside your form from 7:49... could you please provide some instructions on how to do so? Thank you in advance. I love your videos by the way.
+Gerso Guillen I have thinking about doing a series on the mechanics of building forms. You've validated that idea. I'm in the middle of a series on an admin form. That might be a good next thing for me to do.
![[TH] 2024 PMSL SEA Finals D1 | Fall | ยิงต้องโหด ให้เหมือนโกรธกันมาก่อน](http://i.ytimg.com/vi/OnTdfGySmds/mqdefault.jpg)
Excellent video! Thank you so much for explaining this concept! I sent 5 hours reading to try and understand it, but learned nothing. Your 35 minute video was what I needed!
This tutorial is one of the wonderful works on access attachment data type.
Recommended!
I want to Thank You, Thank You, Thank You for this tutorial! I spend half a day trying to figure it out by myself and I just wasn't getting very far. But now, it all works the way I wanted to. Thanks and God Bless. :-)
Hi, just greate video you created.
Very nicely explained with a lot of useful information provided.
Thank you
My name is Brandon Wilson and I just watched your video on 'VBA for Working with Access Attachments', but unfortunately I could not find the code so that I could try it out; is there some way that you could post it again, because it was really good and clearly explained what you were doing?
Brilliant!!!!
This is Brandon again, and I was just wondering if you might attach a link directly with the 'VBA for Working with Access Attachments' video itself, because it will make it easier to find and copy.
Many many many thank you , it was very useful
Thank You very much for the video on this item. It's a great help.
Thank you for this BUT, I'm new to access and I am desperately trying to figure out how to create a document database such as this. My frustration with your tutorial is that you don't show step by step how to create this subform and link it etc... In viewing I have to try to guess how to recreate what you did and since I'm new to this, I can't follow exactly what you did. Can you please show this step by step? Or maybe if you have a template to download? PLEASE?
Hi
I have Err ( user -defined type not defined ) when i run this program & I Add "Microsoft Office 14.0 Object Library" in references "
Hi great video..I have a query if i want to export access report with attachment images..if i do that as pdf the images are showing but when i export as excel images column is removed..can you make a video or guide how this can be achieved!!
how to validate attachment to make sure user add the attachment...if not cant go to the next form....
Hi
Thank you for your wonderful explanation.
Can't we use the Application. Follow hyperlink instead of the shellexcute?
Thanks for a very helpful video. I created the second form with add/delete/open buttons. On the add button everything seems to be working, file attaches but the subform does not requery and I'm getting Error Number: 424 = Object Required message. If I change records and go back the added files shows. I have triple check the coding and everything seems correct. Any suggestions?
Hi,
Thx for the video, How do I manage to show the thumbnails in the subform?
Thx in advance
This is a great video. I would just like to ask a few questions.
- Would it be possible to add a date to each individual document and a comments field and have it sort the documents based on the date? 1 record from the main table would have several documents and comments for each document.
If yes, where would I store that data as apparently the attachment type does not let me select each file individually in the table.
Thanks in advance for all your advice.
+Joao Kharis Unfortunately, what Microsoft has built in at the document level is just name, and file type. Storing a date and comments would need to be a custom solution built by you. If I needed to do that, I would make a rule for myself that I would only allow storage of one document per database row. That would allow you to have a column for date and comments in the 'outer' table, with a one to one match.
If you really like the idea of multiple documents per row, then you could create a second table with a column that would match up to the primary key of your attachment table. Then a second column in that table to match up to the file name, then you could have your date and comments in that table.
help me about Append query containing attachment in ms access 2016, please
Works like a charm, thx!👍
Hello. This video is great and I have had loads of fun learning from it. Could you please provide a link to the video in which you describe the use of "fDialog"? Also, I am interested in saving photos outside of access (in a shared network folder) rather than using attachments. Is it possible to map the location (rather than creating an attachment) each time a photo is selected using a dialogue box like in this video? Thanks.
Hey Ethan, sorry for the huge delay. Yes, I have a video here: th-cam.com/video/DTtDchA4XQ4/w-d-xo.html, on the file dialog. Yes, you can use the file dialog to get the full path to an image file, and save the path as a string in a database column. One thing I have done, is use the file dialog to find the image, or file you want to save, and then use VBA to copy the file to a folder structure used by your database, and save the new path as a string in a database column.
Great video. Thanks for sharing! I do have a question...
I have something very similar to the form in the video, and I'm almost there. I never saw the control sources for the text box controls in the subform. I have the subform built with the control source for two of the attachment field subtypes (Filename and file type). They both show the correct information when I have the subform open as a stand alone. However, when I bring that subform into the main form and view the data, both text control boxes return the value of ?Name. Any ideas as to what I'm doing wrong?
Hello,
Thanks for this great video! I have chosen for the no code option, but actually I want to add a bit of code for the refresh button you mentioned to update the FileName en FileType of the Attachment. Since I am a beginner in vba and access, could you give me an example of the code needed to do this? Thanks in advance for your answer.
+Frank van Rooij Two methods to try:
If you put the button on the main (outer) form:
subformName.Requery
If you put the button in the header of the subform:
me.requery
Thank you for this GREAT video. It help me a great deal. Again THANK YOU!
Hey, is there an easy way to allow users to view and add attachments but not delete attachments? I have an attachment field in one of my forms that I need this feature, any help would be appreciated
Hi, how can I save Image direct from Scaner to Table>
Tank you
Hi can you provide access to your code please? The link in the description does not display the code
I would like to thank you for your time and reply. So if I understand your 2nd suggestion correctly, I could have 2 tables: 1) The table with my records (I want 1 record to be able to have more than 1 file) with a column (AttachDocID-FK) linked to the PK of the attachment table; 2) an AttachDocTbl with 4 columns AttachDocID (PK) - autonumber, "attachment", "date" and "comments".
Would this work? I still don't understand how I would be able to browse or force that files are stored in different rows in the attachment table, instead of all of them being saved in 1 row.
+Joao Kharis We might be saying the same thing. If you want to store more than one document on a row in your attachment table, then you'll need a second table to hold your date and comments for each document. The reason being, when we add an attachment column to a table, Microsoft controls the columns that are "inside' that attachment column: we only get filedata, filename and filetype. So you don't have a way of saving a date and comments at the document level there. You would need two tables, your attachment table, with a primary key, maybe AttachID, and then your comments table. Your comments table would need two foreign keys: one to point to the primary key of the attachment table (AttachID), which gets you to the correct row, and then another foreign key to point to the appropriate document stored on that row - and I think your only choice to use here is the FileName sub-column. This plan will break down if Microsoft lets us store two documents on the same row with the same name.
If you restrict yourself to only saving one document per attachment table row, then you can add a date and comments column to your attachment table, and be done. How to restrict yourself to only one document per attachment table row? Not sure how to answer that, this will depend on your interface, and implementing code, etc that only allows you to add one document per row.
Hi.
I have watched your wonderful videos, but i think my situation is somewhat unique.
I have a table referred to as: tblAgenda
I have an unbound form referred to as: frmAgendaReqst. The form is for users to enter data. The "Save and Submit" button when clicked will refer to the code below allowing for data to be entered into specific fields in the tblAgenda. One of my fields in the form is for attachments. Not sure how to use an unbound control for attachments. When i use the bound control and specify the control source field in the table, it creates a new record for just the attachment instead of placing the attachment in the new record for the item.
I need the attachment field in the form to add the attachments to the attachment field (Files_Attached) in the table showing as a part of the whole new record not as a new record by itself.
Code:
Private Sub btnSaveSubmit_Click()
Dim rs As DAO.Recordset, db As DAO.Database
Dim txt, msgReply, Filter_Name
Dim recent_Agenda
'Last_Item = DMax("[Agenda_ID]", "tblAgenda")
'SQLtext = "SELECT * FROM [tblAgenda] WHERE [AGENDA_ID]=" & Last_Item
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAgenda", dbOpenDynaset, dbSeeChanges)
With rs
.AddNew
'!Files_Attached.Fields("FileData").LoadFromFile Me.attFiles
' common to two tabs
!AgendaTopic_Num = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num
!AgendaTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic
!MtgDate = Me.txtMtgDate
!SubTopic_ID = Me.SubTopic_ID
!Background = Me.Background
txt = ActionRequested
!ActionRequested = txt
!Action_Request_Desc = Me.Action_Request_Desc
!Budget_Policy_Impact = Me.Budget_Policy_Impact
!Attached_Files = Me.chkAttachment
'txt2 = Me.att
' !Files_Attached = Me.attFiles
!Attachments_Desc = Me.DescribeFile
!Presenter_1 = Me.Presenter_1
!Presenter_2 = Me.Presenter_2
!Presenter_3 = Me.Presenter_3
!SubmittedBy = Me.SubmittedBy
!Email = Me.Email
!Phone = Me.Phone
!DateSubmitted = Me.DateSubmitted
!ET_Submit_Chk = Me.ET_Submit_Chk
txt1 = ETMember
!ET_Submit_Appvl = txt1
'Select Case Me.OptionBtns
If Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num 17 Then
'Case 1 ' txtTopicOptions
' unique to gen topic
!SubTopic_ID = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtSubID
!AgendaSubTopicTrkg = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopicTrkg
!AgendaSubTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopic
!Responsible = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtResponsible
!TimeNeeded = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime_Required
!TimeNeeded_txt = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime
!AgendaSubject = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubject
!Focus_Tasks = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtFocus_Tasks
Else
' unique to ad hoc
!SubTopic_ID = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocSubID
!AdHocDescription = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.AdHocDescription
!AdHocTimeDecimal = CDbl(Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtTimeNeeded.Column(0))
!AdHocTimeNeeded = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocTimeTXT
End If
'End Select
.Update
End With
Agenda_ID = DMax("[Agenda_ID]", "tblAgenda")
Filter_Name = "[Agenda_ID] = " & Me.Agenda_ID
rs.Close
db.Close
DoCmd.OpenReport "rptsubmitteditem", acViewReport, , Filter_Name
DoCmd.Close acForm, "frmAgendaReqst"
'msgReply = MsgBox("Do you want to print?", vbYesNo, "Print Report")
' If msgReply = 6 Then 'Yes
' DoCmd.PrintOut
' End If
'DoCmd.Close acReport, "rptsubmitteditem"
End Sub
what is the way to calculate how many attachments in one field
If your table with attachments has two fields: documentid and document
(this is the attachment field), this SQL will list all of the
attachments on each row: "SELECT documentid, document.filename from
attachments". In my video example table it yields:
documentid
document.filename
First
HospitalWide32.bmp
First
TempXcel.xlsx
First
DollarSign32.bmp
Second
ImportPDF32.bmp
Second
TestDoc.docx
Second
InfoCircle32.bmp
Second
TempXcel.xlsx
AppIcon
InfoCircle32.bmp
One solution would be to loop through that recordset in VBA and count.
Wow, the formatting really got messed up with I posted that. The word at the top of each pair is the documentID, the word on the bottom of each pair is the attachment's filename. So you can see there are 3 attachments on the first and 4 attachments on the second.
Hi, do yo have any videos on how to use the first option that you said, this program worked great for me but I have a big volume of images and it keeps increasing, I want to select the object in the file dialog, save it to another folder, and save its route. Thanks!!
I don't have a video on that topic. If that is the approach you want to take, I would suggest forgetting about the attachment data type, and simply save the file path as a string in a regular table column.
Thanks a lot! I just did it.
Is there a way to have the attached file preview in a form? --Thx!
Not just the icon or paperclip but the contents of the file itself.
the form that lists all of the attachments on a single record is showing the actual image, not an icon. I sized the picture box to fit the size of images I was using in the video. It will display larger images, based on how you set the properties of the picture box (clip, stretch, zoom).
Is there a link to download this file?
Thanks for your videos! If its not much to ask could you reupload the files please, the provided link is not working anymore. Great channel!
I have recopied the link into the comments below the video. It worked for me after this. Thanks for watching!
Can we write a code to attache directly from scanner
I don't think there is a way to do that.
To my freinds on facebook
@@Accessjitsu I've been trying to contact you through your blog but no luck. there is such code. I'm wondering if you interested in improving it.
Hello, I would like instructions on how to create your sub form inside your form from 7:49... could you please provide some instructions on how to do so? Thank you in advance. I love your videos by the way.
+Gerso Guillen I have thinking about doing a series on the mechanics of building forms. You've validated that idea. I'm in the middle of a series on an admin form. That might be a good next thing for me to do.
+Access Jitsu looking forward to the series
Un vídeo un poco largo, pero útil