By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Unbound control form/attachment control

hargrav123
P: 5
Using Access 2016. Form using unbound controls. After entering data with an attachment, data goes into a table (tblAgenda). The attachment information shows up as a new record of its own in the table versus showing up with the data (record) it was submitted with. Below is my code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveSubmit_Click()
  2. Dim rs As DAO.Recordset, db As DAO.Database
  3. Dim txt, msgReply, Filter_Name
  4. Dim recent_Agenda As Integer
  5. Set db = CurrentDb
  6. Set rs = db.OpenRecordset("tblAgenda", dbOpenDynaset, dbSeeChanges)
  7. ' Activate edit mode.
  8.    rstblAgenda.Edit
  9.  
  10.    ' Instantiate the child recordset.
  11.    Set rsFiles_Attached = rstblAgenda.Fields("Files_Attached").Value
  12.  
  13.    ' Add a new attachment.
  14.    rsFiles_Attached.AddNew
  15.    rsFiles_Attached.Fields("FileData").LoadFromFile
  16.    rsrsFiles_Attached.Update
  17.  
  18.    ' Update the parent record
  19.    rsFiles_Attached.Update
  20.     With rs
  21.         .AddNew
  22.  
  23.         ' common to two tabs
  24.             !AgendaTopic_Num = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num
  25.             !AgendaTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic
  26.             !MtgDate = Me.txtMtgDate
  27.             !SubTopic_ID = Me.SubTopic_ID
  28.             !Background = Me.Background
  29.             txt = ActionRequested
  30.             !ActionRequested = txt
  31.             !Action_Request_Desc = Me.Action_Request_Desc
  32.             !Budget_Policy_Impact = Me.Budget_Policy_Impact
  33.             !Attached_Files = Me.chkAttachment
  34.              'txt2 = Me.attFiles
  35.             '!Files_Attached = attFiles
  36.             !Attachments_Desc = Me.DescribeFile
  37.             !Presenter_1 = Me.Presenter_1
  38.             !Presenter_2 = Me.Presenter_2
  39.             !Presenter_3 = Me.Presenter_3
  40.             !SubmittedBy = Me.SubmittedBy
  41.             !Email = Me.Email
  42.             !Phone = Me.Phone
  43.             !DateSubmitted = Me.DateSubmitted
  44.             !ET_Submit_Chk = Me.ET_Submit_Chk
  45.             txt1 = ETMember
  46.             !ET_Submit_Appvl = txt1
  47.         'Select Case Me.OptionBtns
  48.         If Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num <> 17 Then
  49.         'Case 1  ' txtTopicOptions
  50.  
  51.            ' unique to gen topic
  52.             !SubTopic_ID = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtSubID
  53.             !AgendaSubTopicTrkg = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopicTrkg
  54.             !AgendaSubTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopic
  55.             !Responsible = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtResponsible
  56.             !TimeNeeded = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime_Required
  57.             !TimeNeeded_txt = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime
  58.             !AgendaSubject = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubject
  59.             !Focus_Tasks = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtFocus_Tasks
  60.  
  61.          Else
  62.            'Case 2
  63.             ' unique to ad hoc
  64.             !SubTopic_ID = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocSubID
  65.             !AdHocDescription = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.AdHocDescription
  66.             !AdHocTimeDecimal = CDbl(Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtTimeNeeded.Column(0))
  67.             !AdHocTimeNeeded = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocTimeTXT
  68.         End If
  69.  
  70.         'End Select
  71.  
  72.  
  73.  
  74.       .Update
  75.  
  76.     End With
  77.     Agenda_ID = DMax("[Agenda_ID]", "tblAgenda")
  78.     Filter_Name = "[Agenda_ID] = " & Me.Agenda_ID
  79.     rs.Close
  80.     db.Close
  81.  
  82.  
  83.     DoCmd.OpenReport "rptsubmitteditem", acViewReport, , Filter_Name
  84.     DoCmd.Close acForm, "frmAgendaReqst"
  85.   'msgReply = MsgBox("Do you want to print?", vbYesNo, "Print Report")
  86.    ' If msgReply = 6 Then 'Yes
  87.     '    DoCmd.PrintOut
  88.    ' End If
  89.     'DoCmd.Close acReport, "rptsubmitteditem"
  90.  
  91. End Sub
Mar 6 '19 #1
Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,127
hargrav123,

Welcome to Bytes!

There should be no need for rsFiles_Attached. Just add the files to the attachment field when you create your "parent" record. Once you perform the .AddNew and .Update to rsFiles_Attached, the record is made. When you perform the .AddNew and .Update to rs, you will always get a new record.

Hope this hepps!
Mar 6 '19 #2

hargrav123
P: 5
Hi twinnyfo.

