Solved

I need help using an unbound datasheet in Access 2013

Posted on 2014-12-24
4
570 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 250 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 250 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

728 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