0

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
JustinSane
  • 15
  • 4
  • You want to INSERT new record with a single field value of "Data confirmed"? Or do you want to UPDATE existing record in Access table? – June7 Oct 19 '20 at 18:01
  • The columns are already in Access and the columns "Confirmation" is empty at the beginning. What we wan't is to show the current data to the people with a hyperlink "Click to confirm" at the end. Once clicked on that, a text "Data confirmed" has to be inserted into access ( for that particular line) so that we know which data is confirmed and which isn't. – JustinSane Oct 20 '20 at 07:50
  • So you mean records already exist and you want to UPDATE field of particular record(s). That would be an UPDATE, not INSERT, action SQL. – June7 Oct 20 '20 at 17:29
  • Well the column is there but at the beginning its empty ( so every line gets a hyperlink to confirm the data). When someone clickes the hyperlink , the column should fill up with text "Data confirmed". So not sure if its an update or insert since you insert the text "Data confirmed" into the column – JustinSane Oct 21 '20 at 09:11
  • It's an INSERT if record does not already exist, its an UPDATE if record does exist. Which is it in your situation? If you mean by 'the columns are already in Access' that there are records with data in every column except "Confirmation" and you now want to add data to "Confirmation", then you are updating records. – June7 Oct 21 '20 at 18:32
  • @June7 I've changed to thinking process. There will be 3 colimns // Column 1 contains "Previous estimation". Column 2 contains "New estimation" ( Which is empty on default, People will have to enter their new estimation in this column.) and column 3 contains the hyperlink. When a hyperlink gets clicked, lets say in row 5. The input from column 2 ( New estimation) on Row 5 has to be saved in Access. Any idea how to implement this in the code above? – JustinSane Nov 18 '20 at 10:42
  • Does that mean you are now using UPDATE action? – June7 Nov 18 '20 at 10:46
  • @June Yes, i was thinking about using "SET Column 2 = New estimation" to then Update the access. – JustinSane Nov 18 '20 at 11:35
  • Probably need to use WHERE clause to specify which record(s) to UPDATE. – June7 Nov 18 '20 at 11:39
  • @June7 Thx! I can't seem to find a way to implement the data saving into the 2nd part of the code.. (https://stackoverflow.com/questions/64351946/show-a-commandbutton-on-datapulled-lines/64354569?noredirect=1#comment113925617_64354569) Do you have any idea how to solve this? Thanks! – JustinSane Nov 19 '20 at 14:41
  • What does "can't seem to find a way" mean - what have you tried? Need to grab record identifier (or some combination of data that uniquely identifies Access record) for WHERE clause. Which column has record key and which column has hyperlink? Why use Excel? Why can't engineers open Access? – June7 Nov 19 '20 at 19:21

1 Answers1

0

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.Execute "UPDATE NewPO SET Confirmation='Data confirmed' WHERE ID = " & ActiveCell.Offset(0, -1).Value

June7
  • 19,874
  • 8
  • 24
  • 34
  • Basodre helped me get a hyperlink on every row and gave me a -second- part code do something whenever the hyperlink is clicked. The code he gave me is : ////// 'Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Confirm that this is a hyperlink in column 3 If Not Intersect(Target.Range, Columns(3)) Is Nothing Then MsgBox SaveData(Target.Range) End If End Sub Private Function SaveData(rng As Range) As Boolean Debug.Print rng.Address & " has been saved." SaveData = True End Function' ////// Not sure how to implement your code in the code above tbh. – JustinSane Nov 24 '20 at 08:07
  • It would go in place of the INSERT statement in your SaveData procedure. – June7 Nov 24 '20 at 08:49