Using VBA to update a table (field)

[mobius1983]

Hi everyone,

Hope someone can help. I am writing a module, it takes the data from a table, performs a calculation and saves the result in a variable (that works fine!). Now i need to place that variable back into a table (thats the bit im stuck with!).

Any help/ideas would be greatly appriciated.

Nick

 

[scriverb]

I need a little more infomation. Are you in a form with a record bound to the form? Where are you getting the data from the table? What is the identifier to link back and find the correct record to update?

If you are in a form with the current record being the one that you are using the data from it is real easy.

Just give us a little more information.

Bob scriver

 

[mobius1983]

Hi Bob

Thanks for your quick reply. I appologise for the lack of info, im new to this and wasnt sure what to write.

I am making an external Module with the main aim to update a table, not connected to a form.I have accessed the table using:

Dim rscnt As ADODB.Recordset
Dim sqlcount As String
Set rscnt = New ADODB.Recordset

sqlcount = "SELECT COUNT(number) As cnt FROM n1"
rscnt.Open sqlcount, CurrentProject.Connection
rscnt.MoveFirst

This then performs a calculation, and places the result in a variable. I then need to update the field in the table with the calculated figure.

Hope this has made it clearer and once again thank you.

Nick

 

[scriverb]

Because I am still using DAO exclusively I will take a stab at this and see what happens:

Dim rscnt As ADODB.Recordset
Dim sqlcount As String
Set rscnt = New ADODB.Recordset

sqlcount = "SELECT COUNT(number) As cnt FROM n1"
rscnt.Open sqlcount, CurrentProject.Connection
rscnt.MoveFirst

vYourVariable = Cnt * 100 'Example expression

It is here that you want to update a record in your table. But, which record? Is there an identifier that you would have that would match a field in the table n1. Let's say that it is a variable called vCustID which is Numeric.

rscnt.FindFirst "[CustID] = " & vCustID
If NOT rscnt.NoMatch then
rscnt.edit
rscnt("FieldNameToUpdate&quot = VYourVariable
rscnt.update
else
MsgBox "No record found to update"
end if
rscnt.close

No I have made a few assumptions here. That the record to be updated is in fact in the table n1. That you have a variable with a value to search through the table to find a record. If your field to search for is alphanumeric than a little different syntax is necessary to perform the FindFirst action. Just provide a little more information and we can get you going on this.

Bob Scriver

 

[mobius1983]

Bob

I have tried to use the above code. Unfortunatly it wont let me use .findfirst .edit or .update

I have used rscnt.seek to replace the rscnt.findfirst but am still having trouble getting the coading to write the data back into the table.

Once again thankyou for your time. If the full coading to my project would help I can email it to you , it is a bit to long to place on this message board.

Nick

 

[mobius1983]

Bob

I have found the problem. It was in the opening of the recordset.

It now reads:

Rst.Open sqlid, CurrentProject.Connection, adOpenDynamic, adLockPessimistic

Thankyou very much for your help, it is most appriciated

Nick

 

[scriverb]

Great!! Glad to be of help. I wasn't sure if the code I provided would work with the ADO type code. I am still stuck in ACCESS 97 DAO as that is what my office is using. I really don't mean stuck but just not able to actively grow with the newer technology until someone else here decides to upgrade.

I haven't yet found anything that I can't accomoplish with DAO but ADO looks exciting and powerful.

Bob Scriver