?
Solved

Combo box fill Error 3022

Posted on 2004-11-16
15
Medium Priority
?
536 Views
Last Modified: 2012-06-21
Help!!!

I have a combo box that when data is entered it fills the first row with the CustomerID# (Autonumber) and the CompanyName. At this point I am trying to  fill an empty table but I get the error message code 3022 (duplicate data).  

It's not accepting my entry into the combo box.  I have the following codes:

Private Sub Select_A_Customer_AfterUpdate()
Dim strSQL As String

    Me("CompanyName") = [Select A Customer].Column(1)
    Me("Address") = [Select A Customer].Column(2)
    Me("City") = [Select A Customer].Column(3)
    Me("StateOrProvidence") = [Select A Customer].Column(4)
    Me("PostalCode") = [Select A Customer].Column(5)
    Me("Country") = [Select A Customer].Column(6)
    Me("BusinessNo1") = [Select A Customer].Column(7)
    Me("Ext1") = [Select A Customer].Column(8)
    Me("BusinessNo2") = [Select A Customer].Column(9)
    Me("Ext2") = [Select A Customer].Column(10)
    Me("FaxNumber") = [Select A Customer].Column(11)
    Me("EmailAddress") = [Select A Customer].Column(12)
    Me("BestTimeToCall") = [Select A Customer].Column(13)
    Me("TimeZone") = [Select A Customer].Column(14)
    Me("Notes") = [Select A Customer].Column(15)
   


End Sub

Private Sub Select_A_Customer_GotFocus()
[Select A Customer].Dropdown
End Sub

Private Sub Select_A_Customer_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL

strMsg = " ' " & NewData & " ' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Company") Then
    Response = acDataErrDisplay
Else
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Customer")
    rst.AddNew
        rst("CompanyName") = NewData
        rst.Update
    Response = acDataErrAdded
        rst.Close
End If

End Sub

I have my references set to use DAO but should I try using ADO.  I am also using Access 2002.  

I need it to accept new data as well as being able to select from the combo list and filter the data based on the selection.

I've tried to get help with this before but to no success.  I've made some progress on my own but I can't seem to find out what my problem is.
0
Comment
Question by:Divinedar0923
  • 8
  • 7
15 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12595436
Where does the event stop?  Is CompanyName your PK for the Customer table?  Have you verified that your test data does not exist in that table anywhere?  Try using a company name you could not possible have in your database, like "Schmumf's House of Ugly Yaks"

DAO should work fine for this, as it IS the native class for Access.
0
 

Author Comment

by:Divinedar0923
ID: 12595647
Where does the event stop?  

It debugs at "rst.Update".

Is CompanyName your PK for the Customer table?  

No. CustomerID is the primary key and CompanyName is the next row down.  It is not a primary key.

Have you verified that your test data does not exist in that table anywhere?

The table is totally empty before I try putting anything in it.

  Try using a company name you could not possible have in your database, like "Schmumf's House of Ugly Yaks"

Okay it accepted "Schmumf's House of Ugly Yaks" but when I entered it into the combo box, it went to the CustomerID field and look like it inserted another record.  It was on record one when I entered the name and after entering the name it says it's on record 2.  

Question:

Does things like " ' " matter when you're entering a name in the field.  The first company I entered was Comtech Systems, Inc.  It seems to have a problem with the punctuation.  Is that possible.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12615894
It is most definitely possible.  Quotes are a source of many annoying problems in Access.  Any time you deal with strings, Access requires them to be delimited with double-quotes.  In the event you need double-quotes within a string (as you would with SQL, for example), you can use single-quotes as an alternative.  For a few minor examples:

MyStr = "Comtech Systems, Inc."   '<--- No problem
MyStr = "Al's House"                     '<--- No problem
MyStr = "My sister said, "I don't want to go"."   '<--- problem.  Access will think MyStr="My sister said, " and return a syntax error for the rest
MyStr = "My sister said, 'I don't want to go'."    '<--- No problem.

One thing I did notice is that you are not updating the combo box after you add data.  This may result in data being stored in the table, but not showing in the combo box.  Add the following line anywhere you need the data in the combo updated:

Me.Select_A_Customer.Requery

