updating records

[ariel81]

i have a form "A" with two textboxes called "txtName" & "txtResults"
i have a table "tblA" with two columns "PersonName" & "PersonResults"

upon the form is open the data "PersonName" & "PersonResults" is loaded into the textboxes respectively.

upon clicking the "OK" button inside the form, the "txtResult" values will be different. how can i update the values inside "txtResult" into the table "PersonResults"?

 

[ariel81]

hi,

refer to attachment (sample.zip):
i have a table "tbl Data Entry".

how can i update all the records in column "AgeYrsMthsDays" upon one click at the "OK" button?

e.g: user input a reference date at "Text18"...let's say 01/04/2007 (dd/mm/yyyy)
upon user click "OK" button, all the records in "AgeYrsMthsDays" will
be updated (the newly calculated yrs mths days will be stored inside
"AgesYrsMthsDays")

 

[stopher]

First of all you sort source out the source data for your form. At the moment you seem to have "tbl data entry" entered twice albeit once as an alias. It shouldn't cause an issue but get rid of the alias from your source so that there's no risk/confusion.

Next you can bind the the text16 and text20 boxes to the corresponding fields in your data source. Binding means that the data in your table will be displayed in your boxes and the data in your boxes will be stored in your table. You bind by setting the control source for each box to the corresponding field in your datasource. Note that you are leaving text18 unbound because you just want to use this to enter temporary info.

That's really it. You've done everything else. You can call up a record and the current data will be shown in text16 and text20. Then you enter your input date in text18 and click ok. This will run your code and stick the result in text20. The fact that text 20 is bound means that it will be saved "automatically".

All this aside, why are you storing this info. The reason I ask is that it looks like you want to store peoples ages. But this info can be calculated whenever needed (particularly if you make your code into a function). It's bad practice to store data that you can calculate at any time.

hth
Stopher

 

[ariel81]

actually when the user input a date in text18, the age (yr,mth,days) will be calculated upon user click "OK" button.

the reason for storing the calculated age (yr,mth,days) is because if i don't store this info then it will be troublesome for the user to click "OK" button everytime he/she wants to see the calculated age(yr,mth,days).

it will be more convient for the user if the whole record (yr,mth,days) will be calculated and stored "one-shot" with reference to an input date "text18". this will allow easy referencing for the user to scroll thru every person's age without clicking "OK" everytime.

how can i compute and store every persons' calculated age upon clicking "OK" once only? any reference codes or programs to do so?

 

[Bat17]

you can calculate the age on the fly in a query or in the form so it does not need saving. Are you wanting thier age at the time the data is stored or viewed?

Peter

 

[ariel81]

upon a user enter a reference date and clicked "OK" once.
all the calculated age for all persons will be updated in the "AgeYrsMthsDays" column of the table.

 

[Bat17]

I think that you are doing things the hard way

run the function on the fly and you wont need to click a button.
try this function

Function strAge(date1 As Date, date2 As Date) As String
    Dim y As Integer
    Dim M As Integer
    Dim d As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(date2), Month(date1), Day(date1))
    y = Year(date2) - Year(date1) + (Temp1 > date2)
    M = Month(date2) - Month(date1) - (12 * (Temp1 > date2))
    d = Day(date2) - Day(date1)
    If d < 0 Then
        M = M - 1
        d = Day(DateSerial(Year(date2), Month(date2), 0)) + d
    End If
    strAge = y & " years " & M & " months " & d & " days"
End Function

and add a call to it from a field in you form (or possibly in the underlying query if you are using a continous form)

=strAge([startdate],[enddate])

Change field names to suit

Peter

 

[ariel81]

thank you. however, i wanted something like this (below)

the purpose of the "OK" button is to allow user to enter a reference date and "OK".
What I needed is something like this: (refer to sample1.zip)

1. at the tab -B-
2. enter (a Ref. date) lets put in 01/04/2007
3. click "OK"
4. the calculated age is 36 Yrs 3Mths 9 Days based on the table for "apple" only.

however i wanted to compute the age for everyone in the table e.g: for apple, banana and durian too. this must be done "one-shot" upon entering the reference date and click "OK" once only.

 

[Rich]

the purpose of the "OK" button is to allow user to enter a reference date and "OK"

Then you only need RunCommandacCmd SaveRecord

 

[ariel81]

sorry i forgotten to upload the sample1.zip

Rich, i am new to ms access...can illistruste how to use that command?

 

[Rich]

In your buttons onClick Event
DoCmd.RunCommand acCmd SaveRecord
that's assuming you've taken the advice about not storing calculated values

 

[ariel81]

i want to save the computed age.
refer to sample1.zip
the result only compute for "apple"
i want to have result of all the persons ( banana, durian) as well.

 

[Bat17]

if you really want to do things the hard way then create an update query that will do what you want and save it. you can then run this from code to update the underlying table.

Peter

 

[ariel81]

how shall i do it? can show me how to do it which can produce the same result as i wanted? thank you.

 

[stopher]

What exactly is the relevance of the input date? Is it a specific date in time with some meaning e.g. a policy start date. Or is it today's date i.e. you want to calculate all the ages as they would be today?
Stopher

 

[Bat17]

using the function i gave earlier and assuming you have a field in tblA called DOB then something like this should work

Dim strSQL As String
Dim endDate As Date
DoCmd.SetWarnings False
endDate = Format(me.Text18, "MM/DD/YYYY")
strSQL = "UPDATE tblA SET tblA.PersonResults = strAge([dob],#" & endDate & "#);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

HTH

Peter