VBA Query Problems

LiamBFC · #1 (**permalink**) April 11th, 2006, 05:55 AM

Right, well I'm working on a small 3 step system that searches the Database for a value entered by the user and if it's there, the user will proceed to step 2.

Now I know this code isn't the most appealing but with a deadline of the end of the week to get the whole thing done, I've not had time to do what I really want to :(, however this 3 step system is part of a much bigger application.

Code:

Private Sub Form_Load()
    Dim tRef As Integer
    tRef = Me.OpenArgs
    Me.txbRef.Value = tRef

    'set SQL query to get request values
    Dim strSQL As String
    strSQL = "SELECT OrderList.R1, OrderList.R2, OrderList.R3, OrderList.R4, OrderList.R5, 
OrderList.R6, OrderList.R7, OrderList.R8, OrderList.R9, OrderList.R10, OrderList.R11, 
OrderList.R12, OrderList.R13, OrderList.R14, OrderList.R14A, OrderList.R14B, 
OrderList.R14C, OrderList.R14D, OrderList.R19, OrderList.R20, OrderList.R15 FROM OrderList
WHERE [REFERRAL No]= " & tRef & ""

    Dim reqRS As New ADODB.Recordset
    reqRS.Open strSQL, CurrentProject.Connection

    If Not reqRS.EOF Then
        Dim tR1 As Integer
        Dim tR2 As Integer
        Dim tR3 As Integer
        Dim tR4 As Integer
        Dim tR5 As Integer
        Dim tR6 As Integer
        Dim tR7 As Integer
        Dim tR8 As Integer
        Dim tR9 As Integer
        Dim tR10 As Integer
        Dim tR11 As Integer
        Dim tR12 As Integer
        Dim tR13 As Integer
        Dim tR14 As Integer
        Dim tR14A As Integer
        Dim tR14B As Integer
        Dim tR14C As Integer
        Dim tR14D As Integer
        Dim tR19 As Integer
        Dim tR20 As Integer
        Dim tR15 As Integer

        tR1 = reqRS.Fields(R1).Value
        tR2 = reqRS.Fields(R2).Value
        tR3 = reqRS.Fields(R3).Value
        tR4 = reqRS.Fields(R4).Value
        tR5 = reqRS.Fields(R5).Value
        tR6 = reqRS.Fields(R6).Value
        tR7 = reqRS.Fields(R7).Value
        tR8 = reqRS.Fields(R8).Value
        tR9 = reqRS.Fields(R9).Value
        tR10 = reqRS.Fields(R10).Value
        tR11 = reqRS.Fields(R11).Value
        tR12 = reqRS.Fields(R12).Value
        tR13 = reqRS.Fields(R13).Value
        tR14 = reqRS.Fields(R14).Value
        tR14A = reqRS.Fields(R14A).Value
        tR14B = reqRS.Fields(R14B).Value
        tR14C = reqRS.Fields(R14C).Value
        tR14D = reqRS.Fields(R14D).Value
        tR19 = reqRS.Fields(R19).Value
        tR20 = reqRS.Fields(R20).Value
        tR15 = reqRS.Fields(R15).Value

        R1.Value = tR1
        R2.Value = tR2
        R3.Value = tR3
        R4.Value = tR4
        R5.Value = tR5
        R6.Value = tR6
        R7.Value = tR7
        R8.Value = tR8
        R9.Value = tR9
        R10.Value = tR10
        R11.Value = tR11
        R12.Value = tR12
        R13.Value = tR13
        R14.Value = tR14
        R14A.Value = tR14A
        R14B.Value = tR14B
        R14C.Value = tR14C
        R14D.Value = tR14D
        R19.Value = tR19
        R20.Value = tR20
        R15.Value = tR15
    Else
        MsgBox ("ERROR!")
    End If

End Sub

The problem I'm having is that when the code is excuted, it errors saying that "Item cannot be found in the collection corresponding to the requested name or ordinal.". However, the value of tRef comes through fine, and I have run the query myself and not had a problem as it has returned the correct results, but within VBA, the above error occurs.

Thanks for any help you can give in advance,
LiamBFC

Bob Bedell · #2 (**permalink**) April 11th, 2006, 07:22 AM

If, for example, R1 is a field name, as it appears to be, it should be in double quotes so VBA doesn't think its a variable, as in:

reqRS.Fields("R1").Value

But then reqRS.Fields(R1).Value shouldn't compile unless R1 is defined somewhere else as a variable. Same with R1.Value. Is this assigning the value of tR1 to the field R1? But the previous code already assigned the value of R1 to the variable tR1. It just looks redundant or something.

HTH,

Bob

LiamBFC · #3 (**permalink**) April 11th, 2006, 07:26 AM

R1.Value = tR1 is assigning the variable tR1 to the text field R1 on the form.

Ah I though I'd missed something, thatnks for the help!

Need to download code?

Navigation