Create query to take select data from multiple other tables on command button click

Question

I am going to try to make this question as detailed as possible as it's a really confusing one, at least in my mind. First of all lets start with what everything is called. There are 2 forms, 1 button, 4 tables, and 1 temp-query.

The 2 forms are called WorkOrderDatabaseF & WorkOrderInfoF. The button is called WorkOrderMoreInfoButton, which creates the temp-query and opens up the form WorkOrderInfoF. The 4 tables are WorkOrdersT, ClientsT, UnitsT, and CustomersT. All have relations to eachother, in a straight forward line up of (all one to many from left to right) ClientsT>CustomersT>WorkOrdersT>UnitsT. 1 Client can have as many customers which each customer can have as many work orders which any work order can have as many units. The temp-query when made is called WorkOrderMoreInfoQ.

Lets jump right in. What happens is this: There is a subform on the WorkOrderDatabaseF which lists the Work Orders; which which customers and clients belong to which work order. When you select one and click the more info button it pops up the WorkORderInfoF form and doesn't close the main database. From here it has text boxes, and another subform and button (the button won't be used for what I am asking so I won't go into detail about it). The text boxes currently are hooked up to show the data from the selected by a temp-query made when you clicked the button. It took the selection you clicked, and made a query which has 1 line with the information about that work order (the only foreign key in the workorderT is CustomerID which is related to the CustomersT key; which has a foreign key of ClientID which is as you guessed it, related to the key on ClientsT.) The text boxes show all the data correctly, except the CustomerName and ClientName. The subform would show all units that pertain to the workorder selected only. The popup form has the recordsource of the temp-query which I'm calling temp because when you close the more info form it deletes the query.

All code I have so far for things:

More info button:

Private Sub ProjectMoreInfoBtn_Click()
   On Error Resume Next
   DoCmd.Close acForm, "WorkOrderInfoF"
   DoCmd.DeleteObject acQuery, "WorkOrderMoreInfoQ"
   On Error GoTo Err_ProjectMoreInfoBtn_Click

   Dim qdef As DAO.QueryDef
   Set qdef = CurrentDb.CreateQueryDef("WorkOrderMoreInfoQ", _
                                       "SELECT * " & _
                                       "FROM WorkOrdersT " & _
                                       "WHERE WorkOrderID = " & txtWorkOrderID.Value)



   DoCmd.OpenForm "WorkOrderInfoF"

Exit_ProjectMoreInfoBtn_Click:
   Exit Sub
Err_ProjectMoreInfoBtn_Click:
   MsgBox Err.Description
   Resume Exit_ProjectMoreInfoBtn_Click
End Sub

On form close delete temp-query code:

Private Sub Form_Close()
   On Error Resume Next
   DoCmd.DeleteObject acQuery, "WorkOrderMoreInfoQ"
End Sub

How would I make the customer and client name that is associated with tthe workorder be shown in their respective text boxes on the more info form? Also, how would I make only the units for the work order selected show up on the subform (datasheet) in the moreinfo popup form?

Kiril Rusev Kiril Rusev · Accepted Answer · 2013-11-26T14:21:41

Why do you make your life so difficult... What if you create a temporary variable called tempWorkOrder which stores the workorderID as default value. Afterwards, create a query based on that value and use it as a datasource for your 2nd form..The tempVariable should be set at your first form on the button control by using macro, preferably.

      setTempVar
   name:
   Expression= workorderID(or textboxN.text)

EDIT: As we spoke yesterday..

 Set qdef = CurrentDb.CreateQueryDef("WorkOrderMoreInfoQ", _
                                   "SELECT * " & _
                                   "FROM WorkOrdersT inner join WorkOrdersQ on workordersT.WorkOrdersID=WorkOrdersQ.WorkordersID " & _
                                   "WHERE WorkordersT.WorkOrderID = " & txtWorkOrderID.Value)

Create query to take select data from multiple other tables on command button click

1 Answers