Lookup fields in a form 1

[gk7828]

Hi

I am new to Access although I used it breifly about 5 years ago.

I am trying to recall data of upto 5 fields in a form ie

Date, ProjectNo, AccountNo, Customer, Comments

I have tried the Combo Wizard but can only get 1 field to display.

I am starting to travel in circles please help

gk7828

 

[ycim]

Are you saying that you want to get the combo box to display more than 1 field?

 

[gk7828]

I need to recall all 5 fields by when entering ProjectNo

 

[dhookom]

Why are you using "recall" when this really has no meaning in Access? Do you want to display multiple fields in the drop down? Afte selection of the appropriate record from the combo box, do you want to see 5 fields in the one control or would it work to see 1 field in the combo box and other fields in other controls?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gk7828]

I would like to see 1 field in the combo box and the other fields in seperate controls.

Thanks

 

[dhookom]

Set your combo box row source to include as many columns as you would like to see on your form. You can then use text boxes with control sources like:
=cboProjectNo.Column(2)
where your combo box is named cboProjectNo and you want to display the 3rd column.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JRMS]

dhookom,

I am attempting to do the same thing. I get an "NAME?" error message. Below is an example of what I am trying to do:

System (*************) <=== combo box
--------------------------------------
System ID (********) <==== Retrieve from selection above
Location (********) <==== Retrieved from selection above


I am trying to automated the details of the system to automatically fill the text boxes.

Please help.

Jerrell

 

[dhookom]

What are these properties of your combo box:

Name:
Control Source:
Column Count:
Bound Column:
Row Source:
Column Widths:

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JRMS]

Below is the information from the property of the combo box:

Name: Combo2

Control Source: blank

Column Count: 1

Bound Column: 1

Row Source: SELECT TblTanf.[System Name (As In TAFT)] FROM TblTanf ORDER BY [System Name (As In TAFT)];

Column Widths: 1"

Thanks.

 

[dhookom]

You would need to add the System ID and Location to the Row Source of the combo box.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JRMS]

Excuse my unknowing. But why should I correct something on the combo box when it is working fine. My other text boxes are not populating with the detail information from about the selected item in the combo box. Thanks in advance.

 

[dhookom]

My suggested solution in this thread takes advantage of the additional columns in the Row Source query of the combo box. If you don't have additional columns then you can't use the solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IGPCS]

you need to add any other columns you want by going in the query for that combobox (you can get to it by clicking on the small button with 3 ... in the row source propeties) and then you add the columns in the query and when you close it, it will ask you if you want to apply those changes you click yes, then you need to change the coluns count to the amount you want, then you could apply the data in the columns by adding it to the after update properties in the following code when the first column number is 0

example for the secon column: text1 = combo1.column (1)
'this will enter the second columns of the record you choosed in the combobox in the object field named text1

hope you get if not reply i will be happy to assist you

 

[dhookom]

Good comments from IGPCS. However, there is rarely a need to store the extra columns since this would not be normalized. Most of the situations, should simply involve displaying the related information on the form which can be handled by setting the control sources of the text boxes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IGPCS]

what i understood is that he watnts to auto fill the text boxes with the informtion he has in the combobox

 

[dhookom]

It isn't at all clear but I read this "I am attempting to do the same thing". I stand by my "generalization" that storing the lookup values is often a bad idea.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IGPCS]

can you explain why is that? when i do store it, it helps me process queries much faster i deal with tables that have over 60.000 records

i hope i didnt as a vague question

 

[JRMS]

I do apologize for not being clear. I am attempting to auto fill testboxes once I select an item from the combo drop down list. There are around four textboxes that need to be auto filled. Any and all suggestions I would appreciate.

 

[IGPCS]

well thats exactlly what i told you if i was unclear please let me know

IGPCS
Brooklyn, NY

 

[dhookom]

JRMS,
Are the text boxes bound or unbound? Do you expect to store the auto filled text box values in another table or do you just want to display the values on the form.

IGPCS,
You might be correct about JRMS intentions but I would stand by my statement regarding storing related values in multiple fields. With properly indexed tables and efficient use of queries, you should not have a performance issue with 60,000 records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]