VBA to Update Information on Multipage Form

Hello,

I have this repair system which has the ID for records AFR#. There are AFRsDB and AFRsParts sheets that contain the data. I created a multipage form for inputting, retrieving and editing this data.

On my AFRsInput sheet there are requirements and a shape 'Show Form' for the multipage form.

How can the form be programmed to fill the requirements please? (I have the requirements on the AFRsInput sheet)

Thank you very much,
FF

Here's one way to do requirement#1:
This goes in your userform module and will fire every time the value in textbox1 is changed. When it finds a match in column A of the AFRsDB sheet it will populate the form.
I've only done the first few controls but you should be able to follow it and complete the rest.

Please Login or Register  to view this content.

I'll take a look at the other requirements and come back to it soon.

BSB

Thank you BadlySpelledBuoy I will get to work on the rest of the controls.

This amendment to the above code should take care of requirement#2. When it finds a match and populates the form it will lock the controls you mention from editing.

Please Login or Register  to view this content.

Got to go cook dinner for the kids now, but will come back to it after.

BSB

OOops, forgot to mention that for the above to work you need to put an x in the Tag property of the controls you want locked from editing.

BSB

In Post#6 I should have said the buttons will need codes to do that. I have a dream

The bit with the x was my addition to automatically lock those controls when a match was found and the form populated.
The controls have a "tag" property that can be set. By setting it for the controls you want to lock you can then loop through all the controls and lock only those with an x in the tag property and ignore the rest.

This means no need for the Lock button you added.

In the attached I've set all that up for you.

I've also added code to the Unlock button that will ask for a password (currently set to Password) and will unlock those controls if it matches.

Getting there?

BSB

Thanks yes it is getting good!

An update button might be needed to save any changes to the record. Also maybe next would be how to get the parts?

Originally Posted by fredfarmer

How do the x's determine which text boxes, check boxes, or combo boxes to be locked?

For the 'Clerk' area there needs to be more locked fields. All of the fields on the left column of the form plus the Customer Complaint and the Preliminary Inspection.

My bad these work just fine. I forgot to click the 'Lock' button

You shouldn't need to click the Lock button. All of those controls should lock automatically when the tool finds a matching AFR.

I will work on the update button now.

BSB

I don't know where you want your update button so I've just popped one at the top of the form.

It works in a similar way to the retrieve code, but with things switched around.
Here's a sample of it. Again you can add the rest of the controls.

Please Login or Register  to view this content.

Having thought about it though you don't necessarily need to include the "locked" controls if they cannot be updated.

BSB

Okay I will get to work on the rest of the controls. As Sintek mentioned previously "baby steps".

I appreciate all the help BSB

Baby steps indeed. Wise words from Sintek!

Happy to help where I can. I need to disappear for the evening soon but will come back to this tomorrow.

In post #11 you said "maybe next would be how to get the parts?". I don't really understand what that means or what is needed so you'll have to give some guidance on that.

BSB

The parts I mentioned I would like to have on the AFRsParts tab of the form. These parts will be associated with the AFR#. The tech will enter these parts to be ordered against this AFR#.

Til tomorrow or Monday maybe thanks for everything. Cheers!

Would the AFRsParts tab have a multi column listbox?

Good day all,

Does anyone know how to have parts on the other tab of the form that will stay with the AFR# ?

Thank you,
FF