Have you verified that the table is empty by opening it in datasheet view?  Or are you relying on the combo box to list everything in the table?  Is your form bound to the table?  I think I have a good feel for what you are trying to do, but I believe you are going about it the hard way.  We can get this method to work, without a doubt, but you may want to consider a different strategy.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Divinedar0923
ID: 12616562
Thank you routinet.  I'm answering the following questions.  I need all the help I can get.

Have you verified that the table is empty by opening it in datasheet view? Or are you relying on the combo box to list everything in the table?  

   I deleted any information out of the table itself before entering any information in the
   combo box.  When I enter the information in the combo box I get an error message
   that it may create duplicates.

Is your form bound to the table?  

     I have the Row Source Type as the following query named qrySelectCompany

         "SELECT DISTINCT Customer.CustomerID, Customer.CompanyName,
          Customer.Address, Customer.City, Customer.StateOrProvidence,
          Customer.PostalCode, Customer.Country, Customer.BusinessNo1, Customer.Ext1,
          Customer.BusinessNo2, Customer.Ext2, Customer.FaxNumber,
          Customer.EmailAddress, Customer.BestTimeToCall, Customer.TimeZone,
          Customer.Notes
          FROM Customer
          GROUP BY Customer.CustomerID, Customer.CompanyName, Customer.Address,
          Customer.City, Customer.StateOrProvidence, Customer.PostalCode,
          Customer.Country, Customer.BusinessNo1, Customer.Ext1,
          Customer.BusinessNo2, Customer.Ext2, Customer.FaxNumber,
          Customer.EmailAddress, Customer.BestTimeToCall, Customer.TimeZone,
         Customer.Notes
         ORDER BY Customer.CompanyName;"


I think I have a good feel for what you are trying to do, but I believe you are going about it the hard way.  We can get this method to work, without a doubt, but you may want to consider a different strategy.

       Please help me because I can't figure out what I'm doing wrong.  Tell me, What should be my first step.  Would it work better if I had two forms but I only need a combo box on the one form.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12618176
First, let's see if I have a good grasp of your project.  You have a form to display customer information.  You have placed a combo box on this form to allow users to bring up a particular record.  If the users type in a name that does not exist, you assume it is a new company, and prompt the user for more information.  If the user selects a valid record from the combo, you bring up the information from the record and drop it into your text controls on the form.  Is that about right?

What I THINK is happening...well, I have a few different theories:

1) Your NotInList event fires when you type in a new company name.  This adds the new company to the Customer table.  Afterwards, the AfterUpdate event will probably fire (I haven't tested this...) because the combo box has changed.  Access may also save the record, since your call to AddNew forces a change in the current record.  The result is a blank record being saved.  You can test this theory by:
     a) run this SQL: "DELETE * FROM Customer"
     b) attempt to add a company through the combo box
     c) look at the datasheet view of the table after each step (entering a new name in the combo, adding the new name to the combo list, etc.) and see
         if a blank record was added to table.  If so, this is at least part of your problem.
     d) subsequent attempts to add new names should result in the error, but this SHOULD work the very first time after you complete step a.

2) Your SQL, because of the GROUP BY clause, in not updateable.  For some reason, instead of returning the proper error, Access tells you there is duplicate data.  You can test this by removing the GROUP BY clause from the SQL in the form's RecordSource property.

3) You have a required field in the Customer table that is not being populated (for example, Address or City?).  This has the same problem as #2, in which this means Access is returning the wrong error.  You can test this by examining the properties of the fields in the Customer table.  Any that have their Required property set to True should also have a default value assigned, or have a value assigned to it in your NotInList event before you attempt to call .Update.

