Filling the empty rows on the basis of diffrent Column

[suji]

Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am so thankful to the people who are supporting us in solving our issues...<o></o>
I am stuck with the below tool...please support me in making it work...<o></o>
<o></o>
I am creating a excel tool which is linked with access database. <o></o>
in "Col B" or "Col C" sales team will enter their product IDs and then they will click the button "Get Details", Now on the basis of the either one column the excel ie B or C VBA code should get the details from Access Database like its discount ok,no discout,see massage etc in "Col E".<o></o>
There will be some Product ID not in Database in that case the Col E pertaining to the Product to show "NOT Found" and additional message should show in "COl F" as "Contact Mgr".<o></o>
<o></o>
I can only use DAO code to integrate Excel and Access...<o></o>
<o></o>
Please help me with the sample code so that I can fix it...<o></o>
<o></o>
Thanks a ton in Advance<o></o>

 

[SydneyGeek]

See if this article helps.

Denis

 

[suji]

Hi Denis...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks a lot for the response....<o></o>
Yes - the link is really helpful...<o></o>
Now I am stuck at 2 places...Please guide me here as well...<o></o>
<o></o>
1. I successfully wrote the code to get the data from access...but there are some product ids that do not have details in access database for that the corresponding columns should throw the result as "No Information" in next row " contact manager".<o></o>
Is there any way we can do that.<o></o>
<o></o>
2. We have some Product IDs with * (wild card) such as "ACC-DEC-*" , "BOW-COM-*","EWSA*" etc.....(this Ids are in Access Database).....Is there are way to write DAO code to identify the product ids in excel such as "ACC-DEC-1234" and match it with the database and get the data pertaining to in the excel....<o></o>
<o></o>
<o></o>
<o></o>
These are very crucial issues i am trying to solve desperately from couple<o></o>
of days buy not getting success.....Please guide me soon.....<o></o>
<o></o>
<o></o>
Thanks a lot in advance.....<o></o>
Suji

 

[SydneyGeek]

For the wild cards, the criteria need to change slightly.
For example, an exact match would be something like WHERE Somefield = 'ABC'
A wildcard match would be something like WHERE Somefield LIKE 'ABC%'

(In ADO you use % instead of * for wildcards).

Denis

 

[suji]

HI Denis ..Thank you for the reply....

This is how i am wrting the code but i am not able to get the data in Excel....Please let me know where i am making mistake....

Set rs = dbconn.OpenRecordset("Select * from [TestData_ETIF_08-1 Query] where [Product ID] like '*ABC-*'")

Set rs = dbconn.OpenRecordset("Select * from [TestData_ETIF_08-1 Query] where [Product ID] = '" & xlws.Range("B6").Value & "'")

For Row_Number = 1 To 300

Excel_Product_Id = xlws.Cells(Row_Number + 7, 2).Value
Set rs = dbconn.OpenRecordset("Select * from [TestData_ETIF_08-1 Query] where [Product ID] ='" & Excel_Product_Id & "'")



xlws.Cells(Row_Number + 7, 15).CopyFromRecordset rs

Next

rs.Close


Please wirte to me soon as i am stuck at this p[oint from long time....


Thanks a Ton...
Suji

 

[SydneyGeek]

Try using the % symbol instead of *. ADO doesn't use *.

Denis