How does this code look now?

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveSubmit_Click()
  2. Dim rs As DAO.Recordset, db As DAO.Database
  3. Dim txt, msgReply, Filter_Name
  4. Dim recent_Agenda As Integer
  5. Set db = CurrentDb
  6. Set rs = db.OpenRecordset("tblAgenda", dbOpenDynaset, dbSeeChanges)
  7.    ' Add a new attachment.
  8.    rsFiles_Attached.Fields("FileData").LoadFromFile
  9.  
  10.     With rs
  11.         .AddNew
  12.  
  13.         ' common to two tabs
  14.             !AgendaTopic_Num = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num
  15.             !AgendaTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic
  16.             !MtgDate = Me.txtMtgDate
  17.             !SubTopic_ID = Me.SubTopic_ID
  18.             !Background = Me.Background
  19.             txt = ActionRequested
  20.             !ActionRequested = txt
  21.             !Action_Request_Desc = Me.Action_Request_Desc
  22.             !Budget_Policy_Impact = Me.Budget_Policy_Impact
  23.             !Attached_Files = Me.chkAttachment
  24.              'txt2 = Me.attFiles
  25.             '!Files_Attached = attFiles
  26.             !Attachments_Desc = Me.DescribeFile
  27.             !Presenter_1 = Me.Presenter_1
  28.             !Presenter_2 = Me.Presenter_2
  29.             !Presenter_3 = Me.Presenter_3
  30.             !SubmittedBy = Me.SubmittedBy
  31.             !Email = Me.Email
  32.             !Phone = Me.Phone
  33.             !DateSubmitted = Me.DateSubmitted
  34.             !ET_Submit_Chk = Me.ET_Submit_Chk
  35.             txt1 = ETMember
  36.             !ET_Submit_Appvl = txt1
  37.         'Select Case Me.OptionBtns
  38.         If Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num <> 17 Then
  39.         'Case 1  ' txtTopicOptions
  40.  
  41.            ' unique to gen topic
  42.             !SubTopic_ID = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtSubID
  43.             !AgendaSubTopicTrkg = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopicTrkg
  44.             !AgendaSubTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopic
  45.             !Responsible = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtResponsible
  46.             !TimeNeeded = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime_Required
  47.             !TimeNeeded_txt = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime
  48.             !AgendaSubject = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubject
  49.             !Focus_Tasks = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtFocus_Tasks
  50.  
  51.          Else
  52.            'Case 2
  53.             ' unique to ad hoc
  54.             !SubTopic_ID = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocSubID
  55.             !AdHocDescription = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.AdHocDescription
  56.             !AdHocTimeDecimal = CDbl(Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtTimeNeeded.Column(0))
  57.             !AdHocTimeNeeded = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocTimeTXT
  58.         End If
  59.  
  60.         'End Select
  61.  
  62.  
  63.  
  64.       .Update
  65.  
  66.     End With
  67.     Agenda_ID = DMax("[Agenda_ID]", "tblAgenda")
  68.     Filter_Name = "[Agenda_ID] = " & Me.Agenda_ID
  69.     rs.Close
  70.     db.Close
  71.  
  72.  
  73.     DoCmd.OpenReport "rptsubmitteditem", acViewReport, , Filter_Name
  74.     DoCmd.Close acForm, "frmAgendaReqst"
  75.   'msgReply = MsgBox("Do you want to print?", vbYesNo, "Print Report")
  76.    ' If msgReply = 6 Then 'Yes
  77.     '    DoCmd.PrintOut
  78.    ' End If
  79.     'DoCmd.Close acReport, "rptsubmitteditem"
  80.  
  81. End Sub
Mar 6 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,127
Have you tried this code?

Did it work at all?

I thought not, on both counts.

Whenever you use any VBA, make sure you have the statement Option Explicit at the top of the VBA module. This will identify errors and undeclared variables before you try to execute--and then you can't.

Your first posting of code could not work becuase you never declared rstblAgenda, but you tried to open it and extract information. The same thing happens with the second posting, with rsFiles_Attached, which, as I described above, you don't need.

I began to clean up your code and work through the very easy modifications required to get you functioning, but I am hesitant to do that at this point.

You will find that we are very helpful on this forum when posters are willing to put some sincere effort into their projects. I would rather you put some effort into this before I put any more effort into it. If you compile your code, you will find numerous problem areas. If you come across any errors that you are unable to fix on your own, I'm glad to walk you through some troubleshooting tips.

Standing by to assist.
Mar 6 '19 #4

hargrav123
P: 5
Thank you twinnyfo. I am not trying to extract from the tblAgenda, rather enter data (attachments) into it.
Mar 6 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 3,127
Yes, I know what you are trying to do.

Have you tried the two versions of code that you posted? What are the results that you get when you do?
Mar 6 '19 #6

Post your reply

Sign in to post your reply or Sign up for a free account.