Type Error

[nathan sanders]

Hi,

When I try and run this code i get an error message on the .requery telling
me that: Error '3251' Operation not supported for this type of object'

What's happening please?


Public Function ChangeField(rstTable As Recordset, intID As Date,
strFieldName As String, varValue As Variant) As Integer
With rstTable
.Requery
.FindFirst "ID = " & intID
If .NoMatch = True Then
ChangeField = -1
Else
.Edit
.Fields(strFieldName).Value = varValue
.Update
ChangeField = intID
End If
End With
End Function


Thanks
Nathan

 

[Ken Snell [MVP]]

How are you running the code? Are you calling it from other code? Are you
trying to run it as posted?

 

[nathan sanders]

Ken,

Good point. Following is the 'other' code...
Sub process_bank_trans()

Dim strsq1
Dim TXTDATA As Recordset
Dim rstTrans, rstsumm As Recordset
Dim dbs As Database
Dim strsql, strfil1 As String
Dim trnval As Currency
Dim trncount As Long
Dim hashtot As Long
Dim FILDATE As Date
Dim intreturn As Integer


'FILDATE = InputBox("Enter Payment Date")
Set dbs = CurrentDb
strsql = _
"SELECT * from indata"
Set TXTDATA = dbs.OpenRecordset(strsql)
Set rstTrans = dbs.OpenRecordset("transactions")
Set rstsumm = dbs.OpenRecordset("summary")

Do Until TXTDATA.EOF

FILDATE = TXTDATA!field1
trnval = TXTDATA!field2
Select Case Left(TXTDATA!field3, 20)

Case "DD CALTEX N Z LTD CA" 'handle
fuel account payment

trnval = 0 - trnval
rstTrans.AddNew
rstTrans!TRN_DATE = FILDATE
rstTrans!TRN_PAY = trnval
rstTrans!crednum = 81
rstTrans.Update



Case "DC AMERICAN EXPRESS "

intreturn = ChangeField(rstsumm, FILDATE, "amexpaid", trnval)

..
..
..
..
..
..
..
..


..

 

[Justin Hoffman]

Hi,

When I try and run this code i get an error message on the .requery
telling me that: Error '3251' Operation not supported for this type of
object'

What's happening please?


Public Function ChangeField(rstTable As Recordset, intID As Date,
strFieldName As String, varValue As Variant) As Integer
With rstTable
.Requery
.FindFirst "ID = " & intID
If .NoMatch = True Then
ChangeField = -1
Else
.Edit
.Fields(strFieldName).Value = varValue
.Update
ChangeField = intID
End If
End With
End Function


Thanks
Nathan

intID As Date? Was that not supposed to be integer or long?

You should also specify which type of recordset you are using. In your
case: rstTable As DAO.Recordset to distinguish it from ADODB.Recordset.

I don't really see the point of such a generalised function - especially
when there is no error handling. And since varValue is variant it could be
null when you assign it to a required field, or it could equal 'banana' when
you assign it to a date field.

Also slightly odd to return an integer - you could just return true or false
since you already know the id.

 

[Ken Snell [MVP]]

Justin has pointed out the problem.. FILDATE is declared as a Date variable
in your main code, and you use that variable as the second argument when you
call your function. But the second argument in the function is declared as
an integer. That mismatch is your problem.


--

Ken Snell
<MS ACCESS MVP>

 

[nathan sanders]

Ken, Justin,

Thanks for your help. The joys of cut&paste! I have moved the code that
changes the record into my case statement. It still gets the same type
error on the .requery line. I changed the declaration of the recordset to be
dao.recordset (although I do not understand what that is about). new revised
code is below;

Sub process_bank_trans()

Dim MLIST, MESSTEXT, strsq1
Dim TXTDATA As dao.Recordset
Dim rstTrans, rstsumm As dao.Recordset
Dim dbs As Database
Dim strsql, strfil1 As String
Dim trnval As Currency
Dim trncount As Long
Dim hashtot As Long
Dim FILDATE As Date
Dim intreturn As Integer


'FILDATE = InputBox("Enter Payment Date")
Set dbs = CurrentDb
strsql = _
"SELECT * from indata"
Set TXTDATA = dbs.OpenRecordset(strsql)
Set rstTrans = dbs.OpenRecordset("transactions")
Set rstsumm = dbs.OpenRecordset("summary")

Do Until TXTDATA.EOF

FILDATE = TXTDATA!field1
trnval = TXTDATA!field2
Select Case Left(TXTDATA!field3, 20)

Case "DD CALTEX N Z LTD CA" 'handle
fuel account payment

trnval = 0 - trnval
rstTrans.AddNew
rstTrans!TRN_DATE = FILDATE
rstTrans!TRN_PAY = trnval
rstTrans!crednum = 81
rstTrans.Update



Case "DC AMERICAN EXPRESS "

With rstsumm
.Requery
.FindFirst "date= " & FILDATE

If .NoMatch = True Then

Else

.Edit
.Fields("amexpaid").Value = trnval
.Update

End If
End With

Thanks again
Nathan

 

[Justin Hoffman]

Ken, Justin,

Thanks for your help. The joys of cut&paste! I have moved the code that
changes the record into my case statement. It still gets the same type
error on the .requery line. I changed the declaration of the recordset to
be dao.recordset (although I do not understand what that is about). new
revised code is below;

Sub process_bank_trans()

Dim MLIST, MESSTEXT, strsq1
Dim TXTDATA As dao.Recordset
Dim rstTrans, rstsumm As dao.Recordset
Dim dbs As Database
Dim strsql, strfil1 As String
Dim trnval As Currency
Dim trncount As Long
Dim hashtot As Long
Dim FILDATE As Date
Dim intreturn As Integer


'FILDATE = InputBox("Enter Payment Date")
Set dbs = CurrentDb
strsql = _
"SELECT * from indata"
Set TXTDATA = dbs.OpenRecordset(strsql)
Set rstTrans = dbs.OpenRecordset("transactions")
Set rstsumm = dbs.OpenRecordset("summary")

Do Until TXTDATA.EOF

FILDATE = TXTDATA!field1
trnval = TXTDATA!field2
Select Case Left(TXTDATA!field3, 20)

Case "DD CALTEX N Z LTD CA" 'handle
fuel account payment

trnval = 0 - trnval
rstTrans.AddNew
rstTrans!TRN_DATE = FILDATE
rstTrans!TRN_PAY = trnval
rstTrans!crednum = 81
rstTrans.Update



Case "DC AMERICAN EXPRESS "

With rstsumm
.Requery
.FindFirst "date= " & FILDATE

If .NoMatch = True Then

Else

.Edit
.Fields("amexpaid").Value = trnval
.Update

End If
End With

Thanks again
Nathan

Without commenting on the code as a whole, if you are getting type errors
then you need to watch your variable types and dimensioning them. I always
dimension each variable on a new line (sometimes with an explanation of what
it is) whereas you have dimensioned multiple variables on one line.
If you do this:
Dim strsql, strfil1 As String
You dimension the first as a variant and the second as a string. You could
use:
Dim strsql As String, strfil1 As String to dimension two strings
But I would just always use a new line:
Dim strsql As String
Dim strfil1 As String

By keeping a naming convention, you may help avoid confusion so make sure
all your recordsets begin with rst, dates with dte, etc, etc. Also don't
forget dbs is part of the DAO object library (i.e. DAO.Database)