Form that updates/adds to part of database (1 Viewer)

Sharkman1885 · Mar 28, 2018

Hey All,
Is there a way to make edits or partial updates to a database using forms? To give an example of what i am after, lets say a new croc is tagged. That croc would have a brand new entry since he/she was never tagged before. If a few months later the same croc is seen in a canal somewhere, I want to be able to add data into every table but Crocodile or ID for the given croc. I would also like the ability to go in and edit if a mistake is made but that may have to be done by going into the table and fixing the mistake there.

I have attached my database with little to no data as I wanted to make sure my entry form would work. the form is not very pretty (will work on aesthetics and tab order after I get the form working how I want) but it is functional. The form is entitled Crocodile. It lacks buttons and I think Ill end up using buttons to achieve what I want to achieve.

Links to my previous posts about this same database are below

As always thank you in advance for all your help

https://www.access-programmers.co.uk/forums/showthread.php?t=297322
https://www.access-programmers.co.uk/forums/showthread.php?t=298112

Beetle · Mar 28, 2018

Is there a way to make edits or partial updates to a database using forms?

The simple answer to your question is yes, that is the purpose of forms. The reason your Crocodile form is only allowing entry of new records is because you have the Data Entry property set to Yes. Correct that and you will be able to view/edit existing records as well as add new records.

However, the appropriate answer to your question is that you may be getting ahead of yourself because your table structure may be flawed. This has been mentioned by other responders in your other threads but I'm not sure if you have really addressed it yet. I get it, it's frustrating to feel like you're making design progress only to have someone tell you that you've made some foundational mistakes and you need to take a few steps backwards, but the reason that advice is given so often in this forum is because we know it is infinitely more frustrating to put a lot of time and effort into designing forms and reports, only to come to the eventual conclusion that you are going to have to scrap all that work because the table structure is wrong.

I know very little about Crocodiles, but it seems to me, at least on the surface, that some (or maybe all) of the fields in your ID table are simply attributes of the Crocodile. For example, can a single Croc have more than one of any of the following attributes?

S
DL
DR
PIT
CattleNum
CattleColor
CattleShape

The design of your form would suggest that the answer to this question is no, in which case these fields belong in the Crocodile table, but I can't say for sure.

I would ask the same about your Biology table. Obviously measurements like HeadLength, TailGirth, Weight, etc. can change over time, however, for any single Encounter with a Croc there would only be one value for each of these right? So shouldn't this just be an attribute of the Encounter?

These are the types of questions you should be asking yourself about the table structure here (I know you inherited this application) before you get too deep into designing user interface elements.

Sharkman1885 · Mar 29, 2018

Hey Beetle,
Thank you for the response and sorry for my late reply as I has having issues connecting to the forum's server this morning. Ill do my best to answer each of your questions about the design of the database and give reasons why they are the way that they are.

Ok your first question about the ID table. While yes the information in that table is what is used to ID a given crocodile, they theoretically can change (and in some cases have) I'll explain. First there is the scute (ridges on the tail) pattern (indicated by S, DL, and DR). When captured a few of the scutes are clipped to provide identification similar to a whale fluke. These scutes can be damaged in the wild, such as during mating. The PIT tag could potentially fail. The cattle tags can (and have) break off and have to be replaced if the croc is captured again. These identifiers are what is used to identify the croc in the wild from a distance or to confirm ID upon additional captures.

You are correct that the biological data does change with time and thus per encounter date, however to reduce duplication I decided to put the biology table separate. Ill explain. One encounter/location can have multiple crocodiles in one area at the same date and time, such as what happens during mating season or higher densities. However the biological information only goes to a single crocodile at that encounter. If the biological information was included on the encounter table then I would have duplicate encounters in this situation.

Please do correct me if I am wrong. As you can see I have been learning as I go and this forum has been a great help.

Sharkman1885 · Mar 29, 2018

Beetle,
Forgot to actually address my original question. Is there a way to have a button change the form to one which you can enter new information for part of it.

For instance we recapture a croc but have to update location and biological metrics as well as the SNAP reference. I wouldn't want to get rid of any of the original data, I would just want to add data to that croc. Doing this would allow a query to be run on the history of that croc

Beetle · Mar 29, 2018

Ok your first question about the ID table. While yes the information in that table is what is used to ID a given crocodile, they theoretically can change (and in some cases have)

So if that happens do you;

A. Need to keep the record with the old information that is now no longer valid and enter a new record with the new information.

-or-

B. Overwrite the existing record with the new information since the old info no longer applies.

If it's A, then you need the child table. If it's B, then I think these fields should just be in the Croc table.

