Programming with the Current Event

[Mongooseman]

I have a list box on a form, and I want to change the list box's Rowsource property every time the current record changes. I created a sub called populate() that builds a SQL statement and assigns it to the Rowsource property. The SQL looks like this:

SELECT RDM_No, part FROM RDM_Numbers WHERE part = [variable]

[variable] is the name field from the Form's Record Source (the Parts table). Populate() originally obtained [variable] from a text box whose Control Source property was tblParts.name, but although this method worked beautifully for simple viewing, it caused problems when editing (the text box value may not match the actual field value if the text box is still dirty). I then changed populate() to get [variable] from Me.Recordset.Fields("name").Value. This worked fine for editing, but it caused a strange problem.

Populate() is assigned to the Form's Load and Current events. It also runs when a change is made to the RDM_Numbers table via a Command Button. It works fine except when called from the Current event. [variable] is assigned before the record changes, the list box's values do not match the Form's current record. It did not have this problem when populate() pulled [variable] from the text box instead.

Any idea how I can fix this?

 

[gemma-the-husky]

the sqlstrg needs to be

"SELECT RDM_No, part FROM RDM_Numbers WHERE part = " & [variable]

assuming variable is a number or

"SELECT RDM_No, part FROM RDM_Numbers WHERE part = " & chr(34) & [variable] & chr(34)

if variable is a string

chr(34) is the " character

 

[Mongooseman]

Thank you for the reply. I should have clarified, that was just pseudo-code. The actual code looks like this:

Public Sub populateRDM()
    Dim strSQL As String
    Dim strPart As String
    
    strPart = Me.Recordset.Fields("name").Value
    
    strSQL = "SELECT tblRDM_Numbers.part, tblRDM_Numbers.RDM_No " _
        & "FROM tblRDM_Numbers " _
        & "WHERE tblRDM_Numbers.part = '" & strPart & "';"
    
    Me!lstRDM.RowSource = strSQL
End Sub

This procedure is called by the Form's Current and Load events. The SQL runs fine; the problem is that it assigns strPart the name field from the record before the record changes, so the list box's contents goes with the record before the currently displayed one.

As an example, I have three parts with "Resistor," "Capacitor," and "Battery" in their name fields. Resistor has related entries in the RDM_Numbers table 111, 222, and 333. Capacitor has the entry 555, and Battery has the entries 999 and 888. When the Resistor record is displayed, the list box should contain the values 111, 222, and 333.

When the Form loads, it works as it should. However, when I go to the next record, Capacitor, 111, 222, and 333 still show up. If I then go to Battery, 555--Capacitor's RDM number--shows up. The list box's record lags behind the current record, in other words.

 

[gemma-the-husky]

do you just need to requery the listbox after setting the sqlstrg, as below?

Public Sub populateRDM()
    Dim strSQL As String
    Dim strPart As String
    
    strPart = Me.Recordset.Fields("name").Value
    
    strSQL = "SELECT tblRDM_Numbers.part, tblRDM_Numbers.RDM_No " _
        & "FROM tblRDM_Numbers " _
        & "WHERE tblRDM_Numbers.part = '" & strPart & "';"
    
    Me!lstRDM.RowSource = strSQL

   [COLOR="Red"]Me!lstRDM.requery[/COLOR]
End Sub

 

[Mongooseman]

I tried adding a requery call, but it effected no change. The list box is requeried automatically when Current is called from the form.

I'm fairly certain the problem is that current is called before the Recordset moves. If I move from Resistor to Capacitor, for instance, strName is assigned Resistor, and the list box query shows results for Resistor, while the rest of the form displays information about Capacitor, the current record. The problem would be solved if I had a way to call populateRDM() after the record changes, but I'm still looking for a way to do that. Current doesn't help much because it's called before the Recordset moves. It would be lovely if they had BeforeCurrent and AfterCurrent events...