Field Dependent on Combo Box (1 Viewer)

[mklitwin]

Hello, i'm trying to make a form in which one field is automatically populated from the selection in a combo box (which pulls from a table)

Bascially i have a list of unique entity numbers and unique entity names. What i want to have happen is i select the entity number from the list and the other field automatially pulls the corresponding name (which is right next to it in the table)

I created a formula which replaced the control field but of course, although it populates it on the form, it does not save it within the table as the contol source is now replaced.

Is there a way to do this so that i it will save the corresponding value in the table?

Thanks

 

[vbaInet]

DLookup() function will do it:

http://www.techonthenet.com/access/functions/domain/dlookup.php
http://www.mvps.org/access/general/gen0018.htm

 

[Uncle Gizmo]

Link the combo box directly to the field. Once you do this the selection in the combo box will be recorded in the underlying field.

 

[mklitwin]

im somewhat of a novice with Access so if possible could someone walk me through it

Uncle Gizmo - i think you may be missing the issue where i am able to get the combo box itself to be recorded in the tables field but i need a field dependent on the combo box's selection (which comes from the same table in an adjacent cell) to be populated into my main table...

 

[boblarson]

And you shouldn't be storing both pieces of data as you already have the Entity Number which should be stored but you can get the Entity Name any time using a query. So you should just display the second part.

 

[boblarson]

i think you may be missing the issue where i am able to get the combo box itself to be recorded in the tables field but i need a field dependent on the combo box's selection (which comes from the same table in an adjacent cell) to be populated into my main table...

Sounds like your data structure is flawed. In a properly normalized database no field is dependent upon any other field in the same table.

Also, I hope you're not using Lookups at TABLE level. If so, see here for why that is a bad, bad, bad, bad idea.

 

[vbaInet]

Maybe a screenshot of your relationships would help.

 

[mklitwin]

we will have alot of redundant data in my database (but its somewhat necessary for what we are using this for as we need a history of records and relationsihps that change frequently)

i havent used a lookup yet..

i have this main table which is pretty much the fields in my database we need filled to run reports. We are using the form simply as an esay way to populate those fields for later.

I have additional tables with the entity name's and numbers which need to be populated per record in a specific fashion into that main table. For ease of use i simply need to be able to select from a dropdown of entity numbers (from my entity table) have it store that value in the main table under "entity number" for example, and automatically populate and save the corresponding entity name (which comes from the adjacent column in the "entity table" and save that in the main table as well..

Its a very simple structure, that may not be the "best" way to do things.. but it needs very minimal functionality.. i just want to avoid having to type in the name of the entity and risk a spelling error etc.

 

[boblarson]

So, have the data in the combo box and use this code in the combo's After Update event:

Me.YourTextBoxNameHere = Me.YourComboNameHere.Column(1)

Where Column(1) is the second column (zero based) and your combo should have the
ColumnCount property set to the number of columns in your combo's rowsource.

See this example on how to include that info in your combo.

 

[mklitwin]

for example....

my main tablle has the following fields
Entity Number, Entity name, etc etc


my entity table has
entity number, entity name
E1 Entity1
E2 Entity 2


in a sense we just need to be able to pull data results from the entity table and put them into the main table

so on my form, i need to be able to pull E1 from a combo box (which i get by making the dat source" entity table" and have the control source be "main table." But i also, need to have in another field, "Entity1" pull up when i select E1, and populate into the Entity name field of the "main table" ie its control source is "entity name"

 

[mklitwin]

perfect... absolutely perfect.. you guys are good.. i acutally had a code like that with an =[Parent 2 Code].column(1) in the control section, but obviously that removed it from begin recorded

now heres a brain teaser.. what iff... i want that a similar functionality right, but i wanted both dependent on eachother, ie, if i dont know the entity code but know the number or vis versa is that possiblee?

what we have now solves it perfectly just wondering if htats possible too?

 

[boblarson]

perfect... absolutely perfect.. you guys are good.. i acutally had a code like that with an =[Parent 2 Code].column(1) in the control section, but obviously that removed it from begin recorded

now heres a brain teaser.. what iff... i want that a similar functionality right, but i wanted both dependent on eachother, ie, if i dont know the entity code but know the number or vis versa is that possiblee?

what we have now solves it perfectly just wondering if htats possible too?

Have 2 combo boxes which have the same Rowsource essentially but with the fields reversed order and still have the ID bound to the same table field. You will have to change the one from Bound Column 1 to Bound Column 2 to make sure the ID is captured from the combo. Then you can use the same code in each After Update event but just modify the column number to be the applicable column.

Hope that makes sense.

Selecting the value from either combo would then automatically change the other combo.

 

[mklitwin]

all i know is your first way worked perfectly and you guys impress the hell out of me... but im going to give this thing a shot since im intrigued...

 

[boblarson]

all i know is your first way worked perfectly and you guys impress the hell out of me... but im going to give this thing a shot since im intrigued...

Let me know if you want me to modify my example to have the two combos.

 

[mklitwin]

if you could show me how to do that it woudl be great.. here are the acutal field names and things so maybe i would undersatnd better

Main entity table - there are the fields that need to be filled in

"Parent_1_Code" "Parent_1_Name"

Entity name table - lookup table per se

"Entity_Code" "Entity_Name"

 

[mklitwin]

yeah i'm having a bit of trouble undersatnding exactly what you are trying to do.. i convered the text box to a combo box, and was obviously able to get it to pull from column 2 (the names section) but not sure how to make the 2 boxes now interact with eachother as you had mentioned...

 

[boblarson]

yeah i'm having a bit of trouble undersatnding exactly what you are trying to do.. i convered the text box to a combo box, and was obviously able to get it to pull from column 2 (the names section) but not sure how to make the 2 boxes now interact with eachother as you had mentioned...

I'll do up a sample but you don't change the text box. In fact, you don't change much of anything except adding a NEW combo box.

 

[mklitwin]

im confused... how does that work then? if i only will be showing these 2 fields on my form? why do i still need the text box?

 

[boblarson]

im confused... how does that work then? if i only will be showing these 2 fields on my form? why do i still need the text box?

Check out the attached sample.

 

[boblarson]

Oh, and I guess you wouldn't need to have the text box if you are just selecting from the combo those two fields.