Modify VBA routine to pass Form combo box value into Tbl created by event procedure

[JimStrand]

I have a form which allows the user to select a drop down filter for several roles in a company (think SVP, VP, ect.) After user clicks a the Combo box filter and selects a name within the values list, a VBA procedure is run that creates a [propertytbl] containing all properties and there parameters associated with that person. Along with the properties and associated parameters in a table called I want a field (call it Role) in [PropertyTbl] table which will contain the persons name we selected using the combo box value named - cboEVPOName. Any ideas on how to modify this procedure to achieve desired result? Thank you in advance.

Private Sub cboEVPOName_AfterUpdate()

Me.cboDivisionName = vbNullString
Me.cboDivisionName.Requery
Me.cboRegionName = vbNullString
Me.cboRegionName.Requery
Me.cboPropertyName = vbNullString
Me.cboPropertyName.Requery


Me.cboEVPSName = vbNullString
Me.cboEVPSName.Requery

Me.cboEVPFName = vbNullString
Me.cboEVPFName.Requery

Me.cboSVPRMName = vbNullString
Me.cboSVPRMName.Requery

Me.cboSVPOName = vbNullString
Me.cboSVPOName.Requery

Me.cboVPOName = vbNullString
Me.cboVPOName.Requery

Me.cboSVPSName = vbNullString
Me.cboSVPSName.Requery

Me.cboVPSName = vbNullString
Me.cboVPSName.Requery

Me.cboVPFName = vbNullString
Me.cboVPFName.Requery

Me.cboRDRMName = vbNullString
Me.cboRDRMName.Requery

Me.cboADRMName = vbNullString
Me.cboADRMName.Requery


DoCmd.SetWarnings False

Dim pmDelete As String
pmDelete = ""
pmDelete = pmDelete & "Delete * from PropertyTbl;"

DoCmd.RunSQL pmDelete

Dim db As Database
Dim qdf As QueryDef
Dim tbl As TableDef


Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")


Dim strProperty As String


strProperty = Forms![checkmax]!cboEVPOName

Dim StrSQL As String

StrSQL = ""

StrSQL = StrSQL & " SELECT Property.PropertyCode,"
StrSQL = StrSQL & " Property.PropertyName, "
StrSQL = StrSQL & " Property.Region, "
StrSQL = StrSQL & " Property.RegionName, "
StrSQL = StrSQL & " Property.Division, Property.DivisionName, "
StrSQL = StrSQL & " Property.EVPO, "
StrSQL = StrSQL & " Property.EVPS, "
StrSQL = StrSQL & " Property.EVPF, "
StrSQL = StrSQL & " Property.SVPRM, "
StrSQL = StrSQL & " Property.HR, "
StrSQL = StrSQL & " Property.VPF, "
StrSQL = StrSQL & " Property.RDRM, "
StrSQL = StrSQL & " Property.ADRM, "
StrSQL = StrSQL & " Property.SVPS, "
StrSQL = StrSQL & " Property.VPS, "
StrSQL = StrSQL & " Property.VPO, "
StrSQL = StrSQL & " Property.AGM, "
StrSQL = StrSQL & " Property.SVPO, "
StrSQL = StrSQL & " Property.GM, "
StrSQL = StrSQL & " Property.Portfolio "
StrSQL = StrSQL & " INTO PropertyTbl "
StrSQL = StrSQL & " FROM Property "
StrSQL = StrSQL & " WHERE (((Property.EVPO) "
StrSQL = StrSQL & " =[forms]![checkmax].[cboEVPOName]));"


Debug.Print StrSQL

qdf.SQL = StrSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "qdf"

DoCmd.SetWarnings True


End Sub

 

[JimStrand]

I think I solved my own question for Once. By adding this code before my End sub statement I get the intended result:



'UPDATE PropertyTbl SET PropertyTbl.Portfolio = [Forms]![checkmax]![cboEVPOName];

DoCmd.SetWarnings False

Dim pmUpdate As String
pmUpdate = ""
pmUpdate = pmUpdate & "UPDATE PropertyTbl SET PropertyTbl.Portfolio = [Forms]![checkmax]![cboEVPOName];"

DoCmd.RunSQL pmUpdate

End Sub