Basic Access2000 question on linked tables

[ChrisGreaves]

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.

Cont_002.JPG

[JohnH]

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.

[ChrisGreaves]

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.

Thanks for the speedy response, John.
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"

But when I change from the literal address to a value from the form, a little box pops up asking me to key in the address by hand (and then the list box goes blank).

Code: Select all

SELECT qryDetail.GIVEN, qryDetail.SURN FROM qryDetail WHERE (((qryDetail.ADDRESS)=[frmdetail].[address]));

At any rate, you have answered my question, Thanks: I don't need to fabricate some VBA code to get this done. Access 2000 provides a high-level tool to achieve my results.

I'll play around a bit more and see what happens.

[JohnH]

Try

Code: Select all

    SELECT qryDetail.GIVEN, qryDetail.SURN FROM qryDetail WHERE (((qryDetail.ADDRESS)= [forms]![frmdetail].[address]));

[ChrisGreaves]

Code: Select all

    SELECT qryDetail.GIVEN, qryDetail.SURN FROM qryDetail WHERE (((qryDetail.ADDRESS)= [forms]![frmdetail].[address]));

Thanks John.
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

It annoys me no end that Access 2000 VBA doesn't have ListBox.Clear (.AddItem etc) like Word/Excel.

[Wendell]

It annoys me no end that Access 2000 VBA doesn't have ListBox.Clear (.AddItem etc) like Word/Excel.

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.

[JohnH]

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.

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

In this case you don't need to requery. It happens automatically if you change the row source.

[ChrisGreaves]

But that's the beauty of it you see. ... you don't have to add items one at a time,

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.
(Please see also my concurrent reply to JohnH)

[ChrisGreaves]

I think you are making this much more complicated than it needs to be.

John, if I were forty years younger I'd say "Eileen's Lounge ROCKS!"; but I'm not, so I won't.
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 had to play around a bit to get both names to appear in the ListBox, settling on a space between the single-quotes.
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)