Auto population of one field based on entry of second field.

[zoobeeda]

OVERVIEW PDF

I have created a PDF that gives an updated overview of the issue as well as a summary of the developments in the thread. The overview currently includes all posts up to:　

page 3 post #1 [FONT=Arial,Arial Narrow][FONT=Arial,Arial Narrow](posted 07-03-2014, 02:45 PM)[/FONT]

[FONT=Arial,Arial Narrow][FONT=Arial,Arial Narrow]If this issue interests any reader they can now just read the overview PDF and then jump to the last post included in the overview to continue with the thread, thereby avoiding a lot of the redundancy and confusing complication. The PDF, though fairly concise, is still quite lengthy. I try and make it digestible for beginners. It does have the advantage of including images.[/FONT][/FONT]

[FONT=Arial,Arial Narrow][FONT=Arial,Arial Narrow]The PDF may be accessed at the end of this post.[/FONT][/FONT]

[FONT=Arial,Arial Narrow][FONT=Arial,Arial Narrow]The original first post begins after this point.[/FONT][/FONT][/FONT]
__________________________________________________

[FONT=Arial,Arial Narrow][FONT=Arial,Arial Narrow]THE ORIGINAL FIRST POST[/FONT][/FONT]




PROBLEM = How to auto populate one field based on entry of data into a second field.

MY EXPERIENCE LEVEL = intermediate beginner




BACKGROUND:

I like to rate records to facilitate accessing desired records. I’ve already included a rating function into my first Access database and I expect I will be including a rating function in many future databases I make—so I wouldn’t mind getting this right.

In my database I indicate rating using digits ranging from 0 to 10 (10 being the highest or most important and 0 being the lowest). I also supplement the numerical value with a visual representation in the form of a bar, similar to the idea of stars. I currently am placing the two variations, the numerical rating and the bar rating, into one field (cell). This rating field I have set up as a combo box with a drop down menu.


The rating pairs appear as follows:


10 O I I I I O I I I I O
09 O I I I I O I I I I
08 O I I I I O I I I
and so on down to . . .
02 O I I
01 O I
00 O




PROBLEM IN DETAIL:

I’ve been thinking that it might be better to separate these two variations of a rating value and put each into their own fields. I’m anticipating that when it comes to doing searches, in particular searches involving ranges of values, that doing a search on a field containing just a single numerical value will be easier.

I would also like to incorporate into this new set up of two rating fields, an auto populate feature. To facilitate the process of data entry I want to be able to enter a numerical value into the numerical ratings field whereby the bar ratings field will auto populate with the corresponding bar value. It is this auto populate feature that I will need help with.



In sum, the function I would like to set up is as follows:

field 1= numerical rating (eg. 0, 1, 2 etc)
field 2 = bar rating (eg. O, O I, O I I etc)
​

1. in field 1, when you enter a numerical value ranging from 0 to 10 . . .
2. field 2 will auto populate with a corresponding bar value
3. for example when you enter 7 into field 1, field 2 will auto populate with O I I I I O I I
​

· I would probably want field 1 to be a combo box with a drop down menu, even if typing a digit might be easier. I know how to set up combo boxes.
· I hope that the auto populate function can be set up at the table level, although if it can’t be set up at table level then form level will do.


​

I am guessing that implementing this auto populate function will require setting up a macro. Will this require having a bunch of “if” statements to specify that “if such a value is entered here, then this value is entered there”? Would I have a separate table or tables listing the paired values which the macro would read, compare and then auto populate accordingly?

I currently have little to no knowledge of VBA and SQL. If this auto populating maneuver I want to set up will require a macro I think I would like to construct as much of it as possible, if not all of it, using the expression builder. It seems that learning how to use the expression builder is a good early step.

On my end, responses with kindergarten simple and kindergarten clear explanations might be helpful, although I guess I can ask further questions if I don’t understand something.


Thank you to anyone who can help me with this.

 

[plog]

Let me see if I can summarize this in 1 sentence:

You want to create a string that corresponds to a rank.

If that's the case, you are way over thinking this thing. You don't store that string in the same table, you use a new table and just link the tables. This would be that table:

RankingValues
Rank, RankString
0, "O"
1, "O|"
2, "O||"
...
...
...
10, "O||||O||||O"

Then when you need that string you join your tables and pull it in.

 

[zoobeeda]

I don’t know what a string is. I looked string up and from what I can tell a string is like a concatenation (I’ve used concatenations in Excel):

