Member Avatar for Mongooseman

I'm working with Access 2000. I have a form with data from a table, tblParts, and a list box with data from another table, tblRDM_Numbers. The two tables have a one-many relationship with tblParts.name as a foreign key for tblRDM_Numbers. I would like the list box to update every time the Form's record moves. The sub I wrote 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

It works fine when I call it almost anywhere except Form_Current(). When Form_Current() calls populateRDM(), it strPart is assigned Me.RecordSet.Fields("name").Value before the record changes. Then, the record changes, and the list box contains information for the record before the change.

For example, I have three parts, Battery, Capacitor, and Resistor. I load the form, and Battery's information is displayed. The RDM numbers in the list box are those for battery. Then I move to the next record. The information for Capacitor is displayed, but the list box contains Battery's RDM numbers. I move to the next record, Resistor, but Capacitor's RDM numbers are displayed. I move back to Capacitor, but Resistor's RDM numbers are shown in the list. Etc.

Is there a way to call populateRDM() after the record moves, instead of before? It would be lovely if VBA had BeforeCurrent and AfterCurrent events, but I'll work with what I've got.

  • 2 Contributors
  • 2 Replies
  • 117 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Mongooseman
Member Avatar for timothybard

That is odd... I am using MS Access 2002 and it works fine for me. I would be surprised if the timing of the on current event changed from access 2000 to 2002. I thought the on current event always ran after the record was changed.

Have you tried this in another version of Access? Are you able to provide me with the database to see if I can duplicate the result?

Member Avatar for Mongooseman

Thank you for replying.

I asked this question on another website, and it's working now. I had to change the field tblParts.name to tblParts.partID because name is a reserved word in Access. Current works appropriately when I use Me.partID instead of Me.Recordset.Fields("partID").Value. The reason for this phenomenon is that Me.[field name] always matches the current record, but DAO's Recordset behavior is not clearly defined for the Current event. That's what I was told, at least.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.