You are correct that the biological data does change with time and thus per encounter date, however to reduce duplication I decided to put the biology table separate. Ill explain. One encounter/location can have multiple crocodiles in one area at the same date and time, such as what happens during mating season or higher densities. However the biological information only goes to a single crocodile at that encounter. If the biological information was included on the encounter table then I would have duplicate encounters in this situation.

Then you have a many-to-many relationship between Crocodiles and Encounters. A Croc can have one or many encounters over it's lifetime and an Encounter can involve one or many Crocs. This is handled by a junction table. You appear to have partially acheived this with your Biology table because you do have both CrocID and LocationID (from the Enounter table) in there, but you don't have a relationship between the Croc table and the Biology table. I don't exactly know how the data from your Other Info table is supposed to relate to the rest of it so I can't say if that should be merged into the junction table of not.

Is there a way to have a button change the form to one which you can enter new information for part of it. For instance we recapture a croc but have to update location and biological metrics as well as the SNAP reference. I wouldn't want to get rid of any of the original data, I would just want to add data to that croc. Doing this would allow a query to be run on the history of that croc

You don't need a button for this. You would normally use a subform. The main form would, for example, display information about the Croc and the subform would display all of the related data for that Croc. You can have multiple subforms if needed in order to display various different types of related data. Conversely, you could have another form that displays information about a given Encounter with a subform that displays info about all of the Crocs related to that Encounter.

Again, you appear to have partially grasped this concept with the forms that you have created, but these forms are not quite designed correctly so you can't really view or manage the data the way you need to.

Sharkman1885 · Mar 29, 2018

Hey Beetle,
For your first inquiry the answer is A. The reason is we want to be able to look back at the history if needed.

I thought that too originally, until it was pointed out to me that one croc can only be at one place at one time. though a place (encounter) can have many crocs at the same time. I thought this was the definition of a one-to-many relationship. A canal at point "A" can have crocs "X" and "Y" at the same place at the same time but croc"X" and croc "y" can only be in one place at one time. Croc "x" may move away from the area and only croc "y" remains at a different date and time. In the database everything should track back to the crocodile rather than the location. I dont know if I made that better or worse to understand.

The Other info is linked directly to the encounter and includes a reference number that is used to look up information in a separate database (which I have no control over) that table also includes handling reports (.doc) if an animal has to be handled.The reason it is in a one to many relationship is because there could be multiple calls about the same animal in the same location but each would be assigned their own reference number (this calling process was explained in a previous thread I had posted).

As far as viewing the data I have a form and a query that does the job rather well which was not included in the original posting but I have attached here. This database also includes some of the data types that will be entered. I have removed any sensitive information so there are some boxes to click through for the query to run.

I can also update a new croc using the forms I made in the blak database. I just dont know how to go about adding more data to a croc. Say one that has a red 20 tag. How do I populate part of a form with all the Red 20 information but have the encounter form and biological/other info forms blank.

Beetle · Mar 29, 2018

I thought that too originally, until it was pointed out to me that one croc can only be at one place at one time.

While that may be a true statement, it's incomplete logic and irrelevant to the design of your database. I'm going to make the assumption that, among other things, you need this database to do the following;

1) Keep track of all of the Crocs that you collect data on at a given Encounter (location).

2) Keep track of all of the Encounters you have with a given Croc.

The logic of "one place at one time" would only apply if you were only ever going to do one Encounter at one location at a single point in time, then never again have any other encounters, at any other locations or dates, with any of those Crocs. I assume that's not the case, and that it would more like the following scenario;

March: Go to location "A" and encounter Crocs X, Y and Z
April: Go to location "A" and encounter Crocs Y and Z
May: Go to location "B" and encounter Crocs W and X

Every Encounter has had multiple Crocs, and Crocs X, Y and Z have all had multiple Encounters. Many-to-Many.

Sharkman1885 · Mar 30, 2018

Your assumptions are indeed correct.

March: Go to location "A" and encounter Crocs X, Y and Z
April: Go to location "A" and encounter Crocs Y and Z
May: Go to location "B" and encounter Crocs W and X

Every Encounter has had multiple Crocs, and Crocs X, Y and Z have all had multiple Encounters. Many-to-Many.

I am confused with this explanation though. Each encounter also has a date associated with it on the table (which is why it is called encounter and not location) so even if multiple crocs show up at different times at one location that is still considered one encounter per croc. The issue comes with multiple crocs at one location at one time

Beetle · Mar 30, 2018

Do you ever record Croc data at different locations? If so, is it possible that you could see a given Croc at one location, then sometime in the future see that same Croc at a different location, and record a different set of data for that Croc at the new location?

Also, is it possible that you could see Croc X at location A, then sometime in the future go back to location A and not see Croc X, then sometime further in the future go back to location A and see Croc X again?

Sharkman1885 · Mar 30, 2018

