Solved

Execute VBA code on current record in MS Access form

Posted on 2016-07-28
6
86 Views
Last Modified: 2016-08-04
I need to have code run on on the current record on a form.  I have been out of VBA programming for a while so am pretty rusty.  But here is my scenario;

My form is called frmContacts that contains a single record from my table CONTACTS.  On this form is a button called MoveAttach that takes a file stored in the CONTACT table as an attachment data type and moves it to a local folder.  I want this code to execute on the current record that the form is focused on (i.e., the CID value of of the record on the form).

Thanks in advance for your help.

Here is my code:


Private Sub MoveAttach_Click()

Dim db As Database
Dim rsContacts As Recordset2
Dim rsCAttach As Recordset2
Dim strCID As String

Set db = CurrentDb()

strCID = Me.CID.Value

'  Instantiate the parent recordset.
Set rsContacts = db.OpenRecordset("CONTACTS")


' Instantiate the child recordset.
Set rsCAttach = rsContacts.Fields("CAttach").Value

Do While rsContacts.Fields.Item(1).Value = strCID



    Do While Not rsCAttach.EOF

        rsCAttach.Fields("FileData").SaveToFile _
        "S:\BLS\DB_BLS\Attachment\" & strCID & rsCAttach("FileName")

        rsCAttach.MoveNext
   
    Loop
   
    rsCAttach.Close
    rsContacts.Close
   
    Loop
   
End Sub
0
Comment
Question by:Jinghui Li
6 Comments
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
Comment Utility
I guess all you need is to call this function at the OnCurrent event:

Private Sub Form_Current()

    If Me.NewRecord = False Then
        Call MoveAttach_Click()
    End If

End Sub

Open in new window

/gustav
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Unless there is something else going on here.  What is the purpose of a form that just displays one record?  Is this perhaps a form with DataEntry set to Yes, so it just shows the new (blank) record), or is it a form bound to a recordset, where you can move from record to record, or is it filtered to show just one record, or what?  The answer might determine where you need to place your code.
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
I was thinking the same thing ... how do you move from one record to another record???

ET
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Jinghui Li
Comment Utility
Thanks for your help.  It is a single form bound to the CONTACTS table.  The form has a combo box to look up a record by CID.   Once I navigate to the record, I want to execute the code that will identify the attachment from the selected record and SaveToFile in a shared drive.  Then, I will delete the attachment that is stored in the Attachment data type.  Data Entry is set to No.  Allow Edits is set to yes.

What I am trying to accomplish is a way in which the users can go in an archive attachments.  I grossly underestimated the bloat associated with using the Attachment datatype in Access so am removing old/inactive files to an archive folder and then simply storing the path in a hyperlink field.  This way we can still access the files when needed but removing them from the database will hopefully reduce the size of the table.  We have reached our 2 GIG limit due to volume of pdfs being saved into the CONTACTS table.

Thanks again for all of your help.
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 250 total points
Comment Utility
The Current event would work, or you could call the code from the AfterUpdate event of the combo box record selector.
0
 

Assisted Solution

by:Jinghui Li
Jinghui Li earned 0 total points
Comment Utility
I ended up calling the code from the OnClick event for a button on my form.  The hardest part was programming the loops to identify the current record, iterate through the selected record in the recordset, and then through the  multi-valued field.  

Private Sub MoveAttach_Click()

Dim db As Database
Dim rsContacts As Recordset2
Dim rsCAttach As Recordset2
Dim rsTblAttach As Recordset2
Dim strCID As String
Dim strPath As String
Dim strCount
Dim strSQL As String
Dim strMID As String

Set db = CurrentDb()
strCID = CID
strMID = MAUI_Master_ID

'  Instantiate the parent recordset.
Set rsContacts = db.OpenRecordset("CONTACTS")


'  Checking the total count of parent record set
If rsContacts.RecordCount > 0 Then
rsContacts.MoveFirst

'  Looping through the  parent record set
Do While Not rsContacts.EOF
    If rsContacts.Fields("CID").Value = strCID Then
    Debug.Print rsContacts.Fields("CID")

    ' Instantiate the child recordset.
    Set rsCAttach = rsContacts.Fields("CAttach").Value
    Set rsTblAttach = db.OpenRecordset("ATTACH")
 
Do While Not rsCAttach.EOF
    If rsCAttach("FileName") Like "*.*" Then
    strPath = "[enter path here]" & strCID & "_" & strCount & rsCAttach("FileName")
         
        If Dir(strPath) = "" Then
        rsCAttach.Fields("FileData").SaveToFile _
        strPath
       
        rsTblAttach.AddNew
        rsTblAttach!CID = strCID
        rsTblAttach!MAUI_Master_ID = strMID
        rsTblAttach!Attachment_Path = strPath
        rsTblAttach.Update
       
     
        'Forms!FrmCONTACTS![ATTACH subform].Requery
       
        rsCAttach.Delete
 
        'Forms!FrmCONTACTS.Form.Requery
       
       
        Else
        MsgBox ("File Exists. Unable to Move")
        End If
   
  End If
rsCAttach.MoveNext

Loop
 
  rsCAttach.Close
 
 End If

rsContacts.MoveNext
Loop
   
        Dim strBookmark As String
        strBookmark = Me.Bookmark
        Me.Requery
        Me.Bookmark = strBookmark
   
End If
rsContacts.Close
   
           
End Sub
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now