Updating a table from a CmdButton on a Form using Code

G

Guest

I am trying to update a table from a subform using a cmd button. This is what
I have so far. The purpose of this is to keep track of the Remaining shares
that are put in manually and I need to add this Shares to a History Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
D

David Lloyd

Generally this error is associated with an incorrect (misspelled) reference
to an item in a collection. For example:

..Fields("DateApporved") = rstForm![DateApporved]

has the word "approved" misspelled. You should check all of the field names
used and make sure that they are consistent with the data source. If that
does not solve the issue, you may want to specify which line of code is
actually generating the error.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am trying to update a table from a subform using a cmd button. This is
what
I have so far. The purpose of this is to keep track of the Remaining shares
that are put in manually and I need to add this Shares to a History Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
B

Brendan Reynolds

Do you really have a field and a form control named "DateApporved" or should
that perhaps be "DateApproved"?
 
G

Guest

Thanks for catching the misspelled word but that is not the issue.

It pukes on this line
If rstForm![TradeAmount] <> 0 Then

could it be that my qry is a datasheet style and the code that I have the
button needs to be put on the qry.

I have a form with two subform qrys on it. One subfrorm qry is on top. The
other subform qry is on the bottom of the main form. Is there code that I
maybe missing to refernece the Top Subform qry.



Brendan Reynolds said:
Do you really have a field and a form control named "DateApporved" or should
that perhaps be "DateApproved"?

--
Brendan Reynolds

Crazyhorse said:
I am trying to update a table from a subform using a cmd button. This is
what
I have so far. The purpose of this is to keep track of the Remaining
shares
that are put in manually and I need to add this Shares to a History Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
D

Douglas J Steele

Might TradeAmount be null when it's failing?

Try

If Nz(rstForm![TradeAmount],0) <> 0 Then

assuming you don't want to add the new record if TradeAmount is Null, or

If Nz(rstForm![TradeAmount],1) <> 0 Then

if you do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Thanks for catching the misspelled word but that is not the issue.

It pukes on this line
If rstForm![TradeAmount] <> 0 Then

could it be that my qry is a datasheet style and the code that I have the
button needs to be put on the qry.

I have a form with two subform qrys on it. One subfrorm qry is on top. The
other subform qry is on the bottom of the main form. Is there code that I
maybe missing to refernece the Top Subform qry.



Brendan Reynolds said:
Do you really have a field and a form control named "DateApporved" or should
that perhaps be "DateApproved"?

--
Brendan Reynolds

Crazyhorse said:
I am trying to update a table from a subform using a cmd button. This is
what
I have so far. The purpose of this is to keep track of the Remaining
shares
that are put in manually and I need to add this Shares to a History Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
G

Guest

Thank you but that did not work.

I am still getting the Run-Time error'3265'
Item not Found in this Collection.

It is still puking at the same spot
If Nz(rstForm![TradeAmount], 0) <> 0 Then

This is killing me
I know it is something dumb.

Thanks

Douglas J Steele said:
Might TradeAmount be null when it's failing?

Try

If Nz(rstForm![TradeAmount],0) <> 0 Then

assuming you don't want to add the new record if TradeAmount is Null, or

If Nz(rstForm![TradeAmount],1) <> 0 Then

if you do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Thanks for catching the misspelled word but that is not the issue.

It pukes on this line
If rstForm![TradeAmount] <> 0 Then

could it be that my qry is a datasheet style and the code that I have the
button needs to be put on the qry.

I have a form with two subform qrys on it. One subfrorm qry is on top. The
other subform qry is on the bottom of the main form. Is there code that I
maybe missing to refernece the Top Subform qry.



Brendan Reynolds said:
Do you really have a field and a form control named "DateApporved" or should
that perhaps be "DateApproved"?

--
Brendan Reynolds

I am trying to update a table from a subform using a cmd button. This is
what
I have so far. The purpose of this is to keep track of the Remaining
shares
that are put in manually and I need to add this Shares to a History Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
D

Douglas J Steele

The implication, then, is that TradeAmount doesn't exist in rstForm. Are you
certain about the spelling?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Thank you but that did not work.

