Solved Form actions

[Slap]

I have created a database (attached) in which I am having an issue.

In essence there are 4 types of forms
- Switchboard from which to launch actions
- data maintenance (frmAddAngler, frmAddFishType, frmAddPhoto & frmAddVenues)
- view data (frmViewCatches)
- add catch data (frmAddCatch)

I am only experiencing an issue with the last form listed (frmAddCatch), the others are working as intended.

In the problem form I wanted to be able to select various fields/records by utilising combo boxes and have the relevant FK be pushed into the related (bound) table (tblCatch), those combo boxes are shown below:

Although I have the combo boxes correctly selecting from the data tables, instead of pushing the correct values into my table they actually create a new record in each data table and post that value to my bound table.

Can someone explain what I have done wrong so I can get this working as expected?

Thanks in advance,
Tony

 

[Gasman]

I do not see that?
It appears to behave normally for me. I added one record. Albeit you need to sort out your foreign Keys.
You had the form set at Data Entry = Yes.
This is badly named, it means you can only add records. If I take that off, I get to see all records, all 9 of them. Mine being the 9th.

OK, I see what you mean. I believe it is because you have your joins the wrong way around.?

I have just changed Venues and Fish type. They should be the Parents of the FK in tblCatch.
Amend the others to suit.

You need to redo the query as well as that has the same structure.
Then you will get too many outer joins. I am not sure how to handle that other than to follow the advice from the error message.

 

[Slap]

Thanks Gasman, it was intentional that this form only allows data entry, I have a separate form for viewing/editing.

Your new entry has spawned a new entry in the venues table (ID=5), likewise in the FishType, Photo and Angler tables, which is exactly what has been happening to me also.

I want it to add the correct foreign key based on the combo box selection and not create new entries into the related tables.

Any ideas?

Also "sort out my foreign keys", I thought all that was working correctly and has also been reflected in the relationships?

Tony

 

[Gasman]

I changed the relationships as I understand how they should be, then used your form again.
It still created the extra records as you describe, because the query has the same structure as the old relationship.
You can change that form back to Data Entry then. I always used the same form and the * button. or if they insisted I gave them a dedicated button for a new record. Saved keeping two forms in line for validation then.

Tables Venue, fishType are parents of the tblCatch records and so should reflect that, not the other way around, as you had it.
As you enforced RI, I beleive Access created the extra records to obey that?

Not something I have ever encountered TBH.

Perhaps wait until an expert jumps in?

 

[Slap]

Tables Venue, fishType are parents of the tblCatch records and so should reflect that, not the other way around, as you had it.
As you enforced RI, I beleive Access created the extra records to obey that?

OK, I swapped those around, but I have not had any success in stopping the 'add new record' behaviour other than to trigger an error saying records did not exist in the parent table, but still not working as expected.

To recap I want to use a combo box for data entry and pull the related FK into my tblCatch based on the user friendly combo box selection.

It's only for personal use, so if necessary I'll just add manually into the table as required, but a form that works would be nicer

Thanks again,
Tony

 

[Gasman]

Yes, I know. I have done that countless times.
If you have the keys set up correctly and the direction the correct way, that is all you should need to do. Out and about in Southamoton atm, so on phone.
Did you do it in the query though, as that is the source for the form. Then you will get a join error, if you did.

 

[GaP42]

