Results 1 to 8 of 8
  1. #1
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23

    Using Find and Replace on Combo Boxes

    My form has a bunch of combo boxes for entering order information. Some of these fields change regularly in mass (ex: a product will move locations throughout its process) . When I try to use the Find and Replace (ctrl +h) I get this error.

    "This table contains one or more lookup fields. display values for lookup fields cannot be changed by using the replace operation."



    I've attached an image of the combo box properties. Any help around this issue would be appreciated.




    Click image for larger version. 

Name:	Capture.JPG 
Views:	11 
Size:	46.7 KB 
ID:	38965

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    First of all, advise not to build lookups in table.

    Second, are you doing Search/Replace for SlideLocationID value or the alias Location text? The actual saved value is the ID, not the descriptive text.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23
    I did not build lookups in the tables.


    I am trying to store the primary key of the tblSlideLocation as the foreign key in
    tblSlide but I am trying to interact with the Text rather than the autogenerated number within the form.

    Is there other information that I can provide that will help convey my issue?

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Why can't you just select the new product location from the dropdown on the form? That's the whole idea of a combobox function.
    Are you trying to add new locations to tblSlideLocation? The not in list event will allow that.

  5. #5
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23
    A typical order has multiple products (average 15...but 100 is not uncommon) and the location of those products can be the same for the entire order or some subset. So changing en masse is preferred when necessary.

    In the image below a common change would be to change all "E" locations to "S" locations or potentially all locations * to "Chkout"

    Click image for larger version. 

Name:	Capture.JPG 
Views:	9 
Size:	116.5 KB 
ID:	38970

    These are values in the corresponding table, but my understanding was that I should be storing foreign keys rather than the text that they represent.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    suggest you remove the combo control for this case. IMHO most often they are better used as controls for criteria. Used in that fashion, anyone can accidentally alter the location by selecting a different value, and they make for a poor means of altering a bunch of records en masse. As a textbox, it would appear as the rest of the form fields (based on pic). You'd use control(s) on the main form or form header as a means of updating, so after making said change, run an update sql for this order and change the location to be the main/header control value and requery the datasheet.
    Last edited by Micron; 07-02-2019 at 03:06 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23
    heh.. Most of those form fields are combo boxes as well.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I just tested Find and Replace on a form with comboboxes using alias. Won't work. Doesn't matter if I search for the saved value or the alias text. The find-only works, but replace fails.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find and Replace
    By Keibri in forum Access
    Replies: 3
    Last Post: 05-31-2019, 11:43 AM
  2. you can't use find or replace now
    By ntambomvu in forum Access
    Replies: 2
    Last Post: 10-25-2017, 02:58 PM
  3. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  4. Find and Replace
    By dweekley in forum Queries
    Replies: 3
    Last Post: 04-12-2013, 07:16 AM
  5. Find as you type Combo boxes in MS Access
    By HAPPYWITHU in forum Programming
    Replies: 13
    Last Post: 06-03-2010, 08:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums