Combobox driven listbox issues

[boostbyslinky]

Forgive me, for I have sinned.
It has been some time since my last post and an almost equal amount of time since my last piece of access development.

(Enough of that melodrama, let's cut to the chase)....

I'm building a form on which I want the end users to select a site reference from a combobox (easy part, that's sorted). Selecting the site reference will then populate a listbox (although this could be subject to change) with data delivered by the following vba element..

Private Sub Site_Select_1_Change()

List8.RowSource = "SELECT Hardware.Type, Hardware.Supplier, Hardware.Manufacturer, Hardware.Model, Hardware.Serial_Number, Hardware.Installation_Date, Hardware.Comments FROM Site_Data INNER JOIN Hardware ON Site_Data.ID = Hardware.Site_Reference WHERE (((Hardware.Status) = 1) And ((Site_Data.Site_Reference) = """ & Site_Select_1.Value & """)) GROUP BY Hardware.Type, Hardware.Supplier, Hardware.Manufacturer, Hardware.Model, Hardware.Serial_Number, Hardware.Installation_Date, Hardware.Comments; "

End Sub

Now the intelligent amongst the audience will have spotted my problem immediately and drawn the conclusion that the record ID will be returned from each table, and not the actual data that I'm wishing to display to the end user..

Congratulations to you, you've earned a trip to the coffee machine!

Now, once you've refilled your fuel tanks, can you give me some guidance around how the hell I'm going to get this form to display what I want? I know I'm probably missing something silly, but I'm blaming my time away from the grind stone..

Off to say some hail marys!

 

[pr2-eugin]

Okay from the code you have given..

* Best to use AfterUpdate() rather than Change()..

* Of the Following fields Hardware.Type, Hardware.Supplier, Hardware.Manufacturer, Hardware.Model, Hardware.Serial_Number, Hardware.Installation_Date, Hardware.Comments Which data do you wish to show?

* How many columns does your Lisbox have??

 

[boostbyslinky]

Thanks for responding..

I'll amend the change() to AfterUpdate(), thanks for that one..

I wish to display data from all of the fields mentioned. The intention is to show assets attached to the site in question.

The listbox has 7 columns.

I guess I should add that the hardware table is primarily built with lookups to other tables, so the supplier value is drawn from the supplier table, etc etc.. It is these values that are being problematic.. The date and comment fields are being populated correctly...

As I'm at a very early stage in the development of the database, it is totally feasible to move to an alternative method of storing the values in the tables..

Thanks again..

 

[pr2-eugin]

Hello.. Try this as your code..

Private Sub Site_Select_1_AfterUpdate()
    Dim dbObj AS DAO.Database
    Dim rst AS DAO.Recordset
    Dim strSQL As String
    Me.[B][COLOR=Blue]List3[/COLOR][/B].RowSource = ""
    strSQL = "SELECT Hardware.Type, Hardware.Supplier, Hardware.Manufacturer, Hardware.Model, Hardware.Serial_Number, Hardware.Installation_Date, Hardware.Comments FROM Site_Data INNER JOIN Hardware ON Site_Data.ID = Hardware.Site_Reference WHERE (((Hardware.Status) = 1) And ((Site_Data.Site_Reference) = """ & Site_Select_1.Value & """)) GROUP BY Hardware.Type, Hardware.Supplier, Hardware.Manufacturer, Hardware.Model, Hardware.Serial_Number, Hardware.Installation_Date, Hardware.Comments;"
    
    Set dbObj = CurrentDB
    Set rst = dbObj.OpenRecordset(strSQL)
    
    Do While Not rst.EOF
        Me.[COLOR=Blue][B]List3[/B][/COLOR].AddItem (rst!Type & ";" & rst!Supplier & ";" & rst!Manufacturer & ";" & rst!Model & ";" & rst!Serial_Number & ";" & rst!Installation_Date & ";" & rst!Comments)
        rst.MoveNext
    Loop
    rst.Close
End Sub

The AddItem method adds the data into the listbox.. by using the semi colon you are separating the columns.. Post back if you find something hard to follow.. Blue bits need to change..

 

[boostbyslinky]

Thanks for the code Paul..
I've ameneded the necessary entries (those in blue) and changed the properties of the listbox to be a value list.. ........ but...

Unfortunately, it's still pulling the IDs rather than the values..

harrumph..

 

[pr2-eugin]

Right I missed that entry you made in Post #3.. yes, you should not have LookUp in table level.. it is well explained here.. However.. What you need to do is Join.. by the look of it several Joins..

 

[boostbyslinky]

Joins I can cope with...

Hi ho, hi ho, it's off to kill lookups I go...

Big thanks for your help Paul..

 

[pr2-eugin]

You are most welcome.. Here is a link to Bob's site on removing LookUp's at table level (if you needed it)..

 

[boostbyslinky]

That link was appreciated..

I will be designing routines to add/move/retire hardware from the database, so will use values from the additional tables at that point to force the selection of correct data..

Cheers.

 

[nanscombe]

The attached database should give you a few pointers of how I would go about achieving what you are trying to do.

Have a nose around and I hope it helps.

 

[boostbyslinky]

Nigel, that, is blooming fantastic!

Unfortunately, I can only click Thanks once!!!

 

[nanscombe]

You're wellcome.