ACCESS VBA Question - FAO Anthony

Hi Anthony

I have replicated the VBA code form we put together on the course, but I want the 2nd object Box to only pull through materials for the supplier picked in the first un-bond object box? Then with the ability to see letters sent for that material in the bottom box.

I've attached a print screen.

this is my code, what do I need to change in the 2nd combo please?

Firstly, they select the supplier, then choose materials, currently it just pulls my entire material table list in.

Then I click analyse, and it should bring up all communications between us and the supplier, for that chosen material/supplier option, then it add's the number of comms in the bottom text box.

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 SupplierID = " & Chr(34) & strSupplier & Chr(34))

Call DeleteSupplierDetails

Me.lstSupplierDetails.RowSourceType = "Value List"
Me.lstSupplierDetails.ColumnCount = 2
Me.lstSupplierDetails.AddItem "Supplier ID;" & rstSupplier.Fields("SupplierID")
Me.lstSupplierDetails.AddItem "Supplier Details;" & rstSupplier.Fields("SupplierDetails")


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


Private Sub cmdAnalysis_Click()

'this subroutine contains the code to drill further into the comms by supplier

Dim strSupplier As String
Dim strMaterial As String

strSupplier = Me.cmbSupplier.Value
strMaterial = Me.cmbMaterial.Value

strSQL = "SELECT CommunicationTypeID, CommunicationLogDate, SubstanceGroupID, CommunicationLogNotes "
strSQL = strSQL & "FROM qryCommsLogList where SupplierDetails = "
strSQL = strSQL & Chr(34) & strSupplier & Chr(34) & "AND MaterialDescription = "
strSQL = strSQL & Chr(34) & strMaterial & Chr(34)


'MsgBox strSQL

Me.lstCommsDetails.RowSourceType = "Table/Query"
Me.lstCommsDetails.ColumnCount = 4
Me.lstCommsDetails.ColumnHeads = True
Me.lstCommsDetails.RowSource = strSQL
Me.lstCommsDetails.ColumnWidths = "4cm; 4cm; 3.2cm; 6cm"

If Me.lstCommsDetails.ListCount = 0 Then

MsgBox "No Records Found"

Exit Sub

End If

Call orderSummary

End Sub


Sub orderSummary()

Dim dbdata As DAO.Database
Dim rstsales As DAO.Recordset

Dim Intcount As Integer

Set dbdata = CurrentDb

Set rstsales = dbdata.OpenRecordset(strSQL)



rstsales.MoveLast

Intcount = rstsales.RecordCount

'MsgBox Intcount

Me.txtItems.Value = Intcount

rstsales.MoveFirst

End Sub

Private Sub cmdCloseForm_Click()

Dim bytResponse As Byte

bytResponse = MsgBox("Sure about that?", vbYesNo + vbExclamation, "Warning")

If bytResponse = vbYes Then


DoCmd.Close

End If

End Sub

Private Sub Combo41_BeforeUpdate(Cancel As Integer)

Dim strSupplier As String

strSupplier = Me.cmbSupplier.Value

strSQL = "SELECT MaterialID, MaterialName"
strSQL = strSQL & "FROM qryMaterialList where SupplierDetails = "

'MsgBox strSQL

Me.Combo43.RowSourceType = "Table/Query"
Me.Combo43.ColumnCount = 2
Me.Combo43.ColumnHeads = True
Me.Combo43.RowSource = strSQL
Me.Combo43.ColumnWidths = "4cm; 4cm"

End Sub

I don't know what to put into the 2nd combo box, how to select materials for the supplier selected above, I've tried linking the supplier id with my materials by supplier report, but it doesn't work.

Also, what code would I need, to make a more user friendly search box in the header on my supplier (and material forms)? I'd like them to be able to pick to search by supplier name, or to select to search by the suppliers ID number?

Thanks so much

ps am still working through my sister in laws auction database at home, I want to do more coding/Access at home to keep my hand in and improve my skills, as I don't get chance to do as much at work as I'd like.

Best wishes
Clair