Solved VBA Code for Search function with list box

[Sarahbyrne3]

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

 

[Ranman256]

use the query: qsFilter that uses the text box txtFind with wildcards.
qsFilter= select [field] from table where [field] like '*" & forms!fMyForm!txtFind & "*'

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

 

[LarryE]

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]

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]

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]

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]

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:

You needed Foreign Keys in each of three of the tables that relate to other tables with Referential Integrity enforced.

 

[Sarahbyrne3]

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.

 

[silentwolf]

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]

I have ticked that option and nothing happened? It hasn't done anything?

 

[isladogs]

Ticking the option only adds Option Explicit to any new code module. You need to add that manually to exisitng modules

 

[Sarahbyrne3]

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

 

[arnelgp]

so you are Filtering the listbox?
see your material form.
also i added Criteria to the Query.

 

[Sarahbyrne3]

OMG Thank you you so much! I just did a little jig around the office. Amazing thank you again!