CODEWITHSUNDEEP
sql-server-2005ms-accessvbscriptms-access-2003
pssguy
pssguy

Reputation: 3505

vb referencing a column via a variable

I am trying to update an Access database with a vb script by putting a soccer scoreline into a column dependent on when a goal is scored.

So I have a table, tblScoreLinesByMinute, with columns

matchID 1 2 3 4.... 89 90

which in my script I have set

Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblScoreLinesByMinute", dbOpenTable)

Let us say there was a goal scored by the home team in the 3rd minute I have captured this with a variable numTime = 3 and created a variable strScoreline = 10. I wish to update the table something like

rst![numTime] = strScoreline

but if I try this or CStr(numTime) I get a runtime error "Item not found in this collection". A hard coded

rst![3] = strScoreline does work

I am also keen to set the other columns to the relevant scoreline. Let us say that the away team equalized in the 88th minute. What I am after is having the row for this particular match showing a scoreline for columns 1,2 of '00', columns 3 to 87 inc '10' and columns 88-90 '11'

I could use mssql as well if there is a simpler method for that

Upvotes: 0

Views: 141

Answers (1)

HansUp
HansUp

Reputation: 97101

If I understand the first part of your question correctly, I think you should try this:

rst.Fields(numTime) = strScoreline

I have no clue about the second part of your question.

Edit: Your comment made me think your numTime text values were being interpreted as numbers rather than string values by rst.Fields. However, that doesn't appear to be the case here.

Opening a recordset based on the same table and field names, this code throws an error ("Item not found in this collection"):

numTime = "0"
Debug.Print rst.Fields(numTime)

But this code prints the value of the column named "2":

numTime = "2"
Debug.Print rst.Fields(numTime)

So I still don't understand why it's not working for you. But I've never used numbers as column names; it just seems wrong to me. I would be more surprised if you renamed your columns as MatchID, f1, f2, f3 and this approach still doesn't work.

Upvotes: 1

Related Questions