Need help using a form with 3 tables (1 Viewer)

[markarmer]

I am trying to create a database to manage animals on my hobby farm. Three tables are relevant to this question:
1) Animals (Contains details about the animal)
2) Expenses (Contains bill/payment information)
3) Medical Records
What I am trying to do is create a form where in the first box I select the animal name then after that enter the expense information and details of the medical procedure.
This form will create a new record in expenses and medical which are associated with the animal in question based on the animal name which is pulled from the animals table.

Sounds simple but I have been going round and around in circles with this for days and am unable to create the correct relationships to make this happen.

Thank You in advance for anyone willing to take a look

 

[boblarson]

Well, I can't see a whole lot because it looks like you have 2010 and I currently am at work and can only use 2007 to try to view it. The table structure might not be the best and the forms - you would not use 3 tables on a single form. You would normally use one table for the main form and then the child tables would be subforms on the main form.

But like I said I can't get too far into it as it won't let me.

Also in the relationships window you don't select system tables (those with MSys at the beginning).

 

[markarmer]

Thank You Bob - The MsYs tables just showed up in there and I don't know how to get rid of them; I have not touched them in terms of linking though.

Regarding the other, I just spent 3 full hours trying to make it so selecting the name of an animal from a drop down allows me to input the medical info and expense detail but sadly to no avail. Pen & Paper sure are looking great again right now!!

 

[boblarson]

The main issue (besides not being completely normalized) is if you set up your forms, you need to make sure that you only include one table for each form (that is a general rule which can be broken at times but you need to know when and for you it is basically - the one table/one form rule).

So, you have the main form which would be the Animals table (I'm just going off of vague memory of looking at your database which I can't do again right now) and then you would have a form for medical info and a form for expense info and those would then be dragged and dropped on the main form and as long as you had AnimalID as primary key in the Animals table and as Foreign Key in the other tables Access would automatically set the master/child links to that field for you.

 

[thhui]

Could you save your db example as mdb 2000 format for testing?

 

[markarmer]

Bob, I tried this and it looked like it was working perfectly, however it is creating a new record in the Animals table each time as well as in the Expense & Medical tables.

Possibly something I have set wrong in the relationships?

 

[markarmer]

Thhui - I just tried saving the database in an earlier format ant Access refuses to allow me. Sorry

 

[markarmer]

Bob - When I look closer, it is the new Animal ID created when I select the animal that populates the Animal_ID field in Expenses and Medical tables, so, somehow I need the top dropdown to select an existing animal from the Animals table and put the corresponding Animal_ID into the new record created in Expenses and Medical.

 

[boblarson]

Thhui - I just tried saving the database in an earlier format ant Access refuses to allow me. Sorry

I know - it is because of specific items being used from 2010 which aren't available in any other version.

So, I will have to take a look when I get home. I was busy last night so I couldn't do it then. I apologize for the delay but perhaps someone with 2010 will chime in and you won't need to wait.

 

[markarmer]

Bob, no worries - I really appreciate any help you are able to give.
I just attached the database again, this one has the forms created as you suggested so you can see what I tried and hopefully where I went wrong

Thank You again
Mark

 

[boblarson]

Well, I was able to get in and take a look and the first thing up - you have lookups defined at table level, which is not good. See here for why. You should follow this and remove them. That would be the first place to start.

I have modified your database (there are quite a few changes. I didn't attack any of the reports because I need to go to bed soon. Check it out and see if it helps.

Some changes:

1. Modified some table fields
2. Modified some field names
3. Modified the relationships
4. Removed table level lookups
5. Modified some of the forms, including the medical one. The combo is now a search which brings you to the correct animal record so you can use the subforms.

and there may have been more. Hope this helps.

 

[markarmer]

Thank You very much Bob, I really appreciate you taking the time to look at this.

I am still a little stuck though as while the 'Animal Name' dropdown on the Medical form finds related records in existing Medical & Expense entries, the idea of this page is for adding a new entry, for example when the vet has visited I need to choose the animal then type in what he did and the payment info.

The problem is that now when I go to create a new record, when I choose an animal in the drop down I get a VB Run time 3021 error stating 'No current record' - If it helps, the animal ID box next to the drop down remains at (New) rather than changing to the ID of the animal just selected.

 

[boblarson]

1. The drop down on the medical form is just to select the animal you want for the medical subforms. Since the main form is bound to the Animals table it makes it easy to just use that so that the ID is connected to the two medical subforms.

If it helps, the animal ID box next to the drop down remains at (New) rather than changing to the ID of the animal just selected.

2. You do not want to create a new record in the main form. In fact, I should have removed the record navigation from it and left the record navigation on just the subforms.

3. Since you didn't have the names in the table for any of them but Fred, I made it so that if the name was null it would put the animals_ID number in as the display so that there weren't blank lines in the combo.

 

[markarmer]

Bob,
That was it - My mistake!!

Thank you so very much for your assistance; I was able to duplicate what you showed me to create other forms & lookups tonight and think I can take it from here getting all of my data entry forms & tables setup correctly.

Reporting might be another issue when I get that far, but for now I am all set

Thank You again

Mark

 

[markarmer]

Bob,
You have done enough already for which I am VERY grateful so if this is asking too much please say so, otherwise, there are two last things I am stuck with at the development stage of my database....

I am attaching a copy of my updated database (I had to zip it up to meet forum requirements) with all the new forms & things included, when I run the anylizer tools in access there are no warnings but what I would like to achieve is...

1) I have a batch named "Milk Cows", currently we only have one milk cow named Dolly but her daughter 'Daisy' will be giving us milk soon and potentially we will have one or two more that I would like to add to this batch.

