ACCESS : Import last saved record of particular Name from access database to excel userform

[manissinha]

HI

I want to Import last saved record of particular Name from access database to excel userform.

It give me the first record saved for particular name. (FIFO)
I want to import using LIFO method/ code

please help

Regards
Manish

 

[manissinha]

my code is : searchstring = "SELECT * FROM Table1 WHERE [Name] = '" & TextBox1.Value & "'"

Access database tabel1 :

1	James	2017	abc
2	Mike	2016	123
3	Mike	2017	456
4	James	2018	789
5	James	2019	000

<tbody>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] Name [/TH]
[TH="bgcolor: #c0c0c0"] Year [/TH]
[TH="bgcolor: #c0c0c0"] Address [/TH]

</tbody>

my code give me first saved "Mike" data year "2016" and address "123"
however i wand last saved "Mike" data , Year "2017" and address "456"

my excel uerform contains Name as Textbox1
Year as Textbox2
Address as Textbox3
search record using Textbox1 by entering name manually

 

[welshgasman]

Use a query to sort the data in the order you require and select from that.

 

[manissinha]

How?

 

[welshgasman]

What do you mean how.?
You just create a query, use the GUI if you are not familiar with sql. The you use *that* as your source. So your select would be

searchstring = "SELECT * FROM MySortedQuery WHERE [Name] = '" & TextBox1.Value & "'"

 

[manissinha]

Tried with below code but not working

searchstring = "SELECT * FROM Table1 ORDER BY NAME DESC WHERE [Name] = '" & TextBox1.Value & "'"

[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]ID[/TH]
[TH="align: center"]Name[/TH]
[TH="align: center"]Year[/TH]
[TH="align: center"]Address[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]James[/TD]
[TD]2017[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mike[/TD]
[TD]2016[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD] 3[/TD]
[TD]Mike[/TD]
[TD]2017[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]James[/TD]
[TD]2018[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]James[/TD]
[TD]2019[/TD]
[TD]000[/TD]
[/TR]
</tbody>[/TABLE]


for Mike require below output [TABLE="class: cms_table"]
<tbody>[TR]
[TD]3[/TD]
[TD]Mike[/TD]
[TD]2017[/TD]
[TD]456[/TD]
[/TR]
</tbody>[/TABLE]


for james require below output in userform text boxes

5 James 2019 000

 

[welshgasman]

Syntax is SELECT, then WHERE *then* ORDER

I would have thought you would need something along the lines of

"SELECT * FROM Table1  WHERE [Name] = '" & TextBox1.Value & "'"  & " ORDER BY YEAR DESC

to get all the records. If you are just interested in the one record, then perhaps

"SELECT TOP 1 Table1.* FROM Table1  WHERE [Name] = '" & TextBox1.Value & "'"  & " ORDER BY YEAR DESC

Tested via QBE

SELECT TOP 1 Transactions.*, Transactions.Description, Transactions.Date
FROM Transactions
WHERE (((Transactions.Description) Like "*208271"))
ORDER BY Transactions.Date DESC;

HTH