Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need help using an unbound datasheet in Access 2013

Posted on 2014-12-24
4
Medium Priority
?
616 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 40516568
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 85
ID: 40518312
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
ID: 40520191
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 40520321
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

610 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