Full Version: Pop-up search form for subform?
UtterAccess Forums > Microsoft® Access > Access Forms
gkmros
In my quest to make the perfect Workorder entry application for my customer I have dealt with a plethora of problems in trying to keep the data entry as simple as possible for the end user.
For example, in my main workorder screen which the mechanic or service manager fill in the information about the service job being performed on a vehicle there are two subforms: one for entering the amount of labour performed; the other for entering and listing the parts used in the repair. The Labour subform is straight forward and does not require any further work. The Parts subform is a little more difficult for the user. He must select the part from a combo box which lists the Part number. Sometimes the user knows the part number and can find the part very quickly. But other times the user only knows the description of the part or its general category, and the subform does not contain a way to search by these parameters.
There are two tables for tracking the parts used: Parts, whcih contains the PartID(pk), Part number, description, category, quantity on hand, supplier, cost, and markup percentage; and WorkorderParts, which contains the WorkorderPartID (pk), fkWorkorderID, fkPartID, and Quantity.
I would like to give the user 3 ways to search for the Part: by Part number, by Description, and by Category. Category is a general description that describes the type of part used (Brakes, Engine, Cooling System, etc.). I would like to implement a pop-up form (from the subform) with only these three fields showing as combo boxes. The user can drop down any of these lists and select the part, which would then populate the row of the subform.
I found something close to what I need on one of the Access Tip pages HERE. It deals with Dialog Boxes. But I am not sure exactly how to implement this into my application. I have also tried a few other techniques that have been suggested by other members here, but I have had little success. Can anybody help?
Jack Cowley
You should be able to do this without too much difficulty. Create your popup form and the code in the subform to open it. In the After Update event of the combo box(es) you can use code to fill in the controls on the subform. This is 'air code' but should get you started:
CODE
Dim rs As New ADODB.Recordset
rs.Open "NameOfTableToUpdate", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!WorkOrderID = Me.NameOfControlWithWorkOrderID
rs!PartID = Me.NameOfControlWithPartID
rs!NameOfPart = Me.ComboBoxName.Column(n)
...more fields that need to be filled in...
rs.Update
rs.Close
Set rs = Nothing
[Forms]![MainFormName]![SubformControlName].Form.Requery

Note that 'n' in the column is the physical number of the column that has the data you want minus 1.
My 3 cents worth as there are other ways to skin this Access cat....
hth,
Jack
NoahP
Here's a little demo I was working on while Jack was posting. A little different way to get there, but should be close to what you want.
TH
Noah
Jack Cowley
Nice demo Noah! As always I took the easy route...
lapper Valve? 16d nails... coated or uncoated? Is there anything you are not familiar with?
Jack
gkmros
Yes Noah, that is a good demo. I had a good look at it and have even tried to adapt some of the concepts to my application. I am a little curious about the way you have set up the tables for the Parts. You have a seperate table for the categories. Is there an advantage to creating a seperate table? I have a category field in my Parts Table. Do you think I should create a separate table for categories?
Back - I have not tried your solution yet, but I will take a look at it. Thanks.
Jack Cowley
gkmros -
o with Noah's approach as it is, I believe, what you are looking for.
I do not want to answer for Noah, but I will say that his suggestion for a table of Categories is an excellent one, in my humble opinon. Noah is an excellent programmer and his suggestions are always sound and accurate.
Jack
NoahP
Thanks for the kind words Jack! I do appreciate them, especially coming from you!
I have done a little of everything in my short life. Soldier, Carpenter, Handyman, Programmer, Instructor, Supervisor, even sing and play drums a bit. Pretty much a Jack of All Trades I fear.
qkmros: Anytime you have a field in which a value is repeated multiple times over different records, you really need a lookup table like I did with the categories. It can virtually eliminate errors in keying data.
Noah
gkmros
I agree. I can see the logic of creating a table for Categories. I will give it a try.
Thanks guys!
NoahP
You are welcome!
oah
Jack Cowley
Noah -
You are one of the stalwarts here at UA so you have earned every kind work you get! Hmmmm. Sounds like we have similar backgrounds as well, except I was never an Instructor nor can I sing a note!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.