单击超链接后，保存数据以访问文件 [英] Save data to access file after clicking on a hyperlink

问题描述

感谢basodre，我设法让excell向Excellsheet中的每个数据抽取行添加超链接.下一步将是在屏幕上保存文本数据已确认".在Access中，单击特定行后面的超链接之后.例如，有10行带有超链接单击以确认".在最后.一旦第8行的数据正确，就必须单击单击以确认".在第8行上.一旦点击，它应该保存"Data Confirmed".进入第8行.

Thanks to basodre i've managed to let excell add a hyperlink to every datapulled rows in my Excellsheet. Next step would be to save a text "Data confirmed" in Access once the hyperlink , behind a particular row, gets clicked. For example, there are 10 lines with a hyperlink "click to confirm" at the end. Once data of line 8 is correct, one will have to click "click to confirm" on row 8. Once clicked, it should save "Data confirmed" to access for row 8.

我目前有以下代码，但仍无法保存已确认数据"单击我的访问文件中的超链接.有什么想法吗?

I currently have following code but it still doesn't save "Data confirmed" in my access file once clicked on the hyperlink. Any ideas?

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'Confirm that this is a hyperlink in column 3

    If Not Intersect(Target.Range, Columns(16)) Is Nothing Then
        MsgBox SaveData(Target.Range)
    End If
    
End Sub

Private Function SaveData(rng As Range) As Boolean
    Dim cnDB As New ADODB.Connection
    
    VsDBPath = ThisWorkbook.Sheets("Settings").Range("B2").Value
  
    cnDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & VsDBPath & ";" & "Jet OLEDB:Engine Type=5;" & "Persist Security Info=False;"
 
    cnDB.Execute "INSERT INTO NewPO (Confirmation) VALUES ('Data confirmed')"
       
    
    cnDB.Close
    SaveData = True
End Function

推荐答案

需要获取WHERE子句的记录主键(或唯一标识Access记录的数据组合).使用偏移"来引用与活动单元有关的单元，该单元应该是单击的超链接单元.如果主键数据位于左侧1个单元格中:

Need to grab record primary key (or some combination of data that uniquely identifies Access record) for WHERE clause. Use Offset to reference a cell in relation to active cell which should be the clicked hyperlink cell. If primary key data is 1 cell to the left:

cnDB.执行"UPDATE NewPO SET Confirmation =数据已确认"，WHERE ID ="&ActiveCell.Offset(0，-1).Value

这篇关于单击超链接后，保存数据以访问文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！