form will not update query and/or tables associated with it

[kattz]

I have a payroll form which is based on a query which is based on a number of tables.
In the form i have calculated controls and bound controls where i enter the time card data.
My table/query only has the bound control data - not the calculated control values. I need to create a number of different reports with these values and i can't do this at the moment. I have tried a number of things such as changing the fields etc but nothing has worked. As the expressions are quite complicated - i don't want to put them into the query, why won't the query update with the values?
i don't know anything about SQL programming so things need t be kept as simple as possible

 

[blom0344]

The query is the source for the form isn't it? The query itself does not physically exist, it is nothing more than the outcome of the SQL over the tables. (A select query) Why do you expect that the query itself would be updated this way (or the underlying tables)?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[kattz]

Hi T Blom

I realise that now but I was hoping it would work - I have another data base and that works but it isn't based on a query just the table.

Can you suggest anything - I have an employee data base with all current and terminated employees + time card table + trip table. I set up a query because i had to only have current employees for the payroll but also not all current employees.

Can I set up a table based on the query and then base the form on the table - would that work? With the data I need base hours, total of time half double time etc. This I then have to send to our payroll divisions. I then need to do certain reports for our records and our employees based on some of the data. There are a lot of formulas invovled as there are a lot of variables

Or can I write an event procedure to update the underlying query?

Any help you can give me would be appreciated

 

[blom0344]

I think that this may be a case to use some VBA/SQL statements to update data in the sources based on the form values.

In terminology terms ,one cannot update a query, but one can issue an update query that updates the underlying sources. A very good piece of documentation is here:

http://www.fontstuff.com/access/acctut15.htm

Alternatively you could create a make-table query that creates a new table from your sources and base the form on this newly created table. You do not need any VBA here, just creating a macro with the correct events will do the trick. Place the macro under an action button or on a switchboard and you are there....
Personally I would try to get as much as intelligence in the make-table query, cause using complex calculationss in Access reports is not Access'forte.

I life by SQL, so if you any assistance I'll help you out there ....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[kattz]

T Blom

Thanks for your help but I realised it would be easier to do all the different reports I need to do and put the expressions in there (I found out this is what I actually did in the other database - it didn't update tables either - I was looking at the wrong column!!)

Anyway, I have written an event to clear the controls on my form to be ready for new data. This only does it per page

I have 65 records - 1 for each employee that I do payroll for. I have used an expression
[mon start]=""
to clear the start time for monday. I have done this for all the text boxes I need clearing.

This clears that particular box but then I go to the next employee, I have to press the command button again. What can I put in to ensure it clears all the employees data? The Form is called Payroll Form

Also if I put expressions into a query, can I use the same format as in the form?

For example, I have expression in the form:
=IIf([fri cal]>=0,7.6,IIf([fri cal]<0,(7.6+[fri cal])))

Can I copy this directly into the query so that when I do reports I don't have to put in expressions all the time

thanks for your time.

 

[blom0344]

Using the IIf should be no problem.

Anyway using expressions in queries is more efficient than anywhere else.

The other problem is a bit confusing. What do you mean 'only per page' Are you using the form to DISPLAY data or for DATA-ENTRY ?

For a service application I build I use a form for data-entry and one for displaying existing data. I use a button on both forms to switch between forms....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[kattz]

It's for data entry - one record per person.

When I open up the form I see the form for the first person, i enter the data necessary then go onto the next person (via the little arrows down the bottom of the screen which also tells me I have 65 records)

In the form I have text boxes where I enter start and finish time, then I have calculated controls which work out overtime etc. I need to clear the times I enter for all the records not just the persons whose page I am seeing. At this stage I have to go to each record and press the clear button

I don't need to have another form to display the data because I have to print off various reports and email reports to various people.

I have put the expressions in the query and it works beautifully, doing just what I want it to do. I knew it was something so simple that i wasn't seeing!!

If you could just help with this last little thing, I should be able to finish it before I go on leave. It's weird isn't it, I'm doing all of this hard 'stuff' to make it easier for another person to do my job !!

 

[blom0344]

I think there is just the form, once you navigate to the next record the form is loaded with the data belonging to the next person. It is not the next page loading, just new data loaded into the form. So , if the underlying source has the time fields filled for a person that data will be loaded into the form....

You can clear all these fields in one stroke by running an update query against the underlying table that clears the time fields, I guess.

Steps:

1. Create the update query against the source-table, emptying the time fields.
2. Create a macro that triggers the update query
3. Assign the macro to the action button.

This way of course all time fields will then be cleared, in all records..........

I am assuming here that you are modifying existing records, which puzzles me all the same

T. Blom
Information analyst
tbl@shimano-eu.com

 

[kattz]

thanks T

I actually don't have to keep track of all the records at least on the computer - this is done by payroll in Wagga who actually do the pays.I just give them the data they need via a spreadsheet and print reports for various people and then just keep copy of spreadsheet in a folder with original time cards and trip advices (I work for Toll Vehicle Transport). So that's why I can modify existing records - i don't need them.

Thanks for all your help, I have finally got something I can really work with.

It has taken up a lot of my time but it is something I really enjoy doing. I am planning on doing an advanced Access Course so that should be good.

I have had to set up everything from scratch. When I started this role, the computer was only used for word processing. I have enjoyed setting up different databases for all the differnt things I have to do. I have saved so much time in the long run. My employee database has been taken up by other states as well as they didn't have anything - Go figure!!

Anyway, as an Information Analyst, what do you actually do? How did you get into the role? Which state are you from? I would like to do something different than what I am doing because I'm happiest when I have a problem to solve on the computer. At the moment I deal with Occupational Health and Safety which is OK but so much paperwork and bulls#@t I am so sick of it.

You're probably sick of hearing me go on, so I'll go now and get some work done.

thanks again
Kattz

 

[blom0344]

I am actually more a database specialist, but what's in a name.....
Working with access is actually a byproduct for me. Most of the time I am creating datamarts and using BI tools to help create or create reports. I am more familiar with real databases like ORACLE/DB2/SQL server, but it is nice to play around with Access once in a while.

A very useful link for getting snippets on access is this:

http://www.rogersaccesslibrary.com

If you want to localize me, picture Europe,Netherlands....

Why not venture a career in datawarehousing?

T. Blom
Information analyst
tbl@shimano-eu.com