Re the AddCatch form : your source to which the combo is bound is incorrectly set:
for the combo FishName has row source FishTypeID and Fishname. The display is correct, however the source is set to FishName (a value in the query, but not in the tblCatch) - it must be set to the column FishTypeID in the tblCatch so that when the combo is changed it writes that value (the first column value in the combo list - the bound column value), to FishTypeID in tblCatch (make sure it is not to the FishTypeID in tblFISHType.

The same type of problem appears to hold true for each of the other combos

Your record source for the form is not the query but all the tables linked as per relationships. Not Needed - just use tblCatch and the combos, with changes suggested will still show the current selection of fish type, venue etc.

- based on Gasman's posted version of the database. When the above changes suggested here were done, extra/spurious rows were not created, the new catch data record appeared. Limited testing.

Good luck

 

[Pat Hartman]

1. I removed all the default join types. They were backwards.
2. I changed some of the data types.
3. I removed the 0 defaults for the FK's. Having 0 as the default for a FK is wrong.
4. I made at least one field besides the PK required for every table. Examine each table and think about what other fields should be required.
5. I removed all the indexes on the FK fields. Access automatically creates these indexes when you create relationships using the relationship window.
6. I changed AllowZeroLength to NO for all text fields.
7. I would not make the path a default string at the table level. I would create a separate table to hold the default path. That will make it easier to change should that be necessary.
8. For each bound form, the RecordSource query should only include the primary table. The combos will show the "text" fields.


I fixed frmCatch and frmAngler to add a combo to the header to search for specific records. This allows the form to open empty and prepared to add a new record. You MUST change the DataEntry property to NO for the other forms to make this work. Then add criteria to the query and a Requery to the click event of the "find" combo.

You need to fix the other three forms otherwise, you won't understand what I did to make them work.

The problem with your original setup is that you had the combos bound to the wrong field. Look carefully at the two forms I fixed. Notice that the bound field for a combo is the "ID" field which acts as the Foreign key. The display values come from the RowSource query. Your original forms had the combos bound to the field from the lookup table. That was why records were being added to those tables.

Remember, I only fixed TWO of the five forms.

 

[Slap]

@Gasman
@Pat Hartman
@GaP42

Thank you all for your help. GaP42's solution has worked as expected and my form now works correctly having corrected the relationships in reverse

Thanks again,
Tony

 

[Pat Hartman]

GaP42's solution has worked as expected and my form now works correctly having corrected the relationships in reverse

The problem that made the form not work was that the combos were bound to the wrong fields. You MUST fix that. There are a number of other things that also need fixing.

 

[Mike Krailo]

Pay attention to the additional items that Pat has advised you on, she is spot on. Especially the one table as the record source per form. The only reason I can see for using a query record source that joins multiple tables like you did originally is for viewing purposes only (no edit/add/delete). Usually the other table data is shown in a sub form, but if you are only viewing the records, it's OK to do the giant table joining query and show all the fields you want. I made the same exact mistake way back when I first got started into Access.

It looks like your fishing from beach/pier locations and not from a boat so my idea of marking Lat Longs of exact locations and times when the fish were caught might not be applicable in your case. Maybe add in data about the weather and water temps for that day would be useful. Data about bait type and/or lure type (with pics) might be useful.

Since this is just a personal database and not likely to run up a massive amount of records, I would have just used attachments for the pictures. Just a personal preference though.

 

[Pat Hartman]

Also, just in case I was unclear. Do NOT create separate forms for Adding/Editing/Viewing. Proper use of the main and subform's BeforeUpdate events and additionally, the BeforeInsert event of the subform to ensure that there is a record loaded in the main form will ensure that your Adds and Edits only allow valid data to be saved. You do NOT want to be writing the same validation code in three forms!!!! And even for a personal database, validation code is important to keep your data clean. If it is important enough to you to attempt to create a database to store it, then it is important enough to validate.

The method I showed in the two forms I modified allows you to easily accomplish all three functions with a single form provided you do the validation correctly and in the correct events. I didn't go as far as adding validation. I just made a couple of fields required. If you need help with validation, you can watch the videos I posted. The presentation to the user group is probably the most entertaining because of the interaction with the audience but Uncle Gizmo was a great help with the other two and asked interesting questions.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[Mike Krailo]

Do NOT create separate forms for Adding/Editing/Viewing.

I know Mike Wolfe uses one form for adding new records only, and one for doing all the rest. Its definitely a viable option. He pointed out some of his reasons for doing so here: 6 Reasons Why I No Longer Use Bound Forms to Add Records in Microsoft Access.

 

[Pat Hartman]

Let me address the points as I see them.
1. Yes, sometimes you allow users to initially save incomplete records but in that scenario, they might fill in the blanks incrementally so update ends up being like add. The data that is entered is validated as well as possible. The blanks are not. Usually, you would keep a flag on the record to indicate "complete" to ensure that only the edit form has to worry about missing data. Other processes can simply ignore the record. I know of no situation where value "a" is valid for an add but not for an update. In my experience it is only looseness on which columns are required that affects add vs edit rather than actual values.
2. Not sure what this is all about.
3. As long as you set the cycle property to "current record", Access doesn't move to the next record. It either stays on the last control or goes to the first control depending on a different setting.
4. True, if you want to guide a user through a complex form, you can build a special interface for adding.
5. I've never had to use SQL Server replication so I've never run into that bug. I am aware that @@Identity will return the wrong value when there are triggers involved that insert rows into other tables. The dates in the link were from 2011. One would hope that MS has found the time to fix Access since then. They have certainly had the time to add useless "features" and break things that used to work. This seems like a serious problem that should have been fixed decades ago. How hard could it be? The code should be localized in Access so there is probably only one line of code that needs to be changed to swap @@Identity to Scope_Identity(). How many lines of could would possibly be returned on a search for @@Identity??????
6. I don't want to ever have to duplicate validation logic. If you use two different forms, you need to isolate the logic so you can call a single procedure for each control. You might in some cases have two versions. One called by Add and one called by Update. If you actually duplicate the logic, you are creating a maintenance nightmare for yourself. Plus you are leaving the app open to anomalies because different rules are in place for adds than for edits. Very poor practice indeed.