operation not supported

[Guest]

I have a form with a button that takes a ID from the form and searches for
related records in a table in the mdb. If it doesn't find a match there,
search on our server for data and append it to a temp table in the mdb. Then
I want to search that temp table and copy the related fields for the matching
record to my form. My code does append the values from the server to the temp
table, but then I get an error message "Operation is not supported for this
type of object"--I think on the line:
rst.FindFirst CritText
Part of the code I'm using is below. Since my second search is nested
within the If..Then..Else, I thought maybe the problem was that I hadn't
closed the previous record set...but adding a rst.close in the Else didn't
help.

Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
rst.FindFirst CritText

If Not rst.NoMatch Then
--then copy values from CLAIM table to my form (this works fine)
txtMbr = rst![MbrFullName] (etc.)
Else
--rst.close (get error with or without this line)
--run query that pulls data from our network server into a table in the mdb
DoCmd.OpenQuery "qappBatchPull"
Set rst = dbs.OpenRecordset("zsysTempBatchPull")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
--(same claim id so this variable value hasn't changed)
rst.FindFirst CritText ---ERROR MSG

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName] (etc.)

Is the problem confusion over the value of rst?
Something with the FindFirst and looking for a record when there is only one
rec?
Something else altogether?

Thanks for any help!

 

[Perry]

Which version of Access are we talking about?

By means of precautionary measures, I always declare
DAO typed recordsets explicitely as well as ADO recordsets

In yr case, replace
rst As Recordset

by
rst As DAO.Recordset

Kindly repost if this doesn't remedy
Krgrds,
Perry

 

[Guest]

I'm working in Access 97
tried adding dao.recordset, but same error message as before

Which version of Access are we talking about?

By means of precautionary measures, I always declare
DAO typed recordsets explicitely as well as ADO recordsets

In yr case, replace
rst As Recordset

by
rst As DAO.Recordset

Kindly repost if this doesn't remedy
Krgrds,
Perry

I have a form with a button that takes a ID from the form and searches for
related records in a table in the mdb. If it doesn't find a match there,
search on our server for data and append it to a temp table in the mdb.
Then
I want to search that temp table and copy the related fields for the
matching
record to my form. My code does append the values from the server to the
temp
table, but then I get an error message "Operation is not supported for
this
type of object"--I think on the line:
rst.FindFirst CritText
Part of the code I'm using is below. Since my second search is nested
within the If..Then..Else, I thought maybe the problem was that I hadn't
closed the previous record set...but adding a rst.close in the Else didn't
help.

Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
rst.FindFirst CritText

If Not rst.NoMatch Then
--then copy values from CLAIM table to my form (this works fine)
txtMbr = rst![MbrFullName] (etc.)
Else
--rst.close (get error with or without this line)
--run query that pulls data from our network server into a table in the
mdb
DoCmd.OpenQuery "qappBatchPull"
Set rst = dbs.OpenRecordset("zsysTempBatchPull")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
--(same claim id so this variable value hasn't changed)
rst.FindFirst CritText ---ERROR MSG

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName] (etc.)

Is the problem confusion over the value of rst?
Something with the FindFirst and looking for a record when there is only
one
rec?
Something else altogether?

Thanks for any help!

 

[Perry]

table, but then I get an error message "Operation is not supported for
Considering yr above statements, pls explain
What is this query "zsysTempBatchPull" (or table)

Krgrds,
Perry

I'm working in Access 97
tried adding dao.recordset, but same error message as before

Which version of Access are we talking about?

By means of precautionary measures, I always declare
DAO typed recordsets explicitely as well as ADO recordsets

In yr case, replace
rst As Recordset

by
rst As DAO.Recordset

Kindly repost if this doesn't remedy
Krgrds,
Perry

I have a form with a button that takes a ID from the form and searches
for
related records in a table in the mdb. If it doesn't find a match
there,
search on our server for data and append it to a temp table in the mdb.
Then
I want to search that temp table and copy the related fields for the
matching
record to my form. My code does append the values from the server to
the
temp
table, but then I get an error message "Operation is not supported for
this
type of object"--I think on the line:
rst.FindFirst CritText
Part of the code I'm using is below. Since my second search is nested
within the If..Then..Else, I thought maybe the problem was that I
hadn't
closed the previous record set...but adding a rst.close in the Else
didn't
help.

Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
rst.FindFirst CritText

