• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 943
  • Last Modified:

Error "Method or data member not found"

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
Divinedar0923
Asked:
Divinedar0923
  • 13
  • 8
  • 3
2 Solutions
 
Data-ManCOOCommented:
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
 
Divinedar0923Author Commented:
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
 
Data-ManCOOCommented:
I'm missing the quotes at the end

....like to add it?",

mike
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
Divinedar0923Author Commented:
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
 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
stevbeCommented:
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
 
Divinedar0923Author Commented:
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
 
Divinedar0923Author Commented:
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
 
Divinedar0923Author Commented:
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
 
Divinedar0923Author Commented:
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
 
Data-ManCOOCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 13
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now