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 · Accepted Answer · 2013-11-27 08:59:05Z

1

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)

edited Nov 27, 2013 at 8:59

answered Nov 26, 2013 at 14:21

Kiril Rusev

7533 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

D347HxD Over a year ago

That's basically what the text box and button do on the first form, but it only makes 1 query to store all the information about that work order and that query is the record source of the 2nd form; "WHERE WorkOrderID = " & txtWorkOrderID.Value)

Kiril Rusev Over a year ago

"the customer and client name that is associated with tthe workorder " - Are these stored in a separete table and if so, is there atable which relates them with the work orders? -You could build an expression which extracts a particular client name based on the workorderid..

D347HxD Over a year ago

The customer and clients each have their own table and IDs. The customer has a foreign key for the client, and the work orders table has a foreign key for the customer. I need both the client and customer name as they are different people (Lets say CIBC hired Walmart to work on the Work Order for example)

Kiril Rusev Over a year ago

OK! I got it now, however according to me the database structure could be improved. For instance, what happens if you have more than 1 work orders for the same customer?= You end up with 2 records in Customers table. Also what if we have multiple clients for the same customer= Multiple records in Clients tbl.. On the other hand if you have both of these ID's into the work orders table (CustomerID and ClientID) you will end up with only 1 record in all three tables which means that you are not storing any redundant data. Furthermore these two 1 to many relations will allow you do make any comb.

Kiril Rusev Over a year ago

I suppose that would make your work on these forms much easier ,since you will be able to directly source the ID and the name associated with it.

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related