1

Once every 3 months we make a file available for our engineers. This Excel files, pulls data from an Access file and shows it in Excel format.

Since some of this data doesn't change, we don't know whether the engineers haven't looked at it or whether the value isn't changed. What i'm trying to implement is some sort of "confirmation" button so we know the value shown is actually confirmed.

What i'm trying to do is enter an extra column in our access file called "confirmation". When we pull this data in our excel file, i'm trying to find a way to convert that "confirmation field" into a commandbutton so whenever the data gets pulled, a commandbutton shows up on every line. Whenever the button gets clicked, the data gets saved in our Access file so we know the line is actually confirmed.

Maybe there are some other , easier, ways to do this?

I currently have some code to save excel data in Access but its not working in its current form:

Sub S_SaveDataToDB()

If ActiveSheet.Name = "Estimate" Then
    ViKey = 1
Else
    ViKey = 2
End If
For i = 1 To ActiveSheet.ListObjects("TB_ACC" & ViKey).ListRows.Count
   VsData = "SET [BE] = '" & F_FilterData(ActiveSheet.Cells(7 + i, 17)) & "', [PO STATUS] = '" & F_FilterData(ActiveSheet.Cells(7 + i, 18)) & "', [REMARKS] = '" & F_FilterData(ActiveSheet.Cells(7 + i, 19)) & "', [LOGDATE] = '" & Now() & "', [LOGID] = '" & Environ("Username") & "' WHERE [PO item] = '" & ActiveSheet.Cells(7 + i, 9) & "'"
   
    If Len(F_FilterData(ActiveSheet.Cells(7 + i, 16))) + Len(F_FilterData(ActiveSheet.Cells(7 + i, 17))) + Len(F_FilterData(ActiveSheet.Cells(7 + i, 18))) > 0 Then Call S_UpdateDataInDB(VsData)
Next i

MsgBox "Data has been saved"

and

Sub S_UpdateDataInDB(VsData)
Dim cnDB As New ADODB.Connection

VsDBPath = ThisWorkbook.Sheets("Settings").Range("B2").Value
VsTable = "KCD"
cnDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & VsDBPath & ";" & "Jet OLEDB:Engine Type=5;" & "Persist Security Info=False;"
cnDB.Execute ("UPDATE  " & VsTable & " " & VsData)
cnDB.Close

End Sub Differences here are: I want to just save text ("Data confirmed") for that particular cell. So if one wants to confirm data on Row 8 and clicks "Data confirm". It should only save "Data confirm" for row 8 in access.

JustinSane
  • 15
  • 4
  • How many rows does the excel file have? Maybe add a Column with a drop down, where the user can select "yes" or "no" and then at the end load the value of the column into the database? – MGP Oct 14 '20 at 12:20
  • Amount of rows isn't fixed. There's a possibilty to add rows so one can add as many lines he/she has to. Adding a column as you mention is another good way i think! Don't think it's possible to add a commandbutton on certain excel lines espacially when the lines aren't fixed – JustinSane Oct 14 '20 at 12:40
  • It is possible to add Command-Buttons dynamically (also per row), but they are not fixed to a certain row. Therefore it would be difficult to determine what row the command-Button is referencing to. – MGP Oct 14 '20 at 12:47
  • Figured that aswell, I'd need a variable amount of cmd buttons for a variable amount of rows whereas the reference for each cmd button is 1:1 with the rows Would be nice to it add x amount of cmd buttons for x amount of lines automatically. So whenever someone adds a line, it automatically "creates" an extra cmd button for that row aswell. I'll try to do it your way, should work aswell! – JustinSane Oct 14 '20 at 12:51

1 Answers1

0

Generally, when I'm trying to add a feature to every row in a column, I'll use a hyperlink. It fits neatly into the cell, it can be anchored to a specific cell, and it also shows when it's been followed (the color changes). I've mocked together some code as an example; try to adapt it to your application and let me know if you need help.

First, in a standard module, enter the following code to create the hyperlinks. Presumably, you'd embed this into the code that pulls the data.

Sub PullData()
    Dim sh As Worksheet
    Dim lastRow As Long
    
    'Pull the data
    ' DO STUFF
    
    'Identify the range of the pulled data
    Set sh = Sheets("PulledData")
    lastRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
    
    'Loop from row 2 through last row
    For i = 2 To lastRow
        'Assuming the 'save' option is in Column c
        sh.Cells(i, "C").Hyperlinks.Add Anchor:=sh.Cells(i, "C"), Address:="", _
        SubAddress:="", TextToDisplay:="Click To Save"
    Next i
End Sub

