How to get selected cell via ADO in access 2007

[luckycharms]

Hi Folks,

I'm trying to write a script that will fill empty cells in the selected column with the value of the first non-empty cell above (such code has been posted here before). However, I can't figure out how to get the selected cell into a recordset object in the first place (from which I assume i'd be able to address the column, and then run the code on that column).

Any help would be greatly appreciated!

thanks,
allie

 

[pbaldy]

It would have helped if you posted the code you found. Typically this would be done with a recordset sorted on a field that keeps the sometimes empty column in proper order. Then in a loop you use a variable to check against the current value; if the field is empty, populate it from the variable, else change the variable to the current value.

 

[luckycharms]

thanks for the reply. so, while i understand the algorithim, it's the getting of the column that is eluding me. How would I get a the column of the selected cell into a recordset, for example?


here's some example code i pulled from elsewhere:
Sub fillValues()

strPrevValue = ""

Do While rsCurr.EOF = False
If IsNull(rsCurr.Column1) Then
rsCurr.Column1 = strPrevValue
rsCurr.Update
Else
strPrevValue = rsCurr.Column1
End If
rsCurr.MoveNext
Loop

End Sub

 

[pbaldy]

Numerous ways. By name:

rsCurr!FieldName

by position (zero based):

rsCurr(0)

 

[luckycharms]

thanks again for the reply. My VB editor doesn't recognize "rsCurr" as anything (that is, it doesn't seem to be a reserved word, etc). How do I assign a column to rsCurr in the first place? thanks again, sorry for the newbie questions. VBA is a very occassional tool for me, so I don't know a bunch of the basics.

 

[pbaldy]

I used your variable name; I assumed that prior to that point in code, you would have opened a recordset with that name. Is that not the case?

 

[luckycharms]

Well, I've tried, but I can't seem to get a single column. I can get a SQL query into a recordset variable, but I have trouble grabbing particular columns from that recordset, and then looping through the cells of that column.

I'm including all the code below, with nothing left out. The lines of interest are marked with **.

Sub FillDown()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
myRecordSet.CursorType = adOpenDynamic
myRecordSet.LockType = adLockOptimistic

sql_query = "SELECT * FROM MyTable"
myRecordSet.Open sql_query

' ** have user enter column name, since i can't figure out how to find the selected cell
colname = InputBox("Enter Name of Column to Fill")
Dim f As ADODB.Field
rsCurr = myRecordSet.Fields(colname) ' ** Try to get the column of interest here. This is where i get errors for some reason.

strPrevValue = ""

Do While rsCurr.EOF = False
If IsNull(rsCurr.Column1) Then
rsCurr.Column1 = strPrevValue
rsCurr.Update
Else
strPrevValue = rsCurr.Column1
End If
rsCurr.MoveNext
Loop

End Sub

 

[pbaldy]

Ah, you're using the wrong variable. The recordset is myRecordSet, so:

myRecordSet(colname)

should return the value of the requested column. I would include an ORDER BY clause with your SQL. With what you're trying to do, you want to make sure records are in the required order. rsCurr is an undeclared variable that you're trying to use as a recordset. I suspect you copied code and have two different variables going, which you need to clean up.

 

[pbaldy]

This is untested and needs an ORDER BY clause, but gives you a better start:

  Dim cnn1                    As ADODB.Connection
  Set cnn1 = CurrentProject.Connection
  Dim myRecordSet             As New ADODB.Recordset
  Dim sql_query As String, colname As String, strPrevValue As String
  
  myRecordSet.ActiveConnection = cnn1
  myRecordSet.CursorType = adOpenDynamic
  myRecordSet.LockType = adLockOptimistic

  sql_query = "SELECT * FROM MyTable"
  myRecordSet.Open sql_query
  colname = InputBox("Enter Name of Column to Fill")

  'this assume the first record's field will never be Null
  'and that the table will always contain records
  strPrevValue = myRecordSet.Fields(colname)

  Do While Not myRecordSet.EOF
    If IsNull(myRecordSet.Fields(colname)) Then
      myRecordSet.Fields(colname) = strPrevValue
      myRecordSet.Update
    Else
      strPrevValue = myRecordSet.Fields(colname)
    End If
    myRecordSet.MoveNext
  Loop

You also need to clean up by setting variables to nothing that got Set.

 

[luckycharms]

ok, what a PITA this was. 2 days working on this crap to probably save me half a day's manual entry work! oh well, such is life.

I figured I'd post my final code here for all those who might need to do the same thing. Note that I added the ADO library to access VBA prior to running this code.

Note that I never figured out how to do it by using the current column of the selected cell in grid view, nor did i figure out how to do it by opening the entire table, and then just operating on the specified column via the RecordSet.Fields collection.

Thanks for your help!

Sub FillDown()
    Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
    Dim myRecordSet As New ADODB.Recordset
    Dim col As New ADODB.Recordset
    myRecordSet.ActiveConnection = cnn1
    myRecordSet.CursorType = adOpenDynamic
    myRecordSet.LockType = adLockOptimistic
    'myRecordSet.CursorLocation = adUseClient ' necessary to make RecordCount work, but also breaks the update loop
    
    colname = InputBox("Enter Name of Column to Fill")
    'myRecordSet.Open ("[TableName]")
    sql_query = "SELECT [" & colname & "] FROM [TableName] Order By ID" 'Make sure to order the rows such that the empty rows properly appear below the filled ones
    myRecordSet.Open sql_query
    
    strPrevValue = ""
    
    Do While myRecordSet.EOF = False
      If IsNull(myRecordSet(0)) Then
        myRecordSet(0).Value = strPrevValue
        myRecordSet.Update
      Else
        strPrevValue = myRecordSet(0).Value
      End If
      myRecordSet.MoveNext
    Loop
    
End Sub