User input preferences "Field Input Prompts" ... (1 Viewer)

I wanted to create a Table that listed the preferred titling to Input Fields, e.g. some companies use the following titles for the same "Type" of information: Shop Order, Works Order, Customer Order, and depending on the size of the company or what information system they use, e.g. SAP, IFS, etc, call the very same data different names.

Is it possible to set up a "Customisable" Table that allows Admin Users, of said companies, to change the name of Fields, so that Users in a specific company can more easily recognise the data required for input.

Example:

Company A
InputField InputPrompt
Data Field 1 "Works Order"

whereas,

Company B may update their Table as
InputField InputPrompt
Data Field 1 "Shop Order"


I know how to do this for List Box's etc but not sure for Form Field Prompts?

Thanks, Simon.

Assuming you want to customise the displayed labels on the form.
Table: tblCustomName
Field: LabelName
Field: LabelCaption

On Form Load event cyles through the labels and change their caption.
Every label will need a custom name or this code will either set them to null of error out. You could add a test to exclude missing entries.


If it was to be done for multiple forms make it public and pass the form name as an argument in place of Me.

Make a form so the end user's admin can set the names. This way you can stop them deleting label names or other damage.

Yes you can do exactly as you said. Presumably you have a Company Table, and a universal Works Order Table. Create a 3rd Table that contains CompanyID, InputFieldID (as we know it), CompanyFieldName (as they know it) : CompanyID and InputFieldID combined as PrimaryKey.
e.g.

CompA, WorksOrderID, Works Order
CompB, WorksOrderID, Shop Order
CompA, SalesOrderID, Sales Order
CompB, SalesOrderID, Sales Order
CompC, SalesOrderID, Customer Sales Order

Now you can use this table for multiple customers for multiple input fields.


Now when displaying Works Orders for ComB, create a query that links this table (by CompanyID only, and filter on [InputFieldID] = "WorksOrderID") to your Company Table and Works Order Table. Now instead of using a "label" on your form, use a bound control to the field [InputFieldID]. When displaying the same form for CompanyA you should see "Works Order", but for CompanyB you should see "Shop Order".

Repeat for "Sales Orders", but link query to Sales Order Table and not Works Orders, and set [InputFieldID] = "SalesOrderID".


If Company changes it's understanding of an input field you can change it in the table once.

To keep history, i.e. used to be "Shop Order" and is now "Shop Sales Order", create a field in your Order Table, that is populated from this 3rd table at the time of creation and use this field as your "label control" on your form.

The above principle applies to pop-up messages etc, and not just form displays.

Took me a while to understand Endre's technique. The crux is the use of textbox controls in place of of labels. Very clever idea.

OK, Endre's idea is on the right track, let me have a play around with this technique, I wasn't actually considering several companies access the same database all at the same time and having locally understood Field Captions, however this would be good across multi-language companies?

I suppose I would need to identify the "Company" by which User is logged in?

Food for thought?

I'll try and pull an example together and submit it, S.

It sounds as though you are looking at a multi-language set-up. Even though the languages are SAPese, Cognosese, IFSese etc - its the same thing. I wrote a full blooded appllication years ago with complete multi-language capabilities, but need to remember how I set it up. Forms, Help messages, comments, fields - the whole lot. And the way I did it was very simple indeed - just can't remember !!! If I figure it out will let you know as you can then expand the concept to all objects, table fields, forms etc. Is this what you require?

simonwar said:

I suppose I would need to identify the "Company" by which User is logged in?

Click to expand...

You would not have to include the full simultaneous multicompany capabilty. Drop the company field from the table so there is just one entry for each "label" and allow the user's admin to set the displayed name.

Someting to think about carefully is how the user's admin will know which particular term they are renaming. Showing the actual field or control name on the form may not help them identify the field again since its actual name won't be showing on the form.

Perhaps the editing form should hide the real name of the "label" control but rather display the current name and allow the entry of the new name next to it. After the update the record is requeried the current name is updated.

I would also condsider a set to default capability on the edit form to revert a name back to your own standard term. This will help if the admin confuses the term while editing and enters the wrong name. Include a defaultname field in the table.

