Populate Combo Boxes using Form_Current Options

[James D Smooth]

I've got 3 unbound combo boxes on my form that are used to narrow
down
and find results in a couple of tables that are used in an Insert
Process. The combo boxes work as expected except when cycling through
records they do not show the values that should be associated. I've
tried using the Form_Current function in order to populate the values
when the record in shown, but for some reason it only works for 1 of
the 3 combo boxes (Licensor). I've attached the Form_Current Logic
that I've currently got in place. Any help would be greatly
appreciated, I'm sure it's something that I'm overlooking, but after
2
days of playing with this code I still can't seem to figure out what
I've missed.

Private Sub Form_Current()
Dim LIC_ID As Integer
Dim strSQL As String
Dim AccessDB As DAO.Database
Dim tmpQry As QueryDef
Dim rst As DAO.Recordset


Set AccessDB = CurrentDb()
LIC_ID = IsNull(Me.LIC_ID)
If LIC_ID = False Then
strSQL = "SELECT DISTINCT lic.RLTY_CD, lic.LICENSOR,
apc.ACCT_PROF_DESC FROM ACCT_PROF AS apc INNER JOIN LICENSES AS lic
ON
apc.ACCT_ID = lic.ACCT_ID WHERE lic.HISTORIC = 0 AND lic.LIC_ID = " &
Me!LIC_ID
Set tmpQry = AccessDB.CreateQueryDef("Update Combo", strSQL)
Set rst = AccessDB.OpenRecordset("Update Combo", dbOpenDynaset,
dbSeeChanges)
DoCmd.DeleteObject acQuery, "Update Combo"
Me.cmb_Rlty_Cd = rst!RLTY_CD
Me.cmbLicensor = rst!LICENSOR
Me.cmbAcctProf = rst!acct_prof_desc
Me.cmb_Rlty_Cd.RowSource = "SELECT DISTINCT RLTY_CD FROM LICENSES
WHERE HISTORIC = 0 AND LIC_ID = " & Me!LIC_ID
Else
Me.cmb_Rlty_Cd.RowSource = "SELECT DISTINCT RLTY_CD FROM LICENSES
WHERE HISTORIC = 0 ORDER BY RLTY_CD"
cmb_Rlty_Cd = Null
cmbLicensor = Null
cmbAcctProf = Null
End If
Me!cmb_Rlty_Cd.Requery
Me!cmbLicensor.Requery
Me!cmbAcctProf.Requery
End Sub

 

[Arvin Meyer [MVP]]

Unbound combo boxes are independent of the data in the form. If you bind the
combo to the form, changing it will change data on the form. If you find it
disturbing that it isn't in sync with the form, it is better to clear the
contents of the 3 combos in the Current event of the form. Something like:

Private Sub Form_Current()
Me.cboComboName1 = ""
Me.cboComboName2 = ""
Me.cboComboName3 = ""
End Sub

 

[James D Smooth]

If you review the statement below if LIC_ID is false (means that it is
not a new record on the table) then it will update the value of the 3
combo boxes (cmb_rlty_cd, cmblicensor, cmbacctprof) with the values
that are returned in the rst. This logic works fine for the
cmblicensor, but not the other 2 combo boxes. If the LIC_ID is true
(it's a new record in the table) then the combo boxes will be null
like you stated. I realize that unless the combo boxes are bound to
the form they will not update automatic, that is why I created this
logic. Is there something wrong with my thinking?


If LIC_ID = False Then
strSQL = "SELECT DISTINCT lic.RLTY_CD, lic.LICENSOR,
apc.ACCT_PROF_DESC FROM ACCT_PROF AS apc INNER JOIN LICENSES AS lic
ON
apc.ACCT_ID = lic.ACCT_ID WHERE lic.HISTORIC = 0 AND lic.LIC_ID = "
& Me!LIC_ID
 Set tmpQry = AccessDB.CreateQueryDef("Update Combo", strSQL)
 Set rst = AccessDB.OpenRecordset("Update Combo", dbOpenDynaset,
dbSeeChanges)
 DoCmd.DeleteObject acQuery, "Update Combo"
 Me.cmb_Rlty_Cd = rst!RLTY_CD
 Me.cmbLicensor = rst!LICENSOR
 Me.cmbAcctProf = rst!acct_prof_desc
ELSE
   cmb_Rlty_Cd = Null
   cmbLicensor = Null
   cmbAcctProf = Null
End If

 

[James D Smooth]

It was just a matter of binding each of the combo boxes to itself,
changed the Bound Column from 0 to 1, and now it works as expected