If Not rst.NoMatch Then
--then copy values from CLAIM table to my form (this works fine)
txtMbr = rst![MbrFullName] (etc.)
Else
--rst.close (get error with or without this line)
--run query that pulls data from our network server into a table in
the
mdb
DoCmd.OpenQuery "qappBatchPull"
Set rst = dbs.OpenRecordset("zsysTempBatchPull")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
--(same claim id so this variable value hasn't changed)
rst.FindFirst CritText ---ERROR MSG

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName] (etc.)

Is the problem confusion over the value of rst?
Something with the FindFirst and looking for a record when there is
only
one
rec?
Something else altogether?

Thanks for any help!

 

[Guest]

Both "CLAIM" and "zsysTempBatchPull" are just tables within my mdb. CLAIM is
being populated elsewhere in the app and "zsysTempBatchPull" is being
populated by running the append query noted above from docmd.openquery.

I'm baffled why the FindFirst seems to work with one table and not another.
They both contain a field called "ClaimID" which is always unique where it
appears. In this particular case, the ClaimID I'm searching for would be the
same for both FindFirst--if I can't find it in CLAIM, then I run the append
query which pulls more data from the server into zsysTempBatchPull where I'm
trying to find the right record and copy into another temp table.

Considering yr above statements, pls explain
What is this query "zsysTempBatchPull" (or table)

Krgrds,
Perry

I'm working in Access 97
tried adding dao.recordset, but same error message as before

Which version of Access are we talking about?

By means of precautionary measures, I always declare
DAO typed recordsets explicitely as well as ADO recordsets

In yr case, replace
rst As Recordset

by
rst As DAO.Recordset

Kindly repost if this doesn't remedy
Krgrds,
Perry

"perky2go" <[email protected]> schreef in bericht
I have a form with a button that takes a ID from the form and searches
for
related records in a table in the mdb. If it doesn't find a match
there,
search on our server for data and append it to a temp table in the mdb.
Then
I want to search that temp table and copy the related fields for the
matching
record to my form. My code does append the values from the server to
the
temp
table, but then I get an error message "Operation is not supported for
this
type of object"--I think on the line:
rst.FindFirst CritText
Part of the code I'm using is below. Since my second search is nested
within the If..Then..Else, I thought maybe the problem was that I
hadn't
closed the previous record set...but adding a rst.close in the Else
didn't
help.

Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
rst.FindFirst CritText

If Not rst.NoMatch Then
--then copy values from CLAIM table to my form (this works fine)
txtMbr = rst![MbrFullName] (etc.)
Else
--rst.close (get error with or without this line)
--run query that pulls data from our network server into a table in
the
mdb
DoCmd.OpenQuery "qappBatchPull"
Set rst = dbs.OpenRecordset("zsysTempBatchPull")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
--(same claim id so this variable value hasn't changed)
rst.FindFirst CritText ---ERROR MSG

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName] (etc.)

Is the problem confusion over the value of rst?
Something with the FindFirst and looking for a record when there is
only
one
rec?
Something else altogether?

Thanks for any help!

 

[Perry]

Got it.

Y're invoking a new, second instance on rst when y're in the middle of
evaluating
the first instance of rst (the If Not rst.NoMatch).
In other words, Access is still processing the IF statement on the first
instance.
Eventhough, you close this recordset in the false branche of the IF/End IF
it's still in memory ...

My advise:
Close the recordset after the If/End If and use another object variable forDim rs as dao.recordset
Set rs = dbs.OpenRecordset("zsysTempBatchPull")

Kindly repost if this doesn't remedy ...
Krgrds,
Perry

Both "CLAIM" and "zsysTempBatchPull" are just tables within my mdb. CLAIM
is
being populated elsewhere in the app and "zsysTempBatchPull" is being
populated by running the append query noted above from docmd.openquery.

I'm baffled why the FindFirst seems to work with one table and not
another.
They both contain a field called "ClaimID" which is always unique where it
appears. In this particular case, the ClaimID I'm searching for would be
the
same for both FindFirst--if I can't find it in CLAIM, then I run the
append
query which pulls more data from the server into zsysTempBatchPull where
I'm
trying to find the right record and copy into another temp table.