Using the Text Box controls, in its simplest form to start with, can you have a look at the attachment and help me with the expression, or what I need to call up the record P01.

Once its set, its set and the customisability comes from Admin modifying the Table tbl_InputPrompts.

I will consider developing this later, but at this stage only need Admin controls of the form, and not User or Multi-Company at this stage.

Thanks, S.

Typed a long message last night and when I hit SUbmit, found I was off-line and lost my whole message..

Any way - if I explain how I set up the multilanguage capability, you will find it pretty much is the way that GlaxiomAtHome describes it.

So (I hate redoing "lost" work):

I only had 2 forms in my entire application - a "single form, and a "continuous" form. Although the user sees many many "different" forms, they actually see the same one - it just gets re-created each time you open up the form. But you can do this having different forms anyway.

I created several "language" tables, one for pop-up messageboxes, one for messages, and one to contain the detail of the forms.

For each Object that will appear on a form it is stored in the form language table. The table has several fields all relating to the basic (but not all) properties that an object can have on a form e.g. x y coordinates, size, height, font, fontsize, colour, back-colour. i.e. a subset of the full properties that you see in design mode. The Primary Keys in this table are Language, FormToBeUsedIn, ObjectID.

You create a base standard language e.g. "000", and every object for evry form must have an entry in this base language. When you "open up" a form, the code restricts by language and FormToBeUsed, and then loops through all objects to be created and places and designs them according to the data in the table.

To create a variant "language" e.g. "SAP", take your table entries that represent the object on the form, copy them and append with new language ID from "000" to "SAP". Now change those values e.g. Change Label value from "Works Order" to "Customer Order".
WHen you "open up" the form with the new language, your code should look for all objects in the table of your language, AND if not found, use the value for "000". This way you only maintain differences between the languages and your base language, rather than another set of full languages. If users have access to this table, they can "manage" their own form design.

I also had a function called z_Comment. This effectively does the same as above except the table only had 2 primary keys - Language, Comment Text. This was used for messages and popup message boxes etc. e.g MsgBox("Please enter your " & z_Comment(OrderID01) & " in this box.") z_Comment would find the translate value for OderID01 and return the translate value back, so the message could read "Please enter you Works Order in this box", OR "Please enter your Customer Works Order in this box" - depending on your language choice.

And it works great!

Will check your attachment for you. Miserable weather here in Jo'burg today - normally bright sunshine all winter - we have had no rain nor cloud since end Feb (normal). Today is rainy (not normal), and end of month, so have to do finances else get shouted at by the wife.

Hi Simon - here is a language .mdb that does what you want, you can use this to modify anything on your forms, and the VBA is really simple.

Hope this is what you want - I really like it.

It's up to you how you set the language for the users.....

Its the business!
Exactly what I wanted.

A further thought >>>>

>> it would make sense to have a DEFAULT setup, e.g. LANGUAGE "999", which cannot be modified even by the administrator, so that if titling goes astray, the Original Defaults can be re-instigated immediately.

Q. How do you store data in a table that cannot be modified without some exceptional intervention, e.g. do I create a dummy replicated table with only the default settings and call that through some special VBA on hitting a Restore Defaults Button? Not that hot on VBA.

Appreciate you've been a great help, but any further pointers will be appreciated.

Thanks, S.

You could have a separate database with the language table in it with only "000" values, and put security restrictions on this. Then link this table into your current database. If you ever need to update your current "000" values, run an append and then update query from your linked language table to your current database language table for "000" values. Append first if values have got "lost", then secondly update to new values. Check up on security for this though - search these forums you'll find plenty on it, but this is the easiest way as you can secure the entire linked table database without worrying about multiple users and security. Only you will have access. Security is not a strong point of mine though.

2 things:

Just remember that we are playing around with a virtual form. You will not lose the original design in any way, just go into design view and you can check that everything is still the same regardless of changing language and resaving the form. So you will always have the original "000" values.

You can also write some code that does the following:

For each form that I have:
For each object on the form:
For each property of each object on each form:
Write the values to my language database as "000"....


Now you have some code that will generate the values in your language table for you. I suggest place some restrictions in your properties you want to have language values for, else you will get everything.....
So if someone messes up the values, you can regenerate them from the forms designs.