Solved

MS Access Text Box/Command Button to be used as Search for List Box Population

Posted on 2009-04-11
5
743 Views
Last Modified: 2013-11-28
Hi-  (Sorry for not using proper naming conventions)
I have a ListBox (List16)
I have a Text Box (Text67)
I have a Command Button (Command71)

The data being looked at is in Table: Company, Field: Company_Name.
My Company Unique ID is called: DUNS_Number
My main form is called: CompanyMain

I would like the user to be able to add something like this to Text67
Ash*

And then they could click Command71 and List16 would show a list of companies that contained Ash.. such as Ashland Chemicals or Ashley Corporation ect.

I woudn't mind if they could use the * at the beginning either..
*Ash*
Returns: Ashland Chemical or Stashouse Brick

I also have two other ListBoxes (List45 and List53) which would have to be set to null at the end of the process.

I was reading another thread about this but I got myself confused..
I don't even know if this is what I should be using (as I mentioned I got this from another thread) so a complete re-write is fine with me too..

Code is below -
Thanks for any help.
Stacy



Dim strSQL As String
 
'Set row source for list box
   strSQL = "SELECT Company.DUNS_Number, Company_Company_Name, "
   strSQL = strSQL & "WHERE Company_Company_Name like '" & [Forms]![CompanyMain]![Text69] & "*'"
 
'Update List Box with search results
   Me!List16.RowSource = strSQL
       DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    Me.List45 = Null
    Me.List53 = Null
 
 
End Sub

Open in new window

0
Comment
Question by:stacydr
  • 3
  • 2
5 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24122964
Im getting used to your db but getting more confused by the access generated names, lol

Dont bother with the user entering *, just add it in yourself

on the click event of Command71, add your code

private sub Command71_Click()

Dim strSQL As String
 
'Set row source for list box
   strSQL = "SELECT Company.DUNS_Number, Company_Company_Name "
   strSQL = strSQL & "WHERE Company_Company_Name like '*" & NZ(Me.Text67,"") & "*'"
 
'Update List Box with search results
    Me.List16.RowSource = strSQL

    Me.List45 = Null
    Me.List53 = Null
end sub
 
 
End Sub
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24122969
got duplicate end sub in the code, so ensure you ignore one of them
0
 
LVL 2

Author Comment

by:stacydr
ID: 24124489
Again - works great -- but :) anyway we can add an Order By Company_Name somewhere in here for the results...
I tried but no luck..
Thanks, S.
0
 
LVL 2

Author Comment

by:stacydr
ID: 24124496
Got it -- nevermind ...

Me.List16.RowSource = strSQL & "ORDER By Company.Company_Name"

thanks again!!
def a pleasure
0
 
LVL 2

Author Closing Comment

by:stacydr
ID: 31569199
def awesome working with you -- your codes are the best! thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question