Spurious row in database

[jledda]

Folks, hi. Hope you can help.

I have a form which displays a filtered set of data based on a FamilyID field (i.e. I have a family Table and I'm displaying Tax Return data specific to a member of that family). When I click the new record button on the form, I get a blank form page, with a Family ID of 0.

I don't have a family member with that ID, but the table 'Family' has a default for Family ID of 0.

Now, I think I understand why this occurs (something to do with the data set being filtered?).

I've subsequently written some code to insert the record into the database with the correct FamilyID, but I've still got this "duplicate" record with FamilyID=0.

Just wondered if anyone can a) explain what's going on and b) get around this "feature".

Thanks to all who contribute a solution.

Kind Regards
John

 

[pr2-eugin]

Hello John, Welcome to AWF..

This "feature" suggests that there is something wrong with your design, Is your relationships defined properly? If so are you using Main form/Sub form style? Because, if you have used it you do not have to use any filter, it will automatically filter based on the relationship..

 

[jledda]

Thanks for your feedback.

Always possible that design is wrong, but don't thinks so. Here's a bit more info:

I've got a "family" table, with several fields, but for the purposes of this, let's say two fields, FamilyID and Name. There are 5 rows of data (FamilyID=1 is me and the others are members of my family).

My main form just displays the family member's Name (FamilyID is a hidden field on the form).

I've got a "tax" table. Again, for the purposes of this exercise, it has two fields, FamilyID and TaxDistrict.

The sub-form simply shows the tax info (different data depending on the FamilyID). There is also a button on the sub-form called Tax Returns.

I've got a "tax return" table with, say, two fields, FamilyID and TaxCode. There can be several TaxCode values (i.e. several records for each FamilyID.

When I press the button, a new form appears. It displays the data for that particular FamilyID (so that bit works). My issue occurs when I try to add a record to this form. The data goes into the database, but the FamilyID is 0 (and not that of the family member in question).

Hope this clarifies a bit.

 

[pr2-eugin]

Hmmm..

There are 5 rows of data (FamilyID=1 is me and the others are members of my family).

This does not sound right.. But I might be wrong as I have not see your relationships or data.. Can you post a screenshot of your Relationships? Maybe a bit of sample data as well.. I think your data is something like..

familyID    nameOfMember
1            Paul
1            Eugin
1            Francis
2            Marie
3            Chris

 

[jkl0]

You can use the Before Update event to check for FamilyID=0, and if it is then cancel the save and use a Message box to let the user know they need to change the FamilyID

 

[jledda]

That's it!! I knew it would be a fairly simple solution as my tables are pretty basic.

What's the code for cancelling the save? I have the code to ensure the correct FamilyID is stored in the record.

Thank you.

 

[pr2-eugin]

So your data is not stored as I have given in Post#4??

Anyway to cancel an update, use Cancel = True in the BeforUpdate event..

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If familyID = 0 Then Cancel = True
End Sub

 

[jledda]

Thank you so much. I had tried to reply to your previous post, but I used the "quick reply" feature in this forum and managed to screw up somehow.

Anyway, thanks for all your help. It's gonna work now!

Kindest Regards to all

 

[jledda]

Pat, hi.

Good advice. I will get rid of the 0 default.

In fact, I might design things very differently in future (say, using Forms and SQL to add records to my database).

Anyway, thanks for the advice. I agree that PKs shouldn't have defaults - why did I do it? Don't know....

Kind Regards
John