Referencing Access fields as variables (1 Viewer)

PaulCooper

New member
Local time
Tomorrow, 05:27
Joined
Feb 7, 2017
Messages
3
Hi

In the following code W1 = Week 1, W2 = Week 2 and so on. I am trying to write a loop to refer to W1 (access field) as a variable e.g "W" & i; i being 1 to 10 (weeks in a school term). I have had a good look around and can;t seem to find how to do this. All the other references work fine. Any help would be very much appreciated.

Kind Regards

Paul


Select Case j <> 100

Case rcdOutput![W1] = "x" Or rcdOutput![W1] = "X"

Sheet.Range("H" & i) = "P"

Sheet.Range("H" & i).Font.Name = "Wingdings 2"

Sheet.Range("H" & i).Font.Size = 11

week1 = week1 + 1

Case Else

Sheet.Range("H" & i) = rcdOutput![W1]

Sheet.Range("H" & i).Font.Name = "Calibri"

Sheet.Range("H" & i).Font.Size = 11

End Select
 

sneuberg

AWF VIP
Local time
Today, 11:27
Joined
Oct 17, 2014
Messages
3,506
If you are trying to refer to field "W" & i in a record set try

rcdOutput.Fields("W" & i )
 

PaulCooper

New member
Local time
Tomorrow, 05:27
Joined
Feb 7, 2017
Messages
3
In response to Jdraw's question "In plain English --no jargon and no quasi programming code --what are you trying to accomplish?"

I am trying to update an Excel Spreadsheet conditionally based on the content of each Access record. In the Access Data base there are columns W1 - W11 (weeks 1 to 11) and the staff put an "x" in the field if the child comes or something else e.g. "sick" if they do not. I have written the code for weeks 1 -11 in a verbose method that works fine. However, I'm now getting "Procedure too large" error so I want to cut the code down with a loop. As I say, references such as rcdOutput("W" & i).Value = " "
work just fine. It is referencing the access column name as a variable that is the issue.

Kind Regards

Paul
 

Users who are viewing this thread

Top Bottom