Search for vendors by goods/services supplied (1 Viewer)

[OverSight]

Hello, I have a vendor database that keeps track of basic vendor contact information as well as goods/services they provide. I want to have the function where the end user can search for an item like "Laptop" and a list of vendors that supply a "Laptop" will show. Right now I have it that the end user free types the goods/services the vendor supplies and so there would be multiple entries of "Laptop" or variations depending on how it is input by the end user, such as "Laptop, Silver" or "Laptops".

I am not sure how add this search functions as I have pretty much zero Access skills. I have attached a copy of my database, note it was built using 32 bit version of access as that is all we are able to use.

Thank you in advance!

 

[jdraw]

Nothing wrong with 32 bit Access.
Do you have some description of your requirements? Often better to create a model (at 30,000 ft level) and progressively add detail. Very long journey if creating a physical Access(or any) database and attempting to incrementally add detail. Make a model, construct some test scenario and test data, play stump the model to vet and revise the model. That will lead you to a blueprint for your database.

Instead of allowing user to free form type, I'd recommend a set of cascading comboboxes. This will improve communications and reduce typos/issues.
I'll take a look at your database.

There is a hierarchy to GSINs. There is a separation of Goods and Services to Goods.
Can you describe in some steps how a user might identify a specific Good from the GSIN details?

I can find Computer Supplies GSIN 527, but no more detail. Where/How would GSIN get you to a laptop?

Are you certain that GSIN has the detail you need to meet your requirements?

SELECT tblGSINs.GSIN_ID_PK
, tblGSINs.GSINShortDescription
, tblGSINs.GSINLongDescription
FROM tblGSINs
WHERE
(((tblGSINs.GSINShortDescription) Like "*computer*"))

 

[OverSight]

Nothing wrong with 32 bit Access.
Do you have some description of your requirements? Often better to create a model (at 30,000 ft level) and progressively add detail. Very long journey if creating a physical Access(or any) database and attempting to incrementally add detail. Make a model, construct some test scenario and test data, play stump the model to vet and revise the model. That will lead you to a blueprint for your database.

Instead of allowing user to free form type, I'd recommend a set of cascading comboboxes. This will improve communications and reduce typos/issues.
I'll take a look at your database.

There is a hierarchy to GSINs. There is a separation of Goods and Services to Goods.
Can you describe in some steps how a user might identify a specific Good from the GSIN details?

I can find Computer Supplies GSIN 527, but no more detail. Where/How would GSIN get you to a laptop?

Are you certain that GSIN has the detail you need to meet your requirements?

SELECT tblGSINs.GSIN_ID_PK
, tblGSINs.GSINShortDescription
, tblGSINs.GSINLongDescription
FROM tblGSINs
WHERE
(((tblGSINs.GSINShortDescription) Like "*computer*"))

Hi there, thank you for taking the time to look into this for me. Basically I have a table with all the GSIN codes and descriptions that we might use to categorize a good or service. Right now this is just being used to provide a list for the end users.

In my Vendor Details form "frmVendorDetails" I have a tab called GSINs/Goods/Services where the end user can selectthe GSIN and then free type in an actual product or service they have already purchased from a vendor. The GSIN is just to help categorize the item for information only. Really the end users just want to add any actual good or service the purchased from a vendor into the database and then have a way they can search for the a good or service and see a list of vendors that supply that item. I do like the idea of cascading comboboxes, but we buy a million different items and it might be too complex to add every single item that way. That is why I opted for a free form type, with the idea of having a way to search by just typing in whatever is known to the users so if they wanted to look for "garbage bags" they could start typing any part of that and the database would start displaying and vendor that has that criteria in its record. But since this data is not in the Vendor Record but a separate table as a subform I just don't know how to incorporate that.

So to find a GSIN or product that is categorized under a GSIN we use our internal procurement software. The end user would already know the info at time of procurement, the GSIN and identification codes are all supplied and tagged for us.

With this Access Database really the goal is to provide a quick and dirty way to search for a vendor that might supply something like Garbage Bags or Laptops or whatever. Most of us over the years just gain that knowledge have a general idea of our local vendors but for anyone new employees they don't have that experience or knowledge so just wanted to have a fast way for them to do this.

The GSIN is just a cataloging/categorizing thing for us. Really all the end user wants to do is search for the actual good or service a vendor supplies.

Ya so the GSIN "527" is the unique Access Primary Key for GSIN 7045: Computer Supplies. After doing the table I realized that I could have used the actual GSIN codes as Natural Primary Keys. And I didn't think about having the functionality to search for an actual product at the time.
For the purpose of this database, selecting the GSIN and entering an actual product or service is for quick look up. The actual determination of what GSIN to use for what product is done on our internal procurement systems.

I know I am doing a terrible job at explaining this.

 

[jdraw]

There are several Product codification schemes. Every now and then there is a new one, and sometimes revisions to existing ones. GSIN has been used with government procurement for a long time.
There also exists NAICS , UNSPSC, Harmonized... and FSC/Federal Stock Number, NATO Stock Number

And many industries (Electronics..) have their own set ups.

Each has value, but is dependent on your needs/requirements. Proper assignment of codes to "user descriptions" can be difficult to very trying. So you'll have to decide/determine the level of detail required for your needs.

The actual determination of what GSIN to use for what product is done on our internal procurement systems.

How does your Access database/system communicate/relate to the internal procurement system?
Do you work with a government agency/department?

