Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Some of the records in subform disapper (Access 2003)

    Hi,

    I have a table: tbl_ClientInfo (Primary Key: ClientID-AutoNumber) and a table: tbl_ActiveInfo (Primary Key: ActiveID-AutoNumber). Also I put the ClientID as number in tbl_ActiveInfo. tbl_ClientInfo & tbl_ActiveInfo is one to many relationships.

    I have a form: frmA & subform: subA base on those two tables. The "Link Child Fields" & "Link Master Fileds" is ClientID.

    Later I find out some record that users enter in subA is disappeared. For example, there's client info in tbl_ClientInfo, but no record in tbl_ActiveInfo. I am sure users have entered the data in subA. The perceage of missing data is about 30%. Also in tbl_ActiveInfo, there are some ClientID filed are black.

    I did Google search and I find the following notes:

    Subform records with no main form record
    You have a table with a primary key, and related table with a foreign key. You have created a relationship between these two tables, with referential integrity. Now you create a main form with a subform for these two tables. Some of the records you enter in the subform seem to disappear never to be seen again. Where have they gone?

    Referential integrity does not block Nulls in the foreign key. Normally Access fills in the foreign key in a subform to match the primary key in the main form. But if the main form is at a new record, Access merrily accepts the entry in the subform with a Null foreign key. These records never show up again in the subform, since they do not relate to any record in the main form.

    Can anyone validate and provide solutions?

    It's really bad that data is missing and sometime I can't even recover it.

    Thanks in advance.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Some of the records in subform disapper (Access 2003)

    If you look at the tbl_ActiveInfo table directly, can you find all the "missing" records there, or only some of them?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Yes, Hans. Some ClientID is missing in tbl_ActiveInfo. Since other fields are not required, so I really can't tell.

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Some of the records in subform disapper (Access 2003)

    You could create a Before Update event procedure for the subform:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.ClientID) Then
    MsgBox "ClientID cannot be blank.", vbExclamation
    Cancel = True
    End If
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Hi Hans,

    But there are no blank data in ClientID field of tbl_ClientInfo. Also since ClientID is reference key between tbl_ClientInfo & tbl_ActInfo, so clientID should be the same for both tables.

    How should I do to make sure ClientID in tbl_ActInfo as same as in the tbl_ClientInfo (Autonumber)?

    Thanks

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Some of the records in subform disapper (Access 2003)

    The code is meant for the subform based on tbl_ActiveInfo, not for the main form.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Thanks, Hans.

    But I don't want to pops up the message since the ClientID in the tbl_ActiveInfo should auto insert the same number as the ClientID in tblClientInfo. For some reason, the ClientID filed in tbl_ActiveInfo is missing the number and other fileds still have the data, also as primary key in the tbl_ActiveInfo.

    Thanks

    Regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Some of the records in subform disapper (Access 2003)

    If you have linked the subform properly to the main form, the only situation in which the ClientID could remain blank in the subform is when the user goes to a new record in the main form, then (without entering anything in the main form) enters data in the subform. The code I posted will prevent the user from saving the record in that situation. Under normal circumstances, the ClientID should be filled in automatically and correctly.
    If it doesn't work that way for you, I'd need to see (a stripped down copy of) the database to know what's happening.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Thanks,Hans.

    If I want the ClientID in subform is the same as in form, do you think the following codes is working (form is frmA; subform is subfrmB):

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.ClientID) Then
    Me.ClientID = frm!A!ClientID
    End If
    End Sub

    Thanks

    Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Some of the records in subform disapper (Access 2003)

    If you need that code, the subform hasn't been linked to the main form correctly. If the link is correct, the ClientID will be filled in automatically on the subform, unless there is no ClientID on the main form.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Thanks, Hans.

    I did put ClientID in the main and sub form. So do you think that's why some ClientID data is missing in tbl_ActiveInfo. The reason I didn't put ClientID in both form is I don't want users to see the ClientID since it's auto number. Also I think it's should be fine since the data source of form is the tbl_ClientInfo which already have ClientID.

    Please correct me if I am wrong.

    Thanks

    Regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Hi Hans,

    I attached a test database have the same tables, forms, relationships as my current database since there are all confidential data.

    Thank you so much for your help.

    Regards
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Some of the records in subform disapper (Access 2003)

    The only way I can create "orphan" records in tbl_ActiveInfo (using the form/subform) with a blank ClientID is - as I have described several times now - if I go to a new record in the main form, then enter data in the subform without having entered anything in the main form. I cannot create an "orphan" record while the main form shows an existing client.
    You can use simple code to prevent the user from creating orphan records, using the Before Update event of the subform, or perhaps even better, the Before Insert event of the subform:
    <code>
    Private Sub Form_BeforeInsert(Cancel As Integer)
    If IsNull(Me.Parent!ClientID) Then
    MsgBox "You must enter client data in the main form first!", vbExclamation
    Cancel = True
    End If
    End Sub
    </code>
    Can you manage to create an orphan record in the attached version using the form/subform?
    Attached Files Attached Files

  15. #15
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some of the records in subform disapper (Access 2003)

    Yes, I can't create any orphan record in your version.

    I have another question regarding "Me.Parent!ClientID). Is Parent reference form? If it is, can I use child reference sub form?

    Thanks

    Regards

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •