Search box to find parts to add to job instead of a combo box

[andymartin3186]

Hi everyone
I currently have a job form, which has a job parts subform which looks up components from a components table. There is currently part number, description, manufacturer, more specifics, supplier price etc and you look up a part to add by the part number which is a combo box.
This works OK at the moment as my components table has 50 or so items in it but as it grows it becomes difficult finding parts, and the part number alone isn’t always very clear what that part is.
What I would like is maybe another subform showing the entire components table with search boxes to search on various fields, or maybe just a search all box. This would then filter down and you could then add the desired component to the job parts table.
Im fine with creating a subform with search criteria using the keywords in a query and having it display as continuous forms with a button on every line, but how would I go about adding the chosen item to the job parts table when clicking the add button? Im assuming it needs to run some code to use the chosen partID and append it to the jobparts table set to the current jobID?

Any help welcome. Happy to explain further if its unclear.

 

[MarkK]

how would I go about adding the chosen item to the job parts table when clicking the add button? Im assuming it needs to run some code to use the chosen partID and append it to the jobparts table set to the current jobID?

Maybe get more specific about where you are stuck with the bit I quoted. There are multiple steps to accomplish this, for instance...
• hook up a button click event handler
• execute an insert query
...each having distinct requirements.

Also, if you are inserting parts into a job, is there other related data like quantity, unit cost, dates, etc..., that makes sense to handle at the same time? Create a tool that enables the user to complete the task.
• beginning programmers commonly write features that are data-centric (what's the easiest way for me to manage the data)
• users want a task-centric process (what's the fastest way I can get the job done)
• think about what the user needs to get completion on the task.

 

[June7]

Could have code filter combobox list based on text entered. Similar to http://allenbrowne.com/ser-32.html. Modify to use wildcard on each side of input.

 

[andymartin3186]

Maybe get more specific about where you are stuck with the bit I quoted. There are multiple steps to accomplish this, for instance...
• hook up a button click event handler
• execute an insert query
...each having distinct requirements.

Also, if you are inserting parts into a job, is there other related data like quantity, unit cost, dates, etc..., that makes sense to handle at the same time? Create a tool that enables the user to complete the task.
• beginning programmers commonly write features that are data-centric (what's the easiest way for me to manage the data)
• users want a task-centric process (what's the fastest way I can get the job done)
• think about what the user needs to get completion on the task.

Thank you

The (link table?) jobparts links job.jobid & parts.partid and also has a qty field.
Apologies Im not a programmer Ive built this from scratch over the past 3 months and now it all works Im improving bits
The qty used is usually input when adding a part. At present this does nothing but store the number however my plan is for it to decrement the qtyinstock which is stored in the part table in future to create a working stock system

Currently my subform is an empty datasheet where you add job parts one by one by a combo box on partid which displays the part number in the combo box. This is OK but the part number isn’t very clear plus once there are thousands in the parts table it will be a long scrolling exercise so I would like 2 subforms, one being the full parts table with filter boxes above, and once filtered the appropriate row has a button which adds it to job parts subform. Does that help? Apologies I maybe need to add screenshots

 

[Gasman]

If you type into a combo, it jumps to the next matching item?

 

[andymartin3186]

If you type into a combo, it jumps to the next matching item?

That works as long as you know what you’re searching for which is sometimes difficult as electronic components have part numbers, marking codes, descriptions. Ideally I would like to filter on these multiple fields instead of just a part number combo box then add the desired filtered result to the job,

For example a particular capacitor may have a part number

MCAX25V477M10X21​

But its description would be “470uf 25V capacitor”and it would be good to filter by typing 470uf capacitor rather than searching the part number field using scroll bars in a combo box and then see all the different ones available then choose the desired one to add to the job

 

[June7]

Did you see post #3? The filter criteria could apply to multiple fields with OR operator.

 

[MarkK]

We are talking about multiple things here. 1) Select a part from a list, 1a) select using combo vs search multiple fields in a table, 2) hook up a button to run code, 3) manage data collection of JobID, PartID and Quantity to add a part to a job, 4) execute an insert into a table,
• My 2c is narrow down the focus of inquiry so that the question is very clear and simple. This makes it way more likely that a clear and simple solution will be developed from the inquiry. Once that inquiry solves a problem, move on to the next sub-problem.
• If you have a problem where the focus is too broad, this makes solving the problem harder. Focus in on discrete units of work within the broad problem, and solve each one independently.
• To start on this problem, for instance, I would develop a procedure that takes all the parameters required to add a part to a job.

