Recordsetclone mistake

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Doing the following I'm getting a type mismatch mistake.
Private Sub Form_Current()
Dim ii As Long, iii As Long
Dim rst As Recordset
ii = 0
iii = 0
Set rst = Me.RecordsetClone ' here is a mistake
'Run time error '13'. Type mismatch
'Clone form's recordset
With rst
.MoveLast
.MoveFirst
Do Until .EOF
ii = [txtTextBox1] * [txtTextBox2]
iii = iii + ii
.MoveNext
Loop
End With

rst.Close
End Sub

Can anybody advise what's wrong there.

Thanks
 
Alex said:
Doing the following I'm getting a type mismatch mistake.
Private Sub Form_Current()
Dim ii As Long, iii As Long
Dim rst As Recordset
ii = 0
iii = 0
Set rst = Me.RecordsetClone ' here is a mistake
'Run time error '13'. Type mismatch
'Clone form's recordset
With rst
.MoveLast
.MoveFirst
Do Until .EOF
ii = [txtTextBox1] * [txtTextBox2]
iii = iii + ii
.MoveNext
Loop
End With

rst.Close
End Sub

Can anybody advise what's wrong there.

Thanks
Click to expand...

If you are using Access 2000 or Access 2002, you probably need to add a
reference to DAO (the Microsoft DAO 3.6 Object Library). That reference
is added from the VB Editor environment, by clicking Tools ->
References..., locating the reference in the list, and putting a check
mark next to it.

Then, if you are using Access 2000, 2002, or 2003, make sure Access
knows that it's a DAO recordset you are declaring, not the
(incompatible) ADO version of the Recordset object, by modifying your
Dim statement as follows:

Dim rst As DAO.Recordset

Incidentally, your call to the recordset's .MoveLast method serves no
purpose that I can see.
 
Thanks a lot. Now, rst is fine.
But, ii = txtTextbox1 * txtTextbox2 is not working. The
mistake is "Run-time error '2447'. There is an invalid use
of the .(dot) or ! operator or invalid parentheses."

I think the problem is that I'm getting textbox1 from
cboBox1.Column(1). In the Form Control source for
txtTextbox1 is =cboBox1.Column(1).

Then, I tried ii = Me.[cboBox1].Column(1) * Me.txtTextbox2.
There is no any error message but iii = iii + ii
and .MoveNext doesn't change the values and ii is always
the same.

Then, I tried to add
DoCmd.GoToRecord acDataForm, strFormName, acNext

The values are changed and calculation is correct. But, I
have a problem with .EOF.
It doesn't know when it reaches the end of the records and
on the last record I have a mistake.

Could you advise how to handle with it.

Thanks




-----Original Message-----
Doing the following I'm getting a type mismatch mistake.
Private Sub Form_Current()
Dim ii As Long, iii As Long
Dim rst As Recordset
ii = 0
iii = 0
Set rst = Me.RecordsetClone ' here is a mistake
'Run time error '13'. Type mismatch
'Clone form's recordset
With rst
.MoveLast
.MoveFirst
Do Until .EOF
ii = [txtTextBox1] * [txtTextBox2]
iii = iii + ii
.MoveNext
Loop
End With

rst.Close
End Sub

Can anybody advise what's wrong there.

Thanks
Click to expand...

If you are using Access 2000 or Access 2002, you probably need to add a
reference to DAO (the Microsoft DAO 3.6 Object Library). That reference
is added from the VB Editor environment, by clicking Tools ->
References..., locating the reference in the list, and putting a check
mark next to it.

Then, if you are using Access 2000, 2002, or 2003, make sure Access
knows that it's a DAO recordset you are declaring, not the
(incompatible) ADO version of the Recordset object, by modifying your
Dim statement as follows:

Dim rst As DAO.Recordset

Incidentally, your call to the recordset's .MoveLast method serves no
purpose that I can see.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Alex said:
Thanks a lot. Now, rst is fine.
But, ii = txtTextbox1 * txtTextbox2 is not working. The
mistake is "Run-time error '2447'. There is an invalid use
of the .(dot) or ! operator or invalid parentheses."

