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

 

[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

 

[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.

 

[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.