[RESOLVED] Help with REPLACE in Excel

[thelocaluk]

I am having trouble with a form that will know display the correct data from a search, but when I try to replace data with the same form I get errors.

[DKenny]

Can you give a detailed explanation of what you are trying to achieve with this form, and also what is the error that you are receiving?

Also, have a look at my post here on user forms.
You have all your business logic contained in the form code - bad idea. With the detailed explanation of the forms function we can re-write this in a much more robust fashion. Let me know if you want to pursue this?

[thelocaluk]

Hi Dkenny, I've read your post on "Best Practise" before and thinks its a great idea, but I have such limited knowledge of VB that theres alot of things I've still to learn. I've attached the same but slightly enhanced excel file that you've helped me with before. What I want to do is have a command button on the main form that brings up a find and replace form. On this form I want to be able to call up a line of data from the data base using the Job ID as the find text as this is unique to each entry. Then I want to make it possible to change any of the data from that row and replace it on the data sheet. This will be useful for correcting mistakes to data or making necessary changes.
The passwords are "secret" for the form and "getmein" for the code.

The LocationFind file was something to test my idea. It wasn't an error sorry, but it was replacing eveything with the same name, I just wanted to replace the single range from the row it found the Job ID.

As always, your help is much appreciated.

[DKenny]

OK
I was really bored today, so here goes.....

The attached is a sample form which uses the methods I described in my other thread .

The 'SampleCode' procedure is an example of how to

1. Call your form
2. Return values from its properties(as opposed to its controls)
3. And then use those returned values to update the database if that was the users intent.

Based on your comments above that

Job ID as the find text as this is unique to each entry.

I have changed the user selection tool from a textbox to a combobox. For small sets of unique values, combo's are always a good choice.

The form has 4 public facing properties and these properties, along with the .Show and .Unload methods, should be the only elements of the form that are exposed in any calling procedures.
These 4 properties are:

1. JobID (read-only) - which returns the selected Job ID.
2. Save (read-only) - which tells the calling procedure whether or not to update the database.
3. DataSet (read-only) - which is an array of updated data which can be passed back to the calling procedure to allow it to update the database.
4. JobList (write-only) - which is used by the calling procedure to pass a list of Job ID's to the form.

In addition to this, all business logic has been stripped out of the forms code. Now all that happens in the forms code is that the variables that underlie these public propertries are updated. The beauty of this is that we can change the business logic at any time without having to re-code the form.

In this example all the data-entry controls are Textboxes, but you can easily change any of them into whatever control you like. For example: the "LateJob" control should probably be a checkbox. Were you to make this change in the form, the calling procedure will not need to be changed as we have not exposed any controls to it and therefore it doesn't know or care what type of control we use.

I've used the .Tag property of the forms controls to identify all the data-entry control. If you replace any of the textboxes with a different control, please make sure that you update the new controls .Tag with the appropriate value. The .Tag value is in the form "Fieldx" where x is a number (it can be multiple digits) and this number denotes which element of the DatSet property is altered by that control.
So, for example the Technician field is the first element in the DataSet array, therefore the corresponding control has a .Tag property of "Field1".

Really walk through the code and let me know if you have any questions.

Some notes on my naming conventions.
All variable are prefixed with a single letter denoting their type (l=long, s=string,b=boolean, etc)
A prefix of 'a' means the variable is an array.
A prefix of 'm' means Module level, 'g' means Global and blank for procedure level.
Therefore malJobList is a modular level array contiang Long data.

[thelocaluk]

WOW, thats fantastic work Declan, its exactly what I was looking for. I have read through your code twice and still can't quite get my head around some of it, but I'm learning fast. My learning tends to come from trial and error and playing around with code to see what there effects are. As computers ain't my bread and butter but instead a hobby my knowledge is limited and some of the jargon can seem confusing.
The sample you've made will be what I'll use as it does the job perfect, just changing a couple of text boxes for check boxes instead.
Could you recommend a good website or book that would be useful as a learning aid?
Again my gratitude is of the upmost respect for you for all your hard work, I hope your boss appreciates you Thankyou