Public Sub AddPartToJob(JobID as Long, PartID as long, Quantity as Single)
End sub

• Once that is working, move on to the next sub-problem. I would concern myself next with selecting a part.

 

[June7]

Selecting part from BOUND combobox should be able to save to record without resorting to a "search form" and code to INSERT a record. My suggestion is code to reduce the combobox list to a smaller set of choices for user to review and select from.

 

[GaP42]

You might also consider a find as you type capability - see @MajP (FAYT classes) has provided a range of capabilities that can be applied - as a search form (for parts), or a list box, a text box or combo that can filter as you type across any number of fields. Once an item (part) is identified insert the part id into you job-part list as you do currently (I think?).

https://www.access-programmers.co.u...u-type-controls-combobox-listbox-form.327863/

 

[June7]

FAYT is basically what I suggested, just didn't put a name to it.

 

[gemma-the-husky]

I've done this sort of stuff before by letting users enter a string and splitting it into its substrings, then searching for matches in the product descriptions on the substrings.

This was an effective alternative to multiple combo boxes.

So you enter something like "8 black widget" to reduce the selection size.

 

[andymartin3186]

Hi everyone,
I've attached a few screenshots of the existing design, and also the last one which is how I filter to look up my jobs. When I look up my jobs I can type into any one of the top boxes and it filters on this keyword, I can then open the job by clicking the button.

I would like to do the same thing when adding a part to a job, rather than have to drop a combo box down on a single field, I would like to be able to filter on multiple fields - part number, description, marking code, order code etc, and once the required item is found, add it to the job parts subform by clicking the button on the right. I can do the first bit no problem, it will just be a case of creating another subform with filters, but its the code needed to get the details of the chosen part, and add it to the jobparts subform as a new record. Does that make sense?

1,png shows current job form with jobparts subform empty
2.png shows the part code combo box - it is difficult to use as the part code isn't always self explanatory
3.png shows how I find my jobs, I would like this method to find and add parts to jobs

 

[andymartin3186]

Update - I've done all the work to add the 2nd subform. I just need the code now to put into the button so when I choose a component from the components table it adds its partID as a record to the jobparts table using the current jobID

 

[GaP42]

When you press the button - triggering the On Click event obtain the PartID, and the JOBID. In your code construct a SQL INSERT statement, which you then execute:
INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

so it might be

intPartID = me.PartNo
intJobID = me.JobID

strsql = "INSERT INTO JobParts (PartID, JobID) Values ( " & intPartID & ", " & intJobID &)
debug.print strsql

Docmd.runsql strsql

you need to dim the variables, and you should use Debug.print strsql to check it is properly formed / valid before executing then comment it out
And when it is working to your satisfaction you may then want to turn off the warning message confirming are you sure you want to insert the record, immediately before the Docmd and turn warnings on again immediately after.

 

[mike60smart]

If it is just the one item you are selecting then you use a simple Insert Into OnClick Event

 

[Gasman]

Would you also need a requery?

 

[Gasman]

If it is just the one item you are selecting then you use a simple Insert Into OnClick Event

Where does one find that event?

 

[MajP]

@andymartin3186,
On a current database I am working we have lots of large lists with lots of categories that using a standard combobox would be hard. You need lots of features to search what you want. This design gives us a lot of flexibility without a lot of code to sort filter and search.

In yellow on the main form (lower right) I need to pick resources. I might need to sort or filter on Type, Resource Group, Unit etc. But the list could be hundreds or thousands. I might not know the exact name so a Find as You Type combobox may not be the best.
If I click on the resource field (or add a button) it pops open the resource selector form. Since it is a datasheet I can filter or sort on any column. Then either double click the row I want or select OK to return the current selection.

On the pop up I could add way more capability, but the built in filter capability of that datasheet is way beyond what most people can code and what I want to spend time doing.
Imagine trying to recreate this flexibility. If you have dates the provided filter is way, way beyond what is easily codeable.

 

[GaP42]

Would you also need a requery?

Yes. And the JobID is probably obtained from the parent form: intJobID = me.parent.JobID and the quantity of the part is likely also needed as a further item in the insert statement - set to 1 and allow the user to adjust the quantity in the subform after requery: set the focus to that control may be?