A
Amduke
Hello,
I encounter some problems finding information in a recordset.
I have 2 recordsets, rs_BO and rs_FTP.
Using a loop to run trough rs_BO, I would like to find information in
rs_FTP, find code hereafter, I encounter a run time error '3001' "Agruments
are of the wrong type, are out of acceptable range, or are in conflict with
one another" and I can't find the reason.
Here is the code:
Option Compare Database
'Declare variables
Dim SqlBO As String
Dim SqlFTP As String
Dim cnnstr As String
Dim m_datum As String
Dim m_PF As String
Dim m_PG As String
Dim m_VSS As String
'Dim m_RepType As String
Dim PFupd As Boolean
Dim PGupd As Boolean
Dim VSSupd As Boolean
Dim Mark As Variant
'Define connection in current database
Dim rs_BO As New ADODB.Recordset
Dim cnn, cnn2 As New ADODB.Connection
Dim rs_FTP As New ADODB.Recordset
Public Sub AddFormType()
'Fill variables with defaults
Count = 0
Set cnn = CurrentProject.Connection
'Define connection to an outside database
cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"
cnnstr = cnnstr + "Data Source=I:\be\_agb\agfaproj\logistiek\WOS\WOS
XP_be.mdb"
rs_BO.activeconnection = cnn
rs_FTP.activeconnection = cnnstr
'Opening the tables (Recordsets)
'Table 1
SqlBO = "Tbl_BackOrders"
rs_BO.Open SqlBO, cnn, adOpenStatic, adLockOptimistic
'Table 2
SqlFTP = "Tbl_FormTypeParam"
rs_FTP.Open SqlFTP ', cnn2, adOpenStatic, adLockOptimistic
'Positioning at the top of the table
rs_BO.MoveFirst
'Loop through all the records
Do Until rs_BO.EOF
'Fill up some variables
If IsNull(rs_BO.Fields("ProdFam").Value) Then
m_PF = ""
PFupd = False
Else
m_PF = rs_BO.Fields("ProdFam").Value
PFupd = True
End If
If IsNull(rs_BO.Fields("PG").Value) Then
m_PG = ""
PGupd = False
Else
m_PG = rs_BO.Fields("PG").Value
PGupd = True
End If
If IsNull(rs_BO.Fields("VSS").Value) Then
m_VSS = ""
VSSupd = False
Else
m_VSS = rs_BO.Fields("VSS").Value
VSSupd = True
End If
'Find the value in the parameter table containing the report type
description
rs_FTP.MoveFirst
'Check for variable PFupd
If PFupd = True Then
' Find first record satisfying search string. Exit
' loop if no such record exists.
Mark = 1
rs_FTP.Find "PF = " & m_PF, 1, adSearchForward, Mark
Do While rs_FTP.EOF <> True
Debug.Print " Report Type FOR pf = " & m_PF & "/ " &
rs_FTP.Fields("RepType")
Count = Count + 1 'Count the last PF found.
Mark = rs_FTP.RecordCount 'Note current position.
If Mark < 0 Then
Mark = 2
End If
rs_FTP.Find "PF = " & m_PF, 1, adSearchForward, Mark
Loop
'Now we have to search on the PG level to find out if there are
related items
If PGupd = True Then
PGloop
End If
Else
'Now we have to search on the PG level
' Find first record satisfying search string. Exit
' loop if no such record exists.
PGloop
End If
rs_BO.MoveNext
Loop
rs_BO.Close
rs_FTP.Close
End Sub
Public Sub PGloop()
rs_FTP.MoveFirst
Mark = 1
rs_FTP.Find "PG = " & m_PG, 1, adSearchForward, Mark
Do While rs_FTP.EOF <> True
Debug.Print rs_FTP.Bookmark & " Report Type FOR pg = " & m_PG &
"/ " & rs_FTP.Fields("RepType")
Mark = rs_FTP.RecordCount 'Note current position.
If Mark < 0 Then
Mark = 2
End If
rs_FTP.Find "PG = " & m_PG, 1, adSearchForward, Mark
Loop
End Sub
I encounter some problems finding information in a recordset.
I have 2 recordsets, rs_BO and rs_FTP.
Using a loop to run trough rs_BO, I would like to find information in
rs_FTP, find code hereafter, I encounter a run time error '3001' "Agruments
are of the wrong type, are out of acceptable range, or are in conflict with
one another" and I can't find the reason.
Here is the code:
Option Compare Database
'Declare variables
Dim SqlBO As String
Dim SqlFTP As String
Dim cnnstr As String
Dim m_datum As String
Dim m_PF As String
Dim m_PG As String
Dim m_VSS As String
'Dim m_RepType As String
Dim PFupd As Boolean
Dim PGupd As Boolean
Dim VSSupd As Boolean
Dim Mark As Variant
'Define connection in current database
Dim rs_BO As New ADODB.Recordset
Dim cnn, cnn2 As New ADODB.Connection
Dim rs_FTP As New ADODB.Recordset
Public Sub AddFormType()
'Fill variables with defaults
Count = 0
Set cnn = CurrentProject.Connection
'Define connection to an outside database
cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"
cnnstr = cnnstr + "Data Source=I:\be\_agb\agfaproj\logistiek\WOS\WOS
XP_be.mdb"
rs_BO.activeconnection = cnn
rs_FTP.activeconnection = cnnstr
'Opening the tables (Recordsets)
'Table 1
SqlBO = "Tbl_BackOrders"
rs_BO.Open SqlBO, cnn, adOpenStatic, adLockOptimistic
'Table 2
SqlFTP = "Tbl_FormTypeParam"
rs_FTP.Open SqlFTP ', cnn2, adOpenStatic, adLockOptimistic
'Positioning at the top of the table
rs_BO.MoveFirst
'Loop through all the records
Do Until rs_BO.EOF
'Fill up some variables
If IsNull(rs_BO.Fields("ProdFam").Value) Then
m_PF = ""
PFupd = False
Else
m_PF = rs_BO.Fields("ProdFam").Value
PFupd = True
End If
If IsNull(rs_BO.Fields("PG").Value) Then
m_PG = ""
PGupd = False
Else
m_PG = rs_BO.Fields("PG").Value
PGupd = True
End If
If IsNull(rs_BO.Fields("VSS").Value) Then
m_VSS = ""
VSSupd = False
Else
m_VSS = rs_BO.Fields("VSS").Value
VSSupd = True
End If
'Find the value in the parameter table containing the report type
description
rs_FTP.MoveFirst
'Check for variable PFupd
If PFupd = True Then
' Find first record satisfying search string. Exit
' loop if no such record exists.
Mark = 1
rs_FTP.Find "PF = " & m_PF, 1, adSearchForward, Mark
Do While rs_FTP.EOF <> True
Debug.Print " Report Type FOR pf = " & m_PF & "/ " &
rs_FTP.Fields("RepType")
Count = Count + 1 'Count the last PF found.
Mark = rs_FTP.RecordCount 'Note current position.
If Mark < 0 Then
Mark = 2
End If
rs_FTP.Find "PF = " & m_PF, 1, adSearchForward, Mark
Loop
'Now we have to search on the PG level to find out if there are
related items
If PGupd = True Then
PGloop
End If
Else
'Now we have to search on the PG level
' Find first record satisfying search string. Exit
' loop if no such record exists.
PGloop
End If
rs_BO.MoveNext
Loop
rs_BO.Close
rs_FTP.Close
End Sub
Public Sub PGloop()
rs_FTP.MoveFirst
Mark = 1
rs_FTP.Find "PG = " & m_PG, 1, adSearchForward, Mark
Do While rs_FTP.EOF <> True
Debug.Print rs_FTP.Bookmark & " Report Type FOR pg = " & m_PG &
"/ " & rs_FTP.Fields("RepType")
Mark = rs_FTP.RecordCount 'Note current position.
If Mark < 0 Then
Mark = 2
End If
rs_FTP.Find "PG = " & m_PG, 1, adSearchForward, Mark
Loop
End Sub