Otherwise, here's a plan of attack for how to get this working:

     1) Make sure your form is bound to the Customer table.  Don't worry about generating SQL for it...set the RecordSourceType to "Table/Query", and the
         RecordSource property to "Customer".  If you want to make sure the ordering is preserved, used this SQL instead:
                    "SELECT * FROM Customer ORDER BY CompanyName;"
     2) Create bound controls on the form for all the fields you need displayed or entered by the user.  They should ALL be bound.  If you do not plan to use
         a particular field (the SQL I gave you will SELECT all fields in the table), do not create a control for it.
     3) Create an UNBOUND combobox.  Make sure there are 2 columns for the combo, but hide the first one (set ColumnWidth to "0;xx" where xx is the
         desired width of the CompanyName column).  Set the LimitToList property to True, and verify the settings for your other miscellaneous properties.  For the
         RowSource property, use this SQL:
                    "SELECT CustomerID, CompanyName FROM Customer ORDER BY CompanyName;"
     4) Create your events as shown below.  Most of it should look familiar to you, but there are some important changes in how this form will work.
     5) Create Save and Undo buttons to handle saving or cancelling changes made to a record.  Also, create a "Go" button next to your combobox.

If you need help with any part of this, let me know.  It is a lot to absorb in a short period of time.  A couple of important notes...  Access will attempt to save the current record if you attempt to move to another record, or add a new one.  You may want to add custom navigation buttons to handle it yourself.  Default Access behavior can be a little frustrating, and the less you rely on it the more detail you know about your project.  Remember that single-quotes in the company name will continue to be an issue for you.  Either instruct your users to not enter them in the data, or alter your events to account for the possibility (by using the Replace function, for example).

One final item from the Access help file.  I like how you have the drop-down section of the combobox appearing when the controls gains focus.  However, it may be contributing to your problem:

------------------------------------------------------------------------
When the LimitToList property is set to Yes and the combo box list is dropped down, Microsoft Access selects matching values in the list as the user enters characters in the text box portion of the combo box, even if the AutoExpand property is set to No. If the user presses ENTER or moves to another control or record, the selected value appears in the combo box. In this case, the NotInList event will not fire. To allow the NotInList event to fire, the user should not drop down the combo box list.
------------------------------------------------------------------------



Private Sub GoButton_Click()

    Me.Filter = "[CompanyName] = " & Me.Select_A_Company.Text
    Me.FilterOn = True

End Sub

Private Sub SaveButton_Click()

    ' Do any data validation, then save the record

End Sub

Private Sub UndoButton_Click()

    Me.Undo

End Sub

Private Sub Select_A_Customer_GotFocus()
[Select A Customer].Dropdown
End Sub

Private Sub Select_A_Customer_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim db As DAO.Database

strMsg = " ' " & NewData & " ' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Company") Then
    Response = acDataErrDisplay
Else
    Set db = CurrentDb()
    db.Execute "INSERT INTO Customer (CompanyName) VALUES ('" & NewData & "')"
    Response = acDataErrAdded
    Set db = Nothing
End If

End Sub
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12618239
I forgot one thing.  Make sure your combo box is actually listing the new CompanyName after your NotInList event adds it to the table.  If it isn't showing up, add a Requery to the end of your NotInList event, right after "Set db = Nothing".

Me.Select_A_Customer.Requery
0
 

Author Comment

by:Divinedar0923
ID: 12628158
Okay I tried everything  you sent me and I have the following questions and answers:

1)  I did the delete query and the table did not have any records in it but it still would not except any new data.  I followed you step-by-step.

2) I took of the GROUPBY and that didn't work.

3) I checked for required fields in the table and there were none.  No field is set to required.

The customer table is bound to the form.  When you say set the RecordSourceType to "Table/Query" and the RecordSource property to Customer, are you saying set the form properties or the combo box properties?  If you're talking about the form I set it's RecordSource property to Customer but there is no RecordSource Type on the form.

I set the combo box property as follows:  No Control Source; RowSource Type: Table/Query; Row Source: SELECT Customer.CustomerID, Customer.CompanyName FROM Customer ORDER BY Customer.CompanyName; Column Count: 2; Column Widths: 0";2"; Bound Column: 2; List Rows: 16; LimitToList: Yes; AutoExpand: Yes.

I entered all you codes that you sent.  How does the Go and Save button help?
I would like for the one procedure (code) to handle all of that.  

Should I not be using the dropdown if I use the NotInList property?

I'm getting the following error when I try to enter text in the combo box:

"The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items."

This happens only when I try to tab from the combo box.  It's the only error message I'm receiving so far.  I await your response.

0
 

Author Comment

by:Divinedar0923
ID: 12628186
One more question:

When I enter the code "Me.Select_A_Customer.Requery" I get the following error message:

