[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Error "Method or data member not found"

Posted on 2004-10-26
26
Medium Priority
?
937 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
Comment
Question by:Divinedar0923
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 8
  • 3
26 Comments
 
LVL 18

Accepted Solution

by:
Data-Man earned 1000 total points
ID: 12415212
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
ID: 12415269
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
ID: 12415346
I'm missing the quotes at the end

....like to add it?",

mike
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 1000 total points
ID: 12421615
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
ID: 12467775
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
ID: 12473095
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
ID: 12473177
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
ID: 12473964
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
ID: 12474604
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
ID: 12475250
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
ID: 12475383
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
ID: 12477530
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
ID: 12480938
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
ID: 12482607
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
ID: 12486954
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
ID: 12487111
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
ID: 12487379
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
ID: 12487514
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
ID: 12504791
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
ID: 12505538
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
ID: 12505872
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
ID: 12506016
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
ID: 12524915
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
ID: 12528327
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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