Consider a requirement to find suppliers of a silver laptop, where and how would GSIN be helpful?
Then suppose you wanted it with 16 GB RAM with 1 TB NVME drive...

How much detail do you really need to store and maintain? Then decide how you would keep it current.

 

[OverSight]

There are several Product codification schemes. Every now and then there is a new one, and sometimes revisions to existing ones. GSIN has been used with government procurement for a long time.
There also exists NAICS , UNSPSC, Harmonized... and FSC/Federal Stock Number, NATO Stock Number

And many industries (Electronics..) have their own set ups.

Each has value, but is dependent on your needs/requirements. Proper assignment of codes to "user descriptions" can be difficult to very trying. So you'll have to decide/determine the level of detail required for your needs.


How does your Access database/system communicate/relate to the internal procurement system?
Do you work with a government agency/department?

Consider a requirement to find suppliers of a silver laptop, where and how would GSIN be helpful?
Then suppose you wanted it with 16 GB RAM with 1 TB NVME drive...

How much detail do you really need to store and maintain? Then decide how you would keep it current.

So my database won't communicate with the internal system. Its totally separate and it will only be used as a glorified contacts list or "Yellow Pages" with the ability to add and search for goods and services that a vendor provided us. So instead of an employee writing down in a cheat sheet or sticky note that "Vendor A" sells Garbage Bags, we can share the information among ourselves in the database.

So an employee that needs to buy "Garbage Bags" and doesn't know what local vendors might supply them or what vendor we have purchased them from before can do a simple search and see a list of all the vendors that were tagged as having supplied them.

So the GSIN for this purpose would only be useful as a reference for the employee who might not know the GSIN we use as a categorizing tool.

The detail of the good or service does not need to be in any great detail. I'm happy with as basic as possible, like "Laptops" or "Garbage Bags". We don't need specs or anything in the database. We have some employees that just keep an excel spreadsheet of this basic information but I thought if I could create a more centralized database that would give some additional functions.

So right now using my frmVendorList I can search for vendors based on any field in it like vendor's name, address, phone etc. and this is because I used a search macro provided in a Microsoft template I found, but I would also like to somehow be able to search for the Goods/Services Provided (which is entered through the frmGSINSupply_Subform to and held in tblVendorGoodsServices and have it bring up a list of any vendor that has that criteria associated with it.

 

[MajP]

I simply copied my other search form and changed one paramater.

This can work the Same way as a pop up. You search for a vendor and select the record then hit close and it populates the vendor.
The beauty of a custom class is reusability. To make this work I changed one word (the search field name)
FAYT_Frm.Initialize Me, Me.txtSearch, ffrm_Anywhereinstring, True, "GS_Supplied"

The label in the image is wrong because the search is by "GS_Supplied". If you type in more field names you could also search by Vendor, GSIN code, description etc.

 

[MajP]

FYI on the Goods and Services search form you broke the ability to return the GSIN. You renamed the OK button to cmdCloseVendorList. If you want that capability then you need to move the code from CmdOK into your new event procedure.
Also you removed the cancel button. So now when you call the search form it will always return a value even if you do not select anything. Likely the first record in the form.

Private Sub CmdOk_Click()
   SelectedGSIN = Me.GSIN_ID_PK
   Me.Visible = False
End Sub

Private Sub cmdCloseVendorList_Click()

End Sub

I did not change the code in the vendor form to return the vendor id. But this can be done easily if that is your plan for using it.

 

[OverSight]

FYI on the Goods and Services search form you broke the ability to return the GSIN. You renamed the OK button to cmdCloseVendorList. If you want that capability then you need to move the code from CmdOK into your new event procedure.
Also you removed the cancel button. So now when you call the search form it will always return a value even if you do not select anything. Likely the first record in the form.

Private Sub CmdOk_Click()
   SelectedGSIN = Me.GSIN_ID_PK
   Me.Visible = False
End Sub

Private Sub cmdCloseVendorList_Click()

End Sub

I did not change the code in the vendor form to return the vendor id. But this can be done easily if that is your plan for using it.

 

[OverSight]

Oh yes thank you for pointing that out! I didn't realize the functionality of those buttons. I've fix that issue now. I am wondering with the search function can I add multiple field names so that they can also be searchable or is this only for one field at a time?

 

[MajP]

can I add multiple field names so that they can also be searchable or is this only for one field at a time?

If you look at the initialize event in the first form I listed two fields names separated by commas. This was the short and long descriptions. So you can list as many as you want. Unfortunately you can only set the fields at instantiation

I have some another very similar control using a listbox. It allows you to change it on the fly. In that case a have checkboxes next to the listbox.
Last Name, First Name, Email, Address etc. You can pick one or many fields and it will check within those fields, and you can change it at any time.

If I get time I will fix it. Then for example on the above form you could have checkboxes
Search by:
Vendor
GSIN
Description
Item
All

Then you can pick which ones to search within.
For the meantime just add additional field names separated by commas.

 

[MajP]

Here is an updated demo and I updated the class module to make this easier to do. This now lets you select the search fields dynamically.
You can pick one or more buttons to change which fields to search. You can verify by the image that it is finding "ap" in either column. Since this give so much flexibility this is my standard GUI for text searches.

There is something weird with this demo and not sure it is related to the FAYT. If I select a button then start typing, I cannot change the buttons until clicking out of the text box into another textbox. I cannot replicate that on other forms.