I think the problem is that I'm getting textbox1 from
cboBox1.Column(1). In the Form Control source for
txtTextbox1 is =cboBox1.Column(1).

Then, I tried ii = Me.[cboBox1].Column(1) * Me.txtTextbox2.
There is no any error message but iii = iii + ii
and .MoveNext doesn't change the values and ii is always
the same.

Then, I tried to add
DoCmd.GoToRecord acDataForm, strFormName, acNext

The values are changed and calculation is correct. But, I
have a problem with .EOF.
It doesn't know when it reaches the end of the records and
on the last record I have a mistake.

Could you advise how to handle with it.

Thanks

-----Original Message-----
Alex said:
Doing the following I'm getting a type mismatch mistake.
Private Sub Form_Current()
Dim ii As Long, iii As Long
Dim rst As Recordset
ii = 0
iii = 0
Set rst = Me.RecordsetClone ' here is a mistake
'Run time error '13'. Type mismatch
'Clone form's recordset
With rst
.MoveLast
.MoveFirst
Do Until .EOF
ii = [txtTextBox1] * [txtTextBox2]
iii = iii + ii
.MoveNext
Loop
End With

rst.Close
End Sub

Can anybody advise what's wrong there.

Thanks
Click to expand...

If you are using Access 2000 or Access 2002, you probably need to
add a reference to DAO (the Microsoft DAO 3.6 Object Library). That
reference is added from the VB Editor environment, by clicking Tools
-> References..., locating the reference in the list, and putting a
check mark next to it.

Then, if you are using Access 2000, 2002, or 2003, make sure Access
knows that it's a DAO recordset you are declaring, not the
(incompatible) ADO version of the Recordset object, by modifying your
Dim statement as follows:

Dim rst As DAO.Recordset

Incidentally, your call to the recordset's .MoveLast method serves no
purpose that I can see.
Click to expand...
Click to expand...

Your error now comes from the fact that you aren't qualifying the field
names with the recordset object. That presupposes that "txtTextbox1"
and "txtTextbox2" are the names of fields in the form's recordset. If
that is the case, you would have to change this:
ii = [txtTextBox1] * [txtTextBox2]
Click to expand...
Click to expand...
Click to expand...

to this:

ii = ![txtTextBox1] * ![txtTextBox2]

Also, remove this line altogether:

You didn't open this recordset, so you shouldn't close it.

I assume that you have some code in that routine that you haven't shown
me, because otherwise you're performing that calculation and never doing
anything with the result. If you don't get what you want out of the
corrected procedure, you'd better explain what it is you're trying to
do. As it stands, I don't see any reason for you to be using
DoCmd.GoToRecord acDataForm, strFormName, acNext
Click to expand...

, since you're navigating through the form's recordsetclone using
rst.MoveNext.
 
Thanks a lot, Dirk.

As you asked for an explanation, I need to get a total for
txtDol to show it in the textbox1 on a formfoot of a
continious form.
This continious form is linked with Table1 to be able to
edit data there from this form.

txtDol on this form = [txtRate]*([txtShiftDay]*
[txtStaffDay]+[txtShiftAFT]*[txtStaffAFT]) where

txtRate = Control Source = [cboRateCode].Column(1)
cboRateCode has Control Source [Rate Code] from Table1 but
Row Source is Table2.

ii = [txtRate]*([txtShiftDay]*[txtStaffDay]+[txtShiftAFT]*
[txtStaffAFT]) gives a mistake because it cannot recognize
txtRate. It says "Run-time error '2447'. There is an
invalid use of the .(dot) or ! operator or invalid
parentheses."

When I'm using the advised by you the same but with "!"
(!txtTextBox]) I'm getting "Error '3265'. Item not found
in this collection."

Thanks



