Selection VBA on Form

[dualvba]

Hi



I have created a form, using only VBA code.

However, what do I need to change, in the Materials Code below, to make it select only Materials in the pick list, for the supplier picked in the Supplier box above, please?

The form, basically, asks you to select a Supplier first, from a picklist, and it shows the suppliers details, then the material box, currently shows the entire list of materials, I'd like it to show the materials only, for that supplier to pick from, then you click analyse, and it shows in the bottom box, the list of comms from a comms log table, between us and the supplier, the small count box at the moment, simply just adds the total number of comms.

Thanks so much, here's my code, it clears the supplier and material details each time you select a new supplier, and then material:

Private Sub cmbSupplier_AfterUpdate()
Dim strSupplier As String
Dim dbdata As DAO.Database
Dim rstSupplier As DAO.Recordset
strSupplier = Me.cmbSupplier.Value
Set dbdata = CurrentDb
Set rstSupplier = dbdata.OpenRecordset("SELECT * from tblSuppliersList WHERE SupplierDetails = " & Chr(34) & strSupplier & Chr(34))
'MsgBox strSupplier
Call DeleteSupplierDetails
Me.lstSupplierDetails.RowSourceType = "Value List"
Me.lstSupplierDetails.ColumnCount = 2
Me.lstSupplierDetails.AddItem "Supplier Details;" & rstSupplier.Fields("SupplierDetails")
Me.lstSupplierDetails.AddItem "Supplier ID;" & rstSupplier.Fields("SupplierID")
End Sub

Sub DeleteSupplierDetails()
Dim intSupplierName As Integer
Me.lstSupplierDetails.RowSourceType = "Value List"
For intSupplierName = Me.lstSupplierDetails.ListCount - 1 To 0 Step -1
Me.lstSupplierDetails.RemoveItem (intSupplierName)
Next intSupplierName
End Sub

Private Sub cmbMaterial_AfterUpdate()
Dim strMaterial As String
Dim dbdata As DAO.Database
Dim rstMaterial As DAO.Recordset
strMaterial = Me.cmbMaterial.Value
Set dbdata = CurrentDb
Set rstMaterial = dbdata.OpenRecordset("SELECT * from tblmaterialslist WHERE MaterialDescription = " & Chr(34) & strMaterial & Chr(34))
'MsgBox strMaterial
Call DeleteMaterialDetails
Me.lstMaterialDetails.RowSourceType = "Value List"
Me.lstMaterialDetails.ColumnCount = 2
Me.lstMaterialDetails.AddItem "Material Record ID;" & rstMaterial.Fields("MaterialRecordID")
Me.lstMaterialDetails.AddItem "Material Name;" & rstMaterial.Fields("MaterialName")
Me.lstMaterialDetails.AddItem "Material ID;" & rstMaterial.Fields("MaterialID")
End Sub

Sub DeleteMaterialDetails()
Dim intMaterialName As Integer
Me.lstMaterialDetails.RowSourceType = "Value List"
For intMaterialName = Me.lstMaterialDetails.ListCount - 1 To 0 Step -1
Me.lstMaterialDetails.RemoveItem (intMaterialName)
Next intMaterialName
End Sub

[TG_W]

All of the above seems a bit over complicated. If all of the information you are wanting to cue off of is connected in a table/tables, why not set the Row Source Type to Table/Query? See the other thread I just guided someone else one.

https://www.accessforums.net/showthr...x-with-subform

Hope that helps.

[dualvba]

Oh brill, thank you, I'll look into that