How to display data in a form from a table/query?

[endwarde]

Hello. This seems like such a newbie kind of question, so I apologize in advance. I have been searching for inspiration here online, but I am sure I am not using the right terms.

I have a form which enters data into a table. Pretty simple. One of the combo boxes I have on the form looks up data from a related table. While the record I select there displays fine, I would also like to display some other data from that related table associated with the record I selected (just for user reference).

Now, in the past, I have always created a subform and have had the subform field(s) display data according to the record that I chose from the combo box, and it has worked fine. My question is whether or not there is a "better way" to do this. I have seen other access forms that seem to do this, without the telltale evidence of having a subform present.

Maybe it is just hidden well or something, but if there is another way to do it (via code and a text box or something) I would love some inspiration as to where to get more information on how to do it properly.

Thanks!

 

[Jings00]

there are probably many better ways to do this, but the way I solve this issue is:
1) create a query that will display the related information you want to display - use the combo box as the criteria in the query.
2) create text boxes whose control source is the query fields made above.
3) Use VBA code for the "on update" event for the combo box that refreshes the text boxes.

-I also think you could combine 1 and 2 and have the text boxes control source being an SQL statement

 

[scriverb]

A method that I have successfully in the past had to do with a One-to-Many relationship with the Main form having the One table as its recordsource. I used a Combobox as you have to select records for the Main Form. I have also on this main form created additional Pages. You can put a page break in a form and size you Main form so that you only see Page 1. But you form is taller than Page 1. Let's say page 1 is set to 5 inches by the design ruller. You can Set the Page Break at the 5 inch mark and then pull the form detail section down to the 10 inch mark. now you have two distinct pages. I usually put the Primary Table(one side of relationship) on Page 1. If the user needs to see day from related tables I have a command button setup with a Caption identifying the info on Page 2. (i.e. Main form Customer Page2 Account Transactions ) This button would toggle the view between Page 1 and page 2 with the following code:

If cmdPageButton.Caption = "Acct. Trans." then
     DoCmd.GoToPage 2
     Me.CmdPageButton.Caption = "Customer Info"
Else
     DoCmd.GoToPage 1
     Me.CmdPageButton.Caption = "Acct. Trans."
End If

This code will change the caption of the button depending upon which data is being viewed at this time and would jump the user to the appropriate page of data.

This Page 2 layout would include a small amount of customer info to keep reminding the User of which customer we are looking at but would have a subform detailing the Account transactions associated with this customer.

This can be done for numerous tables on the Many side of the relationship if they exist. They don't all have to show at the same time but can be displayed when needed only.

Post back if I can help more with this kind of concept.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]