You have something like:
“first name” “last name” (in one field/cell)
to pull actual first names and last names from some source.

If that is a string then I don’t think that will help in my situation unless it can do more than this.

My desired function considers two fields in one table or form: enter 3 in one field (cell) and O I I I automatically enters in the other field (cell) (which is the auto population).

If I’m not understanding the first reply then I need more clarification.

I’m thinking that maybe I could start another thread and this time eliminate chatter that might bury or confuse the main idea.

 

[GinaWhipp]

What Plog states is what you are saying. A table holds both values, when you select one, in Field 1, it auto-populates the corresponding data in Field 2.

Side note, not sure where you got your definition of String but this should clear it up...
http://www.functionx.com/access/Lesson19.htm

 

[plog]

First a string is a series of characters ("This is A String", "So is this", "Numerical Characters like 1,2,3 can be in strings", "Or even entirely strings:", "04510"). Someone's name would be a string, address, state, city, etc.

Sounds like your an Excel guy so let me put this in terms you can understand. You would have your main data on one tab (MainTab). One of those columns would be rank and you would put the numerical rank for everyone. Then you would have another tab (RankTab) with the data I described assigning rank numbers to rank strings (this would just have your 11 rows (0-10)). Back on the MainTab you would create a caclculated cell which does a Vlookup into RankTab to take that row's rank number and lookup the rank string.

That's essentially the method you use in Access, instead of tabs you use tables and instead of Vlookup you use a query.

 

[BlueIshDan]

I understand, I'll write your function for you

 

[BlueIshDan]

This is what it would be with a combo box and a text box.

Private Function GetStringRating(ByVal numeric_rating As Integer) As String

    Dim i As Variant
    
    GetStringRating = "O"
    
    For i = 1 To numeric_rating
    
        GetStringRating = GetStringRating & "|"
        If i Mod 5 = 0 Then: GetStringRating = GetStringRating & "O"
        
    Next
    
End Function

Private Sub cboRating_Change()
    If Len(cboRating.Value & vbNullString) > 0 Then: txtRating.Value = GetStringRating(cboRating.Value)
End Sub

 

[BlueIshDan]

Generate this string for display using the rating values that are stored in your table.

Take the advice given from the above posts and do not store this generated string in your table.

A string is an array(list) of characters(single characters "A","a","S","s"). Stored into a single datatype as a single value.

 

[GinaWhipp]

BlueIshDan,

Not sure what you are trying to say? I wasn't suggesting to store the end results in table but doing as Plog suggested and use a *Look-up Table*.

And as for the *String* definition that was not for me but for the OP.

 

[BlueIshDan]

Sorry, but what is the point of storing the data at all? Wouldn't it be considered storing calculat(ed,able) data?

I believe keeping it at the form level would be best. IMHO

 

[BlueIshDan]

Also, sorry I misread your post about the tables

 

[GinaWhipp]

BlueIshDan,

Hmm, so you're asking what's the point of Look-Up Tables? Well, suppose you want to change the *string* to include another character? Or just change it for the sake of change because someboday changes their mind about what it should look like. Your way the OP would have to come back and get someone to change the code, my way they just need to open the table and make the change.

Re: Tables - No problem that's why I asked for clarification.

 

[zoobeeda]

I tried doing what plog originally suggested which is to create a new table containing fields, one for numerical rank and the other for string rank (or bar rank), populate the fields accordingly and then link that table to the main table. However, when I did this no auto-populationality is happening.


This is what I currently have:

table 1 (main table) = contains many fields, three of which are titled: ID1parent, numerical rank, and bar rank

table 2 (supplementary table) = contains 3 fields: numerical rank, bar rank, ID1child



table 2 contains the full list of ranking pairs:

numerical rank field = 0 to 10 (plus a blank, and one cell containing the word uncertain and another cell with the word inapplicable)

bar rank field = O to O I I I I O I I I I O (plus a blank, and one cell containing the word uncertain and another cell with the word inapplicable)


field names
numerical rank, bar rank, ID
data
0, O, 1
1, O I, 2
2, O I I, 3
…
inapplicable, inapplicable, 14



Because I have a blank and the words uncertain and inapplicable, it seems that I am better off with a third field (column) which contains the ID number (with sequential numbers) and which is assigned with the Primary Key. Otherwise the order of the records tend to not want to stay in correct order.

So I linked Table 1 and Table 2 in the Relationships window where the fields ID1parent is linked to ID1child with the stick thing.