Runtime error "2118".  You must the query field before you run the requery action.

I entered the code after Set db = Nothing, just before the End If.  Regardless of where I put the code I still get the same error message.
0
 

Author Comment

by:Divinedar0923
ID: 12710106
Okay I've been waiting for a response to this question and has not received any since 11/19/04.  Since then I have been able to do all the above but I'm still  having problems.

When I enter text into the combo box, the text that is entered is duplicated to the next record.  I have done group by in the query and still getting duplicates in the next record or even the record before.  Also when I select from the list, which is also showing duplicates, none of the other information typed in the other bound text boxes will show.  When I open the table, the information from the other text boxes are in the table, but does not show on the form. I have the following properties on the control of the combo box:

Name: Select A Company (spaces)
Control Source:  CompanyName
Row Source Type:  Table/Query
Row Source: SELECT DISTINCT Customer.CustomerID, Customer.CompanyName
                      FROM Customer
                      GROUP BY Customer.CustomerID, Customer.CompanyName
                      ORDER BY Customer.CompanyName;
Column Count:  2
Column Heads:  No
Column Widths:  0";2"
Bound Column:  2
List Rows:  8
List Width:  1
Limit to List:  Yes
Auto Expand:  Yes

AfterUpdate:

Private Sub Select_A_Company_AfterUpdate()
Dim RS As Recordset, sql As String
sql = "Select * from Customer where CompanyName='" & Me.Select_A_Company & "'"
Set RS = CurrentDb.OpenRecordset(sql)
    Me.CompanyName = RS.Fields("CompanyName")
    Me.Address = RS.Fields("Address")
    Me.City = RS.Fields("city")
    Me.StateOrProvidence = RS.Fields("StateOrProvidence")
    Me.Country = RS.Fields("Country")
    Me.PostalCode = RS.Fields("postalcode")
    Me.BestTimeToCall = RS.Fields("BestTimeToCall")
    Me.BusinessNo1 = RS.Fields("BusinessNo1")
    Me.Ext1 = RS.Fields("Ext1")
    Me.BusinessNo2 = RS.Fields("BusinessNo2")
    Me.Ext2 = RS.Fields("Ext2")
    Me.FaxNumber = RS.Fields("FaxNumber")
    Me.EmailAddress = RS.Fields("EmailAddress")
    Me.Notes = RS.Fields("Notes")
End Sub

On Not in List:

Private Sub Select_A_Company_NotInList(NewData As String, Response As Integer)
 Dim db As Database
 Dim rst As Recordset
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Customer")
   Dim intResponse As Integer
    intResponse = MsgBox("Would you like to add this item to the list?", vbYesNo, "oops")
    If intResponse = vbYes Then 'user clicked YES
   rst.AddNew
      rst!CompanyName = NewData
   rst.Update
Response = DATA_ERRADDED
    Else 'user clicked NO
        Me!CompanyName = ""
        Response = DATA_ERRCONTINUE
    End If
End Sub

ON THE FORM PROPERTIES:

Record Source:  Customer (table)

Private Sub Form_AfterUpdate()
Me.Select_A_Company.Requery
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OpenForm "frmCustomer", acNormal, , , acFormAdd
DoCmd.GoToRecord , , acNewRec
End Sub

Everything else is the default choices.

I figure if anything is wrong it has to be in the above.  Can anyone help me?
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12719495
Your combo box should not have a control source.  You're using it for selection, which means if you select something, whatever you select becomes the active value for the field it is using as a control source.  This is why you are duplicating entries across records.  Most likely, your other problems with bound controls not showing the correct information is related to this problem.  Remember that when you edit the contents of a bound control, you are not only editing the contents of the control but also the contents of the underlying field.  By binding your "Select A Company" control, you are essentially changing the company name for the current record every time you edit it.

I would suggest un-binding the combo box, and placing another bound text box on the form to hold the company name from the recordset.  When you select a company from the combo, use the OnAfterUpdate event to manually find and move to that record in the main form.  A filter should fit the bill nicely.  For your NotInList event, it should add a new record to the table with the name you typed, then move to/filter for the record on the rest of the form.  Remember that when you add the record to the Customer table, you will have to also requery the recordset for the main form and the combo box.  