table, but then I get an error message "Operation is not supported
for
this
type of object"

Set rst = dbs.OpenRecordset("zsysTempBatchPull")

Considering yr above statements, pls explain
What is this query "zsysTempBatchPull" (or table)

Krgrds,
Perry

I'm working in Access 97
tried adding dao.recordset, but same error message as before

:

Which version of Access are we talking about?

By means of precautionary measures, I always declare
DAO typed recordsets explicitely as well as ADO recordsets

In yr case, replace
rst As Recordset

by
rst As DAO.Recordset

Kindly repost if this doesn't remedy
Krgrds,
Perry

"perky2go" <[email protected]> schreef in bericht
I have a form with a button that takes a ID from the form and
searches
for
related records in a table in the mdb. If it doesn't find a match
there,
search on our server for data and append it to a temp table in the
mdb.
Then
I want to search that temp table and copy the related fields for the
matching
record to my form. My code does append the values from the server to
the
temp
table, but then I get an error message "Operation is not supported
for
this
type of object"--I think on the line:
rst.FindFirst CritText
Part of the code I'm using is below. Since my second search is
nested
within the If..Then..Else, I thought maybe the problem was that I
hadn't
closed the previous record set...but adding a rst.close in the Else
didn't
help.

Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
rst.FindFirst CritText

If Not rst.NoMatch Then
--then copy values from CLAIM table to my form (this works fine)
txtMbr = rst![MbrFullName] (etc.)
Else
--rst.close (get error with or without this line)
--run query that pulls data from our network server into a table in
the
mdb
DoCmd.OpenQuery "qappBatchPull"
Set rst = dbs.OpenRecordset("zsysTempBatchPull")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
--(same claim id so this variable value hasn't changed)
rst.FindFirst CritText ---ERROR MSG

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName] (etc.)

Is the problem confusion over the value of rst?
Something with the FindFirst and looking for a record when there is
only
one
rec?
Something else altogether?

Thanks for any help!

 

[Guest]

What you say makes sense to me, but I must still be missing something because
I get the same error. Here's the current code version, somewhat abbreviated.

Private Sub cmdLU_Click()
Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String, Flag As Boolean
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
Flag = False
rst.FindFirst CritText

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName]
Flag = True
End If
rst.Close

If Flag = False Then
DoCmd.OpenQuery "qappbatchpull"

Dim varClaim2 As String
varClaim2 = txtClaim.Value
Dim rst2 As DAO.Recordset, CritText2 As String
Set rst2 = dbs.OpenRecordset("zsysTempBatchpull")
CritText2 = "ClaimID = " & Chr(34) & varClaim2 & Chr(34)
rst2.FindFirst CritText2

If Not rst2.NoMatch Then
txtMbr = rst2![MbrFullName]
Else
MsgBox "No match for this claim found.", vbOKOnly, "Claim Not
Found"
End If

rst2.Close
End If

Set dbs = Nothing
End Sub

Got it.

Y're invoking a new, second instance on rst when y're in the middle of
evaluating
the first instance of rst (the If Not rst.NoMatch).
In other words, Access is still processing the IF statement on the first
instance.
Eventhough, you close this recordset in the false branche of the IF/End IF
it's still in memory ...

My advise:
Close the recordset after the If/End If and use another object variable forDim rs as dao.recordset
Set rs = dbs.OpenRecordset("zsysTempBatchPull")

Kindly repost if this doesn't remedy ...
Krgrds,
Perry

Both "CLAIM" and "zsysTempBatchPull" are just tables within my mdb. CLAIM
is
being populated elsewhere in the app and "zsysTempBatchPull" is being
populated by running the append query noted above from docmd.openquery.

I'm baffled why the FindFirst seems to work with one table and not
another.
They both contain a field called "ClaimID" which is always unique where it
appears. In this particular case, the ClaimID I'm searching for would be
the
same for both FindFirst--if I can't find it in CLAIM, then I run the
append
query which pulls more data from the server into zsysTempBatchPull where
I'm
trying to find the right record and copy into another temp table.

table, but then I get an error message "Operation is not supported
for
this
type of object"

