Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 11th, 2006, 04:55 AM
Authorized User
Join Date: Apr 2006
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
Old April 11th, 2006, 06:22 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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:


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.



Old April 11th, 2006, 06:26 AM
Authorized User
Join Date: Apr 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts

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!

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA Problems with copy and paste bripers Excel VBA 6 September 1st, 2006 05:14 AM
Excel 2000 VBA Problems drsammyb Excel VBA 0 January 17th, 2005 11:41 AM
Problems introducing a formula using VBA mariona_cid Excel VBA 0 June 1st, 2004 07:01 PM
VBA in Outlook: Problems retrieving dates Sach VB How-To 1 April 13th, 2004 07:59 AM
Problems With New VBA Code Ben Horne Access VBA 4 September 22nd, 2003 07:19 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.