form/subform

[freshmaker]

I currently have a form linked to a subform by SSN. If a
previously used SSN is entered into the master form, the
subform displays the information for that SSN. Of course
after a user enters all the other data into the master
form, or tries to make an entry into the subform, they are
told that it was unsuccessful because it would create
duplicate entries.

I would like it to work so if an SSN is entered that has
already been used it will move to the already existing
record so data can be entered into the subform without any
trouble. If anyone can help with this I would surely
appreciate it. If I need to make any clarifications
please let me know.

Thank you.

Jeff


[This message has been edited by freshmaker (edited 01-09-2002).]

 

[Jack Cowley]

You could create a combo box based on SSN's and when the user selects a number it will show the appropriate information. If the SSN does not exist then you can use the Not In List event of the combo box to open the appropriate form for data entry or the current form could be moved to a new record. To create the combo box use the Wizard and select the 3rd item on the first screen, "Find a record...". To use the Not In List event use this Article:
http://support.microsoft.com/support/kb/articles/Q197/5/26.ASP?LN=EN-US&SD=gn&FR=0&qry=q197526&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

 

[freshmaker]

Jack-

I tried adding...

DoCmd.GoToRecord , , acNewRec

...on the NotInList event but it gave me run-time error '2105'. You can't go to the specified record. You may be at the end of a recordset.

What am I doing wrong?

 

[David R]

Does your form possibly have AllowAdditions set to off? There are of course other ways to disable this, opening a form with various arguments, etc, but that could lead to the error you're talking about.

HTH,
David R

 

[freshmaker]

AllowAdditions, Deletions, and Edits are all set to yes. Do you have any other ideas as to why I could get this message?

 

[David R]

*sigh* And now I'm getting the same error. This worked, imperfectly and clumsily, when I used NoMatch = True instead of NotInList. Now it won't work properly.

I posted the code from mine (both versions) in a new topic under Modules & VBA: http://www.access-programmers.co.uk/ubb/Forum7/HTML/002760.html

David R




[This message has been edited by David R (edited 01-16-2002).]

 

[freshmaker]

David-

Thanks for all of your help! It turns out that I came up with a simple solution that has worked well.

My code:

Private Sub SSN_BeforeUpdate(Cancel As Integer)
'If SSN already exists, move to existing record

Dim strLookup As String
strLookup = Forms!frmSubscribers!SSN

If Me!SSN = DLookup("SSN", "tblSubscribers", "SSN = Forms!frmSubscribers!SSN") Then
DoCmd.CancelEvent
Me.Undo
DoCmd.FindRecord strLookup, , True, , True
End If

End Sub

What do you think?

 

[David R]

In the meantime, you can use something along these lines to get it working. Note the If ( NoMatch = True ) section, which does the work of NotInList.

Private Sub ParticipantID_Lookup_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Dim PIL As String
    
    If (IsNull(Me.ParticipantID_Lookup)) Or (Me.ParticipantID_Lookup = "") Then Exit Sub
    
    'Include check for Len(ParticipantID) = 6
    PIL = Left(Me.ParticipantID_Lookup, 6)
        If (Len(PIL) < 6) Then
        MsgBox ("Make sure the ParticipantID has 6 characters.")
        Exit Sub
    End If
    
    Set rs = Me.Recordset.Clone
    
    rs.FindFirst "[ParticipantID] = '" & PIL & "'"
    
    If rs.Nomatch = True Then
        If (Me.NewRecord = True) Then
            If (Me.Dirty = True) Then Me.Undo
        Else
           DoCmd.GoToRecord , , acNewRec
        End If
        
        Me.ParticipantID = Left(PIL, 6)
        Me.ParticipantID_Lookup = ""
    Else
      'Go to the matching record
       Me.Bookmark = rs.Bookmark
       
    End If
    
    Me.ParticipantID_Lookup.Requery
    Me.subformDevices.Requery
    Set rs = Nothing
End Sub

 

[David R]

Your code looks like another way to skin the cat, although I'm not clear what it does in certain circumstances. I considered four conditions when making my lookup box.
What does it do when:
1) In an existing record, you want to look up another existing record;
2) In an existing record, you try to look up a nonexistent record;
3) In a New record, you want to look up an existing record;
4) In a New record, you try to look up a nonexistent record.

I'm a little leery of using the same box to enter lookup data and the primary key of my record. If your form is read-only, of course, you won't have this problem.

Cheers,
David R

 

[freshmaker]

Hi David-

Thanks again for your help. Not just for the solutions you have provided but for getting my mind working as well.

However, since the initial request was more concerned with how to handle an attempted duplicate entry on a new record, rather than looking up existing records, I wasn't particularly concerned with 1 & 2. With my cheesy bit of code...

1) In an existing record, you want to look up another existing record;

If you enter in an SSN that already exists it will take you to that record.

2) In an existing record, you try to look up a nonexistent record;

Doesn't look up anything. Allows the SSN to be edited as long as it doesn't cause duplication.

3) In a New record, you want to look up an existing record;

If you enter in an SSN that already exists it will take you to that record.

4) In a New record, you try to look up a nonexistent record.

Nothing, it will let you continue to enter the new record.

Trying to solve 3 & 4 was my main concern. I didn't want users to have to use a lookup field OR enter data, get warned that it was duplicate, try again, etc...

Thanks again for the help. Perhaps our paths will cross again.

 

[freshmaker]

How did I miss this?

How to Use a Bound Control to Find a Record (95/97) (Q136123)

 

[David R]

You're right that 1, 3, and 4 work as expected. However I work by the motto "you can make your code idiot proof, but they can always build a better idiot".

Is it really your intent to allow them to change the SSN without a warning (case #2)? If so, and you trust your users, then your code is golden. However I'm not quite so trusting.

Please don't take this as condescending or insulting. I've just learned most of this by painful experience, that your users WILL out-stupid anything you can imagine, because they generally have a lot more practice at it.

Cheers,
David R

 

[freshmaker]

David-

No offense taken. I certainly appreciate you advice, and definitely agree with your motto. Thanks again for all of your help.

Jeff