Set rst = dbs.OpenRecordset("zsysTempBatchPull")

Considering yr above statements, pls explain
What is this query "zsysTempBatchPull" (or table)

Krgrds,
Perry

"perky2go" <[email protected]> schreef in bericht
I'm working in Access 97
tried adding dao.recordset, but same error message as before

:

Which version of Access are we talking about?

By means of precautionary measures, I always declare
DAO typed recordsets explicitely as well as ADO recordsets

In yr case, replace
rst As Recordset

by
rst As DAO.Recordset

Kindly repost if this doesn't remedy
Krgrds,
Perry

"perky2go" <[email protected]> schreef in bericht
I have a form with a button that takes a ID from the form and
searches
for
related records in a table in the mdb. If it doesn't find a match
there,
search on our server for data and append it to a temp table in the
mdb.
Then
I want to search that temp table and copy the related fields for the
matching
record to my form. My code does append the values from the server to
the
temp
table, but then I get an error message "Operation is not supported
for
this
type of object"--I think on the line:
rst.FindFirst CritText
Part of the code I'm using is below. Since my second search is
nested
within the If..Then..Else, I thought maybe the problem was that I
hadn't
closed the previous record set...but adding a rst.close in the Else
didn't
help.

Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
rst.FindFirst CritText

If Not rst.NoMatch Then
--then copy values from CLAIM table to my form (this works fine)
txtMbr = rst![MbrFullName] (etc.)
Else
--rst.close (get error with or without this line)
--run query that pulls data from our network server into a table in
the
mdb
DoCmd.OpenQuery "qappBatchPull"
Set rst = dbs.OpenRecordset("zsysTempBatchPull")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
--(same claim id so this variable value hasn't changed)
rst.FindFirst CritText ---ERROR MSG

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName] (etc.)

Is the problem confusion over the value of rst?
Something with the FindFirst and looking for a record when there is
only
one
rec?
Something else altogether?

Thanks for any help!

 

[Guest]

DoCmd.OpenQuery "qappBatchPull"
A bit hard to analyse without knowing what the queries are doing.
But from the name I presume above query appends data.

To which table ??
Is this the same table y're trying to write data to?

 

[Guest]

Sorry!
yes, the "qappbatchpull" is a query pulling data from our network server and
appending it to a table within the mdb called "zsysTempBatchpull".

The append query runs successfully and puts the record specific to the
claimid entered into the table "zsysTempBatchpull", but on the FindFirst
command I get the error message.

DoCmd.OpenQuery "qappBatchPull"

A bit hard to analyse without knowing what the queries are doing.
But from the name I presume above query appends data.

To which table ??
Is this the same table y're trying to write data to?

--
Krgrds,
Perry

I have a form with a button that takes a ID from the form and searches for
related records in a table in the mdb. If it doesn't find a match there,
search on our server for data and append it to a temp table in the mdb. Then
I want to search that temp table and copy the related fields for the matching
record to my form. My code does append the values from the server to the temp
table, but then I get an error message "Operation is not supported for this
type of object"--I think on the line:
rst.FindFirst CritText
Part of the code I'm using is below. Since my second search is nested
within the If..Then..Else, I thought maybe the problem was that I hadn't
closed the previous record set...but adding a rst.close in the Else didn't
help.

Dim varClaim As String
varClaim = txtClaim.Value
Dim dbs As Database, rst As Recordset, CritText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CLAIM")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
rst.FindFirst CritText

If Not rst.NoMatch Then
--then copy values from CLAIM table to my form (this works fine)
txtMbr = rst![MbrFullName] (etc.)
Else
--rst.close (get error with or without this line)
--run query that pulls data from our network server into a table in the mdb
DoCmd.OpenQuery "qappBatchPull"
Set rst = dbs.OpenRecordset("zsysTempBatchPull")
CritText = "ClaimID = " & Chr(34) & varClaim & Chr(34)
--(same claim id so this variable value hasn't changed)
rst.FindFirst CritText ---ERROR MSG

If Not rst.NoMatch Then
txtMbr = rst![MbrFullName] (etc.)

Is the problem confusion over the value of rst?
Something with the FindFirst and looking for a record when there is only one
rec?
Something else altogether?

Thanks for any help!