I am still getting the Run-Time error'3265'
Item not Found in this Collection.

It is still puking at the same spot
If Nz(rstForm![TradeAmount], 0) <> 0 Then

This is killing me
I know it is something dumb.

Thanks

Douglas J Steele said:
Might TradeAmount be null when it's failing?

Try

If Nz(rstForm![TradeAmount],0) <> 0 Then

assuming you don't want to add the new record if TradeAmount is Null, or

If Nz(rstForm![TradeAmount],1) <> 0 Then

if you do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Thanks for catching the misspelled word but that is not the issue.

It pukes on this line
If rstForm![TradeAmount] <> 0 Then

could it be that my qry is a datasheet style and the code that I have the
button needs to be put on the qry.

I have a form with two subform qrys on it. One subfrorm qry is on top. The
other subform qry is on the bottom of the main form. Is there code that I
maybe missing to refernece the Top Subform qry.



:


Do you really have a field and a form control named "DateApporved"
or
should
that perhaps be "DateApproved"?
This
is
what
I have so far. The purpose of this is to keep track of the Remaining
shares
that are put in manually and I need to add this Shares to a
History
Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
G

Guest

Yes you are right it does not exist in the rstForm due to that is the Main
Form. TradeAmount exists in the SubForm of the Main form and the Cmd Button
is on the Main Form. That is because the SubForm is a Datasheet Style. I can
not put the cmdButton on a Datasheet as you know that. Now how would I
reference the cmdButton to the Subform instead of rstForm which is the Main
Form. I need to tell the button not to look on the main form but in the
subform.

Thank you

Douglas J Steele said:
The implication, then, is that TradeAmount doesn't exist in rstForm. Are you
certain about the spelling?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Thank you but that did not work.

I am still getting the Run-Time error'3265'
Item not Found in this Collection.

It is still puking at the same spot
If Nz(rstForm![TradeAmount], 0) <> 0 Then

This is killing me
I know it is something dumb.

Thanks

Douglas J Steele said:
Might TradeAmount be null when it's failing?

Try

If Nz(rstForm![TradeAmount],0) <> 0 Then

assuming you don't want to add the new record if TradeAmount is Null, or

If Nz(rstForm![TradeAmount],1) <> 0 Then

if you do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for catching the misspelled word but that is not the issue.

It pukes on this line
If rstForm![TradeAmount] <> 0 Then

could it be that my qry is a datasheet style and the code that I have the
button needs to be put on the qry.

I have a form with two subform qrys on it. One subfrorm qry is on top. The
other subform qry is on the bottom of the main form. Is there code that I
maybe missing to refernece the Top Subform qry.



:


Do you really have a field and a form control named "DateApporved" or
should
that perhaps be "DateApproved"?

--
Brendan Reynolds

I am trying to update a table from a subform using a cmd button. This
is
what
I have so far. The purpose of this is to keep track of the Remaining
shares
that are put in manually and I need to add this Shares to a History
Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
D

Douglas J Steele

See http://www.mvps.org/access/forms/frm0031.htm at "The Access Web"

Set rstForm = Me!Subform.Form.RecordsetClone

Note that "Subform" above refers to the name of the control on the main
form, which may or may not be the same as the name of the form that's being
used as the subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Yes you are right it does not exist in the rstForm due to that is the Main
Form. TradeAmount exists in the SubForm of the Main form and the Cmd Button
is on the Main Form. That is because the SubForm is a Datasheet Style. I can
not put the cmdButton on a Datasheet as you know that. Now how would I
reference the cmdButton to the Subform instead of rstForm which is the Main
Form. I need to tell the button not to look on the main form but in the
subform.

Thank you

Douglas J Steele said:
The implication, then, is that TradeAmount doesn't exist in rstForm. Are you
certain about the spelling?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Thank you but that did not work.

I am still getting the Run-Time error'3265'
Item not Found in this Collection.

It is still puking at the same spot
If Nz(rstForm![TradeAmount], 0) <> 0 Then

This is killing me
I know it is something dumb.

Thanks

:

Might TradeAmount be null when it's failing?

