I am trying to get the previous record from a table.
Scenario is:
I have a previous button on the form. User browse through the records one by one to go back/forward.
For first time to pull data from sql,i check that if form is empty,if it is then i pull the last record form the table based on Max(id) which is autonumber.
Now, because people would be deleting record as well,there is autonumber not in any consecutive order.
Once i have got the lasr record, and when user click on previous button again, how can i pull the second last record and so on.
ANy suggestions would be a great help!!
Thanks!
5  3947 
Once i have got the max(id),then to get the next last ID from the table, i need somethign like:
select * from table where id = (select max(id) - 'look for first mandatory filed not equal to zero' and get that id number)
how can i do that in sql?
ODBC call failed error '3146'...
How can i close this thread? i have opened another one for teh same problem in detail...
ck9663 2,878
Recognized Expert Specialist
There are a lot of option for you. For one, you don't have to actually delete a record. You just need a flag to say the record is active or not. It should keep your record history.
If that's not an option, try this:
1. Display the current ID on the application.
2. User clicks NEXT
3. select top 1* from yourtable where CurrentID > IDOnYourTable order by ID desc.
This would be slow though, specially if your table is big.
Good Luck!!!
~~ CK
Thank you very much for ur reply and suggestion . I did it by attaching flag to the deleted record.
Query sugesstion is very good coz i didnt know that it could be done in sql that way.
Thanks
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: george |
last post by:
Can anyone help?
I query a database and return a result on the column "reference".
There might be 7 listings. Each row is displayed in a table, with
links through to a detail page. I am working on having a "previous"
record and a "next" record link on the detail page.
This code below works, for the "next" record, by searching the values
in the array $myarray for the variable $ref. It then returns the key
value and the key value, as a...
|
by: eeh |
last post by:
Hi,
Suppose I have a table "product" with fields id,name,price. I want to
get a resultset of previous and current and next record when the
resultset is sorted by id and id=10. The table data is shown in the
following:
id Name Price
1 : :
5 : :<--------------previous record
|
by: Steve |
last post by:
Does any anyone have a procedure for a query where a calculated field returns
the previous record's value in another field. For example:
A
F A
Z F
M Z
The primary key is in random order in the query and the letters ar not sorted.
|
by: AAVF |
last post by:
I have a small table that need to keep a balance of work outstanding. The
fields are as follows
REC_COUNT (auto-index created record counter)
DATE (work date)
QTY_REQD (work added on that date)
BAL (balance of work outstanding at the end of that date)
The field REC_COUNT is used as a sort order field so that there is no
confusion about which record is next.
|
by: Karl Roes |
last post by:
Hi All,
I would like some advice on Next / Previous record buttons.
I have a main form for the client, and a continuous subform listing
client transactions. If I open one of these transactions ( dblclick )
in say 'frmtransaction', I would like to scroll forward and backward
through the transactions as they appear on the continuous subform
without actually having to close 'frmtransaction' and go back to the
subform.
| | |
by: devi |
last post by:
Hi,
In oracle I have a LAG function using which I could get the previous
value of a field.
Do we have anything similar to that in SQL Server or access?
Thanks
Devi
|
by: MLH |
last post by:
Fields in MyTable:
PostID
PostDate
RollQtyXfer
RollDenomination
RollCount37
RollCount23
|
by: Chris |
last post by:
Is it possiable to have a iif function in a query where it looks at
the next record. for example if I run the query which lists years and
if the results in record 2 equals the result in record 1 then it
displays an X.
|
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 recordset for this?
|
by: zoeb |
last post by:
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
