Results 1 to 9 of 9
  1. #1
    Thumbs is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    25

    Prevent combo box from updating first record in list

    Hello,



    I have a search form set up with a combination of bound and unbound combo boxes that populates a subform on the same page to display the search results. When I change the value in the bound combo boxes, it changes the value of that respective field in the first row in the table to whatever I selected (or null if I remove the value from the box). I have done a lot of searching on this issue, but I havent been able to find a solid solution.

    I have seen some suggestions for deteting the control source and manually specifying values in an unbound combobox, however, the actual search criteria for these bound combo boxes is the numeric ID, not the actual displayed value. I cannot seem to figure out how to create a value list with 2 columns, hide the ID column, and yet use the ID as the value that gets passed to the query to return the correct results.

    By the way, I am an access newbie. I am just teaching myself as go on what has turned out to be a very complex project.

    Any help or suggestions would be much appreciated. Thank you.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry, but they are correct. You will have to un-bind the combo box in order for it not to update the underlying data.

  3. #3
    Thumbs is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    25
    Well, I figured that was probably the case, as I couldnt find any evidence to say otherwise.

    The real question then is how do I create the same look and functionality with a value list? I need to create 2 columns, one for the ID field and one for the string value that the user will actually see when he/she selects the drop down list. I want the ID field to be hidden, but to be used as the value that is passed to the query (since the column in the table is a number column with a relationship to the respective table its gets its values from) so the search returns valid results.

    I already tried removing the ID field and doing everything with string values, but I couldnt ever get it to work right. I spent 4 straight hours thinkering with it before I just reverted back to the backup I made before starting that process.

    If anyone has a suggestion for a different method that I havent mentioned, I am all ears.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The normal way of doing a search form is - as you have done - to have a main form with search boxes and then to display the data in the subform. The main form would be unbound, or else attached to a parent table. If the latter, then in the OnClick event you would search for and display that parent record, requery the subform which would display the relevant records.

  5. #5
    Thumbs is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    25
    I guess I'm still not following. What I need to know is how I can pass a value (that is selected from an explicit list to prevent mistypes and so the user knows all of the options available for that field) to the query without that value being visible in the combobox, and without binding the field to a table. Basically, I want it to function like a bound field, only without screwing up my data.

    The search works just fine, minus the deletion or modification of the first record due to the bound list. I have the properties of the subform set up so that it is read only for the search form. There has to be a relatively simple solution here, I just can't seem to find it.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Why is your main form bound to a table?

    There are many ways to find a record - you can google it, how to get a selected record. Personally I use filters - once they select a value I filter the form using that value and then requery it.

    I presume your combo box has more than one value and you are trying to not show the first/key field? Such as CustomerID and CustomerName? In the ColumnWidths property you would put the first width to 0.

    Attach your database if you think that will help.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about a simple example to help us understand this?

    What I need to know is how I can pass a value (that is selected from an explicit list to prevent mistypes and so the user knows all of the options available for that field) to the query without that value being visible in the combobox, and without binding the field to a table. Basically, I want it to function like a bound field, only without screwing up my data.
    Lead us through what it is you are trying to do.

  8. #8
    Thumbs is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    25
    Unfortunately, for security classification purposes, I cannot attach the database without stripping all of the data out (which wouldnt do you guys a whole lot of good anyway).

    I am designing an asset management database that does a lot more than just managing and searching assets, but right now, I am just on the basic asset search. I have a table that contains all of the assets. This is where the bulk of the data is stored. Some of the fields in that table are linked to other tables. For example: I have an AssetType field in the table that gets its values from another table that contains an autonumbered ID field column and a column that contains the actual text values that I want to show up. The autonumbered column is the key field.

    I created a form to search that database using certain criteria. I have not allowed the user to enter a search parameter for every column in the table. Instead, I limited it to about 9 fields that would be important. Some of these fields, like the AssetType field, are currently set as bound because I want to allow the user to see all 10 or so values that are available for that parameter without having to try to know the explicit term used for that classification of asset when they need to find one (this is government stuff, which makes the naming conventions a little convoluted sometimes).

    I have a query that displays in a subform below the search fields to display the results of the search.

    Basically, I want the user to be able to search any combination of fields to return that results. As I mentioned, the search works great. I can enter any combination of information and return the records I am looking for. I am about ready, this point, to just leave the first record in the table blank or fill it with dummy data. That way, when it gets modified by the bound fields, it wouldnt matter. This isnt the proper and clean way to do it though...and I dont want my name on an application that looks haphazardly thrown together.

    And thanks in advance for your help and patience. I am a network engineer, not a developer, so a lot of these concepts are new to me, but I'm just trying to chug along here.

  9. #9
    Thumbs is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    25
    Well, I managed to find a solution. After another attempt at deleting the autonumbered ID field and using a single column for the value list with manually entered values that match the table they are searching, I got it to work flawlessly.

    As it turns out, the whole reason I couldnt get it to work without the ID field in the first place was that I had never changed my column count field from 2 to 1, which was causing me a world of headaches. Oh, technology, how we love thee...

    Thanks anyway folks, I really do appreciate it.

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

Similar Threads

  1. updating a drop down list
    By MissVinegar in forum Forms
    Replies: 8
    Last Post: 01-12-2012, 08:23 AM
  2. Replies: 2
    Last Post: 12-07-2011, 02:51 AM
  3. prevent diplicate record
    By miziri in forum Access
    Replies: 3
    Last Post: 08-22-2011, 12:34 PM
  4. Replies: 2
    Last Post: 05-11-2011, 02:58 PM
  5. List box not updating still
    By cowboy in forum Forms
    Replies: 0
    Last Post: 03-16-2010, 04:43 PM

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