Try

If Nz(rstForm![TradeAmount],0) <> 0 Then

assuming you don't want to add the new record if TradeAmount is Null, or

If Nz(rstForm![TradeAmount],1) <> 0 Then

if you do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for catching the misspelled word but that is not the issue.

It pukes on this line
If rstForm![TradeAmount] <> 0 Then

could it be that my qry is a datasheet style and the code that I
have
the
button needs to be put on the qry.

I have a form with two subform qrys on it. One subfrorm qry is on
top.
The
other subform qry is on the bottom of the main form. Is there code that I
maybe missing to refernece the Top Subform qry.



:


Do you really have a field and a form control named
"DateApporved"
or
should
that perhaps be "DateApproved"?
button.
This
is
what
I have so far. The purpose of this is to keep track of the Remaining
shares
that are put in manually and I need to add this Shares to a History
Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History", DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") = rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 
D

David Lloyd

The following KB article may be a good reference.

http://support.microsoft.com/default.aspx?scid=kb;en-us;209099

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Yes you are right it does not exist in the rstForm due to that is the Main
Form. TradeAmount exists in the SubForm of the Main form and the Cmd Button
is on the Main Form. That is because the SubForm is a Datasheet Style. I can
not put the cmdButton on a Datasheet as you know that. Now how would I
reference the cmdButton to the Subform instead of rstForm which is the Main
Form. I need to tell the button not to look on the main form but in the
subform.

Thank you

Douglas J Steele said:
The implication, then, is that TradeAmount doesn't exist in rstForm. Are
you
certain about the spelling?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Crazyhorse said:
Thank you but that did not work.

I am still getting the Run-Time error'3265'
Item not Found in this Collection.

It is still puking at the same spot
If Nz(rstForm![TradeAmount], 0) <> 0 Then

This is killing me
I know it is something dumb.

Thanks

Douglas J Steele said:
Might TradeAmount be null when it's failing?

Try

If Nz(rstForm![TradeAmount],0) <> 0 Then

assuming you don't want to add the new record if TradeAmount is Null,
or

If Nz(rstForm![TradeAmount],1) <> 0 Then

if you do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for catching the misspelled word but that is not the issue.

It pukes on this line
If rstForm![TradeAmount] <> 0 Then

could it be that my qry is a datasheet style and the code that I
have the
button needs to be put on the qry.

I have a form with two subform qrys on it. One subfrorm qry is on
top. The
other subform qry is on the bottom of the main form. Is there code that I
maybe missing to refernece the Top Subform qry.



:


Do you really have a field and a form control named "DateApporved" or
should
that perhaps be "DateApproved"?

--
Brendan Reynolds

message
I am trying to update a table from a subform using a cmd button. This
is
what
I have so far. The purpose of this is to keep track of the Remaining
shares
that are put in manually and I need to add this Shares to a History
Table.
When I run this I get a error:
Run-Time '3625'
Item not found in this collection

Anything would be great because I hit a brick wall.

Private Sub cmdHistory_Click()
Dim dbsLocal As DAO.Database
Dim rstTable As DAO.Recordset
Dim rstForm As DAO.Recordset

Set rstForm = Me.RecordsetClone
Set dbsLocal = CurrentDb()
Set rstTable = dbsLocal.OpenRecordset("History",
DB_OPEN_DYNASET)

rstForm.MoveFirst

Do Until rstForm.EOF
With rstTable
If rstForm![TradeAmount] <> 0 Then
.AddNew
.Fields("FundID") = rstForm![FundID]
.Fields("AssetDescription") = rstForm![AssetDescription]
.Fields("Ticker") = rstForm![Ticker]
.Fields("NavChkDate") = rstForm![NavChkDate]
.Fields("DateApporved") = rstForm![DateApporved]
.Fields("Shares") = rstForm![Shares]
.Fields("RemainingShares") =
rstForm![RemainingShares]
.Fields("TradeAmount") = rstForm![TradeAmount]
.Update
End If
End With
rstForm.MoveNext
Loop

rstTable.Close
rstForm.Close

MsgBox " Update Complete"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top