Help On Form Converter, and a go at Mockers

[HowardHelp]

I must start by saying yes I’m a newbie not experienced unlike die hard access experts.
I joined this forum because it sounded like newbies were welcome, but a minority of experts seem to revel in comments like practice makes perfect etc. and I should know what I'm doing so to them please go and play on Facebook or Twitter if you can’t offer help or maybe you like mocking others with less knowledge the yourselves.
I hope the administrator reads this, sad but there you go.

If you're interested in helping then please read on.

Is it possible to make a simple SQL / code that can convert a known number into text because the way the original database was designed it will show a number instead of text in the form, the list is short so it wouldn’t involve hundreds of variables, maximum 10 an example below.

0 if, replace to = NotEntered

111 if, replace to = Cat

112 if, replace to = Dog

113 if, replace to = Crocodile

The above isn’t the exact names required but I hope you get the idea.
Any help if you want to help would really be appreciated, or a simple NO if it can’t be done.

 

[Isaac]

Yes, look at the Replace() function on that

 

[Pat Hartman]

Create a table to hold the conversion. It needs two columns, the Numeric ID and the text value.

Use this table as the RowSource for a combo. Let the wizard build the combo for you.

Using the Replace() function will work but it requires hard coding the values and programming changes to add any new values. Using a table allows the users to maintain the cross reference if you build them a form to do so. It also allows you to enforce Referential Integrity.

 

[Isaac]

And I completely agree with Pat, if and when you're able to take the extra time to set it up - and if the longevity of the need warrants.

 

[Uncle Gizmo]

I must start by saying yes I’m a newbie not experienced unlike die hard access experts.
I joined this forum because it sounded like newbies were welcome, but a minority of experts seem to revel in comments like practice makes perfect etc. and I should know what I'm doing so to them please go and play on Facebook or Twitter if you can’t offer help or maybe you like mocking others with less knowledge the yourselves.
I hope the administrator reads this, sad but there you go.

I've looked back through all your posts and I can see no indication of the ill-treatment you seem to think you have been subjected to. Please could you explain further.

Posted by a Forum moderator.

 

[MajP]

way the original database was designed it will show a number instead of text in the form,

Any chance that is an improperly formatted combobox that is showing the wrong column? Or they just forgot the bring in the text field from the related table? Something sounds wrong with the original form. What does the number represent? Can you describe the form and and that field? I do not think you need a table because you may already have that table.

 

[HowardHelp]

Create a table to hold the conversion. It needs two columns, the Numeric ID and the text value.

Use this table as the RowSource for a combo. Let the wizard build the combo for you.

Using the Replace() function will work but it requires hard coding the values and programming changes to add any new values. Using a table allows the users to maintain the cross reference if you build them a form to do so. It also allows you to enforce Referential Integrity.

Hi can the table be linked to the form field, thanks for the sugestion

 

[HowardHelp]

I've looked back through all your posts and I can see no indication of the ill-treatment you seem to think you have been subjected to. Please could you explain further.

Posted by a Forum moderator.

Ill treatment is a bit strong, just snide remarks about my lack of knowledge of access, all I wanted was some help, why post unnecessary remarks that has no bearing on the subject.

 

[Uncle Gizmo]

Ill treatment is a bit strong, just snide remarks about my lack of knowledge of access, all I wanted was some help, why post unnecessary remarks that has no bearing on the subject.

I gather from your comment that you don't need any moderator help.

I will leave it to you to sort it out with the person you had a problem with.

In my experience these sort of incidents are generally misunderstandings.

I suggest you discuss it with the person. Talking, to resolve a misunderstanding often develops into a friendship.

 

[arnelgp]

the original database was designed it will show a number

on Design view of the form, that has this table as Record Source, click on the
Control that has (the field you are referring) its Control Source.
is the control a combobox?
check the Row Source and SQL and which table does it get its value.

now, the previous programmer is correct if that is numeric.
if you make it a "string", it will violate the Normalization rule.
becoz that "string" can be found on another table.
you need to create a Query linked to the "other" table to
show the "text" equivalent of the "numbers".

 

[The_Doc_Man]

Hi can the table be linked to the form field, thanks for the sugestion

If this table of numbers and names exists, you can make it "drive" combo box. Using the Combo Box wizard there is an option to make a table become the source of what you store (and separately, what you see) in the combo box. So you would have the default value of the combo box be 0 (which also corresponds to "not entered"), but you could have the box SHOW the name while working with the code number. At least, that is what it sounds like you MIGHT want to do.

You can also do a JOIN operation for subsequent reporting, in which you have that number in table A but the translation table as table B, then do a JOIN of the two tables on that numeric field (that has to be in both tables for the JOIN to work.)

 

[Pat Hartman]

Hi can the table be linked to the form field, thanks for the sugestion

Please read my entire response in #3. I anticipated this question. Others have elaborated. Please let us know if you need more help implementing the suggestions. The combo wizard is very helpful. I'm pretty sure that all the wizards are on by default but if you're not getting a wizard to build the combo, I'll try to figure out how to turn them back on.

 

[HowardHelp]

on Design view of the form, that has this table as Record Source, click on the
Control that has (the field you are referring) its Control Source.
is the control a combobox?
check the Row Source and SQL and which table does it get its value.

now, the previous programmer is correct if that is numeric.
if you make it a "string", it will violate the Normalization rule.
becoz that "string" can be found on another table.
you need to create a Query linked to the "other" table to
show the "text" equivalent of the "numbers".

