Selecting the previous record in VBA and performing a calculation based on it. | Newbie | | Join Date: Jul 2008
Posts: 17
| |
Hi,
I am a complete novice to Access VBA and looking for some help to select a record. I am looking to perform an operation on the previous record - i.e. adding a new blank field, and then calculating the value in the new field by increasing the value in the previous record by a percentage. However, I am unsure of how to select the previous record. I though that by having a query in the background which finds the maximum record ID would work, however, I can't work out how to translate this to a record selection.
My code is as follows: - Private Sub cmdUpdate_Click()
-
Dim rcdBudgetShareProj As DAO.Recordset
-
Dim rcdMaxID As DAO.Recordset
-
Dim Percent As Double
-
Dim MaxID As Integer
-
Dim I As Integer
-
-
Percent = 1 + Nz(Forms!frmTest![Percent], 0)
-
-
Set rcdBudgetShareProj = CurrentDb.OpenRecordset("tblTest")
-
Set rcdMaxID = CurrentDb.OpenRecordset("qryMaxID")
-
MaxID = (rcdMaxID![MaxID]) - 1
-
-
With rcdBudgetShareProj
-
For I = 1 To 3
-
Call .AddNew
-
![Year] = intYear + 1
-
![Outing] = ![Outing].MaxID * (Percent ^ I) ***************
-
![Increase] = Percent
-
![Time] = ![Time].MaxID * (Percent ^ I) ****************
-
Call .Update
-
Next I
-
End With
-
-
End Sub
The rows with ********* alongside are the ones with the syntax errors.
Any help greatly appreciated,
Kind regards,
Zoe
| | Member | | Join Date: Aug 2008
Posts: 77
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Why not perform the calculations on the last record first and then Add a new record with the required values?
| | Newbie | | Join Date: Jul 2008
Posts: 17
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
That would also work really well, maybe quicker too than putting all the values in the for loop.
My main issue however is the selecting of the "Maximum ID" record (for want of a better description. I just don't know the syntax for selecting a specific record.
Thanks,
Zoe
| | Member | | Join Date: Aug 2008
Posts: 77
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Why not sort the values in ascending order and then select the last record, do the calculations to get the desired new record and then add it?? Quote:
Originally Posted by zoeb That would also work really well, maybe quicker too than putting all the values in the for loop.
My main issue however is the selecting of the "Maximum ID" record (for want of a better description. I just don't know the syntax for selecting a specific record.
Thanks,
Zoe | | Newbie | | Join Date: Jul 2008
Posts: 17
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
That's a really good idea, made even simpler as the records would be added in chronological order so I wouldn't need to sort them.
But unfortunately I don't know how to select the last record - is there a quick way of doing it?
Sorry, complete novice here!
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,710
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Zoe,
There is no real concept in SQL of last and first records per se. For this reason what you ask will not be a simple thing to accomplish.
It is possible to to deal with a sorted set and thereby have a concept of first and last, but I have to say I feel that understanding what you are fundamentally after is more likely to yield an appropriate solution than simply answering your question directly.
Why would you want to have a process update the "previous" record?
| | Newbie | | Join Date: Jul 2008
Posts: 17
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Essentially I have a table called tblTest. Each record repsents annual budget data.
A form has been made on this data with a unbound text box at the top called "Percentage".
What I am essentially looking to do is create a 3 year budget projection based on the percentage entered in that box.
i.e. if 2.1% is entered in that box, a new record would be created, with one added to the year field to take it from 2004 to 2005 for example, and all records would be increased by 2.1%. Then this process would repeat, increasing 2005's year number 1 to make it 2006, then increasing all the calculated 2005 data by a further 2.1%.
This should be looped through 3 times, adding just 3 additional entries for a 3 year projection.
Does this make sense?
I don't think I can be too far away with my code, I should imagine referencing the previous record rather than maxID would be a good way of doing it. But is there a way of calling a record by indexing it so to speak as in MATLAB if you're familiar with that?
Sorry for the late reply, I finished work and have only just come back in.
Thank's for all your help on this it is a greatly appreciated. I only have 2 more weeks to finish the project!
| | Member | | Join Date: Aug 2008
Posts: 77
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Zoeb,
Let me assume you have an employee table with employee ids (which are random and need to be sorted), so you may use the following query which will give you a recordset in ascending order to work with (Remember, the records in actual will not be sorted, its only the recordset which will be sorted): -
Select * from employee order by emp_id asc
-
You may then use this recordset to reach the last record using the Movelast function of the recordset variable. Your code will then look something like this: -
Dim vSQL as String
-
Dim rstMain=ADODB.Recordset
-
Set rstMain=new ADODB.Recordset
-
vSQL="Select * from employee order by emp_id asc"
-
Set rstMain=conMain.Execute(vSQL) 'where conMain is your connection object
-
rstMain.MoveLast
-
'Perform your calculations and then add a new record later
-
Quote:
Originally Posted by zoeb That's a really good idea, made even simpler as the records would be added in chronological order so I wouldn't need to sort them.
But unfortunately I don't know how to select the last record - is there a quick way of doing it?
Sorry, complete novice here! | | Newbie | | Join Date: Jul 2008
Posts: 17
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Thanks for all that code, although I suspect I'm getting little out of my depth here as I don't know what a connection object is...
From the code shown in detail below I get the following error on this line:
Set rstMain = conMain.Execute(vSQL) 'where conMain is your connection object
"Operation is not allowed when the object is closed".
I have included the non-declaration code in the for loop, as I assumed it would not recalculate the value each time otherwise. My code is as follows:
So sorry for all the hassle, I am a complete novice and have ended up signing up to a project which is vastly out of my depth! -
-
Dim vSQL As String
-
Dim rstMain As ADODB.Recordset
-
Dim conMain As ADODB.Connection
-
Set rstMain = New ADODB.Recordset
-
Set conMain = New ADODB.Connection
-
-
-
For I = 1 To 3
-
-
vSQL = "Select * from tblTest order by ID asc"
-
Set rstMain = conMain.Execute(vSQL) 'where conMain is your connection object
-
rstMain.MoveLast
-
-
With rstMain
-
Year = ![Year] + 1
-
Outing = ![Outing] * Percent
-
Time = ![Time] * Percent
-
End With
-
-
With rcdBudgetShareProj
-
Call .AddNew
-
MaxID = (rcdMaxID![MaxID]) - 1
-
![Year] = Year
-
![Outing] = Outing
-
![Increase] = Percent
-
![Time] = Time
-
Call .Update
-
End With
-
-
Next I
-
| | Member | | Join Date: Aug 2008
Posts: 77
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Add the following code before the "Set rstMain = conMain.Execute(vSQL)" - MyDB = <Your DB Path>
-
Set conMain = New ADODB.Connection
-
StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDB & ";"
-
conMain.CursorLocation = adUseClient
-
conMain.Open StrCon, "", ""
-
Please make sure that when you navigate to Tools>References, you have the option "Microsoft ActiveX Data Objects 2.x Library" Selected.
2.x would be replaced with the version you have on your system. You may have multiple options with different version numbers. Please look at the location displayed against each one and select the one against "msado15.dll"
Thanks.
| | Member | | Join Date: Aug 2008
Posts: 77
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Please add the code before the For Loop and not within it...
| | Newbie | | Join Date: Jul 2008
Posts: 17
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Guys, you are absolutely fantastic. I've managed to suss it and I've learnt so much I can apply in other areas.
Thanks for all your help, and sorry it was such a long effort.
Kind regards,
Zoe
| | Member | | Join Date: Aug 2008
Posts: 77
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Not a problem at all Zoeb.. I'm glad I could be of some help :-)
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,710
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Quote:
Originally Posted by zoeb Essentially I have a table called tblTest. Each record repsents annual budget data.
...
Sorry for the late reply, I finished work and have only just come back in.
Thank's for all your help on this it is a greatly appreciated. I only have 2 more weeks to finish the project! That's very helpful Zoe.
Now I understand that when you refer to "previous record" you are talking about the record in the table whose year matches the current record with one subtracted. This makes perfect sense and can easily be handled in SQL.
This will take me a short while to get to grips with. Hopefully I can post something during my lunch break.
BTW not responding immediately is rarely a problem. We pick up on your posts and respond accordingly. We're very rarely short of questions to keep us nice and busy ;)
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,710
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Looks like I should have refreshed my browser before submitting the reply :D
| | Member | | Join Date: Aug 2008
Posts: 77
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
Lol... Not a problem NeoPa.. You may still proceed with your post.. May be we'd learn even more :-)
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,710
| | | re: Selecting the previous record in VBA and performing a calculation based on it.
I'll see if I can find some time Yaaara. As we have a happy OP (learning is even better than simply getting a solution) I have to treat this as lower priority than most of my other ongoing threads.
I have a mental note to see if I can fit something in though. We'll see how things pan out.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,353 network members.
|
