struggling to grasp the concept...

[geepee68]

Hi, 

i am attempting an access DB for a dog rescue. i have a table for people, one for dogs and one for status' (i.e. been surrendered, been fostered, been adopted).

in my dog table i have FK for the person and the dog's current status. 

i would like to keep a record of any changes to a dog's status, i.e. when they're fostered (and by whom) and then when they're adopted (and by whom). i created another table (statusUpdate) that has FKs for the dog, the person and the status and i also have a date field.

my issue is that i don't know how to update both the statusUpdate table as well as the dog table to reflect what the current status is and who the dog is with. 

i'm pretty sure it's a case of me not grasping a pretty simple concept. any assistance would be greatly appreciated.

regards,

g

[#2]

If you have a status update table, you no longer need the current status and current person fields in the dog table. If you want to know them, you can look them up in the status record of the dog in question with the highest date.

[#3]

@XPS35 

thanks. so i currently use a form to add people and another to add dogs. i take it that i would then need another entry form to add the link between the person and the dog and the dog's status?

i'm trying to make data entry as simple as possible as the people likely to use the DB are not the most computer literate. 🙂

thanks again,

g

[#4]

It is usual to use a subform in such a case. You can choose to create a status subform for the dogs form. Fill in the status and the person on the subform. It is also possible to create a subform for person. The dog approach seems the most logical to me.

[#5]

thanks. appreciate your time.
g

[#6]

my apologies for all the questions...
as the dog form is a data entry form (the dog is not in the DB yet) how would i go about adding the dog to the DB as well as it's status and the person responsible for the dog? would i need to do that as two distinct forms and actions, so add the dog and then open another form to add the status and person?
regards,
g

[#7]

@geepee68

If you enter a new dog on the form for the dog, you can immediately enter the first status on the subform. If the person is also new, it is better to enter this in advance. Then you can immediately choose the person when entering the status.

I've put together a very simple sample database to demonstrate those principles. See attached file.

[#8]

thanks again... i must owe you!!!
i'm having problems with the subform, if i run it by itself i get the dropdowns with the status options and the list of people in the DB, however when i drop the subform onto the addDog form i don't get those???
g

[#9]

Sorry I don't understand your question.

[#10]

The file you sent works fine. the subform i've created works when i open it as a form by itself. when i put the subform into the main dogform then i don't get the options in the combo boxes (nothing shows up in the status combo or the person combo). if i add a record into the dogStatus table then those options appear but not when i'm trying to add a new record.

[#11]

It's hard to tell what the problem is without knowing exactly what you're doing. There are many settings where things can go wrong. Can you share the file?

[#12]

@XPS35 

thanks again.

[#13]

On your form addDogF the AllowEdits property is set to No. This means you cannot edit anything on the mainform nor on the subform. Set it to Yes and you can add/edit values on the subform.

[#14]

thank you very much. is there a way that i can stop a user overwriting a record by mistake?

[#15]

That seems difficult to me. How is Access supposed to know the difference between an intended and unintended change?

[#16]

very true. thank you very much for your help.

[#17]

hi again, the struggles are real! i am now attempting to record (using a form) a list of committee members. i have 3 tables 1 - people 2 - committee roles 3 - committee the committee table has foreign keys for people and roles and also records a commencement date and a ceased date. ideally i would like a combo box that has the roles, displays who is currently in that role (when they commenced) and allows the ability to assign a ceased date when they leave the role. a person would be able to hold more than one role. with my current attempts the person doesn't change when the position is selected in the combo. i've also had problems adding roles to a person who has an existing role, the table just updates their role rather than adding another record. FML... any tips would be greatly received. regards, g

[#18]

You need at least one more table. A committee has multiple people and a person can work in more than one committee. So you need a junction table (people-committee) with (so far) two foreign keys (no foreign key to people in the committee table).
A person has a role in a committee. Someone else can have the same role in an other committee. A person can have (different) roles in the committees he is in. Bottom line is: role is an attribute (fk) in people-committee (no fk to role in committee). I assume a person has only ONE role in a committees.

[#19]

thanks. there is only one committee, there is the possibility that a person may hold more than one role in the committee. it's only a small non-profit organisation.

[#20]

also, is there a way that i can have the 'isActive' field changed from yes to no when a ceaseDate is added to the persons role. e.g. when a committee member resigns i would enter the date of that as their ceaseDate which would mean that their position was now available for someone else to perform. i was trying to use the isActive field to show only those roles that no one is currently performing on the form.