Forum

Notifications
Clear all

Searching for Data With a User Form

8 Posts
4 Users
0 Reactions
199 Views
(@owbguy)
Posts: 4
Active Member
Topic starter
 

Hi there,

Created the search box with the macros , as per the web site, doesn't seem to find any records?

Have attached a copy of the excel

 

Any help would be great.

 
Posted : 05/02/2020 5:09 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Christopher

Try changing this line of code from

RecordRow = Application.Match(CLng(TextBox1.Value), Range("Table1[Record]"), 0)

to

RecordRow = Application.Match(TextBox1.Value, Range("Table1[Store]"), 0)

1) You are searching for text so no need to convert to CLng

2) You are searching the wrong field Record instead of Store

Hope this helps.

Sunny

 
Posted : 05/02/2020 9:05 pm
(@owbguy)
Posts: 4
Active Member
Topic starter
 

Hi Sunny,

 

Thank you for your help.  This works only when I am searching by Store, but does not work when I am searching by any other field even if I include the Store in the search, for example :  Store - NOF, product - Apples.  Do not receive the correct answer.

 

Again much appreciate the help

Christopher

 
Posted : 06/02/2020 7:14 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Christopher

The form was designed to search by one field.

Data in that field must be unique  as the form will only search for the 1st occurrence.

In your sample data, the are multiple NOFs and this form will not work for you.

Cheers

Sunny

 
Posted : 06/02/2020 7:02 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi Christopher,

As Sunny said, the form in my blog post was designed to only search using one field and to return 1 record.

How do you wish the search to work?  Do you want to be able to specify multiple fields and then return all matching records?

If so do you want to then pick just 1 record to edit?

I notice that the spelling of certain things is not consistent in your data.  There are several different spellings of Kelloggs and Baguette.  These inconsistencies will mess up searches.

Have you considered just using the filters on the table to find what you want?

Regards

Phil

 
Posted : 07/02/2020 12:18 am
(@owbguy)
Posts: 4
Active Member
Topic starter
 

Thank you Sunny and Phil,  as good as this search form is, I now understand that this is not going help me out.  I appreciate the help on this one. 

Take care

Christopher

 
Posted : 07/02/2020 7:29 am
(@catalinb)
Posts: 1937
Member Admin
 

Hi Christopher,

You can play around with one of the search forms I'm using, see the file attached.

Type 008 for example in the Search for box.

 
Posted : 07/02/2020 8:02 am
(@owbguy)
Posts: 4
Active Member
Topic starter
 

Hi Catalin,

Thank you very much, this seems to be the type of search form I am looking for.  I'll play around with it, again thanks

Christopher

 
Posted : 07/02/2020 12:12 pm
Share: