[thelist] VBA - specifying columns
Travis Reynolds
tr at hl-productions.com
Wed Apr 6 21:10:55 CDT 2005
Hey all -
I've hit a brickwall on a project I'm working on in Access and I can't
figure it out without restructuring the tables which I fear I will have to
do. Anyways, I'm trying to select a column in a table to return the data in
all the rows corresponding to that column. The column name is pulled from a
form. So far the code that I have will only return the first field in the
recordset and populates all the rows with that 1 value. I cannot figure out
how to return the data from the next rows. I know I need some sort of loop
structure and I've tried setting bookmarks and also have used MoveNext.
MoveNext works after you run the query manually each time but it still
populates the entire column with whichever row it last went to. I'm not sure
of what to do next. What kind of loop structure do I need to use? Am I
skipping a step? I've put the VBA code that I'm using at the bottom.
Thanks in advance for any help you all can give me.
Travis
----------------------------------
Option Compare Database
Dim cpRS As Recordset
Dim strMark As String
Global eCPRS As Boolean 'exit carpet recordset, set to True in
cmbHT_AfterUpdate()
Public Function setCarpetRS() As ADODB.Recordset
Set cpRS = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
cpRS.Open "tblCarpetTakeoff", cnn
eCPRS = False
'Set strMark = cpRS
End Function
Public Function getCarpetHT(ColumnName)
'allows selection of housetype by column in takeoff tables
If (eCPRS = True) Then
Call setCarpetRS
End If
'cpRS.Bookmark = strMark
If (eCPRS = False) Then
getCarpetHT = cpRS(ColumnName)
cpRS.MoveNext
'strMark = cpRS.Bookmark
ElseIf (cpRS.EOF) Then
eCPRS = True
End If
End Function
More information about the thelist
mailing list