Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 8th, 2006, 10:48 AM
Registered User
 
Join Date: Feb 2006
Location: , Ohio, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Linking a query to a form

I have hopefully a simple question, but I have not found a good answer to it yet. I have a time tracking database, fairly simplistic, I just need to keep track of employees hours on a weekly basis. These hours need to be approved by the dept. head before payment can be sent. I have an approval form that lists userID total time for the week and a check box to approve or disapprove the time. The data should be pulled from a table containing the same information along with the week ending date, the users straight time and over time. I have a query written that is based off of the week ending date that will pull the appropriate data. What I can not figure out how to do is to get the query data to display in the form first, and second allow me to update the table once the time is approved or disapproved. I'm not looking for a solution, just a little guidance in how to get there. Currently everything is in a plain 2003 database, but can be changed if the desired outcome needs to be implemented in a different format.

Thanks in advance!

  #2 (permalink)  
Old February 8th, 2006, 12:48 PM
Authorized User
 
Join Date: Jan 2006
Location: lagos, lagos, Nigeria.
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

HI,
CREATE A FORM,THAT HAS THE NAME OF THE HEAD OF DEPT AND DEPT NAME.
SECONDLY CREATE A SUBFORM,THAT HAS THE DATA AND FIELDS FROM YOUR QUERY(REPRODUCE THE SAME TABLE OF YOUR QUERY HAS A SUB FORM.

NOW U WILL HAVE 2 TABLES,THE MAIN FORM,AND THE SUBFORM(WHICH IS THE SAME HAS THE QUERY U SPOKE OF),THE MAIN FORM WILL HAVE A ONE TO MANY RELATIONSHIP WITH YOUR SUB/CHILD FORM.


IN THE MAIN FORM,ON THE AFTER UPDATE EVENT OF THE FIELD FOR THE DEPARTMENT NAME,PLACE AN INSERT QUERY IN THE AFTER UPDATE EVENT,THE CRITERIA OF THE INSERT QUERY WILL BE(QUERYTABLE.DEPTNAME=FORMS![DEPTNAME]

THAT IS U WANT THE SYSTEM,TO INSERT INTO THE SUBFORM,DETAILS FROM YOUR QUERY TABLE,WHERE THE DEPT NAME IN THE MAIN FORM=DEPTNAME IN THE UNDERLYING QUERY.

TAKE NOTE,THE SUBFORM MUST BE IN DATASHEET VIEW.

HOPE TO HEAR FROM U.

  #3 (permalink)  
Old February 9th, 2006, 10:55 AM
Registered User
 
Join Date: Feb 2006
Location: , Ohio, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sola,

Thanks for the advice. I can get the subform to show up correctly and technically it will work, but it's not the exact result that I was looking for. Maybe I'm approaching this from the wrong angle.

The form has a text box for entering the week ending date, basing the week ending off of Saturday. Once the user enters the week ending date I want to pull all of the records with that week ending date into my form to update with an approve check or disapprove check. I began with a query because I could pull the data correctly that way. Is there a better way to pull data into a form rather than a query?

The following is my current layout

Table is Time
Primary Key is ID
UserId
Week Ending
Straight
Over
Total
Approve
Dissapprove

The form is laid out as follows

Week Ending
UserID Total Approve Disapprove
UserID Total Approve Disapprove

Any advice would be greatly appreciated.

TIA

  #4 (permalink)  
Old February 24th, 2006, 06:09 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Steve,
This post is related to your last one, right? You may not want a form/subform structure. What are your underlying tables and what fields are in them?

Loralee



Similar Threads
Thread Thread Starter Forum Replies Last Post
Sub Form Query Brendan Bartley Access 2 September 27th, 2006 09:00 AM
linking form linh Access 3 February 9th, 2006 09:26 AM
Query from Form vemaju Access 7 September 15th, 2005 10:43 AM
Linking VB 6 form and Access table takawera VB Databases Basics 1 October 15th, 2004 09:12 PM
Linking form and a table that are build separately takawera Access VBA 2 October 8th, 2003 10:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.