Solved

I need help using an unbound datasheet in Access 2013

Posted on 2014-12-24
4
533 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 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 84
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 84

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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