I'm trying to ignore blank values, the below code takes dropdown.value which is a string, then look up that value and pull a number next to it in a different table.
The code works if all dropdowns are populated, but when one is empty it shows an error.
I looked online and found the Nz() expression but it still isn't working, is there a way to ignore blank dropdowns in my code OR just add the value at the end as a 0?
Thank you
Dim db As DAO.Database
Set db = CurrentDb
Dim Kitchen As DAO.Recordset
Dim strSQLKitchen As String
Dim WC As DAO.Recordset
Dim strSQLWC As String
Dim Bath As DAO.Recordset
Dim strSQLBath As String
Dim ENSuiteA As DAO.Recordset
Dim strSQLENSuiteA As String
Dim ENSuiteB As DAO.Recordset
Dim strSQLENSuiteB As String
Dim Other As DAO.Recordset
Dim strSQLOther As String
'lookup dropdown value and grab a number that resides next to it in a table
strSQLKitchen = "SELECT EOValue FROM Trims WHERE Trim = """ & KitchenTrimType.Value & """"
strSQLWC = "SELECT EOValue FROM Trims WHERE Trim = """ & WCTrimType.Value & """"
strSQLBath = "SELECT EOValue FROM Trims WHERE Trim = """ & BathTrimType.Value & """"
strSQLENSuiteA = "SELECT EOValue FROM Trims WHERE Trim = """ & ENSuiteATrimType.Value & """"
strSQLENSuiteB = "SELECT EOValue FROM Trims WHERE Trim = """ & ENSuiteBTrimType.Value & """"
strSQLOther = "SELECT EOValue FROM Trims WHERE Trim = """ & OtherTrimType.Value & """"
Set Kitchen = db.OpenRecordset(strSQLKitchen)
Set WC = db.OpenRecordset(strSQLWC)
Set Bath = db.OpenRecordset(strSQLBath)
Set ENSuiteA = db.OpenRecordset(strSQLENSuiteA)
Set ENSuiteB = db.OpenRecordset(strSQLENSuiteB)
Set Other = db.OpenRecordset(strSQLOther)
'debug
MsgBox (Nz(Kitchen.Fields(0).Value))
MsgBox (Nz(WC.Fields(0).Value))
MsgBox (Nz(Bath.Fields(0).Value))
MsgBox (Nz(ENSuiteA.Fields(0).Value))
MsgBox (Nz(ENSuiteB.Fields(0).Value))
MsgBox (Nz(Other.Fields(0).Value))
'populate box on form
FormTrimValue.Value = Nz(Kitchen.Fields(0).Value) + Nz(WC.Fields(0).Value) + Nz(Bath.Fields(0).Value) + Nz(ENSuiteA.Fields(0).Value) + Nz(ENSuiteB.Fields(0).Value) + Nz(Other.Fields(0).Value)
Edit:
Error:
'No Current Record'
You need only one recordset and an array to achieve this: