Allow user to enter their own data or select from a list

[hzeigler4]

I have a main form that allows the user to select a workorder from a combo box. This will filter my continuous subform below it. I then want the user to enter any parts used for this work order. Here is the problem. The user can select an existing part or enter a new one(but I do not want the new one to be added to my parts table, just add it to this particular work order). I orginaly thought that I would have a bound text box for the part number and an unbound combo box with a list of parts. I would let the user enter a part in the text box or select one from the combo and fill the textbox with the selection. This obviously does not work because the combo box is unbound and on a continuous form.

Soooo, my other idea was to get rid of the combo box. Keep the part number text box and put a button next to it that says "Select an Existing Part". This would popup a form with all the parts from the part table and allow the user to select one. Then the form would close and fill the part number text box with the selected value from the form. Is this crazy? Impossible? Anyone know how to do this or have a better way? I am attaching my database. The main form is frmPartsUsed and the subform is subfrmPartsUsed.

 

[pbaldy]

You could simply have a bound combo that allows items not in the list to be typed in (Limit to List = No).

 

[hzeigler4]

Won't that automatically add what the user types to the list and my Parts Table??

 

[CraigDolphin]

Well, going with your first idea see the attached DB.

First off, your textbox was bound to a numerical field: which is fine for storing the PartID of parts that already exist in your parts table but you also want to store 'part numbers' of parts that are not in your parts table so obviously there is no numerical PartID in existence for these (and since you don't want to add them there never will be an equivalent PartID). Also your example 'part numbers' can contain alphanumeric values (xyz123) which cannot be stored in a numerical field.

So, I took the liberty of changing your field WOPArtID (Integer) to WOPartNumber (Text) and updated your form accordingly. (I also had to fix one of your queries that listed this field.) I then put some code in the after update event of the combo box to do what you wanted.

If you look at the code, you'll note there's one line commented out. That line simply looks up the price of an existing part from your pricelist. However, because I cannot see the fields in your pricelist spreadsheet you'd need to substitute the correct field names and uncomment the line for it to work.

You also had a lot of compile errors in your db due, I think, to you copying your forms and then deleting controls that had existing vba code associated with them. This left relict lines of code pointing to controls that no longer existed. I commented out the offending lines but you may way to go back through your form's code modules and remove extraneous code.

 

[pbaldy]

Won't that automatically add what the user types to the list and my Parts Table??

No, not if you don't have the appropriate code in the Not In List event (which doesn't even fire if Limit to List is No).

 

[hzeigler4]

pbaldy,

I feel stupid for not knowing this. I just assumed it would add it to the table. That option does work.

CraigDolphin,

That is awesome you were able to get my original idea to work. That is exactly what I wanted!!!!!! It all seems so easy!!!! Yes I realized that I will not be using an integer for my ID. I am going to have to change the structure of my tables a little to be able to use that pricelist table that comes from an excel spreadsheet instead of a parts table like I orginally planned for.

Thanks so much to both of you for helping me resolve this issue!!!