I am a newbie with Access: I am trying to display fields from a table in the database (linked 1->1 or M->1). I have done subforms which show child records (this is very easy in Paradox, which is the database I've programmed in for 10 years). What I'm looking for is e.g. show the name of a student when the ID number is entered (from a combo box), so that the user can see who the person is, but I don't need to break RI to include the name in the table. Thanks in advance!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to display related fields from tables
- Thread starter sonso
- Start date
Sonso,
You can set up the combo box to have two (or more)
columns. You don't have to even display the second
one (width = 0). The RowSource would be something
like:
Select id, name from Students order by id;
You can even populate an unbound visible textbox like:
Me.txtUnboundName = Me.cboStudent.Column(1)
hth,
Wayne
You can set up the combo box to have two (or more)
columns. You don't have to even display the second
one (width = 0). The RowSource would be something
like:
Select id, name from Students order by id;
You can even populate an unbound visible textbox like:
Me.txtUnboundName = Me.cboStudent.Column(1)
hth,
Wayne
- Thread starter
- #3
Thanks for the response, but what I'm trying to get is not the associated field in the combo box, but rather a side-by-side constant display of the related field values from the "other" table. Think of a tableframe that has 2 live value columns, and then say 2 or 3 other "lookup" columns that are related.
Sonso,
OK, on an after update event on your combo box put:
' ***************************************************
Dim dbs as DataBase
Dim rst as RecordSet
Dim strSQL as String
strSQL = "select * from tblOtherTable " & _
"where intKey = " & Me.cboControl & ";"
set rst = dbs.OpenRecordSet(strSQL)
If rst.EOF and rst.BOF Then
MsgBox("Invalid ID."
Else
Me.txtUnboundControl = rst!NameField
End If
set rst = Nothing
' ***************************************************
hth,
Wayne
OK, on an after update event on your combo box put:
' ***************************************************
Dim dbs as DataBase
Dim rst as RecordSet
Dim strSQL as String
strSQL = "select * from tblOtherTable " & _
"where intKey = " & Me.cboControl & ";"
set rst = dbs.OpenRecordSet(strSQL)
If rst.EOF and rst.BOF Then
MsgBox("Invalid ID."
Else
Me.txtUnboundControl = rst!NameField
End If
set rst = Nothing
' ***************************************************
hth,
Wayne
- Thread starter
- #5
From this I gather that you can't show multiple records with their related fields. In Paradox, the links between tables are maintained dynamicly and updates are shown onscreen without any code. It sounds like, I have to run a query to fill a recordset each time I change either the master record or any of the child records on the form. So do I create a dummy/empty subform that uses the recordset generated by the query? If yes, then do I delineate the SQL in the subform, and substitute variable values for the linking fields?
sonso,
You can create a query that joins the tables in one view.
Then you can use the query as the Rowsource for your
form.
1) In the database window, queries tab
2) Query --> New --> Design View
3) Add the names of the desired tables
4) Join the tables by dragging the appropriate field
from one table to the other
5) Right-Click on the new line and specify the join
properties
6) Double-Click on the desired fields to view
7) Specify any sorting orders
8) Run the query
9) When you get the desired information, use the new
query as your form's rowsource.
good luck,
Wayne
You can create a query that joins the tables in one view.
Then you can use the query as the Rowsource for your
form.
1) In the database window, queries tab
2) Query --> New --> Design View
3) Add the names of the desired tables
4) Join the tables by dragging the appropriate field
from one table to the other
5) Right-Click on the new line and specify the join
properties
6) Double-Click on the desired fields to view
7) Specify any sorting orders
8) Run the query
9) When you get the desired information, use the new
query as your form's rowsource.
good luck,
Wayne
- Thread starter
- #7
Well, there doesn't seem to be an object tree to navigate, so I get FormHeader, FormFooter, and Detail (for the subform that should ultimately link to the master record on the form). So for this form there is no rowsource. Additionally, if a user changes the ID# in a particular record, how will Access now to run this query again to update the recordset. It seems needlessly complex and would lead you to want to violate referential integrity by including the related field in the actual table in the subform. I thought that creating the relationships would allow you to automaticly display fields in the related table. Are we basicly saying that you can navigate the table with the master record (which will trigger the query and show the related records on the subform) but you can't update the subform without having to re-trigger the very same query? Won't this cause a lot of traffic on the network?
sonso,
I assume that you put together a query that joins the
tables and gives you a view of the data.
Then create a form based on that query. Even use the
Wizard if you want (I don't prefer it). In design view
you will see a little black square in the upper-left
hand part of the form. Right-click on this and select
properties. Then select the data tab. Then point
"Record Source" to your query. That's it.
Access will handle the physical manipulation of the
data.
As a test, make a copy of your database, try the above
and I don't think you'll find performance to be a
significant factor.
good luck,
Wayne
I assume that you put together a query that joins the
tables and gives you a view of the data.
Then create a form based on that query. Even use the
Wizard if you want (I don't prefer it). In design view
you will see a little black square in the upper-left
hand part of the form. Right-click on this and select
properties. Then select the data tab. Then point
"Record Source" to your query. That's it.
Access will handle the physical manipulation of the
data.
As a test, make a copy of your database, try the above
and I don't think you'll find performance to be a
significant factor.
good luck,
Wayne
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question