Referring to table names in code

[sda7bobp]

I have a table storing information about different drugs in inventory--number of unopened vials and amount in the one opened vial. The fields are named by the drug number and a "V" or "O" following designating full vials and volume remaining in the open vial. (1V, 1O, 2V, 2O, etc) I would like to add vials to the inventory through code and am having a hard time referring to the Field names in this table in my code. The variable I have assigned to the DrugID number from the Purchase form is "lngDrugID". I have tried rst.[lngDrugID & "V"], rst.["lngDrugID & 'V'"], etc. and get Error 3265 "Item not found in collection"

Any suggestions?
Bob

 

[VBslammer]

[tt]rst(lngDrugID & "V")[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[sda7bobp]

Still get the same error message.

 

[VBslammer]

Dim strID As String
Dim lngDrugID As Long

lngDrugID = 1
strID = CStr(lngDrugID & "V")

Debug.Print rst.Fields(strID)
Debug.Print rst(strID)
Debug.Print rst![strID]
Debug.Print rst!strID

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[sda7bobp]

I get the same error, Item not found in this collection with each of the attempts to refer to this field name. Her is all the code for this proceedure:

Dim strName As String, sngVials As Single
    Dim sngOldVials As Single, datDate As Date
    Dim lngDrugID As Long, strID As String
    
        strName = "tblInventory"
        datDate = Me.Date
        lngDrugID = Me.cboDrug
        sngVials = Me.txtVialsPurch
        strID = str(lngDrugID) & "V"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strName)
    
        If Not rst.EOF Then rst.MoveLast
    
        [COLOR=red]sngOldVials = rst![strID][/color]
        
        With rst:
            .AddNew
            ![strID] = sngOldVials + sngVials
            .Update
        End With
        
    rst.Close
    Set db = Nothing
    
        
End Sub

The red is where the error occurs.

Thanks for the time you are putting in on this.
Bob

 

[RoyVidar]

Look a bit closer at VBSlammers initial suggestion - it's the rounded version of the brakcets () not the square thingies you're using. Note also the str function leaves a leading space (to allow for "-" for negative numbers).

To repeat:

[tt]rst(lngDrugID & "V")[/tt]

Roy-Vidar

 

[sda7bobp]

Slammer,
Got it. Thanks for your help! I misread your code at first (read a C for a parenthesis).
thanks, thanks, thanks.
Bob