Next, in the worksheet code for the sheet with the data, enter the below code. This tells the application what to do when a hyperlink is clicked. I created a fake function that is meant to mimic saving the data. You can change this as needed, or use a different design if it suits your needs better.

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
basodre
  • 5,720
  • 1
  • 15
  • 23
  • 1/2 Thanks! The data is saved in an Access file and gets pulled in Excel whenever you open that particular Sheet. After seeing your comment i'm starting to wonder if your code will work how it should. Example. 1) There are 10 rows old data that gets pulled in the sheet. These have to get the "Confirmation button" so we know the value's are still correct. 2) Once confirmed, The confirmed data don't need this "confirmation button" again since its already confirmed. 3) People can add new lines aswell, these lines don't need this button. – JustinSane Oct 16 '20 at 10:39
  • 2/2 I feel like, when i implement your code, the "confirmation button" will appear every single time you open the Sheet (since it refreshes the data it pulls from Access). Data that is (1) Confirmed already or (2) is new ,recently added lines, don't have to get this "confirmation button". Understand what i mean? – JustinSane Oct 16 '20 at 10:41
  • @JustinSane You have the flexibility to modify this code to suit that need. For example, when adding the command buttons/hyperlinks, how should the program know if a record has been "confirmed"? Is that a field stored somewhere? This code would also run when you call it. So, if you only want it for records that are pulled from access, then embed it within that code, and it will only add to those rows. It won't run again (unless it is called). – basodre Oct 16 '20 at 12:56
  • Well i was thinking about adding another column in Access / Excel called "confirmation". When the data is pulled from Access into access , this column is empty so a hyperlink/button shows up. Once clicked, it saves "confirmed" in that newly added column for that particular row. When the sheet refreshes ( and data gets pulled again). That particular row should have "confirmed" in that column so no more hyperlink has to be shown for that particular row because its confirmed already. – JustinSane Oct 19 '20 at 09:13
  • Currently made it that far that once confirmed already, it doesn't give a hyperlink anymore. Just have to implement 2 other things. (1) Once a row is added recently, it shouldn't give a hyperlink because its "confirmed" already. (2) Save "Data confirmed" to a newly added access worksheet. Once Hyperlink is clicked, it should save "Data confirmed" into that access sheet. – JustinSane Oct 19 '20 at 11:14
  • @JustinSane (1) How are rows newly added? Wouldn't the code that adds the hyperlink be able to check if it's a newly added record? (2) Are you saving into Access or Excel? You can use whichever macro is fired when hyperlink is clicked to also update the data confirmed field. If you run into specific issues implementing this feature, definitely post back so we can help. It's just a bit hard to envision the issue, especially in these short comment boxes. – basodre Oct 19 '20 at 13:30
  • (1) the rows are added through a form. Input in Excel, saved in Access. (2) Data has to be saved into Access file since it gets pulled from there. I'm currently having problems with saving the "Data confirmation" to access. Thanks to your code, i've managed to give every row a hyperlink. Once you click on that hyperlink, it should save "Data confirmed" in column X , Row Y in Access. FE: There are 10 lines with each line having a hyperlink. When clicking on Hyperlink in row 8, It should save "Data confirmed" for row 8 in access. So i'm not sure how to code that – JustinSane Oct 19 '20 at 13:39
  • The only problem i'm having at this moment is to get the text "Data confirmed" saved to an external Access file once clicking on a random hyperlink. – JustinSane Oct 19 '20 at 14:16
  • To be honest, I'm not very familiar with Access. I believe it uses some type of `SQL` syntax for updates. If I'm not mistaken, you'd need to embed some sort of SQL to update the corresponding record in Access. First, get the unique id for the row (assuming there is some type of Primary Key in Access that gets pulled into Excel). The SQL syntax would be something like: `UPDATE accessTable SET field_name = "Data Confirmed" WHERE primary_key_field = 'primary_key_value'`. Hopefully that steers you in the right directions. – basodre Oct 19 '20 at 15:59
  • Thanks! I already have some sort of macro that saves excel data into Access but i can't seem to figure out how to implement it in this way. – JustinSane Oct 19 '20 at 16:30
  • @JustinSane Can you edit your original post to include the macro that saves to Access? Maybe we can figure it out from that. – basodre Oct 19 '20 at 16:34
  • I've added the code that saves excel data into Access. Please note that the code is not applicable here in its current form as its used for something different. – JustinSane Oct 19 '20 at 17:05
  • i'm stuck with the Private_Function part.. Not sure what to do there fml – JustinSane Oct 19 '20 at 17:20
  • As I've written it, the `Private Function` part takes the cell that contained the hyperlink as it's argument. So if the hyperlink is in Cell D15, then that value will be passed into the function. You mention that `if row 8 is in excel, then row 8 in access should be updated`. Is there any other identifier besides row position that links them? Is there an Id/primary key field. If so, I think this can be pretty easy to update. Otherwise, I don't know if access allows you to perform an update based on row number. If there is not an ID field, maybe create a new post spceifically for update info. – basodre Oct 19 '20 at 17:47
  • any idea how to implement the Update Query into the 2nd part of the code? Can't figure it out.. – JustinSane Nov 19 '20 at 14:46