What I need to try and do here is, have a SIMPLE form whereby on a daily basis whoever milks the cow(s) can select the name of the cow, put in the amount of milk, a note if necessary and that is it (the batch is automatically assigned based on the form) - This part I can do, but what I can't get is that when they choose the cows name from a drop down it is pulled from a menu of only cows in the 'Milk Cows' batch (not all of the animals) so the form only displays cows in the 'Milk Cows' batch, and after doing so data is saved such that I will be able to develop a report based not only on 'Milk Cows' every day but also by each animal within that batch so we will know how much each cow gives us and how much the batch gives us each day.

2) Chickens - Whether meat birds or laying ones they are assigned to batches. What I need to do is, when I buy food which comes in 50lb or 80lb bags, be able to break down the cost of that food so the initial expense is assigned to the batch, but then somehow the cost is divided by the amount of birds in that batch and their share is applied to the costs of the individual in that batch so I can report on both how much each batch cost in relation to the meat or eggs I get back, and how much each animal in the batch costs in terms of food intake.

(The same principle applies to my cows, sheep and other batches but I hope to be able to learn what you do and apply it to additional forms)

As I said, you have done enough so please don't take this on if you don't have the time but if you are willing it sure will be apppreciated and as you are only up in Oregon I will gladly send you some meat if you would like to compensate your time.

Regards

Mark

 

[boblarson]

Mark:

I'll try to take a look tonight when I get home (my 2010 installation is at home).

 

[markarmer]

Thank you so very much bob

 

[boblarson]

Not enough time to tackle all of this but I've started looking at it. I want to know something -

In your Milk Production table you have a field "Milk Cow" and then also "BatchID" and "AnimalID." I think you only need one of those in there. Since an animal is identified as being part of a certain batch in the animals table and the name is there too, all you need to store is the AnimalID.

To limit the animal selection for that form you can use a query:

SELECT Animals.Animals_ID, Animals.Animal_Name
FROM Animals
WHERE (((Animals.Animal_Type)=2))
ORDER BY Animals.Animal_Name;

Animal_Type is 2 for Milk Cows.

The costing for the Chicken Feed will take a bit to do so hopefully I'll have more time tomorrow night.

 

[markarmer]

Hi Bob,
I don't fully understand (again, I am JUST learning Access) BUT I think you are correct... meaning, I will only ever have one batch of "Milk Cow" and currently there are only 2 cows that fit in there (there will be more) but yes, they will each have their own identity in the "Animal_ID" as each year when we get a calf I will need to add her to animals table in order to keep track of expenses and medical records.

My goal here with this form is simply so we don't have to identify each day that it is indeed a cow (not a chicken or any other batch), don't need to remember a numerical ID and can simply pull a name from a drop down based on what is in the "Milk Cow" batch as we are not a real farm per se (it is a hobby farm) and I will never go beyond 6 cows and even that is stretching it based on the land we have.

My reason for seperating though is that when it comes to reporting I am able to know easily how much milk we got each week/month/year & which cows in the "Milk Cow" batch gave us what individually so ultimately I will be able to break down costs between them and see what I am getting in return for the food we give them.

It's nearly midnight now but I will take a look at the query you sent tomorrow as it looks like it might answer all of the above, just sending this to clear it up incase I didn't explain very well in my last post.

This is more complex than my egg production as that is entirely batch based and my chickens don't require individual recognition for their ouput but if you look at the form for entering their daily production I am hoping to have something just as simple for milk data input but based on the cows name rather than a batch, where the batch info is hidden but still stored, just saved on a drop down of the actual name rather than Animal_ID.

Thank You again

Mark

 

[markarmer]

Hi Bob,
I had to make an unplanned trip with work and haven't been able to do any more with my database however I will be back Friday morning and hope to get all of my data entry forms & tables completed tomorrow & over the weekend.

If you are able to spend a few minutes on this tonight or tomorrow night it would be an awesome help for me if I can get the two issues I am having problems with figured out - No worries if you are unable though, your help this far has been amazing.

Thank You

Mark