operation not supported

[perky2go]

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

 

[perky2go]

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!

 

[perky2go]

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]

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?

 

[perky2go]

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!