-----Original Message-----
Thanks a lot. Now, rst is fine.
But, ii = txtTextbox1 * txtTextbox2 is not working. The
mistake is "Run-time error '2447'. There is an invalid use
of the .(dot) or ! operator or invalid parentheses."

I think the problem is that I'm getting textbox1 from
cboBox1.Column(1). In the Form Control source for
txtTextbox1 is =cboBox1.Column(1).

Then, I tried ii = Me.[cboBox1].Column(1) * Me.txtTextbox2.
There is no any error message but iii = iii + ii
and .MoveNext doesn't change the values and ii is always
the same.

Then, I tried to add
DoCmd.GoToRecord acDataForm, strFormName, acNext

The values are changed and calculation is correct. But, I
have a problem with .EOF.
It doesn't know when it reaches the end of the records and
on the last record I have a mistake.

Could you advise how to handle with it.

Thanks

-----Original Message-----
Doing the following I'm getting a type mismatch mistake.
Private Sub Form_Current()
Dim ii As Long, iii As Long
Dim rst As Recordset
ii = 0
iii = 0
Set rst = Me.RecordsetClone ' here is a mistake
'Run time error '13'. Type mismatch
'Clone form's recordset
With rst
.MoveLast
.MoveFirst
Do Until .EOF
ii = [txtTextBox1] * [txtTextBox2]
iii = iii + ii
.MoveNext
Loop
End With

rst.Close
End Sub

Can anybody advise what's wrong there.

Thanks

If you are using Access 2000 or Access 2002, you probably need to
add a reference to DAO (the Microsoft DAO 3.6 Object Library). That
reference is added from the VB Editor environment, by clicking Tools
-> References..., locating the reference in the list, and putting a
check mark next to it.

Then, if you are using Access 2000, 2002, or 2003, make sure Access
knows that it's a DAO recordset you are declaring, not the
(incompatible) ADO version of the Recordset object, by modifying your
Dim statement as follows:

Dim rst As DAO.Recordset

Incidentally, your call to the recordset's .MoveLast method serves no
purpose that I can see.
Click to expand...
Click to expand...

Your error now comes from the fact that you aren't qualifying the field
names with the recordset object. That presupposes that "txtTextbox1"
and "txtTextbox2" are the names of fields in the form's recordset. If
that is the case, you would have to change this:
ii = [txtTextBox1] * [txtTextBox2]
Click to expand...
Click to expand...

to this:

ii = ![txtTextBox1] * ![txtTextBox2]

Also, remove this line altogether:

You didn't open this recordset, so you shouldn't close it.

I assume that you have some code in that routine that you haven't shown
me, because otherwise you're performing that calculation and never doing
anything with the result. If you don't get what you want out of the
corrected procedure, you'd better explain what it is you're trying to
do. As it stands, I don't see any reason for you to be using
DoCmd.GoToRecord acDataForm, strFormName, acNext
Click to expand...

, since you're navigating through the form's recordsetclone using
rst.MoveNext.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Alex said:
Thanks a lot, Dirk.

As you asked for an explanation, I need to get a total for
txtDol to show it in the textbox1 on a formfoot of a
continious form.
This continious form is linked with Table1 to be able to
edit data there from this form.

txtDol on this form = [txtRate]*([txtShiftDay]*
[txtStaffDay]+[txtShiftAFT]*[txtStaffAFT]) where

txtRate = Control Source = [cboRateCode].Column(1)
cboRateCode has Control Source [Rate Code] from Table1 but
Row Source is Table2.

ii = [txtRate]*([txtShiftDay]*[txtStaffDay]+[txtShiftAFT]*
[txtStaffAFT]) gives a mistake because it cannot recognize
txtRate. It says "Run-time error '2447'. There is an
invalid use of the .(dot) or ! operator or invalid
parentheses."

When I'm using the advised by you the same but with "!"
(!txtTextBox]) I'm getting "Error '3265'. Item not found
in this collection."
Click to expand...

