Full Version: If User Enters A Number In Field 1, Field 2 Automatically Contains "b"
UtterAccess Forums > Microsoft® Access > Access Forms
sdl2
I've got a userform which is a recipe builder. What I'm looking to do is if the user enters a number in a box called "field1" then "field2" is automatically populated with the letter B
as in

H
1 B
G
12 B
H
6 B


Thanks!
tina t
If IsNumeric(Me!MyControlName) = True Then
Me!MyOtherControlName = "B"
End If

but that's a bit incomplete. what's the Data Type of the field that the first form control is bound to? or is the control unbound? if the Data Type is Text, will the user enter letters in the form control sometimes? or only numbers? what do you want to happen if the user enters a letter instead of a number? or deletes the data in the control so that it's blank?

automating a form is usually more complex than you expect it to be. you have to do a lot of thinking, and ask/answer a lot of questions to yourself, otherwise you may end of leaving big holes in the code that the user and the data can fall through, and crash.

hth
tina
sdl2
Well let me add a little more information then haha. The data should be text, the user can enter H,G Home Offset H1,H2,H3,H4,H5 or any number. The idea is to have the user anter the recipe, each line seperatly into a field. This itself creates problems because this document is a router for a factory process. The unique fields already number 150 and i need to add an additional 250 fields for the recipe section. Recipes may be upto that long but often are not. I will give an example below

12 B
H
2 B
G
3 B
G
1 B
H1
13 B

The user enters the right column in box 1-9 and box 10-18 checks the box linked to it and sees if the text is a number only, then is a B if its a number and nothing if it is nothing or a non number. From this i need to be able to total the number of G, h, h1 etc, as well as the number count (31)
BruceM
If you mean you need several hundred fields for each record, it sounds as if you are trying to create a spreadsheet-like table. That is almost certainly not the correct approach.

On the other hand, if you are talking about several hundred records, where each of the lines in your sample is a separate record, then you are on track. In that case one point worth making is that the column that would contain "B" should most likely be calculated on the fly, with something like Tina's approach as a calculated field (column) in a query (using whatever field name you like):

ToBorNot: IIf IsNumeric([FirstField]),"B","")

If the 150 (or whatever) fields are steps in a manufacturing process, the main table would identify the process, and the individual steps would be in a related table.
ScottGem
QUOTE
This itself creates problems because this document is a router for a factory process. The unique fields already number 150 and i need to add an additional 250 fields for the recipe section.


As Bruce said, WRONG design, big time. Data tables should long and thin, not wide. You shouldn't have a field for each process, but a record. Each ingredient in your recipe should be a record tied to the product you are making in a parent table. If you give us a better idea of the fields you are trying to track, we can suggest a more correct structure.
dmhzx
I just wonder if these letters and numbers actually mean anything, or if the user can just enter any old rubbish.

On the assumption that there is a limit to what the user can enter, then surely there is some validation.
In which case why not present a single combo box, and in the after update event fill both current boxes, or fill one and blank out the other as appropriate.
ScottGem
The OP said:
QUOTE
the user can enter H,G Home Offset H1,H2,H3,H4,H5 or any number.


I was going to suggest using a combo but then I read about the, apparently, incorrect design so decided to deal with that first.
dmhzx
Fair do's ScottGem:

It was the 'any number' that I was really questioning.

And also wondered why the two fields were needed.

What would be wrong with a combo holding "H", "H1", "12 B", or even "G Home Offset H1", and putting them into one field: - Whether that field is half a mile to the right on a wide table, or half a mile down a long one.



Happy to leave the data design issues to the rest of you. hat_tip.gif
GroverParkGeorge
We have an excellent set of starter articles for newcomers that expand and explain the concept previous posters have introduced.

Fix your table design first, following the concepts in that reading list, then you can address form and control designs more effectively.

Good luck with your project.
sdl2
Hey,
Thanks for the replies, I think the issue I'm having a hard time dealing with is the following:
This is for a factory process, I have old data from approach that is 150 columns per record. The major problem is a field called "recipe" which is 1 field that contains an entry like this

12B <---2.4 mil
G
2B <----1.7 mil
H
2B <---1.7 mil
G
2B
H1
12B