Many thanks for your help the form (field in question) is linked to the main table and not a combobox in the forms Control Source.
The main table I refer to and don’t want modified has a combobox which links to a smaller table which what is causing my problem as it has the text and numbers in, I don’t mind modifying the smaller table but I don’t think that will help that is why I was more interested having some kind of coding after the main table to bring the text back to the field in the form.

Sorry it's difficult explain what’s happing here

 

[Pat Hartman]

What you are trying to do is common. We do it every day. If you create the cross reference table and add a combo box instead of a text box to your form, The RowSource of the combo is the table with the cross reference. The numeric field is the bound columnan your picture shows. The AUID field of the table of the main form is the ControlSource of the combo. Your column widths property is wrong, It should be 0; 2. Setting the width of the first column to 0 will hid it because you want the user to pick by text. The second column is the text field. I set the width to 2. Make it shorter or wider as appropriate. The wizard would have created all these properties correctly as you answered the questions.

 

[HowardHelp]

What you are trying to do is common. We do it every day. If you create the cross reference table and add a combo box instead of a text box to your form, The RowSource of the combo is the table with the cross reference. The numeric field is the bound columnan your picture shows. The AUID field of the table of the main form is the ControlSource of the combo. Your column widths property is wrong, It should be 0; 2. Setting the width of the first column to 0 will hid it because you want the user to pick by text. The second column is the text field. I set the width to 2. Make it shorter or wider as appropriate. The wizard would have created all these properties correctly as you answered the questions.

Hi sorry for the delay in getting back but I was having a go at a Converter Query which works well but was quite involved so I only done a few conversions of numbers to text.

I shelved that temporary and continued down the combo route you suggested and was done in just a few minutes, I linked the combo to the table with the AUID field using the wizard and linked the form combo to my query which is linked to the main table, and it worked straight away, so many thanks for the help it was very much appreciated.

 

[Pat Hartman]

I'm glad it worked for you. Keeping a table is a much simpler process than using VBA to convert codes to text. I've attached a database that includes forms/reports/tables that will allow you to manage dozens of look up tables without having to create new tables and maintenance forms for each. The app was first implemented 40 years ago in COBOL and I built an Access version in the 90's when I first started using Access. It hasn't actually changed much over the years. Use it for any simple lookup table. For tables that have other than the standard columns I have accounted for, create separate processes.

I also included a PPT to outline how it works.

 

[HowardHelp]

I'm glad it worked for you. Keeping a table is a much simpler process than using VBA to convert codes to text. I've attached a database that includes forms/reports/tables that will allow you to manage dozens of look up tables without having to create new tables and maintenance forms for each. The app was first implemented 40 years ago in COBOL and I built an Access version in the 90's when I first started using Access. It hasn't actually changed much over the years. Use it for any simple lookup table. For tables that have other than the standard columns I have accounted for, create separate processes.

I also included a PPT to outline how it works.

Many thanks again the only problem I have just come up against is that the text is showing (as I wanted, the combo is situated in a sub form) but searching these fields still can only be done by the numeric code and not text, to me it's a strange why the main table did not just use text to start with (instead of the lookup table added to the field) I know I'm looking at it as a novice but that’s how I see it.

Again I appreciate your patience and help

 

[Isaac]

@HowardHelp I don't know if this is going to help or not but hopefully so. This whole thing about comboboxes and text vs. numbers that has been going round and round.

This is generally the way useful comboboxes are made:

1. make the cbo's Rowsource to be at least two columns from a table/query: a) the text you want the user to see/select from, b) the record ID #
2. make the cbo's ColumnCount be (at least) 2
3. make the columnwidths such that the ID column is hidden. So if your rowsource select firstname, ID from employees then your columnwidths property might be 2;0
4. in the AfterUpdate event of the cbo, "Do" something with the ID number value from the combobox. So the person nicely visually selects the name "John", but you as the developer--knowing that the ID# is the second column value (hidden), you code something by referencing Me.ComboboxName.Column(1) which is the second column of the cbo.

Does that help clear things up at all?

Of course you have to have the proper table structure to make any of this work. Everything is in tables with primary key / ID numbers.

 

[HowardHelp]

@HowardHelp I don't know if this is going to help or not but hopefully so. This whole thing about comboboxes and text vs. numbers that has been going round and round.

This is generally the way useful comboboxes are made:

1. make the cbo's Rowsource to be at least two columns from a table/query: a) the text you want the user to see/select from, b) the record ID #
2. make the cbo's ColumnCount be (at least) 2
3. make the columnwidths such that the ID column is hidden. So if your rowsource select firstname, ID from employees then your columnwidths property might be 2;0
4. in the AfterUpdate event of the cbo, "Do" something with the ID number value from the combobox. So the person nicely visually selects the name "John", but you as the developer--knowing that the ID# is the second column value (hidden), you code something by referencing Me.ComboboxName.Column(1) which is the second column of the cbo.

Does that help clear things up at all?

Of course you have to have the proper table structure to make any of this work. Everything is in tables with primary key / ID numbers.

OK thanks I’ll have a go see how I get on, ideally I just wanted a normal field and not a combo box in the subfield, but I’ve hidden the drop down menu with a blank box from the background and disarmed any editing.

 

[Pat Hartman]

You only need to use a combo if you want to update the field by choosing from the list. If you simply want to display the text value as you would in a report, use a query that joins the main table to the lookup table (use a left join if the lookup field is optional). Then select the text value from the lookup table and bind it to a textbox to display. Make sure to LOCK the text box because you don't want anyone to accidentally update the lookup value.