Full Version: Populating Control based on another Control
UtterAccess Forums > Microsoft® Access > Access Forms
Tsi2quick
Hello all!
want to apologize ahead of time if this has been covered (which I am sure it has) but I searched and navigated through the threads but couldn't locate anything similar to use. Here is my issue....
I have one data entry form. This form automatically pulls the machine name and places it in a "Entered By" field so we know who created it. I also have an "Assigned To" field that is currently a combo-box of all team members. I want this to be defaulted to equal the "Entered By" name. One catch, the "Entered By" name is a machine name (i.e. jxsmith) but the "Assigned To" lists full names (i.e. John Smith). I prefer to show the full name in Assigned To as this will be what appears on reports, etc. other wise I would just change the default value to be the same as Entered By.
I do have a table that houses both the machine name (i.e. jxsmith) and the full name (i.e. John Smith) so it's not an issue knowing which machine name = full name. I wrote a query that will do this but do not know how to put that into the picture to make the combo-box default to the correct full name instead of the machine name.
I know this is probably hard to understand and I apologize so feel free to ask as many questions as necessary. Thanks in advance for any help!
adam
hello
use dlookup to get the full name from you query
example
=DLookup("[full name field]", "your query name", "[Entered By] =" _
& Forms![Your form name]!Entered By Control)
Tsi2quick
Just enter that code into the default value attribute or as an event?
Tsi2quick
Hello??
Tsi2quick
Can anyone answer my question of where to put this logic? In the default value field or as an event?
niesz
Hello, Tsi2quick.
Let's see if we can't get this sorted out. May I make a suggestion? Why don't you base the RowSource of the ComboBox off of your query. The first column would be the machine name and the second column would be your Full User Name. Set the first column width to 0 and then you could set the default value to the machine name.
If that isn't acceptable, then we can work through getting the Full Name into the control.
Tsi2quick
That sounds like it could work to just list the name correctly, but I will need the ability to also change the name possibly to someone else on the team. Would this option allow me to do that since it's pulling from the query?
niesz
Only the RowSource comes from the query. If you select a different name, the underlying bound field will change to whomever you select. Try it.
Tsi2quick
Ahhhh, gotcha. Let me give that a try tomorrow and I'll post back the results! I'm giddy with excitement! (how nerdy was that??)
Tsi2quick
I can't get it to work. When I tried using my original query which was setup to pull Full Name it only pulled one name, not the entire list. I went back and took out all criteria from my query (so it pulls all records now) and used that as my Rowsource and it of course works just fine but I can't get the Default Value to work correctly. I can put = [Entered By] into the Default Value and it pulls the machine name, not the full name. How do I get it to pull Full Name there?
I'm screwing up something with the query I think or the Default Value, all of the above?? haha
Tsi2quick
Anyone?
Tsi2quick
Can anyone help me figure out the answer to this??? It's very much appreciated.
niesz
Sorry, I didn't get notified that you had responded. Maybe you could post a copy of your DB so that we could see what's going on under the hood. Without knowing a little more about the structure, I don't think I can answer the question.
Tsi2quick
I am attaching a copy of a test database that I just threw together that basically has the issues on the form. I tried to explain exactly what I am looking for in a text box on the form in case others would like to give it a shot too. Thanks for the assistance.
niesz
I'm at work now, so I only have Access 2000. I'll have to look at this when I get home. Or, if you want, to save it as 2000 and repost....
Someone else may have 2002 or better and be able to help sooner.
Tsi2quick
I've attached a Access 2000 version too. Thanks for the help!
niesz
I've made a few additions to your DB and have attached it.
think this should get you on your way...
Pay particular attention to the ROWSOURCE for the combobox.
Tsi2quick
Thanks for the reply. I understand the example on the bottom, thanks for that! Exactly what I was looking for.
The example at the top though, with the combobox, I do not understand. On the database we use currently, the Login ID field will be populated automatically from the machine name. From that I want the Employee_Name field to "read" the Login ID that is already populated and insert the corresponding name in that field automatically. From reading the logic it didnt appear that the login ID field was being referenced, at least in the ROWSOURCE. I did see that the control was set by clicking the button. I know I have done a bad job of explaining what I need but this is progress for sure from where I was earlier. Thanks for the help though.
niesz
If you notice the code, I am populating BOTH controls with the same string. So whatever method you use to populate the machine name, you can do the same thing for the combobox. With the RowSource set the way it is, you will see the Employee_ID in the combobox by setting it to the machine name.
Tsi2quick
Well, good news! I got the logic to work using the command button. It turns out I needed to change the Bound Column to 1 from 2. However, now what I would like to do is to have it done automatically without having to use the command button. Since the value is filled in as soon as the form opens for the Login ID due to the default value being set on that field, I'm thinking I could add something to the on Open or After_Update logic to get this accomplished. What are suggestions for doing this?
h, sorry it has taken me so long to get back but with the holidays it has been hectic to say the least! Thanks again!
Tsi2quick
Ok, nevermind on the question above, I figured that one out. I just added the logic to populate the combo-box with the [Login ID] data on form Open and then when the New Record button is pressed by the user. That does leave one possible way for that field to be left blank or not automatically populated I should say. If the user uses the mouse wheel to scroll down they will get a new record and that record will not be automatically populated. Anyone know how to disable the mousewheel or somehow put some logic in place to where it's populated then too?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.