View Single Post
  #1 (permalink)  
Old April 11th, 2006, 04:55 AM
LiamBFC LiamBFC is offline
Authorized User
Join Date: Apr 2006
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Query Problems

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.
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
        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,
Liam Gulliver,
A New Programming Chat Forum