Solved

Error "Method or data member not found"

Posted on 2004-10-26
915 Views
Last Modified: 2012-05-05
Can someone tell me why am I receiving this error?  I have the following code:

Private Sub Select_A_Company_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
Dim dbsCurrent As Database
Dim strCriteria As String
Set dbsCurrent = CurrentDb
Set rst = Me.Recordset.Clone
strCriteria = "CompanyName = " & Chr(34) & Me![Select A Company] & Chr(34)
rst.FindFirst = strCriteria
Me.Bookmark = rst.Bookmark
Me.Refresh

    If rst.NoMatch Then
        With rst
            .AddNew
            !CompanyName = [Select A Company]
            .Update
        End With
    End If
    rst.Close
Me.Refresh

End Sub

I'm trying to get it to except new data in a combo box.  I can't figure out where I'm going wrong.
0
Question by:Divinedar0923
    24 Comments
     
    LVL 18

    Accepted Solution

    by:
    You don't need to check for the value  If the notinlist event fired then the data doesn't exist


    Just do this in the event

    If msgbox("The company, " & NewData & ", doesn't exist.  Would you like to add it?,vbquestion+vbyesno,"Confirm")=vbno then
       Response = acdataerrcontinue
    else
      strSQL = "INSERT INTO tblTableName (CompanyNAme) Values ('" & NewData & "')"
      currentdb.execute strSQL, dbfailonerror
      Response = acDataErrAdded
    end if


    that should do it...the combo box will automatically show the new data.

    Mike
    0
     

    Author Comment

    by:Divinedar0923
    There must be something wrong with this line:

    If msgbox("The company, " & NewData & ", doesn't exist.  Would you like to add it?,vbquestion+vbyesno,"Confirm")=vbno then

    because it's all red.  What's missing or not there?
    0
     
    LVL 18

    Expert Comment

    by:Data-Man
    I'm missing the quotes at the end

    ....like to add it?",

    mike
    0
     
    LVL 39

    Assisted Solution

    by:stevbe
    Private Sub Select_A_Company_NotInList(NewData As String, Response As Integer)

    CurrentDB.Execute "INSERT INTO tblCompany ( CompanyName ) SELECT '" & NewData & "' AS Expr1;"
    Response = acDataErrAdded

    End Sub
    0
     

    Author Comment

    by:Divinedar0923
    Sorry for the delay but when I use the following:

    Private Sub Select_A_Company_NotInList(NewData As String, Response As Integer)

    CurrentDB.Execute "INSERT INTO tblCompany ( CompanyName ) SELECT '" & NewData & "' AS Expr1;"
    Response = acDataErrAdded

    End Sub

    I get the error that "can't open anymore databases".

    It seems like it would work but why get this error?

    Also, what is "& "' AS Expr1;" does this need to be the name of a field????

    Also I'm increasing the points to 500 if anyone can help me get this database up and running by 5pm tomorrow.  The entire database. In other words there maybe more questions.
    0
     
    LVL 39

    Expert Comment

    by:stevbe
    I get the error that "can't open anymore databases".

    sounds like you have other code that does not cleanup properly ... any variable that you initialize with the Set command sound be explicitly closed.

    Dim db As DAO.Database
    Set db = CurrentDb ...

     . .. . ..   you code here

    Set db = Nothing

    The SQL statement places the data by position and not by name so the As Expr1 really does not matter.

    Steve
    0
     

    Author Comment

    by:Divinedar0923
    Okay I figured it out and got a new code going on.  Let me tell you what I have and what problem I'm having:

    These are all the procedures I have going on with the combo box:

    Private Sub cboSelectCustomerID_AfterUpdate()
        'Call subroutine to set filter based on selected CustomerID
        SetFilter

    End Sub

    Private Sub cboSelectCustomerID_NotInList(NewData As String, Response As Integer)
    Dim db As Database

        Dim LSQL As String
        Dim LResponse As Integer
        Dim ctl As Control
       
        On Error GoTo Err_Execute
       
        'Category combo box control
        Set ctl = Me!cboSelectCustomerID
       
        LResponse = MsgBox(NewData & " is a new item.  Do you wish to add it to the combo box?", vbYesNo, "Add Item")
       
        'User responded "Yes" to adding the new item to the combo box
        If LResponse = vbYes Then
            Set db = CurrentDb()
           
            'Insert new item into underlying table
            LSQL = "insert into Customer (CustomerID) values ('" & NewData & "')"
           
            db.Execute LSQL, dbFailOnError
           
            Set db = Nothing
           
            'Set Response argument to indicate that data is being added.
            Response = acDataErrAdded
           
        Else
            'Set Response argument to suppress error message and undo changes
            Response = acDataErrContinue
            ctl.Undo
        End If
       
        On Error GoTo 0
       
        Exit Sub

    Err_Execute:
        ctl.Undo
        MsgBox "Action failed"

    End Sub

    Private Sub Form_Open(Cancel As Integer)
       
        'Call subroutine to set filter based on selected CustomerID
        SetFilter
       
    End Sub
    Sub SetFilter()

        Dim LSQL  As String
       
        LSQL = "select * from Customer"
        LSQL = LSQL & " where CustomerID = '" & cboSelectCustomerID & "'"
       
        Form_frmCustomer.RecordSource = LSQL
       
    End Sub

    NOW

    As I type in the combox, the data is accepted, but by the time I'm done it gives me the error message that "primary key field cannot be left blank".  The only primary filed that I have is the CustomerID field and when I type in the combo box, it does fill in the field on the Customer table.  Also when I look at the table it's not adding the other information in the other fields as I type the information in.  What do I need to do or what did I do wrong.

    0
     

    Author Comment

    by:Divinedar0923
    Okay, I got it to accept the first entry with the combo box and it filled in the information in the table.  But when I tried to enter another entry I get the same message "primary key field cannot be left Null".  I also did the following:

    Private Sub cboSelectCustomerID_AfterUpdate()
        'Call subroutine to set filter based on selected CustomerID
        cboSelectCustomerID.Requery
        SetFilter
    End Sub

    I added the requery method.  Is this not right?
    0
     
    LVL 39

    Expert Comment

    by:stevbe
    I think that the issue is the SetFilter. My guess is that as you enter a new record and enter a new customer the code is trying to find a record that has that customer ID that just got added from the NotInList but the record you were working with fails because you only entered some of the info.

    Is the form bound to a query or table before the SetFilter gets called in the Form_Open event?

    Is all of this code running in frmCustomer?

    Can you give us a little more background on the data you are working with and the process flow?


    Private Sub Form_Open(Cancel As Integer)
        Me.RecordSource = "SELECT * FROM Customer WHERE CustomerID='" & Me.cboSelectCustomerID.Value & "'"
    End Sub

    Private Sub cboSelectCustomerID_NotInList(NewData As String, Response As Integer)
        On Error GoTo Err_Execute
       
        If MsgBox(NewData & " is a new item.  Do you wish to add it to the combo box?", vbYesNo, "Add Item") = vbYes Then
            'Insert new item into underlying table
            CurrentDB.Execute "INSERT INTO Customer (CustomerID) VALUES ('" & NewData & "')", dbFailOnError
            'Set Response argument to indicate that data is being added.
            Response = acDataErrAdded
        Else
            'Set Response argument to suppress error message and undo changes
            Response = acDataErrContinue
            Me.cboSelectCustomerID.Undo
        End If

        Exit Sub

    Err_Execute:
        Me.cboSelectCustomerID.Undo
        MsgBox "Action failed"
    End Sub

    Private Sub cboSelectCustomerID_AfterUpdate()
        Me.RecordSource = "SELECT * FROM Customer WHERE CustomerID='" & Me.cboSelectCustomerID.Value & "'"
    End Sub
    0
     

    Author Comment

    by:Divinedar0923
    Okay to give you more information and I hope it's what you need:

    Table Name - Customer

    Forms Name - frmCustomer
                          CustomerIdentification

    Fields - CustomerID - Primary Key
                CompanyName
                TicketNo
                 ContactName
                 ContactTitle
                 Department
                 Address
                City
                StateOrProvidence
                PostalCode
                Country
                etc...

    Now I need to connect the TicketNo to the Troubleshooting Ticket Table and it should fill in when I open the ticket table and assign the autonumber.

    So what I need to do is fill in the CustomerID (combo box) with the ability to fill in the rest of the table on the form.  CustomerIdentification is the main form with the combo box and the frmCustomer has all the other fields in them.

    I replaced the procedures with what you sent me and now when I choose from the combo box I get NO information at all but the information is in the table but not filling the form.  

    I need when I select from the combo box the CustomerID for the other information to fill the other fields.  

    When I enter information into the Customer ID field, I need for the customer table to also receive the information being input.

    Do I need to do this by query? If so, who gets the query, the form or the combo box?

               


    0
     
    LVL 39

    Expert Comment

    by:stevbe
    the problem is that you are using 1 combobox to both enter data and navigate ... this does not work well at all.

    I would make 1 combobox to "Find" records that is not bound to any field but sets the filter and a seperate one to populate clientID with.

    Steve

    0
     

    Author Comment

    by:Divinedar0923
    Okay I don't understand.  Do I break this up into two tables and or do I use two combo boxes and if so how do I synchronize the two combo boxes to work as one.  You say one to find the records and one to filter the records. Please explain a little further if  you don't mind.
    0
     
    LVL 39

    Expert Comment

    by:stevbe
    ok ... Lets start at the beginning ... I think you have an issue in how your table and relationship between Customers and Tickets is set up.

    The tblCustomer hold CUstomer information only ... nothing about the Tickets at all as it is the Ticket table that points to a Customer record ...

    tblCustomer
        CustomerID
        CompanyName
        Address1
        Address2
     ...

    tblTicket
        TicketID
        CustomerID
        OpenDate
        CloseDate

    now in Tools --> Relationships you can add both tables and then drag the CustomerID field from tblCustomer to tblTicket and create a 1 to Many relationship and check the Casecade Updates and Cascade Deletes ... the wizard will walk you through these parts.

    When that is all set we will be ready to move forward. Is the form you are working with for tracking Tickets or just the base Customer information?

    Steve
    0
     

    Author Comment

    by:Divinedar0923
    Okay Steve that's done and it works. I understand that portion.  Now let me tell you how I need it to work.  From the Customer table I need the CompanyName to be the combo box so that the information is filtered in the customer form by company name.

    This database is for keeping track of troubleshooting tickets.  The first form is the customer form where you will fill out the information on the company that's calling.  Also I think I need to make a contact table different from the customer information table because there may be several people calling from one company to report a problem and I want to be able to keep up with them by name and the number to there trouble ticket.  So it would probably be a one to many with the ticket being one and the contact being many.

    Hopefully I can at least put a hole in this today because right now I don't see any light.
    0
     
    LVL 39

    Expert Comment

    by:stevbe
    hmmm ... so if you get a ticket tomorrow for the same customer for an entirely different ticket you will need to open a new customer record? If your customers repeat frequently you would be better of with a 1-M from customers to tickets and then store just the contact name and number in the tickets themselves and not have to repeat address, city, state etc info on the customer.

    let me know which way you want to go and we will finish this up.
    Steve
    0
     

    Author Comment

    by:Divinedar0923
    What I have is a customer information form (customer table) with the following fields:

    Customer Table: (CompanyName is combo box)
    CustomerID; CompanyName; Address;City; StateOrProvidence; PostalCode ; Country; BusinessNo1 ; Ext1; BusinessNo2; Ext2; FaxNumber; EmailAddress; BestTimeToCall; TimeZone; and Notes

    tblContact
    CustomerID; CompanyName; ContactName; ContactTitle; Department; TicketNo

    (ticket number is include to keep track of each contact ticket numbers)

    Ticket
    CustomerID; CustomerId; CompanyName and more

    I want to select the company name from the customer table and it fill in the information stated.  Also when I select the Company it should also add a new record in each of the other two tables by the click of a button to go to those forms and/or tables.  

    I need to be able to add records to the combo box and also when I select a record from the combo box fill in the fields.  I have the following codes so far attached to the customer (companyName combo box)

    AFTER UPDATE

    Private Sub cboSelectCompany_AfterUpdate()
       
    On Error Resume Next

        cboSelectCompany.SetFocus
        If cboSelectCompany.Value > 0 Then
            Me.Filter = "[CompanyName]=" & cboSelectCompany.Value
            Me.FilterOn = True
        End If

    End Sub

    NOT IN LIST

    Private Sub cboSelectCompany_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Company...")
        If i = vbYes Then
          strSQL = "Insert Into Customer ([CompanyName]) values ('" & NewData & "')"
                CurrentDb.Execute strSQL, dbFailOnError
                Response = acDataErrAdded
            Else
                Response = acDataErrContinue
        End If
    End Sub
    Sub SetFilter()

        Dim LSQL  As String
       
        LSQL = "select * from Customer"
        LSQL = LSQL & " where CustomerID = '" & cboSelectCustomerID & "'"
       
        Form_frmCustomer.RecordSource = LSQL
       
    End Sub


    ONFORM LOAD

    Private Sub Form_Current()
      If Me.NewRecord Then
        On Error Resume Next 'It should never occur, just to be sure...
        Me!CustomerID.DefaultValue = Nz(DMax("[CustomerID]", "Customer"), 0) + 1
      End If

    End Sub

    The On-Form Load may be causing the error but what it does is fill the CustomerID with sequence numbering instead of using autonumber.

    The error that I get is "primary key or index key cannot be NULL".  The CustomerID is the primary key and it has a number in it, but I still get this message.  It's like it's not recognizing the code or the number in the field.

    Thank you can help.

    0
     
    LVL 39

    Expert Comment

    by:stevbe
    ok ... so you have a form ... frmTicket and a customer calls you automatically set the default value to a non existant record ... sounds like this could be the start of the trouble ... I would not set this at all and let the combobox bound to the CustomerID field take care of setting the value. In the Ticket table you do not need a field for CompanyName because you have a relationship between the CustomerID field in your Customer table. Include the CusatomerID field in the rowsource query for cboSelectCompany and bind that control to the CustomerID field. I would handle the number sequencing for the a new customer in the code that adds the customer rather than setting it as the controls.DefaultValue

    Private Sub cboSelectCompany_AfterUpdate()
       
    ' On Error Resume Next ... hiding errors is not good, you won't know when something goes wrong :-(

        If cboSelectCompany.Value > 0 Then
            Me.Filter = "[CompanyName]='" & cboSelectCompany.Value & "'"
            Me.FilterOn = True
        End If

    End Sub

    Steve
    0
     

    Author Comment

    by:Divinedar0923
    Okay I get the following error:

    "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the filed or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    Now it lets me add to the combo box but it don't list what I've added and when I try to add another record I get the above message.  The CustomerID I changed to autonumber and cancelled the ONFORM LOAD.  The number changes when I add another record but I get the error above.

    Also when I type in the combo box and tab it doesn't go to the next form field.  It goes back to the beginning of the combo box.
    0
     
    LVL 39

    Expert Comment

    by:stevbe
    adding an item to a dropbox is something I do all the time ... let's remove everything else and get that to work and then we can put the other oieces back in ... this should be the only code you need. Does the combobox list all of your Customers?

    Steve

    Private Sub cboSelectCompany_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    If MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Company...") = vbYes Then
          strSQL = "INSERT INTO Customer ([CompanyName]) VALUES ('" & NewData & "')"
          CurrentDb.Execute strSQL, dbFailOnError
          Response = acDataErrAdded
    Else
         Response = acDataErrContinue
    End If
    End Sub
    0
     

    Author Comment

    by:Divinedar0923
    Okay it lets me add but I still get the error message:

    "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the filed or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    Let me tell you a couple of things that I have:

    For one I have the combo box properties as follows:

    Name = cboSelectCompany
    Control Source = CompanyName
    Row Source Type = Table/Query
    Row Source = SELECT DISTINCTROW Customer.CustomerID, Customer.CompanyName
    FROM Customer ORDER BY Customer.CompanyName;
    Column Count = 1
    Column Heads = 0
    Column Widths = 0";1"
    Bound Column = 1
    LimitToList = Yes
    On Not In List = The code above you gave me

    The Form properties are as follows:

    Record Source = SELECT Customer.*
    FROM Customer ORDER BY Customer.CustomerID, Customer.CompanyName;

    Is there anything else I should have or am I missing something?

    It's giving me the error and also it's not making a list to choose from once the field is filled in.

    0
     

    Author Comment

    by:Divinedar0923
    Okay I think I figured out why it was make my list is because I had the combo box on a subform and not all on one form. Now it fills in the list and keeps the list but when I select from the list it doesn't fill in the appropriate fields.  

    That's my next problem.  How do I get it to fill in the other fields in the form?  Do I have to make all the fields a combo box?
    0
     

    Author Comment

    by:Divinedar0923
    I think I got it to filter with the following:

    Private Sub cboSelectCompany_AfterUpdate()
    On Error Resume Next
         
        cboSelectCompany.SetFocus
        If cboSelectCompany.Value > 0 Then
            Me.Filter = "[CompanyName]=" & cboSelectCompany.Value
            Me.FilterOn = True
        End If

    End Sub

    but the problem now is that my CustomerID field is not being recognized as being empty when it's set to autonumber.  Once I enter the company name the autonumber does propulate the field, it does, but it's not being recognized.  I get an error message that
    "the index key or primary key cannot be left NULL".

    My primary keys are the CustomerID and the CompanyName.
    0
     

    Author Comment

    by:Divinedar0923
    Need help.

    Why is it so hard to get someone with Microsoft Access advise to answer any of my questions?  Are my questions not clear enough?

    Problem #1:

    The following is in NotInList:

    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    If MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Company...") = vbYes Then
          strSQL = "INSERT INTO Customer ([CompanyName]) VALUES ('" & NewData & "')"
          CurrentDb.Execute strSQL, dbFailOnError
          Response = acDataErrAdded
    Else
         Response = acDataErrContinue
    End If

    It pops up and ask the proper message but when I answer it I get the following error:

    "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the filed or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    I get this message a complete blank table, no data is entered into the table at all.

    Problem #2:

    I can't get it to keep whatever is entered new into the combo box to keep as a list to be able to choose from and select data.

    Problem #3:

    It does not fill in the other textboxes.  It's not filtering the data.  I have searched over Expert for a solution but I do not find one.

    I need help, somebody please help.


    0
     
    LVL 18

    Expert Comment

    by:Data-Man
    Your original question was

    Why am I getting this error...and you posted your code and we answered with the proper way to use the NotInList Event.  Stevbe spend a lot of time on this thread.  

    I think your problem is in the design of your form.   Your original question has been answered.  I suggest you close it out and post another question on what you are trying to accomplish.  

    Your're no longer receiving a error message with the code....your problem is in the logic of the form.  That is why you are recieving the error.

    Stevbe, do you agree?

    Thanks,
    Mike
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now