editing query data

[Roosters]

example of what i need to do
table
name time out time home total
ted - 2/2/02 10:00 - 2/2/02 20:00 - 10:00
bill - 3/2/02 12:00 - 3/2/02 13:00 - 1:00
ted - 3/2/02 04:00 - 3/2/02 15:00 - 11:00

query1 - returns todays staff hours of work
name time out time home total
bill - 3/2/02 12:00 - 3/2/02 13:00 - 1:00
ted - 3/2/02 04:00 - 3/2/02 15:00 - 11:00

Query2 - returns the same as above plus a calculated field with how much cumlative time worked in the previous 24hrs saying the current time and date is 3/2/02 16:00
name time out time home total 24hr check
bill - 3/2/02 12:00 - 3/2/02 13:00 - 1:00 - 1:00
ted - 3/2/02 04:00 - 3/2/02 15:00 - 10:00 - 15:00
ie. bill has 4 hrs worked yesterday from 16:00 till 20:00 and 11 hrs today.

the thing i need to do is have a form which displays query1 but includes the calc field from query2.the prob is i need to be able to edit the data from query1 not the calc field from query2 - my staff are only allowed to work a total of 16 hour in any 24hr period always starting from "Now()"

can i link a field from my form with its control source of query1 to the calc field in query2?

tried to explain the best i can hope someone knows what i am trying to do - thanks Phil

 

[KenReay]

Hi

Do you have a common column(s) between the two queries which would allow you to make a third query, joing query1 and query2? - from what you ahve said EmployeeId and DateWorked (or whatever you ahve called them) look like possibilities.

Anyway, if you can do that you could base you form on the third query, thus having any columns afrom Query1 and Query2 available. You can prevent user from editing a given column, but setting the locked property of the control on the form to true Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Roosters]

Thanks for your reply Ken

My problem is not preventing a user from editing, it is allowing them. query2 (the one with the 24hr checking field) is identical to query1 in all aspects except for the calc field. I actually have a totals query working out the time worked in the past 24hrs and use it in query2 to return the data i need - my problem is that all of that data can't be edited in my form. query1 can be edited.

query1 gets data direct from the table
query2 gets data via two other query's

i hope this helps

Phil

 

[KenReay]

Hi

OK, I see, so Query2 is not an updatable query

Have you tried my idea of a query joining you table and query2?, I suspect this will give you anoth non updatable query, but it is worth a check

(one) alternative is to put code on the oncurrent event of the form to look up the value you want from query2 and pop it into the control on the form Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Roosters]

Thanks Ken but no luck!

Sorry about the wait but the net went down at work.

Made a new query as you suggested using table and query2 but it is not updateable.

Query2 is perfect but not updateable - query1 is nearly perfect as it is updateable but it doesn't contain the calc 24hr check field.

If I point the control source of a field to a calc field in a parallel forms using query2 as the control source the result is a repeating of the first records value in the parallel form. I need to know how to return the value corresponding to the identical record in the parallel form.

I.e.

What I get is teds calc field repeated. I think as it is the first record returned query2
Name - <- &quot;other fields&quot; -> - 24 check
ted 2:00
bill 2:00
fred 2:00 etc

It should return

Name - <- &quot;other fields&quot; -> - 24 check
ted 2:00
bill 14:00
fred 8:00 etc

Any thoughts?


Thanks Phil.

 

[KenReay]

Hi

I am not sure that I am still on track here, but a thought. How about making a main from based on your table, this will be updatable. Make a second from containing only two controls, the employee id and the calculated 24 hour field.

On the 'main' form put a subform control, containing the second from and set the link fields to Employee id.

This should give you the appearance of a single form, with updatable columns for the 'table' columns, and a none updatable 24 calc field. You may need to include a requery of the subform contol in the after update event of the 'main' form, to cause the calculation to refresh

Sorry that is a bit complex to explain, but I think easier to actually do.

Good luck
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Roosters]

Thanks Ken but the problem I have is the number of staff I have out sometimes is up to three times the number that will fit on my screen and when I scroll down the 24hr check field won't marry up. It has me stumped.

 

[Roosters]

Thanks Ken but the problem I have is the number of staff I have out sometimes is up to three times the number that will fit on my screen, and when I scroll down the 24hr check field won't marry up. It has me stumped.

Phil

 

[KenReay]

Hi

If it is a subform, as I suggested, it will marry up. You cannot have a subform on a continuous form or a form in datasheet view however, is your form either of those? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Roosters]

Hi Ken

Yes, the form is in datasheet view as a subform on a main form.