Combo boxes: appearing blank and locking up form

[scube]

Hi All

I've done a search on historical posts and have not found a releveant thread. Apologies if there is one - my search criteria must be off.

I'm updating a combo box using the results of previous data - don't believe cascading boxes is the answer.

The code below works in the sense that the queries are correctly updated - when viewed in accessess query screen.

But...
1. The combo box is blank on the form it is embedded into
2. If you select on of the blank options it gives i.e., there are a few blank option lines, it locks the form up so that only the combo box responds (still blank). To reset the form you must close it from the access Database window (even the top right X icon does not respond).

The Row source of the combo box is set to be the query updated "xl_TEST" and the code is tiggered from the "on enter" event - although I have tried other events and they all give the same response.

Has anyone come across either of these issues before? Any help and advice would be appreciated - thanks in advance.

Scube

Private Sub cbo_importance_Enter()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strSQL1 As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qry_xl")
    Set qdf1 = db.QueryDefs("xl_TEST")
     
     
'    MsgBox "change!"
    strSQL = "SELECT tbl_componentDefinition.critical " & _
             "FROM tbl_componentDefinition " & _
             "WHERE tbl_componentDefinition.ID_componentName=" & Me.ID_componentName.Value & " " & _
             "ORDER BY tbl_componentDefinition.critical;"
    Debug.Print strSQL
    qdf.SQL = strSQL
    Set qdf = Nothing
    Set db = Nothing
    
          
    strSQL1 = "SELECT tbl_importanceLevels.importanceDescription " & _
             "FROM tbl_importanceLevels, qry_xl " & _
             "WHERE (([qry_xl]![critical] = [tbl_importanceLevels]![Not allowed if Critical]))" & _
             "ORDER BY tbl_importanceLevels.importanceDescription;"
    Debug.Print strSQL1
    qdf1.SQL = strSQL1
    Set qdf1 = Nothing
    Set db = Nothing
                
'   DoCmd.OpenQuery "qry_xl"
'   DoCmd.Close acForm, Me.name

 

[scube]

Problem Solved!

Hi All

*****EDIT THIS ONLY SOLVED ONE PART OF THE PROBLEM - PLEASE SEE BELOW

thanks to those who looked.

The problem was in the SQL query it was looking for two return field where I only had one should have read

strSQL1 = "SELECT tbl_importanceLevels.importanceLevel, tbl_importanceLevels.importanceDescription " & _

many thanks
Hope I can help some of you guys out in the future

Scube

 

[scube]

Update

Afraid my glee at getting the combo box to populate was premature.

Unfortunatly what happens now is that the combo box doe not update date when a new record is cycled through the form.

The form displays one record at a time and you cycle through them using the habitual grey cursor buttons at the bottom of each form.

As I outlined above the code is triggered by the on enter event of the 'combo box' and a debug line poping up a msgbox confirms that the code is tiggered multiple times - yet the box retains the populated values from the first time it is activated.

Once again - any thoughts - many thanks
Scube

 

[scube]

knocking back the problems

Hi

To solve the not populating problem you need to populate the row source control at runtime

something like: Me.cbo_importance.RowSource = "XL_TEST"

articulating the problems seems to be helping - hope it helps someone else too!

Thanks
Scube