Locking column in query or form allowing edit of others 1

[CliffordTheRedDog]

I have a form that allows users to display/filter a 3 field table (Product, Part No, Qty) the default form allows users to inadvertently change the Product field and allocate the Part No to another Product, I need to make the first field filterable to allow the user to only see the cutting list of the one product but then only allow editing of the Part No or Qty Field. An example would be that product “A” has three parts 1, 2, & 3 each has Qty 1 each. The user must be able to change say Part 1 quantity to 2. Delete part 3 and add a new part say part 4 1off. Where would I put the restraints?
Any pointers would be most appreciated.
TIA

 

[GingerR]

Do you have a table which lists unique products?

I suggest you create a main form, based on a table listing unique Products (tblProducts?). Put a text box showing the Product Name. You have a subform based on the table you mention above, and embed it on to the main form. Make the child/master links for the forms = ProductID. You also put on a combo box on the main form, which is labelled SEARCH; the user picks a Product, the main form moves to that Product, and the subform displays the related Parts and QTY data. How's that sound? This is a typical set up. It keeps the distincting between searching on an item and actually changing the Product.

To make the SEARCH combo box, use the combo box wizard and pick the third option.

So try that out and let us know how it goes!





Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CliffordTheRedDog]

GingerR
Thanks,
I will try this and get back to you
Cliff

 

[CliffordTheRedDog]

GingerR,
I have created a form by adding a combo box and then dragging in a form that creates a subform, which according to help files (and if i understand your message correctly) should create the master child link providing such a relationship exists in the relationship section - which it does. However... the form does not react in the way i expected. I can still change part allocation inadvertantly by scrolling the product field. I tried another method of creating a location table query which uses the combo box product selection as a source. this is fine for ammending and deleting but creating is not possible as the create row does not "know" that the row should be added to the same product. - as selected in the combobox! I think there may be some very obvious mistakes that i cannot see as yet. I do have a Create part form that i can add parts but, should i be able to do all three in the one form; do you see what i am doing wrong?
TIA
Cliff

 

[CliffordTheRedDog]

GingerR
I am looking at your first message, what does the text box do. Does the Form "Move" to the product searched in the combo box by virtue of the fact that the source of the subform is somehow linked to the combo box selection? I Can point the sub form to display the location table but can't link the child or master properties. what do i link them to?
I will continue reading up but any help would be greatly appreciated.
TIA
Cliff

 

[GingerR]

The text box merely shows the current product name. It's just for display.

Did you add a new combo box as I suggested? If so, look at the code in the AfterUpdate property that MS Access wrote for you...what it does is move to the selected record. The combo box should be UNBOUND (meaning no control source). It is for searching only. On the main form, there should be no other controls except a text box and a combo box.

The subform/main form should have child/master links of ProductID (or whatever your ID field is called). You main form should have a recordsource of tblProducts.

Sorry this is all screwed up, maybe because you started with something that already existed and now are trying to backtrack with my instructions, things are getting messed up...i dunno.

Try this:

Make a brand new form, based on a table tblProducts. This should be a table listing unique products.

From the Field Chooser bring ProductID and ProductName on to the form. Add a combo box and use wizard choice #3. Save the form as MAIN.

Make a second form based on the table you first mention. I'll call it tblProductParts. With the field chooser, bring all the fields onto the form. Make the default view be DATASHEET. Name this form SUB and close it.

Drag form SUB onto form MAIN. The child/master links should attempt to be ProductID. If not, set it that way.

Save.

Open form MAIN. In the subform, the ProductID of the PRODUCTID that is showing on form Main should be the default ProductID.

Choose a diff product from the combo box. The MAIN form should move to that record, and the subform should show PARTS related to the new ProductID.

Does that work?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CliffordTheRedDog]

GingerR,
I have been away, back now, will digest and respond thanks for the extra help!
Cliff