i'm trying to run a search with a project number to pull all of the records associated with that project number from multiple tables. can anyone give me some advise as to how to go about doing this.

I started with this code but it seems limited and its not exactly working....

Private Sub Command11_Click()
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim strSQL As String


    strSQL = "SELECT * FROM Tproject WHERE [Project #] = " & Text6
    Set dbs = CurrentDb
    dbs.QueryDefs.Refresh

    For Each qdf In dbs.QueryDefs
        If qdf.Name = "Qqp#" Then
            dbs.QueryDefs.Delete qdf.Name
        End If
    Next qdf

    Set qdf = dbs.CreateQueryDef("Qqp#", strSQL)
    Set dbs = Nothing

End Sub


any help would be greatly appreciated!!!

dstein4d,
My advise to you is when you are inserting, updating or deleting
a field record in a table and consequently want automatically be done in other tables the best is to try one field then use after update event function in the form save as an update function in one of your modules.
for example you have a cust and an ord table:
A field is use on both tables named cust_tel_no:
On your access form for example assuming frm_cust:
on field cust_tel_no you create a afterupdate event function on properties like this:
This will be store on your MODULE TAB named mod_frm_cust_afterupdate
(EVENT)
afterupdate = cust_tel_no_frm_cust_afterupdate():

Private Function cust_tel_no_frm_cust_afterupdate
OnErr GoTo Err_cust_tel_no_frm_cust_afterupdate ???

  Dim rstUpdRec As new ADODB.recorset, sqlUpdRec as sql
  Dim lngCust_Tel_No As long
  Dim intCust_Id As Integer
  Dim strCust_Id, strCust_tel_no

  strCust_Id = Screen.ActiveForm.Cust_Id.Value
  strCust_Tel_No = Screen.ActiveForm.Cust_Tel_No.Value
  If IsNull(strCust_Id) Then
    DoCmd.CancelEvent
  ElseIf IsNull(strCust_Tel_No) Then
    DoCmd.CancelEvent
  Else
    intCust_Id = Screen.ActiveForm.Cust_Id.Value
    lngCust_Tel_No = Screen.ActiveForm.Cust_Tel_No.Value

    '-------------------------------------'
    ' Update Cust_Id record on ord table '
    '-------------------------------------'
    sqlUpdRec = "SELECT cust_tel_no "
    sqlUpdRec = sqlUpdRec & "FROM ord "
    sqlUpdRec = sqlUpdRec & "WHERE cust_id = " & intCust_Id
    rstUpdRec.Open sqlUpdRec, ???????

    If rstUpdRec.EOF then
      rstUpdRec.Close
    Else
      Do Until rstUpdRec.EOF Then
        rstUpdRec("Cust_Tel_No").Value = lngCust_Tel_No
        rstUpdRec.Update
        rstUpdRec.MoveNext
      Loop
      rstUpdRec.Close
    End If

    '
  End If

Exit: ????

Err_cust_tel_no_frm_cust_afterupdate:
  MsgErr.Description
  Exit ????
End Function


I am not sure about the one with ???
Hope you got the idea and may have help you in a way.
John

Queries are not working for you? How do you want to display the results?

A technique I use (using ADO since I do Access/SQL mostly) is to create a table that has all of the fields from all of the tables that I want. Then with the button click, run a delete query on the table to make sure it is empty, then start packaging the new data, first from, in your case, Tproject based on the project number (is this a number or text in your case? If number, your code is okay, if text, you need to add single quotes to your parameter). Then loop back through the table and for each record in the table, go out and get the pieces needed from other tables.

This is easy to do in ADO, but I don't work with DAO, so I am nit sure of the syntax.

Post the tables and their structures and what fields you want and how they are linked and we can help out.



mmcdonal