Solved

I need help using an unbound datasheet in Access 2013

Posted on 2014-12-24
4
497 Views
Last Modified: 2014-12-28
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
0
Comment
Question by:mainrotor
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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
 
LVL 84
Comment Utility
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
 

Author Comment

by:mainrotor
Comment Utility
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now