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
Similar Threads
VBA Query Problems