Forum Discussion

DinkyDon's avatar
DinkyDon
Copper Contributor
Jun 09, 2021

Problem with list box selecting individual records

Hi, I'm currently building a database with a 'master' form that I want everyone to use instead of meddling around in the tables and such. I've got a couple of sub-forms opening up from here, including new record and edit record. 

 

The problem lies with edit record, as I have made a list box to display the results from a search box. The results show up fine, and all records from each client are visible. However, when I go to select a specific record, it defaults to only one record per client. As most clients have multiple instances of software/hardware, it doesn't allow me to open any of the alternative records, only one record per client. 

 

I'm using a button to open the edit data sub form, and the code I have for the on click event is:

 

DoCmd.OpenForm "EditData", , , "[Client_Database.Client]=" & " ' " & Me.EditRecordResults.Column(0) & " ' "

 

The EditRecordResults is the list box, and the after update code I have for the list box is:

 

EditRecordButton.Enabled = True

 

EditRecordButton is the same button I have the on click event for.

 

Any ideas what could be causing this? I'm not sure if the event code causing the issue, or if access's default list box settings are limiting my options, although I don't know how to change these either. 

 

Any help would be greatly appreciated!

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    DinkyDon 

    To offer an in-depth answer, we'd need more detail, but in general it's possible to suggest some things for you to review.

    First, the List box is bound to a particular column, which by default Column(0). As an aside, columns in list boxes as well as combo boxes are indexed from left to right, starting with 0. That means the first column on the left is Column(0). That's the one you are using according to your posted code. 

    That column and selected row in the list box refers to one and only one value. That's the one which you indicate is being used to filter the second form, i.e. "EditData". And that is why it only displays a single record. 

     

    In order to select MULTIPLE records, you have to figure out which values are common to all of the desired records, and that would be the one which relates to records in the other table containing those multiple records. 

    This is just a guess, based on 25+ years of experience and the hints available in your description. I imagine you have two tables, not one. And these tables would be

    • "Client" in which the basic Client data is stored. Each client has one unique ID field, probably an AutoNumber. 
    • "Equipment" in which all of the equipment assigned or issued or sold to clients is recorded. Each Equipment record also has one unique ID field, probably also an AutoNumber. HOWEVER, and this is the important point, this Equipment table (or whatever you called your version of it) also has a field for the ClientID that relates back to the Client table. This allows you to record one or more pieces of equipment (software or hardware) for each client. And THAT is the table you want to refer to when opening this "Edit Data" form and that is the ClientID you want to pass to it in your VBA.

    It sounds like one of two possibilities is where the problem lies.

    Either the "Edit Data" form is bound to the Client table, so you only get that one client record. You can not show multiple equipment records from the second, Equipment, table that way.

     

    Another possibility is that the list box is bound to the Equipment table since what you want to do is work with multiple equipment records. And that means you should pass the ClientID to it, not the EquipmentID. If this is the scenario that applies to your situation, that means, I think, your list box Column(0) actually is bound to the EquipmentID, not to the ClientID and that is the value being sent to the Edit Data form. 

    Since I can't see how this is all set up in your form, as I say, this is just a series of educated guesses. Hopefully, though, it can send you in the right direction to look for answers.