• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

Execute VBA code on current record in MS Access form

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
Jinghui Li
Asked:
Jinghui Li
3 Solutions
 
Gustav BrockCIOCommented:
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
 
Helen FeddemaCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
I was thinking the same thing ... how do you move from one record to another record???

ET
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jinghui LiAuthor Commented:
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
 
Helen FeddemaCommented:
The Current event would work, or you could call the code from the AfterUpdate event of the combo box record selector.
0
 
Jinghui LiAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now