I’ll also mention that in Table 1 the numerical rank field is set up with a combo box that links with Table 2 for the values in its drop down list. I also had the bar rank field with a combo box, but then I deleted the combo box feature in case that was causing problems.



the problem

When I enter a number into the numerical rank field of Table 1, the bar rank field in Table 1 does not auto-populate with the corresponding bar value. I also try all the following and still no autopopulation: I select out of the field first, I press Refresh, and I close and reopen the Access document. No autopopulation seems to be happening.

What I’m doing wrong?

If my description of what I have done in Access is unclear, I could put together a PDF file describing the situation and complement it with snapshots (images generally make the understanding of this kind of stuff easier).



additional:

I got the impression with what plog and GinaWhipp wrote that I don’t need to write any code, just proper linking of properly set up tables. BlueIshDan has suggested some code writing. For the time being I will see if I can get this auto-population right without code writing (I’ve done almost no code writing and the code writing will be a bit of a challenge). I will wait for what people have to say because again, I really don’t know what I’m doing.



the term “string”

string (strng)
n.
6.Computer Science A set of consecutive characters.
The American Heritage® Dictionary of the English Language, Fourth Edition copyright ©2000 by Houghton Mifflin Company. Updated in 2009. Published by Houghton Mifflin Company. All rights reserved.
thefreedictionary.com/string

string (strɪŋ)
n
14.(Computer Science) a group of characters that can be treated as a unit by a computer program
Collins English Dictionary – Complete and Unabridged © HarperCollins Publishers 1991, 1994, 1998, 2000, 2003
thefreedictionary.com/string

char·ac·ter (krk-tr)
n.
12. Computer Science
a. One of a set of symbols, such as letters or numbers, that are arranged to express information.
b. The numerical code representing such a character.
The American Heritage® Dictionary of the English Language, Fourth Edition copyright ©2000 by Houghton Mifflin Company. Updated in 2009. Published by Houghton Mifflin Company. All rights reserved.
thefreedictionary.com/character

character (ˈkærɪktə)
n
13.(Computer Science) computing any letter, numeral, etc, which is a unit of information and can be represented uniquely by a binary pattern
Collins English Dictionary – Complete and Unabridged © HarperCollins Publishers 1991, 1994, 1998, 2000, 2003
thefreedictionary.com/character




Considering these definitions and what plog has written . . .

the word “string”, as used in computer science, refers to:

a group of characters (in a sequence) that can be treated as a unit by a computer program. This apparently can include any arrangement of letters, numbers and symbols that can be represented uniquely by a binary pattern. This would include words and sentences.

I assume spaces are eligible for strings (one or any number of spaces), as well as characters such as follows:

