Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: Issue with Edit Form

    Hi,

    I have a main form with a button which, when clicked, opens up a form with a combo box to choose (or enter in) an product number. On this second form with the combo box, I have another button, which when pressed finds the record related to that ID number and opens up a third form which is the edit form.

    My issue is that every time I select the first product number from the drop down combo box and click the "Find" button, it brings up two records of this. Somehow, it is changing one of the other product numbers to this product number (the one listed first on the list) but keeping all of the other fields the same (which is how I can tell that it's getting changed... the information for the two product numbers which are the same is completely different). Does anyone know why this is occurring or how I can prevent this from happening?

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    make certain the combo box isn't "bound" ie the source is not set to a column in the underlying table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    57
    It's bound to the field in the table that I want to have it filter by...

    So in the combo box it gives me the list of all the product numbers, so I can choose one and then click the "Find" button which brings up the edit form. I think it needs to be bound in order to find an existing product number...

  4. #4
    Join Date
    Nov 2011
    Posts
    57
    I made the product number a primary field so that no duplicates can be made which fixes my initial problem of that duplicate number being made.
    However, now when I try to close the form in which you choose the product number and click "Find" to apply the filter, 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 field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    Followed by:
    "You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyways?"

    I'm assuming this is happening because it is once again trying to change that first value to a duplicate of a value that already exists in the table. But I don't understand why this is happening. Is there a way to stop this from happening?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no: don't bind the control to an underlying column if you want to use the list/combobox as a means of searching. if you are suign a bound control then changing the value of that control will change the value of column in the underlying table

    what you should be doing is placing some code behind the combo to apply the filter
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the combo is bound, changing its value will also change the contents of the field to which it is bound into the table, the same way that when the contents of a textbox is changed, the form becomes "Dirty" and the modification is saved to the table when the current record of the form is saved.
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Posts
    57
    Do you have any examples of what type of code needs to be placed to apply the filter?
    I have no VB experience, which is why I've been trying to use all of the Access tools to accomplish everything I need...

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look at the help system (f1)
    search google for MS Access apply filter
    or
    ms access apply filter - Google Search

    but
    1) change the combo to be an unbound control
    2) click the combo, right click, select build event, select code
    in the combo relevant event (can't tell you becuase its a while since Ive used VBA fdor this sort of thing)
    place some that is similar to

    me.filter = "mycolumn = anumericvalue" ' assuming its a numeric value
    me.filteron=true 'turn the filter on

    me.filter = "mycolumn = 'astringvalue'" ' assuming its a string value
    me.filteron=true 'turn the filter on

    me.filter = "mycolumn = '#adatevalue#" ' assuming its a datetime value
    me.filteron=true 'turn the filter on

    if the value is coming from a list or combo box control
    me.filter = "mycolumn = " & mycontrol.value ' assuming its a numeric value
    me.filteron=true 'turn the filter on

    me.filter = "mycolumn = '" & mycontol.value & "'" ' assuming its a string value
    me.filteron=true 'turn the filter on

    me.filter = "mycolumn = " & format(mycontrol.value, "#mm/dd/yyyy#") ' assuming its a datetime value
    me.filteron=true 'turn the filter on

    you may also need another button to turn the filter off. in that buttons on click event
    me.filteron = false

    note that
    text / string literals should be enclosed by ' eg "acolumn = 'mytext'"
    date / time values should be expressed as US or ISO date format with the #
    eg:-
    "mydatecolumn = #12/02/2011#"
    OR
    "mydatecolumn = #2011/12/02#"
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2011
    Posts
    57
    Perfect! Thanks for the help. I still have a few kinks to work out but I think it's going to work.

Posting Permissions

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