Code to write file path to field always writes to 1st record 1

[renigar]

I have a database that keeps track of water utility repair digs. When the repair dig is complete the crew lead fills out a dig card. I'm scanning the cards (because the cards have hand drawn details of the repair) to pdf. The pdf will be kept in a separate directory and opened into the default pdf viewer with a button click. The code I'm having a problem with opens a file dialog and gets the full unc file path of the pdf and writes it to a field named ViewDigCard. It works accept it always write to the first record. I need a reference to the current record to insure that's where the file path is written. I'm new to Access and don't quite know what I'm doing, but I think the fix is probably easy. Any help would be greatly appreciated. See code below.

Private Sub btnGetLink_Click()
' Get pdf link
On Error GoTo ErrorHandler
    Dim dlg As FileDialog
    Dim selectedFile As Variant
    Dim currentRecord As Recordset
    
' Refresh the data set
    Me.Recordset.Requery
    
' Create a file dialog
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    
' Set the initial directory to the network directory "DigCards"
    dlg.InitialFileName = "\\osdshare\Utilities\Dig Card Database\DigCardImages"
    
' Set the title and filter
    dlg.Title = "Select a file from DigCardsImages Directory"
    dlg.Filters.Clear
    dlg.Filters.Add "pdf Files", "*.pdf"
    
' Show the dialog and capture the selected file
    If dlg.Show = -1 Then
        selectedFile = dlg.SelectedItems(1)
        
' Write the path to the field "ViewDigCard" in the current record
        Set currentRecord = CurrentDb.OpenRecordset("Dig Cards")
        currentRecord.Edit
        currentRecord("ViewDigCard") = selectedFile
        currentRecord.Update
        currentRecord.Close
    Else
' User canceled the dialog
        MsgBox "File selection canceled.", vbInformation, "Information"
    End If
    
'    Stop
    
    ' Clean up
    Set dlg = Nothing
    Exit Sub
    
ErrorHandler:
    ' Handle any errors here (e.g., display a message box)
    MsgBox "Error: " & Err.Description, vbExclamation, "Error"

End Sub

 

[dhookom]

I expect you need to open the recordset to the current record based on the primary key.
Update the first lines like:

Private Sub btnGetLink_Click()
' Get pdf link
On Error GoTo ErrorHandler
    Dim dlg As FileDialog
    Dim selectedFile As Variant
    Dim currentRecord As DAO.Recordset  [COLOR=#4E9A06]'explicit DAO[/color]
    Dim strSQL as String                [COLOR=#4E9A06]'to create recordset[/color]

Then open a query/SQL statement rather than the table:

' Write the path to the field "ViewDigCard" in the current record
        strSQL = "SELECT * FROM [Dig Cards] WHERE [PrimaryKeyField] = " & Me.[PrimaryKeyField]
        debug.print strSQL      [COLOR=#4E9A06]'let's see the value[/color]
        Set currentRecord = CurrentDb.OpenRecordset(strSQL)
        currentRecord.Edit
        currentRecord("ViewDigCard") = selectedFile
        currentRecord.Update
        currentRecord.Close

If the primary key is text, you will need to add some quotes into the strSQL.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[renigar]

Thanks for the reply dhookom,
The primary key is the ID, set to autonumber. I'm not back to work until Tuesday. I'll look into your tips then and let you know.
Thanks again,
renigar

 

[Andrzejek]

Wouldn't be easier to just do:

[pre]
strSQL = "UPDATE [Dig Cards] SET ViewDigCard = '" & selectedFile & "' WHERE [PrimaryKeyField] = " & Me.[PrimaryKeyField]
CurrentDb.Execute strSQL[/pre]



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

dhookom, your suggestion works great. Thank you very much.

Andrzejek, your suggestion sort of works. When I execute it I get a dialog box that says, Error: Invalid Operation. Before I can click the OK button it drops the data in the field. Thank you for your suggestion.

 

[dhookom]

Andy’s suggestion looks like it should have worked but we would need to see your exact code. If you are happy with my answer than please mark it “Great Post!”

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016