~`!@#$%^&*()_+={}[]\|;:’ “,./<>?

Dingbats and wingdings may also be eligible, but I imagine the rarer characters/symbols would be rarely if ever be used in strings. Eligibility for “stringdom” would probably depend on the degree of the symbol’s universality on computers.

Nothing says whether a string has to be included in the ASCII convention (which is a standard for the mapping of characters to binary representation).

Strings could be any size, I suppose, entire paragraphs, essays or books could be strings, but generally I imagine strings are not that long and are between 1 and maybe 20 characters. I don’t know if the absence of any characters could be a string.


examples of strings (where strings are bounded by square brackets):

[a], [m], [drop], [skfkioku], [5], [903], [ui392MSel8f], [a jd], [1 98-uqo], [name], [ageless-agent], [finish this today], [&$ y23-:Qoi], [iiiii o_151515!??]



I believe an important feature of the treatment of strings is the way that a string is demarcated from surrounding data. In equations and formulas that include special identification strings, demarcation of the ID string generally involves conventions using characters placed before and after the special string to demarcate the string from the equation or formula. Characters that often are used to demarcate a string I believe are round and square brackets ( ) [ ] and quotation marks “ ”. For this reason these demarcation symbols would probably less likely be used within an identification string itself.

My guess is that the word “string” might also be used to differentiate exclusively numerical strings from strings that are not exlusively numerical, so that in the following example of three strings bounded by square brackets:

[29387], [ag74gDoppler], [perennial]

. . . one might refer to the first as a number and the second and third simply as strings, even though in the broad sense all three would be considered strings.


Gina Whipp suggested going to the following site for a definition of String:

functionx.com/access/Lesson19.htm

This may actually may not be the best idea. Although that web page does open up with a definition of string:

A string is text made of a readable letter, a symbol, a word or a group of words.

(where there is no explicit mention of numbers)

. . . after the definition there is a large body of text and images that explains various characteristics of strings with regards to Access and the kinds of things you do with strings in Access (which could go on endlessly)—but nothing that really adds to clarify the definition of the word string. It was my glancing briefly at this lengthy narrative that I wrongly equated strings with concatenations.

 

[GinaWhipp]

To auto populate an adjacent Control and depending on which Column it's in in your Combo Box, you could use something like...

Me.YourTextBox = Me.YourComboBox.Column(1)

...where 1 is actually the second column because the numbering of Columns starts at zero(0).

As a side note, there is no need to store the value in Table 1 as it will always be available via your Relationship. When you want to see it just include Table 2 in the query.

As for the two suggested ways to do it... In Access there is always more than one way to do anything and everyone has a preferred way. To of us would use a Table and one of us would write code. Doesn't make any way better, just different, choose the one that works best for you.

And finally, *the definition of string*, each language applies it's own *definition*. Don't get hung up on it. Just realize that using some terms in this Forum are likely to be misunderstood because we think Access' definition of *string* and not Computer Sciences definition of *string*.

 

[zoobeeda]

Right now I think getting the autopopulation feature to work in the simplest way will be best for me, especially since I am a beginner and know very little about code.


Can we say that simply making a connection, in the Relationships window, between:

1 my main table and
2 my supplementary table (containing the list of paired values to be plugged into the main table)
3 by linking the ID fields of the two tables
​

. . . is not quite enough to enable autopopulation in the way that I want in the main table?



So if simply connecting the tables is not enough to enable autopopulation, I assume some kind of code (or macro) or something is going to be required. GinaWhipp has suggested using the following code:

Me.YourTextBox = Me.YourComboBox.Column(1)
​

(where 1 is actually the second column presumably containing the corresponding paired value)



I’m not altogether sure what to do with this code. Where do I put it? (and please, no mention of body parts) Will I be inserting this code at table level or at form level? Which row in a properties pane do I go to? Do I select a build button to open up an editor? Do I simply go to: Database Tools (menu) > Visual Basic (button) and enter code into the visual basic editor that opens? Don’t I also have to specify table names as well as field names in a code?

If I use a properties pane which field do I select when adding the code: the numerical rank field or the bar rank field (the bar rank field is the one that’s supposed to autopopulate)?



Is the Me. in the code a personal pronoun . . .

Me.YourTextBox = Me.YourComboBox.Column(1)
​

. . . or is it a function? What does it do?



I’m not sure how to demarcate the strings that identify fields. This may require square brackets, and sometimes square brackets are within round brackets, and in some cases the notation used goes and follows an entirely different pattern. I recall the use of exclamation marks. I guess I could look at other examples of code.



I half expect that people who are annoyed with newbies are pulling their hair out as they read these posts, and probably are by this point developing full blown trichotillomania. I assume Access World Forums is a place that allows beginners to get help and learn.



I hope I can do this autopopulation function without having to read an entire book on VBA. I may get around to studying more about VBA and SQL at a later point, but for the time being I don’t have a lot of time (I’m spending a lot of my time writing these posts, which is actually draining; I really am stamina challenged).


So . . . once again, thank you for any help!

 

[plog]

First and foremost, you will not be storing the ranking string ('O||', 'O||||O') in your main table (MainTable). That string will only exist in the table I suggested you build (RankTable).

Now, if you want that string to appear in a query, you would link your two tables (RankTable and MainTable) via the appropriate columns and bring down all the fields you want.

If you want that string to appear in a report, you would build a query like I just mentioned and use that query as the reports source.

If you want the string to appear in a form, and change whenever the ranking value changes its a little more difficult, but not beyond you. First you would use MainTable as that forms data source. Then to get that string onto the form and automatically update when the rank value changes, you would put an unbound control on the form whose control source is a DLookup (http://www.techonthenet.com/access/functions/domain/dlookup.php) that uses the rank value to pull the appropriate rank string from RankTable.

Again, the rank string will not go into MainTable, it will only appear in Queries, Reports and forms using the methods I described above. If you need any more help, be specific in where you want the rank string to appear (Query, Report or Form) and what you are trying to do to get it there.

 

[GinaWhipp]

zoobeeda

I see plog has answered the gist of your question. However, I just want to add...

The Me. referred to in my line was not a personal reference, it was a reference to the Form. That line would go in an UNBOUND Text Box next to your Combo Box.

 

[zoobeeda]

brief summary

goal: to create an autopopulation feature in an Access table, where entry of a value in one field will autopopulate a second field

recommendations so far:

· so far the main recommendation has been to have the list of possible values for the two fields (involved in the autopopulation) be put into a separate table (RankTable)
· in the last post by plog, plog says that the rank string will not go into the MainTable, but will only appear in a query, report or form.


next step:

Since I will be entering and managing data for my database primarily in a form, then setting up the autopopulation in a form is what I want to do (I want the rank string to appear in a form).

plog stated:

If you want the string to appear in a form, and change whenever the ranking value changes its a little more difficult, but not beyond you. First you would use MainTable as that forms data source. Then to get that string onto the form and automatically update when the rank value changes, you would put an unbound control on the form whose control source is a DLookup (techonthenet.com/access/f...in/dlookup.php) that uses the rank value to pull the appropriate rank string from RankTable.
​

I assume then that because I will be autopopulating with a string that I would use the following syntax (from the DLookup site):

Syntax for strings: (note the single apostrophe before and after the string value)

DLookup("FieldName" , "TableName" , "Criteria= 'string'")
​

list of items I will be dealing with:

MainTable, RankTable and RankForm

· MainTable (contains) > NumberRank, BarRank (fields)(these fields will contain rank values that will vary depending on the record)
· RankTable (contains) > NumberRank, BarRank (fields)(these fields contain the list of possible paired values)
· RankForm (contains) > NumberRank, BarRank (text boxes)(these text boxes will contain rank values that will vary depending on the record; BarRank will autopopulate when a numerical rank is entered into NumberRank)


creating the lookup:

I would begin by setting the data source of RankForm as RankTable.

you would put an unbound control on the form whose control source is a DLookup
​

Although I thought I knew what bound and unbound mean, now I’m not so sure after looking up some explanations online. Anyways, it seems that you can have an unbound element that can get populated with coding and referencing but that will not necessarily make changes to the main table. Apparently if you want to save the data to the main table you have to include an extra procedure that will save to the table (eg. a Save button?). There doesn’t seem to be much point in doing an autopopulation maneuver in a form if the data isn’t saved to the main table.

Despite all this confusion . . . it seems that on the form titled RankForm, the BarRank text box is what I will attach the DLookup to. I’m assuming that this equation will be inserted at:

Property Sheet > Data (tab) > Control Source (where you have an arrow and an ellipsis for further entry)
​

The equation then will begin like something as follows:

DLookup("BarRank" , "RankTable" , "Criteria= 'string'")
​

I’m not really sure what to do with Criteria=‘string’. I’m guessing that this part has to make some reference to the numerical value that was just newly entered in NumberRank of RankForm so that the appropriate corresponding value can be identified and drawn from BarRank in RankTable and then entered into BarRank in RankForm.

 

[GinaWhipp]

Have a look at...

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

Specifically, at the bottom of the page *Normalizing Data* page 6 which talks about Combo Boxes. Hoping it will help clear things up.

 

[zoobeeda]

goal:


· ([BarRank], RankForm) is to autopopulate depending on what is entered in ([NumberRank], BarRank)
· using the list of data in RankTable

TABLE > FIELDS​

MainTable > NumberRank, BarRank
RankTable > NumberRank, BarRank
RankForm > NumberRank, BarRank​

In the Control Source row of the autopopulating text box ([BarRank], RankForm) I tried this formula and got “invalid syntax”:


DLookUP(“[BarRank]”, “RankTable”, “RankTable![NumberRank] = RankForm![NumberRank]


I have no idea how to correct this. Am I on the right track? Would I have to involve ID numbers? Is there a problem with including spaces, or including spaces in certain locations? Is there other syntax I need to include?




syntax:


The syntax I’m using here I’ve sort of piecemealed from some of the sites I’ve been recommended to see. So I’m not doing this with any kind of certainty.



the recommendation to see the Crystal tutorial:


I looked at the free tutorial by Crystal regarding Combo Boxes as suggested by Gina Whipp. I couldn’t make much sense of it. I spent some time running around the internet like a chicken without a head trying to get some understanding of VBA and SQL syntax.

The Crystal tutorial on Combo Boxes seems to be using SQL (from my limited understanding) and Crystal is using RowSource and not Control Source as has been previously suggested.

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname
​

I can’t even find a row titled RowSource in the Property Sheet.