Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 11th, 2006, 04:55 AM
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.
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
__________________
Liam Gulliver,
http://p2p.neopian-hosting.com
A New Programming Chat Forum
Reply With Quote
  #2 (permalink)  
Old April 11th, 2006, 06:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


Reply With Quote
  #3 (permalink)  
Old April 11th, 2006, 06:26 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:37 PM.


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