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

[zoeb]

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

[yaaara]

Why not perform the calculations on the last record first and then Add a new record with the required values?

[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

[yaaara]

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

[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!

[NeoPa]

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?

[zoeb]

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!

[yaaara]

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

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!

[zoeb]

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

[yaaara]

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>Reference s, 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.dl l"

Thanks.

[yaaara]

Please add the code before the For Loop and not within it...

[zoeb]

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

[yaaara]

Not a problem at all Zoeb.. I'm glad I could be of some help :-)

[NeoPa]

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 ;)

[NeoPa]

Looks like I should have refreshed my browser before submitting the reply :D