Where not specified use 2.0 mil

This is fine but i can't do something like count the the number of G, H and H1, the number of B layers
The correct answers being
G=2
H=1
H1=1
B=30
2.4 =12
1.7 = 4
2.0 = 14.
I'm looking to provide a systematic check and to color the G lines green and the H lines red. So my only thought was to make 250 drop down boxes where any length (upto 250) recipe can be entered

Maybe there is a better way?
Please advise!
dmhzx
Sorry, I'm ducking out of this one. I cannot understand what is wanted, or what's going on at all.

You have one field in a single record that may have this in it?

12B <---2.4 mil
G
2B <----1.7 mil
H
2B <---1.7 mil
G
2B
H1
12B

I don't see how you can write a function to count things that there isn't some form of rule for.
You have to get some sort of grip on the data, and arrange it so that there are clear rules and structure.

You don't even have the same number of hyphens after the Less than sign.
BruceM
QUOTE
I'm looking to provide a systematic check and to color the G lines green and the H lines red. So my only thought was to make 250 drop down boxes where any length (upto 250) recipe can be entered

Maybe there is a better way?

Conditional formatting could take care of the coloring, but the structure you seem to be outlining is not workable. If you are going with wide records (many fields, few records) you may be better off with a spreadsheet. Making 250 combo boxes is not workable.
QUOTE
This is for a factory process

One process, or are there several different ones? If the latter, you need a table that describes just the process (building a washing machine or an engine or whatever it is). A related table lists the steps of the process.

Could you describe the table structure and relationships, and maybe describe something of the real-world situation. Make up an analogous situation if you do not wish to describe the real one.
ScottGem
QUOTE
I think the issue I'm having a hard time dealing with is the following:
This is for a factory process, I have old data from approach that is 150 columns per record.
...

Maybe there is a better way?
Please advise!


The reason you are having a hard time dealing with it is because you have badly designed data structures. Sounds like someone entered this data in a spreadsheet and you are trying replicate that spreadsheet in Access. BIG MISTAKE!

Yes there is a better way. Normalize your data properly. Use Access as it was designed as a relational database. You have a single field called recipe that contains badly formatted text data, this field should be parsed out into a child table with fields for each data point and a foreign key to relate it back to the product being made.

The way you build a relational database is you first determine each single piece of data that you need to capture. Down to the smallest data point. You then organize those pieces of data into logical groupings. The data points become your fields and the groupings your tables.

Sounds to me like you need a complete redesign.
GroverParkGeorge
While I agree with the preceding observations about proper table design, I want to confirm my assumption regarding the source of this data. Is it, in fact, currently or previously from a Lotus Approach database? This is partly curiosity and partly relevant to the way in which you will be able to resolve the design problem.

If the data is currently in Approach, then your strategy would be to properly design the Access tables, and then import the data from the old source. That's not going to be simple or easy, but doable. You'll start importing the data into temporary tables from which it can be pushed into the properly normalized Access tables.

However, it all hinges on solid analysis of the data and business rules which will be turned into normalized tables.

BTW, you can learn more about the process of Normalization in our newcomers' reading list.
GroverParkGeorge
One other thought just occurred to me while rereading the whole thread.

You refer to "recipes" for the process, and "router for a factory process". Bingo. You already have a well-defined process, I would imagine. You could not produce anything without some rules for doing so. That process is what you have to capture in this new relational database. Rather than trying to pick out one field or another in an existing table for attention, start with the over all process. Lay out a flow chart that traces the process from raw materials being dropped off at the loading dock and ending with finished goods being picked up for shipment. You'll find that as you build that flowchart, certain entities will be identified, e.g. "RawMaterials", "Vendors", and so on. Keep track of all of those. Many, or most, will become tables in your new relational database. The relationships between those entities (e.g. VENDORs provide RAW MATERIALs), will be revealed, in part, in that flowchart, and in part from your understanding of how the process works.

The more I think about this, the more I believe you've bit off a pretty good sized chunk of work. However, if you want a solution that will support your business well and efficiently, it's worth the effort to get it right.

Good luck.
ScottGem
I just want to add that I work for a manufacturing firm. We have a system that handles routings so I'm familiar with table design for such processes.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.