Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: NotInList with use of Autonumber errors

    Hello,

    I have a Customer table with Autonumbers used as the customer account number. I've got two main forms (Order frm, Customer frm); the Customer's account number displays in a bound control on each. All relationships are tied to this account number, it's an Order entry system. The trouble comes in after adding a new customer.

    there's a combo control on Customer frm to search a name and if not found, the NotInList function is triggered. The acct number' control is the second control on the Customer frm. Once a new person is successfully added to the table an autonumber 'acct number' is assigned. However, what I've discovered is that somehow, the person is getting 2, 3 autonumbers...and I don't know how this is happening. I found this out when I had difficulty on the Order frm trying to bring up the newly added customer. I couldn't locate the new customer through the combo box on the Order form. So I went into the actual table and found that my newly added (Joe Smoe, acct# 2442) had 2 and sometimes 3 autonumbers (2442, 2443),...I had to delete one of the numbers, in order to bring up the new name. As soon as I have to add another pperson, the same thing all over again. On the Order form, if I make the 'acct number' control unbound, the assigned nnumber from the customer form doesn't come up when you bring up a customers record, only when you make it bound, BUT then, you're assigning again, the customer another number. Help, help

    What a mess does anyone have ideas how to alleviate this?
    Unfortunately, the Customer table (and some of my other tables) were imported with autonumbers already used as Primary Keys).
    There are over 2300 Customer existing records so I can't get rid of these tables.

    Here's my combo box NotInList event:
    Code:
    Private Sub customername_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim FirstName As String
    Dim LastName As String
    Dim i As Integer
    Dim SpacePosition As Integer
    Dim Msg As String
    SpacePosition = InStr(NewData, " ")
    
        'Exit this sub if the combo box is cleared
        If NewData = "" Then Exit Sub
    
        Msg = "'The name," & NewData & "' is not found." & vbCr & vbCr
        Msg = Msg & "Do you want to add new customer?"
        
    FirstName = Trim(Left(NewData, SpacePosition - 1))
       LastName = Trim(Mid(NewData, SpacePosition + 1))
        
        i = MsgBox(Msg, vbQuestion + vbYesNo, "New Customer Name...")
        If i = vbYes Then
            strSQL = "Insert Into Customers ([FirstName], [LastName]) " & _
        "values ('" & FirstName & "','" & LastName & "');"
            CurrentDb.Execute strSQL, dbFailOnError
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
        End If
    
    End Sub

  2. #2
    Join Date
    Aug 2007
    Posts
    102

    AfterUpdate event may be part of the problem

    ON the Order form, there's a requery that goes on.....could that be a part of this problem?
    Code:
    Private Sub custname_AfterUpdate()
    Me.FilterOn = True
    Me.Filter = "CustomerID =" & custacct
    Me.Requery
    End Sub
    imRosie
    thanks so much......

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Take a look at the customer form property sheet. I'll betcha the Data Entry property is set to Yes. Make it No.

    Yes means you can only add data with the form, not modify existing data. Once you add the customer, all changes should modify the original record, not add new ones.

    Hope This Helps,

    Sam

  4. #4
    Join Date
    Aug 2007
    Posts
    102

    They were both set to No

    HI Sam,
    I did as you suggested.....but both forms were already set with Data Entry (no)..
    I did however just change the Customer Form to yes, because that's the form with all the customers personal information (for adding and updating an existing customer).

    now what?
    thanks
    imrosie

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by imrosie
    HI Sam,
    I did as you suggested.....but both forms were already set with Data Entry (no)..
    I did however just change the Customer Form to yes...

    now what?
    thanks
    imrosie
    You have to change it back to No!!! Otherwise you can't access existing records with the form, you can only add new ones.

    Sam

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I had another thought. What is the combo box's control source? Is it a field in the form's record source, or is it unbound? Also, do you automatically add a record each time you hit <Enter> after the last field in the form, or is there some other control for that purpose?

    It seems to me that the combo box customername is likely the first control you encounter when the form refreshes (OnCurrent()). If so, all the other controls in the form are still blank. Hence your VBA is adding a record with only the customername populated. After hitting <Enter> after the last field, you're again adding a record, this time with all fields populated.

    You don't have to add a record in the NotInList() event. All you need to do is set the Response to either Added or Continue, as you do in the end of the routine. You are only verifying that you want to keep the NewData value in the combo box, and allow me to continue, thank you very much. You can leave adding the record for the automatic add that occurs when you hit <Enter> after the last control in the form.

    Sam

  7. #7
    Join Date
    Aug 2007
    Posts
    102

    I think you're on to something Sam

    HI,

    Yes a record is added each time I hit enter it appears. The Customer form has the combo box for searching a customer record and it is unbound. The control source (table/query)for it is:
    Code:
    SELECT CustomerID, [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & " " & [LastName];
    I can bring up an existing customer, but you're right, my purpose of the 'NotInLlist' is simply to add the new name that's in the combo box. I think you must be right, that's exactly what's happening. I hit enter, then suddenly the same name has 2 entries, one with the name only (and an autonum) and one without a name, but all the other controls filled in. So are you suggesting that I alter the 'NotInList' event routine for adding or continuing....I've got the combo box set with Limit to List(yes)....

    I do however, need to parse the 'NewData' into FirstName and LastName, then continue adding other data for the new customer.

    What portion in my 'NotInList' do I remove. Recall, I"m a newbie,so I'm not quite sure....
    thanks
    imrosie

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Except for the portion of your code that confirms that the user wants to add the new name, and the portion of the code that initializes the Response argunemt, I think you should remove all the code in that procedure. I think that's where your problem stems from.

    Tell you what. Don't remove the rest of the code for now, just comment it out. Before the first word on each line, put a single quote ('). That flags the following text as comment. Run the program again, adding another name, and tell us if it adds one record or two, and if the record is fully populated. Deal?

    Sam

  9. #9
    Join Date
    Aug 2007
    Posts
    102
    Do you mean like this?

    Code:
    Private Sub customername_NotInList(NewData As String, Response 
    Dim strSQL As String
    Dim FirstName As String
    Dim LastName As String
    Dim i As Integer
    Dim SpacePosition As Integer
    Dim Msg As String
    'SpacePosition = InStr(NewData, " ")
    
        'Exit this sub if the combo box is cleared
        If NewData = "" Then Exit Sub
    
        Msg = "'The name," & NewData & "' is not found." & vbCr & vbCr
        Msg = Msg & "Do you want to add new customer?"
        
    'FirstName = Trim(Left(NewData, SpacePosition - 1))
       'LastName = Trim(Mid(NewData, SpacePosition + 1))
        
        i = MsgBox(Msg, vbQuestion + vbYesNo, "New Customer Name...")
        If i = vbYes Then
           'strSQL = "Insert Into 'Customers  '([FirstName], '[LastName]) " & _
        "values ('" & FirstName & "','" & LastName & "');"
            CurrentDb.Execute strSQL, dbFailOnError
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
        End If
    
    End Sub
    Last edited by imrosie; 08-25-07 at 09:27.

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You should comment out this line as well.

    'CurrentDb.Execute strSQL, dbFailOnError

    Don't forget you already commented out the strSQL line itself.

    Sam

Posting Permissions

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