That's because, as you now explain, these text boxes -- at least,
txtRate -- are not fields in the form's recordset; they are unbound
controls on the form. So the form knows about them, but its recordset
does not.

I think the best way to go about this is to base the form, not on
Table1, but on a query that joins Table1 and Table2 appropriately and
picks up the necessary fields from Table2. Then you can just use the
Sum() function to calculate the total for the text box in the form's
footer.

If you will please post the true names and field lists from the both
"Table1" and "Table2" -- and any other tables that are involved -- I may
be able to give more specific advice.
 
Thanks a lot again, Dirk.

It's easy to get the sum by using a query for these two
tables and get the form by using it. But, my concern is
that in this case I'll loose the opportunity to edit data
in Table1 directly from the form and I'll need to use
recordset with loop to assign edited data to the table. I
was trying to avoid it.

Please, advise if there is any other ways.

Table1 is actually a variable name (there are some tables
with product code' names). A user chooses this product
code from another form then the program assigns it to
strCode and Me.RecordSource = strCode
tblTable1 tblRate
Operation Rate Code
StaffDay Rate
ShiftDay
StaffAFT
ShiftAFT
RateCode


Alex
-----Original Message-----
Thanks a lot, Dirk.

As you asked for an explanation, I need to get a total for
txtDol to show it in the textbox1 on a formfoot of a
continious form.
This continious form is linked with Table1 to be able to
edit data there from this form.

txtDol on this form = [txtRate]*([txtShiftDay]*
[txtStaffDay]+[txtShiftAFT]*[txtStaffAFT]) where

txtRate = Control Source = [cboRateCode].Column(1)
cboRateCode has Control Source [Rate Code] from Table1 but
Row Source is Table2.

ii = [txtRate]*([txtShiftDay]*[txtStaffDay]+ [txtShiftAFT]*
[txtStaffAFT]) gives a mistake because it cannot recognize
txtRate. It says "Run-time error '2447'. There is an
invalid use of the .(dot) or ! operator or invalid
parentheses."

When I'm using the advised by you the same but with "!"
(!txtTextBox]) I'm getting "Error '3265'. Item not found
in this collection."
Click to expand...

That's because, as you now explain, these text boxes -- at least,
txtRate -- are not fields in the form's recordset; they are unbound
controls on the form. So the form knows about them, but its recordset
does not.

I think the best way to go about this is to base the form, not on
Table1, but on a query that joins Table1 and Table2 appropriately and
picks up the necessary fields from Table2. Then you can just use the
Sum() function to calculate the total for the text box in the form's
footer.

If you will please post the true names and field lists from the both
"Table1" and "Table2" -- and any other tables that are involved -- I may
be able to give more specific advice.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Thanks a lot, Dirk.

I did it by using the following

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Sum(([StaffDay]*
[ShiftDay]+[StaffAFT]*[ShiftAFT])*[Rate]) AS SumLabourDol"
_
& " FROM " & strCode & " INNER JOIN tblRate ON [" &
strCode & "].[Rate Code] = tblRate.[Rate Code]")

Me.txtTotalDol = rst![SumLabourDol]
-----Original Message-----
Thanks a lot again, Dirk.

It's easy to get the sum by using a query for these two
tables and get the form by using it. But, my concern is
that in this case I'll loose the opportunity to edit data
in Table1 directly from the form and I'll need to use
recordset with loop to assign edited data to the table. I
was trying to avoid it.

Please, advise if there is any other ways.

