Populate Combo Boxes using Form_Current Options James D Smooth <ryan.cheek[ at ]amgreetings.com> 12/7/2008 3:33:11 AM 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

Re: Populate Combo Boxes using Form_Current Options "Arvin Meyer [MVP]" <arvinm[ at ]mvps.invalid> 12/7/2008 4:50:07 AM 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 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "James D Smooth" <ryan.cheek[ at ]amgreetings.com> wrote in message news:5c471c23-b26d-417a-81a7-177f0138f6a1[ at ]40g2000prx.googlegroups.com... [Quoted Text] > 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 > >

Re: Populate Combo Boxes using Form_Current Options James D Smooth <ryan.cheek[ at ]amgreetings.com> 12/8/2008 2:20:21 PM 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

Re: Populate Combo Boxes using Form_Current Options James D Smooth <ryan.cheek[ at ]amgreetings.com> 12/8/2008 2:40:23 PM 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