Auto population of one field based on entry of second field. (1 Viewer)

[GinaWhipp]

Syntax...

This will very depending on whether your field is TEXT or NUMERIC...

If your field is numeric...

=DLookup("FieldFromTableOrQuery", "YourTableOrQuery", "[FieldFromTableOrQuery]=" & Me![FieldFromForm])

If your field is text…

=DLookup("FieldFromTableOrQuery", "YourTable", "[FieldFromTableOrQuery]='" & Me![FieldFromForm] & "'")

OR
If a Combo Box…

=[ComboNameHere].[Column](PutColumnNumberHere)

…and remember Combo Boxes start with 0 not 1.

Combo Box...

I think we talking apples and oranges. WHAT are you trying to auto-popluate, the Combo Box or the field adjenct to it? For example...

Combo Box Here - Autopopulate Related Information Here

Row Source vs. Control Source...

Those two are two different things. The Row Source is where the Data is coming from. The Control Source is the Field Name in the Table or Query OR it can also be an expression. Both are located on the Data tab of the Property Sheet. If you do not see them... have you selected the Control? When you first go into the Design Mode of the Form unless you click the Control you will not see those properties in the Property SHeet, instead you see the Properties of the Form.

Based on what you said about not understanding Crystal's tutorial I'm going to suggest you start from Chapter one, so you can get a better understanding of Access because I'm thinking it will help, feel free to ignore.

 

[zoobeeda]

At least it looks like I may be getting closer.



Let’s see if I can make this clear and comprehensible:

Three items are involved in the desired maneuver of autopopulation: 2 tables and 1 form.

> MainTable has the core data and fields
> RankTable has a list of values that will be involved in the autopopulation (one is the triggering value and the other is the autopopulation value)
> RankForm is where I will actually be entering a value into a text box, and where in a second text box autopopulation will occur as a direct result
​

(MainTable is not given much consideration in the following narrative regarding autopopulation)
​

Here is a list of the items and the names of the fields (text boxes) that are involved in the autopopulation.

TABLE > FIELDS
MainTable > NumberRank, BarRank
RankTable > NumberRank, BarRank
RankForm > NumberRank, BarRank (it might be more correct to say that these fields are text boxes or some sort of control boxes)
​

I will be entering values into RankForm but the values are drawn from RankTable.

RankTable has three columns of values, but only two are directly involved in the autopopulation.


I am going to try and make a table to describe the data in RankTable:

(period strings represent divisions between cells of this table improvisation)

HEADINGS…NumberRank……..BarRank………………Order

DATA……………..10……….…O I I I I O I I I I O…………..1
………………….….9…………..O I I I I O I I I I……………..2
………………….….8…………..O I I I I O I I I…………….. 3
………………….….7…………..O I I I I O I I..…………….. 4
………………….….6…………..O I I I I O I………………....5
………………….….5…………..O I I I I O…………………...6
………………….….4…………..O I I I I……………………...7
………………….….3…………..O I I I………………….........8
………………….….2…………..O I I..………………….........9
………………….….1…………..O I..………..…………........10
……..………..…....0 ………….O…………………………….11
……………………___ ……….___………………….....……12
………………uncertain…...….___ ………....………………13
………………inapplicable……___……………….....………14

​

(a series of underscores represents a blank cell: [ ___ ] )



We have three fields (columns) of data here with different data types:

NumberRank = alphanumeric (either number or text)
BarRank = alphabetic
Order = numeric

The reason for having the Order field (which is set as Primary Key) is to keep the data in the table in the correct order. Without it the records don’t stay in the order that I want. When you make combo boxes the order of records can be important.



In RankForm there are two text boxes that are involved in the autopopulation:

NumberRank and BarRank


​

the combo box with drop down menu

The first of the two text boxes titled NumberRank I've converted into a combo box. This combo box draws data from RankTable. What you see in the drop down menu when you open it is the following list of values:

10
9
8
7
6
5
4
3
2
1

uncertain
inapplicable

​

