Solved VBA Code for Search function with list box (1 Viewer)

Sarahbyrne3

New member
Local time
Today, 18:37
Joined
Dec 13, 2021
Messages
19
Hi All,

I have previously used access and new enough to get by. I have since been asked to build a material database for our buyers so that they can search for items they need and add them to a purchase order. I have tried various versions of code that i have found on here but nothing seems to work and i am now staring into my VBA for dummies book feeling like a dummy.

I have uploaded the data base that i have so far. I know its not got everything needed which is probably why i am having problems but im well and truly stuck.

My aim for this is to have a search function that minimises on a list and then to select and add to a report (purchase order). Its just this search list box that im throwing a wobbly over.

Please, please, please send help
 

Attachments

  • RED Materials.accdb
    1.9 MB · Views: 314

Ranman256

Well-known member
Local time
Today, 14:37
Joined
Apr 9, 2015
Messages
4,339
use the query: qsFilter that uses the text box txtFind with wildcards.
qsFilter= select [field] from table where [field] like '*" & forms!fMyForm!txtFind & "*'

Code:
sub txtFind_afterupdate()
if isNull(txtFind) then
lstBox.rowsource = "qsAll"
else
   lstBox.rowsource = "qsFilter"
endif
end sub
 

LarryE

Active member
Local time
Today, 11:37
Joined
Aug 18, 2021
Messages
562
I looked at your database and you have a lot of questions that need to be asked and answered before you do any record searching. First, you have no relationships between any of the tables. Looking at your tables and taking just a guess, it appears you might have multiple projects (tbl_Projects) that may have multiple people involved (tbl_StaffContacts), that may use multiple suppliers (tbl_Suppliers) that may provide multiple materials (tbl_Materials). Is this the correct scenario? ACCESS is relational, so you need to establish which tables have relationships before anything else is done.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

I agree with @LarryE. If you are not familiar with the concept of "normalization," you might want to review it first to help answer some of the questions Larry was talking about.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:37
Joined
Feb 19, 2002
Messages
42,971
When you are talking about searching are you talking about multiple fields or using wild cards to search for substrings in one field. The first does not require code but the second might.
 

Sarahbyrne3

New member
Local time
Today, 18:37
Joined
Dec 13, 2021
Messages
19
Hi and thank you for all your responses. Yes the idea is that multiple projects would order multiple materials from multiple suppliers and would be done by multiple people.

So the relationship is, tbl_StaffContacts, working on tbl_Projects, wants to put in an rpt_OrderRequest (report not made) to tbl_Suppliers 1, 2, 3 & 4 for tbl_Materials x, y & z. tbl_StaffContacts will be able to search for tbl_Materials and add them to a rpt_OrderRequest and then save and issue.

Not sure if that made sense but i did map it out on paper before i started but i think I've got a little lost in transit from paper to computer
 

LarryE

Active member
Local time
Today, 11:37
Joined
Aug 18, 2021
Messages
562
Hi and thank you for all your responses. Yes the idea is that multiple projects would order multiple materials from multiple suppliers and would be done by multiple people.

So the relationship is, tbl_StaffContacts, working on tbl_Projects, wants to put in an rpt_OrderRequest (report not made) to tbl_Suppliers 1, 2, 3 & 4 for tbl_Materials x, y & z. tbl_StaffContacts will be able to search for tbl_Materials and add them to a rpt_OrderRequest and then save and issue.

Not sure if that made sense but i did map it out on paper before i started but i think I've got a little lost in transit from paper to computer
So, your table and relationship structure would look like this:
REDElectronics.JPG
You needed Foreign Keys in each of three of the tables that relate to other tables with Referential Integrity enforced.
 
Last edited:

Sarahbyrne3

New member
Local time
Today, 18:37
Joined
Dec 13, 2021
Messages
19
apologies for not replying sooner. Covid got me :-(

I have added in the relationships, and a search query, and a reducing search function that i pinched from a template but its not running on the search for box.

Would someone mind having a look at the VBA on it and seeing what I've done wrong as its driving me insane.

Thank you all in advance.
 

Attachments

  • RED Materials.accdb
    4.3 MB · Views: 316

silentwolf

Active member
Local time
Today, 11:37
Joined
Jun 12, 2009
Messages
545
Hi,

You should use Option Explicit on top of your Access VPE Editor.. then you have not the problem that you are missspelling variables.
and it is easy to spot where the problems are!
 

Sarahbyrne3

New member
Local time
Today, 18:37
Joined
Dec 13, 2021
Messages
19
I have ticked that option and nothing happened? It hasn't done anything?
 

isladogs

MVP / VIP
Local time
Today, 18:37
Joined
Jan 14, 2017
Messages
18,186
Ticking the option only adds Option Explicit to any new code module. You need to add that manually to exisitng modules
 

Sarahbyrne3

New member
Local time
Today, 18:37
Joined
Dec 13, 2021
Messages
19
Its got that at the top now but its still not doing anything? Before i added that in, it was coming up as the below string is the issue and i cannot figure out why. I have changed it to SearchText.Value and that doesn't do anything either.
SrchText.Value = vSearchString

1642149603955.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:37
Joined
May 7, 2009
Messages
19,169
so you are Filtering the listbox?
see your material form.
also i added Criteria to the Query.
 

Attachments

  • RED Materials (1).accdb
    5.1 MB · Views: 339

Sarahbyrne3

New member
Local time
Today, 18:37
Joined
Dec 13, 2021
Messages
19
OMG Thank you you so much! I just did a little jig around the office. Amazing thank you again!
 

Users who are viewing this thread

Top Bottom