0
 

Author Comment

by:Divinedar0923
ID: 12758598
Okay I got it to the point where I can select the company name and the other fields follow, but I'm still getting duplicates.  But I'm only getting duplicates in the two fields, CustomerID and CompanyName and then the other information goes to the next record with the CustomerID and CompanyName.

When you say filter, hate to sound dumb, but what kind of filter and where would it go.

This has been one long task for me and I'm ready to end it now.  Please Help.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12758918
The form's filter is found in the "Filter" (!) property.  It is basically the WHERE clause of a SQL statement, without the WHERE keyword.  For example, if my form is bound to table Customers, my RecordSource may look like this:

SELECT * FROM Customers

If I wanted to change the SQL to only pick up CustID=2, it would look like this:

SELECT * FROM Customers WHERE CustID = 2

That will work fine, but that limits my recordset to one record (CustID 2), and I'll have to .Requery to be able to change the RecordSource.  By using a filter, though, I can keep the scope of my original recordset, and just 'focus' on a particular sub-set of records.  The WHERE clause, minus the WHERE keyword, will be "CustID = 2"...now to make that VB code.  Let's assume I also put a button on my form...when the user puts a CustID in a textbox or combobox, they hit the button, and this code is executed:

Private Sub MyFindButton()

Me.Filter = "CustID = " & Me.MyCustIDBox
' The above line gets interpreted into something like:    Me.Filter = "CustID = 2"
' Remember to use single-quotes where necessary for text fields
' Me.Filter = "CustID = '" & Me.MyCustIDBox & "'"
Me.FilterOn = True

End Sub

The recordset will still have all the records, but the only ones that will appear now are those that have 2 in the CustID field.  I'm uncertain what affect this has on the RecordCount, etc., properties, so experiment if you do anything with it.

For the two fields that are still being duplicated, make sure they have the correct control source (are bound properly).  If necessary, delete the controls, and drag them over again from the field list available in the form's Design view.  Quick & easy with no typos.
0
 

Author Comment

by:Divinedar0923
ID: 12768420
Okay you lost me.  Let me tell you this.

What I did was added a separate combo box to pick from.  When I add a new name it lets me add the company and it gives it a CusotmerID number automatically.  The CustomerID number it gives only has the CustomerID number and the Company Name.  Then it gives a second record with the CustomerID, Company Name and all the other information that I fill in in the other bound fields.  

Now when you close the form and open it, it changes the first record to what ever is in focus last before you close the form.  It totally deletes the first record.

I don't know what I'm doing wrong.

Question for you:

In the form properties I have a filter now that says SELECT * FROM Customer and the source is the Customer Table.

In the combo box properties I have the row source also as a sql SELECT DISTINCTROW Customer.CustomerID, Customer.CompanyName FROM Customer GROUP BY Customer.CustomerID, Customer.CompanyName ORDER BY Customer.CompanyName;

Does this make difference?  Does one sql kick the other sql out?

When I select a company, it gives me all the information for that company, but it's just when I either add a new company it gives me duplicates and when I close the form and reopen it, it gives me duplicates.  WHAT AM I DOING WRONG?
0
 

Author Comment

by:Divinedar0923
ID: 12768446
One more thing.  When I pick a company from the list, it gives all the correct information except for the CustomerID number.  It never changes with the other information and it is also my primary key, not the CompanyName.

I need to tie the CustomerID number in with the CompanyName so when I pick from the list the CustomerID also changes to the correct CustomerID number.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1500 total points
ID: 12773734
The problem with showing only the last record entered is a result of not removing the filter.  Once the filter is applied, it stays applied until you tell it otherwise.  You can remove the filter by using the following line of code anywhere you would like to restore the entire recordset:

Me.FilterOn = False

For your CustomerID number, make sure the control is bound to the proper field, in this case Me!CustomerID.  Since it is your PK for the underlying table, you would do well to disable or lock this control to prevent any editing.

The rest of your controls should also be bound to their proper fields...use them as an example since they seem to be working properly.  The ControlSource property for each of the controls should be the name of the field to which it is bound.  The combobox you are using for selection and searching should not have a ControlSource, and the RowSource you posted for it looks like it should work fine.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

864 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