Please help, need VBA to get information from access table...

[pedie]

Hi, please help me with VBA to retrive information meeting critiria from access table. I want to get data in to sheet1, A1;
Critiria is if Manager is = Linda and Location is = NY
my table is as shown below;

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=385><COLGROUP><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 658" width=18><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5be97; WIDTH: 14pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 width=18>

ID​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=111>

EMPLOYEE_NAMES​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=50>

Manager​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 60pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=80>

Dept_Name​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>

LOCATION​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=62>

COUNTRY​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5be97; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

Pedie​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

Linda​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

ATT​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

NY​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

US​

</TD></TR></TBODY></TABLE>


I have done a lot of search and tried out alot of them but is not that easy to arrange them together to work...


Thanks in advance for helping.

 

[pedie]

Ruddles, thanks! you rock!

Works perrrrfect....!

 

[ckepley]

Access can get pretty complex. I like to get a couple good reference points or web sites, books ect and just read up and do research on problems I may be having. Check out a website I found www.vbaautofilter.com and go to ms access database there are a couple good links on that page that may help out as well.

 

[pedie]

Ckepley, thanks for the link...I really do need one good link and more practice....
Thanks again

 

[Ruddles]

Whenever I've written a piece of code using new techniques or ideas - or adapted something that someone else has written - apart from keeping a copy of it, I make a stripped-down version containing just the essentials and store that separately with a file name which describes what it is or what it does.

That way, if I ever need it again in the future or someone else needs something like it, I have something ready.

(In fact I export all the code modules as separate BAS files so that I can search them by name or by content: if all I can remember is that I'd previously written a piece of code to update the Windows Registry, I just do a search for the word "registry".)

 

[pedie]

Whenever I've written a piece of code using new techniques or ideas - or adapted something that someone else has written - apart from keeping a copy of it, I make a stripped-down version containing just the essentials and store that separately with a file name which describes what it is or what it does.

That way, if I ever need it again in the future or someone else needs something like it, I have something ready.

(In fact I export all the code modules as separate BAS files so that I can search them by name or by content: if all I can remember is that I'd previously written a piece of code to update the Windows Registry, I just do a search for the word "registry".)

Ruddles, brilliant idea... I will do the same thing...[Currently I use to keep all module in worksheets and then do a word search whenever required... sometime the module name is just 'sub try' and then i just dont know where the code went...]


Thanks again for sharing your thoughts!