By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Selecting the previous record in VBA and performing a calculation based on it.

P: 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdate_Click()
  2.   Dim rcdBudgetShareProj As DAO.Recordset
  3.   Dim rcdMaxID As DAO.Recordset
  4.   Dim Percent As Double
  5.   Dim MaxID As Integer
  6.   Dim I As Integer
  7.  
  8.   Percent = 1 + Nz(Forms!frmTest![Percent], 0)
  9.  
  10.   Set rcdBudgetShareProj = CurrentDb.OpenRecordset("tblTest")
  11.   Set rcdMaxID = CurrentDb.OpenRecordset("qryMaxID")
  12.     MaxID = (rcdMaxID![MaxID]) - 1
  13.  
  14.   With rcdBudgetShareProj
  15.     For I = 1 To 3
  16.       Call .AddNew
  17.       ![Year] = intYear + 1
  18.       ![Outing] = ![Outing].MaxID * (Percent ^ I) ***************
  19.       ![Increase] = Percent
  20.       ![Time] = ![Time].MaxID * (Percent ^ I) ****************
  21.       Call .Update
  22.     Next I
  23.   End With
  24.  
  25. End Sub
The rows with ********* alongside are the ones with the syntax errors.
Any help greatly appreciated,

Kind regards,

Zoe
Sep 1 '08 #1
Share this Question
Share on Google+
16 Replies


P: 77
Why not perform the calculations on the last record first and then Add a new record with the required values?
Sep 1 '08 #2

P: 17
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
Sep 1 '08 #3

P: 77
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??

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
Sep 1 '08 #4

P: 17
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!
Sep 1 '08 #5

NeoPa
Expert Mod 15k+
P: 31,344
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?
Sep 1 '08 #6

P: 17
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!
Sep 2 '08 #7

P: 77
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):

Expand|Select|Wrap|Line Numbers
  1. Select * from employee order by emp_id asc
  2.  
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:

Expand|Select|Wrap|Line Numbers
  1. Dim vSQL as String
  2. Dim rstMain=ADODB.Recordset
  3. Set rstMain=new ADODB.Recordset
  4. vSQL="Select * from employee order by emp_id asc"
  5. Set rstMain=conMain.Execute(vSQL) 'where conMain is your connection object
  6. rstMain.MoveLast
  7. 'Perform your calculations and then add a new record later
  8.  

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!
Sep 2 '08 #8

P: 17
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!

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim vSQL As String
  3. Dim rstMain As ADODB.Recordset
  4. Dim conMain As ADODB.Connection
  5. Set rstMain = New ADODB.Recordset
  6. Set conMain = New ADODB.Connection
  7.  
  8.  
  9.     For I = 1 To 3
  10.  
  11.     vSQL = "Select * from tblTest order by ID asc"
  12. Set rstMain = conMain.Execute(vSQL) 'where conMain is your connection object
  13. rstMain.MoveLast
  14.  
  15. With rstMain
  16. Year = ![Year] + 1
  17. Outing = ![Outing] * Percent
  18. Time = ![Time] * Percent
  19. End With
  20.  
  21.     With rcdBudgetShareProj
  22.       Call .AddNew
  23.       MaxID = (rcdMaxID![MaxID]) - 1
  24.       ![Year] = Year
  25.       ![Outing] = Outing
  26.       ![Increase] = Percent
  27.       ![Time] = Time
  28.       Call .Update
  29.        End With
  30.  
  31.     Next I
  32.  
Sep 2 '08 #9

P: 77
Add the following code before the "Set rstMain = conMain.Execute(vSQL)"
Expand|Select|Wrap|Line Numbers
  1.     MyDB = <Your DB Path>
  2.     Set conMain = New ADODB.Connection
  3.     StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDB & ";"
  4.     conMain.CursorLocation = adUseClient
  5.     conMain.Open StrCon, "", ""
  6.  
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.
Sep 2 '08 #10

P: 77
Please add the code before the For Loop and not within it...
Sep 2 '08 #11

P: 17
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
Sep 2 '08 #12

P: 77
Not a problem at all Zoeb.. I'm glad I could be of some help :-)
Sep 2 '08 #13

NeoPa
Expert Mod 15k+
P: 31,344
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 ;)
Sep 2 '08 #14

NeoPa
Expert Mod 15k+
P: 31,344
Looks like I should have refreshed my browser before submitting the reply :D
Sep 2 '08 #15

P: 77
Lol... Not a problem NeoPa.. You may still proceed with your post.. May be we'd learn even more :-)
Sep 2 '08 #16

NeoPa
Expert Mod 15k+
P: 31,344
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.
Sep 2 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.