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
16 6505
Why not perform the calculations on the last record first and then Add a new record with the required values?
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
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
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 32,557
Recognized Expert Moderator MVP
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?
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!
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!
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
-
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.
Please add the code before the For Loop and not within 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
Not a problem at all Zoeb.. I'm glad I could be of some help :-)
NeoPa 32,557
Recognized Expert Moderator MVP
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 32,557
Recognized Expert Moderator MVP
Looks like I should have refreshed my browser before submitting the reply :D
Lol... Not a problem NeoPa.. You may still proceed with your post.. May be we'd learn even more :-)
NeoPa 32,557
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: special_agent69 |
last post by:
Not sure if this is a stoopid question or not. If it is, my apologies.
I'd like to have an ASP form performing a relatively simple calculation.
For example, if I wanted to create a simple...
|
by: Derek Cooper |
last post by:
I hope you can help me. I posted this in the microsoft sql server newsgroup
a few days ago and got no response so I thought I'd try here. If I can
provide any clarification I'll be glad to do so....
|
by: Doslil |
last post by:
I have a form which has a subform.In the main form I have only one
field (this is a drop down list and has a query attached to it) which
selects empno,Name from the EmployeeInformation table.
...
|
by: Wayne Aprato |
last post by:
I've read most, if not all, of the posts on moving average and still
can't find a simple solution to my problem (if a simple solution
exists!) I have a table with 2 fields: Hours and Injuries. I...
|
by: Sami |
last post by:
I hope someone will tell me how to do this without having to do any VB
as I know nothing in that area. I am a rank beginner in using Access.
I have created a database consisting of student...
| |
by: matthewemiclea |
last post by:
I have a subform, where in the "on current" event, I display code that
is supposed to recognize which record I am pointing to, and then
display more specific info in another subform based on that...
|
by: Patrick |
last post by:
I'm working on a contact management application, and need a hand with one
aspect...
Here's what I want to create:
------------------------------------
A form split into two parts. There is a...
|
by: David |
last post by:
What I envision is this.
The user gets a set of records in a data grid
Then the user gets to select one of the rows to view the detail information
about the record. Then instead of backing...
|
by: thread |
last post by:
Hi all i need to build progression calculator for a record and for
this i need to have the possiblity to get the information for the
previous record.
is it posible to do it or i will need to use...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |