Need a quick solution for MS Access

[cbman]

I have a need to add two entries (numeric data) on a form and store the data into the same table in a totals column.

So far I can get the total to show on the form but storing it in the table is eluding me...

Any Clues?

As well I have to do something similar and check one entry...

If its JKA, SKA, or 1,2,3,4,5,6 it makes another spot =1
If its 7,8,9,10,11,12,13 it makes the spot=1.5

Getting it to work is driving me nuts because I know there is an easy way to get this to work... but it just won't work

[mdhome]

Hello CB
What sort of problems/error messages are you having?
Assuming you have a form field bound to the totals field in the table, then writing the total value to this field and doing a refresh should work.


regards MD

[cbman]

Hey mdhome.

Its kinda interesting..

I need to calculate the value of two fields on a form from a single table and put the result into the form as well as into a seperate spot on the same table.

In my case for this database its AM Kilometres, PM Kilometres, and Total Kilometres.

I can calculate the sum of AM-Kilometres and PM Kilometres.. but I can't get it to go into the the table at total Kilometres...

Its driving me nuts because my guess is there is a simple solution that I can't think of at the moment.

[mdhome]

Hello CB.
Its been some time since I used Access and I am a little rusty. There are also many possible ways to do this. You could try putting some code into the after_update event for both the AM and PM fields. This would allow you to do the validation and write the value to the totals fields (I assume the totals field is visible on the form). Failing that could use this method to run an update query against the table. There are other ways but they escape me at present.

Just a question. Is the totals field visible on your form?


regards MD

[cbman]

Yes it is...

That is part of my problem.. I can use a calculated field to calculate the total of AM+PM... but it only displays the result and will not store the result in the table...

I have the same problem with the Query... I can Calculate the total but can not transfer it into the table.

Oh and just so you know... I don't know much about SQL programming...

And for everyone else... in case you were wondering.. I hate access...

[Wombat]

Hating Access is a good thing.

Try something like

INSERT INTO table
SELECT (am + pm) as total
FROM table

[cbman]

Will do Wombat...

I should really find my notes.. but from what I remember... I slept through most of those classes

[mdhome]

cb
how big is your database? If it is small enough then you can email me a copy and I will have a look for you?


regards MD

[Fat Tone]

I fell at this hurdle once before, so I just calculated stuff for every form/query/report that used the data.

If you get a result, please let us all know.

T.

[cbman]

mdhome... I would send this to you but the problem is it already contains the complete local school bus barns private records on each of the 1000 and some odd school kids in the area.

When I get to work I will see about posting some screenshot veiws of the form and of the design layout for the table.

Oh and anyone know of how to catch the date and input it into a table but then lock that so that it isn't overwritten next time you view that form... Just so that I can keep track of when the kid was input into the database.

[mdhome]

Originally posted by cbman

Oh and anyone know of how to catch the date and input it into a table but then lock that so that it isn't overwritten next time you view that form... Just so that I can keep track of when the kid was input into the database.

Use the date () or now() functions and check for a null or empty value in the date field. If it is blank then update else ignore.

regards MD

[cbman]

If only there was a smilie smacking his head like an idiot...

I completely forgot about the null value

This should make a few other things easier.

Its just so unfortunate that the only person at the company that knows Access is me and I have to teach the basics of it if someone wants lessons.

If ever I goto hell... I can see Satan making me teach Access to get cheap thrills.

[mdhome]

if only_person_in_company_who_knows_access then
if you_go_to_hell then
satan_smacks_bum
end if

else
retain_composure

end if