ms access get next row (1 Viewer)

[gpoogles]

Hi All,

I'm new to this forum, i request your help to get a code for getting next row data from ms access database 2003 using excel vba coding,

To explain clearly--
I have userform created in excel 2003 which contains a two 2 command buttons named "next record" and "previous record" and several text boxes to display a data from access database table, if i pressed those buttons i need the text boxes should display a data from every row accordingly,

I'm searching for this code for last 2weeks, finally i came here to full fill my requirement, it would be grateful if someone help me to resolve my query, thanks in advance

 

[plog]

A table isn't a spreadsheet, even though when you open one it takes that form. You should think of a table as a big bucket of computer memory that records get thrown into. Records just get thrown in that bucket in no particular order when they are created. Tables have no order--there is no first, no last, no next, no 23rd record in a table.

Order exists in a database only when you give it order, mostly through queries that explicitly use the ORDER BY clause. So what you want to achieve isn't possible with the information you have provided us.

To achieve what you want, you need to tell us how order is to be applied on your table. Is there a date field? An autonumber primary key? How do you logically define 'next'?

 

[gpoogles]

Hey plog,
Thanks for your quick response.
My project:
I have access database table named PCRAWDATA which contains many fields like
CaseID - auto number field
Emp ID - text field
Emp-name - text field
Supervisor name - text field


Excel vba form contains

Emp ID - text box
Emp-name - text box
Supervisor name - text box
Previous record (row) - command button
Next record (row) - command button

Database has many records, I want to pull the record one by one respectively when I click the "next button" or "previous button" and it should be populate in the respective text box is userform, order by date and another criteria

 

[plog]

order by date and another criteria

Other than that, you provided no information that makes it seem like you read my prior post. Further, the fields you listed do not have a Date among them. Further yet, I asked for specific ordering instructions and you gave 'and another criteria'.

See my initial post--no 'Next' nor 'Previous' exists unless you explicitly tell the system what those records should be. What is your criteria for doing so?

 

[gpoogles]

My work is to allocate the cases to my colleagues, I receive work from the workflow tool, I pull the necessary data from that tool and put it in common network location ( MS access database table) file, the access table looks like
Case ID - auto number field
Received date - date time field or text field
Case ref - text field
Case status - text field
Allocated to - text field

Example record in access table

Case ID - 1
Received date - 12/07/2015
Case ref - CS354677
Case status - pending or completed or cancelled
Allocated to - "employee names"

Same for excel userform which has same fields to display the data from access database
Received date - combo box ( all dates for one year)
Case ref - text box
Case status - combo box ( pending, completed, cancelled)
Allocated to - text box
Next case - command button
Previous case - command button

If I select the particular "DATE","STATUS" from the respective combo box and pressed the next button I want the "CASE REF", "ALLOCATED TO" text box should display the matching record one by one while pressing the command button order by date and status

Pls let me know if u require any more information

 

[plog]

Can someone move this to the proper forum and help him?

I saw this under the Table's forum and the question referred to 'Next' and 'Prior' so I had to get on my high-horse and explain how order doesn't exist in tables, but I'm no good with Excel Forms and VBA.

Speaking of which, gpoogles, why aren't you using an Access Form if your data is in Access? I could help you there.

 

[gpoogles]

Ms access is not available in all machines, so I'm using excel as front end and access as backend

 

[gpoogles]

Can u help in this? how to pull the record using auto number field, when I tried its showing invalid or incorrect criteria found

 

[JLCantara]

'Access' doesn't have to be installed on all machine to run Access app. Download Access runtime (proper version) , it's free!
Note that quick access toolbar is minimal so you have to create a custom ribbon...

 

[arnelgp]

Access' doesn't have to be installed on all machine to run Access app.

Correct me if I'm wrong but I dont think so.

 

[AlexN]

Hi All,

I'm new to this forum, i request your help to get a code for getting next row data from ms access database 2003 using excel vba coding,

To explain clearly--
I have userform created in excel 2003 which contains a two 2 command buttons named "next record" and "previous record" and several text boxes to display a data from access database table, if i pressed those buttons i need the text boxes should display a data from every row accordingly,

I'm searching for this code for last 2weeks, finally i came here to full fill my requirement, it would be grateful if someone help me to resolve my query, thanks in advance

Excel by default has a number column in the leftmost side of the spreadsheet in order to uniquely identify each row.
When data is transferred to access and rows become records there’s no such number to uniquely identify them. You’ll have to create your own identifier, in order to determine which record comes first, second, third and so on, which is current, previous or next. Should it be a date field (not recommended), should it be an autonumber ID field, that’s for you to decide.

 

[arnelgp]

can you upload you excel file.

 

[JLCantara]

@Arnelgp post #10:

The Microsoft Access 2013 Runtime enables you to distribute Access 2013 applications to users who do not have the full version of Access 2013 installed on their computers.

from MS download site
http://www.microsoft.com/en-ca/download/details.aspx?id=39358

 

[MOHAMEDHAMADA]

Excel by default has a number column in the leftmost side of the spreadsheet in order to uniquely identify each row.
When data is transferred to access and rows become records there’s no such number to uniquely identify them. You’ll have to create your own identifier, in order to determine which record comes first, second, third and so on, which is current, previous or next. Should it be a date field (not recommended), should it be an autonumber ID field, that’s for you to decide.

Can anyone help for this please

 

[MOHAMEDHAMADA]

can you upload you excel file.

Can you send code about it please

 

[The_Doc_Man]

Can anyone help for this please

First, note that the thread was opened in 2015 and the last post before yours was from that year. You have therefore "awakened" a 7-year-old thread. You might have gotten better results by opening a new thread and describing your problem in simple language.

Second, the point being made in the earlier part of the thread is that when your data resides in an Access table, that table is an unordered set of records - unordered in the sense that SQL queries, which are based on Set Theory, have no inherent order. In theory, any SQL action is performed in such a way as to APPEAR as though it was simultaneous even though we don't think it actually happens that way.

A QUERY can include an ORDER BY clause if you have a field in that table that could be used to establish any kind of sorting order. If you have a table with no such ordering field, there IS no valid meaning to FIRST, LAST, NEXT, or PREVIOUS other than "pot luck." That is, without a sort key, the table is stored and viewed more or less based on order of record updates. In such a case, the order of record appearance can change from day to day and will almost appear to be random (though it isn't, really.)

Therefore, and Third: We cannot show you code related to this situation because we know NOTHING AT ALL about your data set. We cannot offer suggestions where we have no hints about what you are doing. Please don't think that I am trying to dismiss you or your problem - but without some information to help us put your situation into perspective, we are kind of useless at the moment.

Think of it this way. If you got to a doctor and tell him, "I'm in pain" - what do you think the doctor would ask? He would say, "Can you tell me what part of you hurts?" And THEN he could treat you himself or refer you to a podiatrist, an orthopedist, a cardiologist, a proctologist, etc.

So... help us to help you.

 

[MOHAMEDHAMADA]

Thank for your comment but my problem is exactly what this thread meant so can any help for this question

 

[CJ_London]

You have a number of answers- what don’t you understand?

 

[MOHAMEDHAMADA]

Thank for your comment but my problem is exactly what this thread meant so can any help for this question

 

[CJ_London]

I see you have started another thread which is the right thing to do - so I would concentrate on that thread since this one is 7 years old