So if you select the value 7 from this drop down menu, then after you commit the change (I'm assuming not before) the adjacent text box titled BarRank should autopopulate with the corresponding BarRank value O I I I I O I I.

Then on the form you will have something like:

NumberRank 7 ................. BarRank O I I I I O I I
​

Where each value is in a separate text box. I will likely omit the second text label BarRank.




the autopopulation function:

The autopopulation function I believe is to be linked to the second text box on the form titled BarRank, which is where the autopopulation is to occur.


The value that I want to be entered into BarRank in RankForm by autopopulation is text (eg. O I I I I O I I). Therefore the formula that would be used I assume is the formula that GinaWhipp suggested for text string entry:

=DLookup("FieldFromTableOrQuery", "YourTable", "[FieldFromTableOrQuery]=' " & Me![FieldFromForm] & " ' ")
​

Translating this into the terms of my database:

After selecting BarRank in RankForm I then go to Control Source of Property Sheet. I enter this formula:
​

=DLookup("BarRank", "RankTable", "RankTable![NumberRank]=' " & Me!RankForm![NumberRank] & " ' ")
​

(where Bar is text and Number is either number or text)


For reference, once again, here is the list of items and fields (or text boxes):

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



Is this formula looking the way it should?


what is Me!?

GinaWhipp tried explaining to me once before what Me is (as used in the formula)—Gina said Me is a reference to a form. Is Me! what I actually write out as is in this formula? Is it an abbreviation? What does it stand for? In the formula I have written should I leave the word Me itself or Me! out? Is Me!RankForm! a redundancy?

I’m assuming the ampersands [&] are to be written as is, as all the quotation marks and apostrophes.




Rewording the formula process (this is kind of extra (or over the top), just to demonstrate that I understand or don't understand the process):


BarRank in RankForm IS TO draw data from BarRank in RankTable, WHERE THE RECORD IN RankTable THAT IS TO BE CONSIDERED WILL DEPEND ON THE value in NumberRank in that same record of RankTable as being the same as the value of NumberRank in RankForm (this should cause an autopopulation of BarRank in RankForm if NumberRank in the same form sees any change in value)

 

[GinaWhipp]

For you Control to show the Bar Rank after a value has been supplied to the Combo Box, enter...

=DLookup("BarRank", "RankTable", "[NumberRank]=' " & Me![NumberRank] & " ' ")
​

No where did I indicate in my examples to enter the actual Form or Table names. Please see...

http://msdn.microsoft.com/en-us/library/office/aa172176(v=office.11).aspx

...for explanation of how the DLookup() works. Note, while it says for Access 2003 has not changed so information still valid.

For further explanation of Me. vs. Me! see...

http://www.access-programmers.co.uk/forums/showthread.php?t=202806

 

[zoobeeda]

So this is the formula that will make the autopopulation work?

=DLookup("BarRank", "RankTable", "[NumberRank]=' " & Me![NumberRank] & " ' ")

One thing I don’t understand here is why in two cases in the formula, field names are referenced but not the broad source name such as table or form.

[NumberRank] > is it because of the immediately preceding "RankTable", that Access will know to go to RankTable?
[NumberRank] > how will Access know to go to RankForm to use that particular Form if RankForm isn’t even mentioned in the formula?

Would this excerpt explain this:

Use Me! (bang) when you want to refer to fields in the form's recordsource.
​

This Me. versus Me! business seems quite a humdinger.

 

[GinaWhipp]

Yes, that formula, as long as we are dealing with TEXT will work.

As for the DLookup, you answered the very question you asked. I also provided links that will provide a more detailed explanation. Not much more I can say that has not already been said or provided in the links.

 

[zoobeeda]

I tried the formula and although I didn’t get an “incorrect syntax” message this time, the autopopulation did not happen. Instead of getting an autopopulation the BarRank field just displays #Name? .

Here is the formula once again.

=DLookup("BarRank", "RankTable", "[NumberRank]=' " & Me![number rank] & " ' ")
​

I double checked if the reference names I used matched those of their sources.


I don’t know if this might be important here, but the last [number rank], which references a box in the form, is a combo box set to display only one value from its drop down list (which after the selection of this value the autopopulation is supposed to occur in the adjacent text box titled BarRank). The point is is that it is a combo box if that makes a difference.

 

[GinaWhipp]

What is the name of the Combo Box? Tyr changing it to cboNumberRank and see if that solves the problem,

 

[zoobeeda]

Adding cbo to the name referencing the form's combo box does not make a difference. The autopopulation still doesn't happen.

I may try creating a new Access database that is small, having primarily the elements involved in the autopopulation. It might be easier to pin point problems in a smaller and simpler database. If I get it to work I can then apply the process to the larger database.

 

[GinaWhipp]

Please post the Rwo Source of the Combo Box here...

 

[zoobeeda]

Please post the Rwo Source of the Combo Box here...
​

I was going to ask what a Rwo Source is. I even googled Rwo. It occurred to me that GinaWhipp meant Row Source. If GinaWhipp did mean Row Source then the Row Source of the Combo Box is:

Row Source = RankTable
​

I was wondering if it’s possible to create a post and submit it in a way that its order in the thread can be controlled, for example submit a post and have it appear at the beginning of the thread. The reason I ask this is that I was thinking of putting together a PDF document summarizing the issue I have with Access as well as any progress to date from submissions, and putting it at the beginning of the thread. I hope to make the PDF file fairly concise and clear; the addition of images would help to achieve this. I find that I’ve been rewriting descriptions of the problem numerous times and that having one description at the beginning of the thread that I could edit, rather than rewrite multiple times, might not be a bad idea. I could then just direct people to that PDF file at the beginning of the thread for an optimally clear description of the issue. I would indicate in the PDF file to which point in the thread it was current; for instance I might say that it is current to such and such a post number. This way readers could conveniently jump to the location in the thread where the material continues from the PDF. If they were so inclined they could still go and read the entire thread.

I would try to summarize all pertinent submissions and developments, if not a comprehensive overview, of all the material. My guess is that most people don't read more than the first few posts and that you probably loose potential contributors simply because they don’t have the time or inclination to go through oodles of sometimes confusing and sometimes redundant posts.

If I do find a solution to my Access problem I think it wouldn’t be a bad idea to have that summed up as well in this initial overview post. Having this overview post I think would make the thread more effective for people looking to solve similar problems—in particular it might make the task less intimidating for newbies. And as I’ve already said , it would save me from rewriting explanations in exhausting multiple little essays.

If there is no way of moving a new post to the beginning of a thread, I suppose I could just go and insert new material in the first post by selecting editing mode.

I may or may not actually make this opening-updating overview post. It's kind of ambitious and it may not seem worthwhile.

From what I understand there is an allowance of only 100 KB for PDF attachments, but 2.00 MB for xls and zip attachments. I’ve downloaded a free zip compressor but even at 2.00 MB I may have to do some extra compression for a document with multiple images (which I hope I will know how to do).

 

[GinaWhipp]

I make typos, I do not use Spell Check and have no plans to... sorry it caused you confusion.

1. How many columns does the Table RankTable have?
2. Is the first column NUMERIC or TEXT?

As for the sorting of threads... not that I have ever seen here. Answers or Threads marked as solved do not sort that post to the top or right under the origianl question. The solution to your problem, though is unique to you, is quite common. However, if you feel you want to post a documentary then you are free to do so.

 

[zoobeeda]

The reply to the questions in the last two posts by GinaWhipp are in the PDF file that is attached with this post.

I have also created a PDF giving an overview of the issue and a summary of the development in the thread (to a specified point in the thread) and attached it to the first post in this thread.

 

[GinaWhipp]

Try...
　

=DLookup("BarRank", "RankTable", "[NumberRank]=" & Me![cboNumberRank])

...because it looks like the first column in cboNumberRank is numeric.

 

[GinaWhipp]

Oops, forgot, if that still doesn't work, please upload the file.

 

[zoobeeda]

The last code didn't work either.


As per request I'm uploading the file. I assume that the file to upload is the database where the autopopulation is supposed to go.



This database is a small one reduced to primarily the autopopulating elements.

 

[GinaWhipp]

Looking now...

 

[GinaWhipp]

Here you go, working now...

There were three issues:
1. My fault, forgot you don't need *Me!* when placing the line as the Control Source only when using in the VBE editor.
2. Naming of Control cboNumberRank, while you named it in the line you did not actually name the Control, so it could not find a Control named cboNumberRank.
3. BarRank is the Field Name in the Table and the line, it cannot also be the name of the Control on the Form or Access becomes confused.

 

[zoobeeda]

I'm speechless!.

I've applied the new code to my main table and have the autopopulation working there as well. Wonderful! Thank you!

There is one more action I would like performed. It occurred to me that this really isn't necessary, but I guess it's nice to still have the choice of including it. I would like the new autopopulated value be saved into a field in MainTable. I already have a field titled BarRank in MainTable and so I would like the autopopulated field value be saved in that field.

I'm guessing this might require a macro, or maybe not. I briefly looked through the Macro Action Catalog and can't find anything that might be appropriate. Maybe there's some function I could add to the current code of the autopopulating control box to do the extra step. Once again, quite clueless.

 

[GinaWhipp]

Why? You only need to store the ID in the one table. The whole purpose of a normalized database is to not store data twice. If you insist on *breaking the norm* then instead of the autopopulation of the field the way you are doing it you would need to...

1. Bind txtBarRank to it's corresponding field in the Form (set the Control Source)
2. Move the DLookup() line to the After_Update event of cboNumberRank, i.e...

Me.txtBarRank =DLookUp("BarRank","RankTable","[NumberRank]='" & Me![cboNumberRank] & "'")

 

[zoobeeda]

I think I would like to know how to configure the autopopulation feature several ways so I have more flexibility on how I present and manage this feature. The bar rating (eg. O I I I I O I I I I O) I prefer for visual display and the number ranking (eg. 10) I want so that doing range searches are easier; I also thought that just typing a number in a combo box might be one fast alternative to using the drop down menu but it may not be worth it.

I haven’t tried the most recent code submitted but I intend to. I would like to get that working as well.

While working with the form to see how I might set things up another arrangement occurred to me. This last arrangement might be the one I will be most inclined to use. In this arrangement BarRank would be displayed in MainForm with a combo box. After entering a value into BarRank in MainForm, NumberRank in MainTable would automatically update with the numerical equivalent. Again, having the NumberRank field in MainTable would probably work better for searches than BarRank.

I remember plog saying that autopopulation does not get done in a table—it is done either in a form, query or report. I’m assuming that to do this third autopopulation configuration that I will require setting up a query with possibly a lookup function.