Query based on another table

[Yecats131]

Hello,

I need to make a query that runs every time a record is opened. Basically I have:

Transaction: Conversion (Contains an EmployeeID field)
Employee File: (Prim. Key is EmployeeID)

Events:

• User is on the Employee File Form.
• They Select the tab that is Listed "Transactions"
• Query runs that matches the EmployeeID from the current record on the Employee File to the EmployeeID located on the Transaction: Conversion table.
• Query pulls back all results and will be displayed on the Employee File form.

What expression should I use? I have built a relationship between both tables linking the EmployeeID fields.

 

[khawar]

upload the db with some sample data

 

[DCrake]

Simple Software Solutions

You have not indicated what control displays the transactions for the selected employee. Is it a subform? A List box? Something else? In any case you need to refresh the recordsource each time you select a different employee as this is the controlling factor.

CodeMaster:

 

[Yecats131]

Oh, whops sorry :0)

The Employee File has the Page Tab control on it. So when ever the user clicks the "Transaction" tab I want it to run the query and bring back all transactions that have the same EmployeeID.

 

[DCrake]

Simple Software Solutions

Yes, I understand that but what is being used to display the child records from within the tabbed page

 

[Yecats131]

I have no idea - I haven't made it that far. :0)

 

[MSAccessRookie]

You could try setting up a sub form with a record source that is similar to the following query:

SELECT { Columns of Interest }
FROM { Transactions Table Name }
WHERE { Transactions Table Name }.EmployeeID = { Employee ID selected in the Parent Form }

 

[Yecats131]

Thank you - Just one question...

Okay, so I figured out how to setup a record source - but I'm not sure how to do the query. Do I put the WHERE under the employeeID COlumn on the query screen?

 

[MSAccessRookie]

Thank you - Just one question...

How do I set up a form to have a record source?

I know how to add a subform.. but that would be it...

Every Form has Properties that can be modified as necessary to provide the kind of flexibility that you will want. On the Properties screen for the Sub Form, the Data Tab will contain a field called "Source Object" where you can either type an SQL Statement (although there may be a limit to the number of characters allowed), or enter the name of a Query containing an SQL statement to execute.

 

[Yecats131]

Red Box - Top Line: "WHERE [conversion].EmployeeID"

Bottom Line: [Employee_Info].[Employee_ID]

It changed my code to this and moved iti nto another box. So, I added this form to my employee file and it pops up a box asking me for the Employee ID#. Then when I type the number in, it doesn't pull anything up.

(I of course do not want it to pop up a parameter box, I want it to pull it up on the fly).

Also, I didn't see Source Object - I saw a "Record Source" which is where I did this. maybe that is why it's acting this way?