Update A field in a table upon clicking a button (1 Viewer)

[karlreiner]

Hi All,

I am fairly new to Access and is having a hard time achieving the things below:

I have two Tables:

Daily Log
Volunteer List

1 form that logs user service times.

Upon clicking a button in the form I need the following to happen:

1) look in the "Daily Log" Table for [Volunteers] that have log a row with [Date] within the last 6 months
Note: The Volunteer Column is only a look up, so the data in there is actually equivalent to Volunteer_ID in the Volunteer List. Which means it is simply numeric.
2) go to the "Volunteer List" Table and update the [Inactive/Active]
a. For those volunteers that are filtered in 1), return "active" in [Inactive/Active]
b. For those that are not, return "inactive" in [Inactive/Active]

Please help

 

[stopher]

Welcome to the forum.

First of all you should not use lookups in tables. It will give you severe headaches at some point. You can still use lookups in your forms though.

Second, what you are asking for here is essentially derived (or calculated) data. As a general rule you should not store calculated data in your database. Instead you should use a query to provide the data as required.

Here's a query that will give you the "status" of your volunteers (I've assumed that 6 months is 183 days):

SELECT [Volunteer List].[Last Name], IIf(Count([Daily Log].[Date])>0,"Active","Inactive") AS Status
FROM [Volunteer List] LEFT JOIN [Daily Log] ON [Volunteer List].ID = [Daily Log].Volunteer
WHERE ((([Daily Log].Date)>=Date()-183 Or ([Daily Log].Date) Is Null))
GROUP BY [Volunteer List].[Last Name]

hth
Chris

 

[karlreiner]

Hi Chris,

Thank you for this information. I checked it out and it gave a result of the statuses of my volunteers.

What would be the best way to update something in the tables? In my case, the volunteers will log their time entry in one form. The system then needs to update their status to active if they have logged in within the past 6 months.

Example: I have 3 volunteers.

A - Active last logged Nov 1
B - Active last logged May 26
C - Inactive Last logged Febuary 12

Volunteer C then logges a time for Nov 27. I need the system to update her status to active again since she logged in. And updates the status of Volunteer B to inactive since she has not logged in for the past 6 months.

Is this possible in Access?

 

[stopher]

It's possible but not recommended. You could do it by changing the query I gave to an Update query and then have the button run the update query.

But I must stress that this is bad practice and goes against the rules of normalisation. There is simple no need to store "status" when you can easily derive it whenever it is needed. You can use a similar function in a form or report so the user will always know the correct status.

Chris

 

[smig]

yes it's can be done easily, but I will go with stopher here and won't advise you to.
what will make this into an "inactive" again after 6 months ?
it's better to use a query when you need the status.
if you need it on a form, for a specific user you can also calculate it "on the fly"

but if you insist doing it
add the Active/Inactive field to your form (you can hide it) and use the Login button OnClick event to change the field data.
the code will look similar to:

me.myActiveFieldName = "active"

or using the explicit writing:
Forms![MyFormName]![myActiveFieldName] = "active"


calculating on the fly:
me.myActiveFieldName = IIf((me.[Daily Log]) >= Date()-183) Or (IsNull(me.[Daily Log])), "Inactive", "Active")