Table1 is actually a variable name (there are some tables
with product code' names). A user chooses this product
code from another form then the program assigns it to
strCode and Me.RecordSource = strCode
tblTable1 tblRate
Operation Rate Code
StaffDay Rate
ShiftDay
StaffAFT
ShiftAFT
RateCode


Alex
-----Original Message-----
Thanks a lot, Dirk.

As you asked for an explanation, I need to get a total for
txtDol to show it in the textbox1 on a formfoot of a
continious form.
This continious form is linked with Table1 to be able to
edit data there from this form.

txtDol on this form = [txtRate]*([txtShiftDay]*
[txtStaffDay]+[txtShiftAFT]*[txtStaffAFT]) where

txtRate = Control Source = [cboRateCode].Column(1)
cboRateCode has Control Source [Rate Code] from Table1 but
Row Source is Table2.

ii = [txtRate]*([txtShiftDay]*[txtStaffDay]+ [txtShiftAFT]*
[txtStaffAFT]) gives a mistake because it cannot recognize
txtRate. It says "Run-time error '2447'. There is an
invalid use of the .(dot) or ! operator or invalid
parentheses."

When I'm using the advised by you the same but with "!"
(!txtTextBox]) I'm getting "Error '3265'. Item not found
in this collection."
Click to expand...

That's because, as you now explain, these text boxes -- at least,
txtRate -- are not fields in the form's recordset; they are unbound
controls on the form. So the form knows about them, but its recordset
does not.

I think the best way to go about this is to base the form, not on
Table1, but on a query that joins Table1 and Table2 appropriately and
picks up the necessary fields from Table2. Then you can just use the
Sum() function to calculate the total for the text box
Click to expand...
in
the form's
footer.

If you will please post the true names and field lists from the both
"Table1" and "Table2" -- and any other tables that are involved -- I may
be able to give more specific advice.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
.
Click to expand...
 
Alex said:
Thanks a lot again, Dirk.

It's easy to get the sum by using a query for these two
tables and get the form by using it. But, my concern is
that in this case I'll loose the opportunity to edit data
in Table1 directly from the form and I'll need to use
recordset with loop to assign edited data to the table. I
was trying to avoid it.
Click to expand...

That would not normally be the case. Normally you can base a form on a
query that joins two tables, and still have the form be updatable.
There are errors in table and query design that can render such a query
nonupdatable, but it's not usually a problem.
Please, advise if there is any other ways.
Click to expand...

That would depend, as I said, on the details.
Table1 is actually a variable name (there are some tables
with product code' names).
Click to expand...

Yikes! Multiple tables containing the same sort of information, with a
crucial datum -- the product code -- hidden in the table name?! Why on
earth is that?
A user chooses this product
code from another form then the program assigns it to
strCode and Me.RecordSource = strCode
tblTable1 tblRate
Operation Rate Code
StaffDay Rate
ShiftDay
StaffAFT
ShiftAFT
RateCode
Click to expand...

Assuming RateCode is indexed in both tables and is the primary key in
tblRate, and the other table has a primary key, then a query along the
lines of

SELECT <Table1>.*, tblRate.Rate
FROM <Table1> INNER JOIN tblRate
ON <Table1>.RateCode = tblRate.RateCode;

ought to be updatable.
 
Alex said:
Thanks a lot, Dirk.

I did it by using the following

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Sum(([StaffDay]*
[ShiftDay]+[StaffAFT]*[ShiftAFT])*[Rate]) AS SumLabourDol"
_
& " FROM " & strCode & " INNER JOIN tblRate ON [" &
strCode & "].[Rate Code] = tblRate.[Rate Code]")

Me.txtTotalDol = rst![SumLabourDol]
Click to expand...

That's a more complicated way of going about it than I think you should
need, but if you're happy, I'm happy.
 
Sorry, Dirk. I missed your posting yesterday.
It looks very good. I really appreciate it. I'll check it.
My concern is whether this Table1 will be updated
automaticaly from the Form with this query or I should do
something in addition to have it updated.

For now, I have no idea how to make it updateable.
If you could, please respond.

Thanks,

Alex
 
Alex said:
Sorry, Dirk. I missed your posting yesterday.
It looks very good. I really appreciate it. I'll check it.
My concern is whether this Table1 will be updated
automaticaly from the Form with this query or I should do
something in addition to have it updated.
Click to expand...

A query such as I outlined should be updatable, and a form based on the
query should update the table automatically, in the normal way of bound
Access forms.
 
Back
Top