I need help using an unbound datasheet in Access 2013

I Experts,
I have two tables in my Access 2013 application (tblEmployee and tblJob).  The tables are joined by the EmployeeID field.
I have a form with an unbound Combo box control that lists all of the employee names from table tblEmployee.
On the combo box's On Change event I want to run a query that will populate an unbound datasheet using the query below.  How do I do this?
 
1. How do I create the unbound datasheet in Access 2013?
2. How do I populate my unbound datasheet using the query below on the Combo Box's On Change event?  
SELECT E.Name, E.Phone, J.* FROM tblEmployee as E, tblJob as J WHERE E.EmployeeID = cboEmployee.value and E.EmployeeID = J.EmployeeID 

Open in new window



Thank you very much in advance,
mrotor
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
place this code in the afterupdate event of the combo cboEmployee

if EmployeeID is Number use this

Me.subformcontrolname.form.recordsource="SELECT E.Name, E.Phone, J.* FROM tblEmployee as E, tblJob as J WHERE E.EmployeeID = J.EmployeeID And E.EmployeeID = " & me.cboEmployee

if EmployeeID is TEXT use this

Me.subformcontrolname.form.recordsource="SELECT E.Name, E.Phone, J.* FROM tblEmployee as E, tblJob as J WHERE E.EmployeeID = J.EmployeeID And E.EmployeeID = '" & me.cboEmployee & "'"



.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To be clear: You can't really have an "unbound datasheet form", unless you want to show one row only. Unbound Datasheet columns simply repeat the last value inserted into them, so an unbound Datasheet form would be pointless.

I think instead you mean a "dynamic" datasheet form. This would be one where you change the Recordsource of that form "on the fly", as Rey has shown you.
0
mainrotorAuthor Commented:
Thank you Rey and Scott,
I suppose I meant "dynamic" datasheet.  How do I place a dynamic datasheet in an Access form?  Do I just place a subform control and then hit the Cancel button?
dynamic datasheet
Please help? Thanks in advance.

mrotor
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your Subform control would have to contain a form, so just select the Form that you want to use as a subform.

Then, the code that Rey showed you should work.

However your query doesn't JOIN those two tables, so you might get odd results. How are tblJob and tblEmployee related? If tblJob stores the ID value from tblEmployee, then you should consider including a JOIN in that query to insure correct results. For example:

SELECT E.Name, E.Phone, J.* FROM tblEmployee as E INNER JOIN tblJob as J ON E.EmployeeID=J.Employee WHERE  E.EmployeeID = " & me.cboEmployee
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.