Yes croc data is taken statewide. However even if say croc 5 is seen multiple times at location A, the dates of the croc being seen would be different. This would mean that even though the location would have multiple crocs over a given time period, there would be only one to very few at any given time. However croc 5 is mobile (all crocodilians, alligators and crocodiles, are) so it can be seen at multiple locations at various times. The database is built around individual croc rather than individual locations.

Beetle · Mar 30, 2018

Your last statement confirms what I have been saying. A Croc can have multiple Encounters and Encounters can have multiple Crocs. Even though the main purpose of the application may be Crocodile data, the Ecnounters/Locations are still key component of the data. I know that the majority of this thread has not addressed your original post, but that's because I'm trying to prevent you from wasting time and effort designing user interface elements that ultimately won't give you the desired results because the table structure is wrong.

If I get time later today, or this weekend, I'll post back a modified version of your most recent upload so you can see how this might work in practice. Maybe it will give you a clearer picture of what I'm talking about.

Sharkman1885 · Mar 30, 2018

Thanks Beetle! Sorry I have been having a bit of an issue grasping, or rather recognizing, some of the core concepts. Ill be able to check back and respond to your suggestions on Monday or later today.

Pat Hartman · Apr 1, 2018

Sharkman, You posted a different database on EE and I can't find the thread there. Either that or you have another open thread here and I am having a senior moment. Please PM me with a link to it so I can respond there also.

I started creating a form that will work better for you than what you started. Please take a look at it and see if it helps. The database opens to my version of your forms. It is a tabbed form. It is far from complete but it should give you an idea of how to proced. You have a number of tables and 1-m relationships and you can't really display it all on one form. The tab form allows a drill down method. There is only one search on the top and it is for CrocID so the form will open unfiltered but if you choose something from the combo, the form will show ONLY that croc's data.

Sharkman1885 · Apr 2, 2018

Pat,
That looks pretty close to what Im looking for. I am using the form more for updating the data or adding new data rather than looking up the data. I have another for that runs a query on the data in order to look up what is needed. That being said the croc ID lookup is exactly what I need in order to add more data to that croc. Thanks a bunch for this!!!

Pat Hartman · Apr 2, 2018

I cleaned it up a little more.
1. changed table names and definitions.
a.Removed allow ZLS
b. Removed defaults
c. Set FK's to required
d. Removed extraneous indexes.
2. Added the OtherInfo form
3. Fixed the Handling combo (I left the field as text because I don't know what you are loading but this field should probably be numeric.

I would not use attachments. The only advantage is that the documents are embedded in the database which makes them easier to distribute if you send the database to others but since the database should be split anyway, this isn't much of an advantage. Also the documents will bloat the database. I would add another table and that table should simply hold the name of the document. They can be presented as a list and in the double click event of the file name, use the FollowHyperlink method to open the doc.

Sharkman1885 · Apr 2, 2018

Pat,

thanks a ton!! I will look into the follow hyperlink method for the docs. Ill play around with stuff to get it to where I want and post what I have created for critique before adding it to the "live" database.

Sharkman1885 · Apr 2, 2018

Pat,

I am having trouble getting the Form to add data. I keep getting an error where the FK in a table is not updating properly. I have copied the tables and forms into a blank database to try and figure stuff out. Any advice on this?

Beetle · Apr 3, 2018

Didn't get a chance to post back a sample file yet but I see that Pat did based on your original table structure. If that example will work for you then I won't post so as not to confuse the issue for you. It looks like my conception of what you would need from a data entry standpoint may have been incorrect, but as long as you have a working solution to your problem so you can move on to the next step then that's what matters.

Pat Hartman · Apr 3, 2018

What form is not working and what error do you get? Please post the database you are working with since you are not using the one I uploaded.

Sharkman1885 · Apr 3, 2018

Pat,
When i try to enter a new croc I get an error saying that required information is not present in an underlying table. Basically says Im missing a FK. I have already gone through and made sure the relationships were correct. Also I am unsure where the "StateID" came from.I have attached the file with some data I attempted to enter as well as a SS of the error. I used the wizard to create the button on the parent form.

Sorry for the delay in responding. I will probably not get a chance to respond back until Monday next week as I have a work conference the rest of the week.

Form that updates/adds to part of database (1 Viewer)

Registered User.

Attachments

Duly Registered Boozer

Registered User.

Registered User.

Duly Registered Boozer

Registered User.

Attachments

Duly Registered Boozer

Registered User.

Duly Registered Boozer

Registered User.

Duly Registered Boozer

Registered User.

Super Moderator

Attachments

Registered User.

Super Moderator

Attachments

Registered User.

Registered User.

Duly Registered Boozer

Super Moderator

Registered User.

Attachments

Similar threads

Users who are viewing this thread