I have a database with two tables - Company and Person.
A simplified form (based on the query which links the two tables) is shown below.
I have in mind that when I am looking at one record of the query (that is, one person of many in a single company), I'd like to have a list-box (or similar) in the top-right corner showing the names of all other people in the same company.
Here's the question: To load the listbox, do I write VBA code (which I know I can do), or is there a nifty on-the-fly query doo-dad in Access that will do it for me?
In the back of my mind is that I might have to build and then modify (in real time) a simple query that obtains all person records for a given company, or perhaps have to write a nifty little SQL-like expression as a data source for the listbox.
Basic Access2000 question on linked tables
-
- PlutoniumLounger
- Posts: 15643
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Basic Access2000 question on linked tables
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.
-
- 3StarLounger
- Posts: 287
- Joined: 09 Mar 2010, 23:16
- Location: Canberra Australia
Re: Basic Access2000 question on linked tables
You could write a query that takes the companyID from the main form as a parameter to return all people from that company.
In the OnCurrent event of the main form you would need to requery the list box for it to be updated when you move to a new company.
In the OnCurrent event of the main form you would need to requery the list box for it to be updated when you move to a new company.
Regards
John
John
-
- PlutoniumLounger
- Posts: 15643
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Basic Access2000 question on linked tables
Thanks for the speedy response, John.JohnH wrote:You could write a query that takes the companyID from the main form as a parameter to return all people from that company.
In the OnCurrent event of the main form you would need to requery the list box for it to be updated when you move to a new company.
Because I have specified a literal, address each record displayed on the form shows the seven employees from the one form at Bertrand Avenue. It's a small step but it worked!
(My form is frmDetail and it is based on a query qryDetail).
Code: Select all
SELECT qryDetail.GIVEN, qryDetail.SURN FROM qryDetail WHERE qryDetail.address="23 Bertrand Avenue"
Code: Select all
SELECT qryDetail.GIVEN, qryDetail.SURN FROM qryDetail WHERE (((qryDetail.ADDRESS)=[frmdetail].[address]));
I'll play around a bit more and see what happens.
He who plants a seed, plants life.
-
- 3StarLounger
- Posts: 287
- Joined: 09 Mar 2010, 23:16
- Location: Canberra Australia
Re: Basic Access2000 question on linked tables
Try
Code: Select all
SELECT qryDetail.GIVEN, qryDetail.SURN FROM qryDetail WHERE (((qryDetail.ADDRESS)= [forms]![frmdetail].[address]));
Regards
John
John
-
- PlutoniumLounger
- Posts: 15643
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Basic Access2000 question on linked tables
Thanks John.JohnH wrote:Code: Select all
SELECT qryDetail.GIVEN, qryDetail.SURN FROM qryDetail WHERE (((qryDetail.ADDRESS)= [forms]![frmdetail].[address]));
I'm sorry about the delay in responding; I had to go to work.
I had to modify the query slightly to get it to go to work!
Here is the current code that seems to do what i want; although I'm probably doing more work than I need to.
Code: Select all
Private Sub Form_Current()
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim strSQL As String
strSQL = ""
strSQL = strSQL & "SELECT [qryDetail].[Given], [qryDetail].[Surn] from qryDetail "
strSQL = strSQL & "WHERE (((qryDetail.ADDRESS)= '" & [Forms]![frmdetail].[ADDRESS] & "'))"
Dim strItems As String
Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
rst.MoveFirst
While Not rst.EOF
' Debug.Print rst.Fields("GiveN") & " " & rst.Fields("Surn")
strItems = strItems & rst.Fields("GiveN") & " " & rst.Fields("Surn") & ";"
rst.MoveNext
Wend
lbOtherNames.RowSourceType = "Value List"
lbOtherNames.RowSource = strItems
End Sub
He who plants a seed, plants life.
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Basic Access2000 question on linked tables
But that's the beauty of it you see. You can bind it to a recordsource so you don't have to add items one at a time, and you can actually edit data in the list box if you choose to, though we usually use it so a user can select a specific record (or records). Access is quite a different beast from Excel and Word when it comes to forms. Being able to bind them to data with no VBA is one of the reasons it is so powerful.It annoys me no end that Access 2000 VBA doesn't have ListBox.Clear (.AddItem etc) like Word/Excel.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- 3StarLounger
- Posts: 287
- Joined: 09 Mar 2010, 23:16
- Location: Canberra Australia
Re: Basic Access2000 question on linked tables
I think you are making this much more complicated than it needs to be.
Here are two options.
* Create a saved query, using the sql I posted (or a variation that works) then set the row source of the listbox to that query. The row source type needs to be table/query.
In the On Current event, requery the list box.
No other code is needed.
* Have this code in the On Current event. Again set the row source type to table/query.
In this case you don't need to requery. It happens automatically if you change the row source.
Here are two options.
* Create a saved query, using the sql I posted (or a variation that works) then set the row source of the listbox to that query. The row source type needs to be table/query.
In the On Current event, requery the list box.
No other code is needed.
* Have this code in the On Current event. Again set the row source type to table/query.
Code: Select all
Private Sub Form_Current()
Dim strSQL As String
strSQL = ""
strSQL = strSQL & "SELECT [qryDetail].[Given], [qryDetail].[Surn] from qryDetail "
strSQL = strSQL & "WHERE (((qryDetail.ADDRESS)= '" & [Forms]![frmdetail].[ADDRESS] & "'))"
lbOtherNames.RowSource = strSQL
End Sub
Regards
John
John
-
- PlutoniumLounger
- Posts: 15643
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Basic Access2000 question on linked tables
Thanks Wendell. My FORTRAN (DO-loop) background shows through every time. I seem to be fixated on the FIRST way I learned to load a ListBox, and had not begun to see the power of Access.Wendell wrote:But that's the beauty of it you see. ... you don't have to add items one at a time,
(Please see also my concurrent reply to JohnH)
He who plants a seed, plants life.
-
- PlutoniumLounger
- Posts: 15643
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Basic Access2000 question on linked tables
John, if I were forty years younger I'd say "Eileen's Lounge ROCKS!"; but I'm not, so I won't.JohnH wrote:I think you are making this much more complicated than it needs to be.
But I'll continue to think it.
Code: Select all
strSQL = ""
strSQL = strSQL & "SELECT qryDetail.Given + ' ' + qryDetail.Surn FROM qryDetail "
strSQL = strSQL & "WHERE (((qryDetail.ADDRESS)= '" & [Forms]![frmdetail].[ADDRESS] & "'))"
lbOtherNames.RowSourceType = "Table/Query"
lbOtherNames.RowSource = strSQL
I suspect that my version of Access or the SQL engine is a little different from yours, but in the end I learned a lot.
Thanks.
(Please see also my concurrent reply to Wendell)
He who plants a seed, plants life.