asked on

Populate unbound subform with crosstab query

Experts,

I have a two-tier access database with a timesheet entry form with a subform that shows all timesheets already submitted for the current Emp.

The sub form Data Source is the sql query below, but this setup keeps the back end open while the form is open on any client

TRANSFORM Sum(tbl_TS_Hours.Hours) AS SumOfHours
SELECT tbl_TS_Hours.ConsultantID, tbl_TS_Hours.WeekEnding
FROM tbl_MasterLocal INNER JOIN tbl_TS_Hours ON tbl_MasterLocal.[wContractor ID] = tbl_TS_Hours.ConsultantID
GROUP BY tbl_TS_Hours.ConsultantID, tbl_TS_Hours.WeekEnding
PIVOT Format([WorkDt],"ddd");

Open in new window

I'd prefer to have the sub form unbound, and when a user submits a new timesheet (cmd button), requery the subform to show the new timesheet added to the query.

I have the Access 2010 VBA Programmer's reference...I just need help with what method to use. Would I use VBA to execute the above sql query with DoCmd.RunSql, object.execute, or use a Recordset object?

Thanks

Jim Dettman (EE MVE)

<<The sub form Data Source is the sql query below, but this setup keeps the back end open while the form is open on any client >>

That's not a bad thing. In fact, you want it to stay open. Repeatedly closing and opening the BE has a lot of overhead (I'm assuming JET/ACE here and not SQL Server or something else).

Developers actually add code to open a connection to the BE at the start of the app and hold that until the app closes.

Jim.

Davisro

ASKER

Interesting. The db I inherited was set up as all unbound forms with only the updating of records opening the back end, so I've been trying to remain true to that thinking that is was easier to update the backend if the mere having a form open on any user machine would lock the file because people could leave the form open all day, leave their computer, etc.

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial