Form.Recordset and combobox.requery problem

[LaBelette]

Hi!
I have a Form FRegulier containing a subform SFRegulier.
The form is bound to a query, the subform SFRegulier is bound to another query.
In the details section of SFRegulier, I have a combobox lstEqNaturelle, who have the RecordSource REquipeNaturelle2, which has 7 fields. The combobox is bound to the first field. It is not bound to my form of subform's RecordSource.

When I modify the combobox, the values in it either update the subform's current record, or add a new one, if the current record is a new one.

Here is my code:

Private Sub lstEqNaturelle_AfterUpdate()
    ' Update the current record using lstEqNaturelle's values
    If Not IsNull(lstEqNaturelle.Value) Then
        With Forms("FRegulier").SFRegulier.Form.Recordset
            ' Determine if we have to Add or Edit
            If Forms("FRegulier").SFRegulier.Form.CurrentRecord > .RecordCount Then
                ' Add
                .AddNew
                !camNum = lstEqNaturelle.Recordset("camNum").Value
                !equSerNumero = lstEqNaturelle.Recordset("equNatNum").Value
                !depNumChef = lstEqNaturelle.Recordset("depNumChef").Value
                !depNumCompagnon = lstEqNaturelle.Recordset("depNumCompagnon").Value
                !quaNum = Forms("FRegulier").Recordset!quaNum
                .Update
                .Bookmark = .LastModified
            Else
                ' Edit
                .edit
                !camNum = lstEqNaturelle.Recordset("camNum").Value
                !equSerNumero = lstEqNaturelle.Recordset("equNatNum").Value
                !depNumChef = lstEqNaturelle.Recordset("depNumChef").Value
                !depNumCompagnon = lstEqNaturelle.Recordset("depNumCompagnon").Value
                .Update
            End If
        End With
        ' Set back the list to Null
        lstEqNaturelle.Value = Null
    End If
End Sub

It works, except the first time i run it. It bugs on the .Update (either after the .Add or .Edit), because lstEqNaturelle.Recordset("everydamnedfields").Value equals Null. But only the first time I use it!!!

I've tryed Requerying the combobox at the very begining of the procedure, but the I got another error : no current record. (Me.Recorset.AbsolutePosition = -1). But then i just press F5, and the code works fine without changing anything.

I think I need to wait after the lstEqNaturelle.Requery, but I don't know how. I've tryed putting a MsgBox, which doesn't work and looping until the value are not Null, which never ends. But if I put a breakpoint after the lstEqNaturelle.Requery and continu execution after waiting 5 seconds, it works.

Is there a way I can force Access to wait until the requery is done?

I am confused. Time is playing against me!!! HELP!!!

Hint : I realized the values form lstEqNaturelle.Recordset("everydamnedfields").Value are Null because the first record of my combobox's RecordSource REquipeNaturelle contains Null values. But it is not the record I selected in the list. If I remove the first record containing the Null values, the values from the first real record are used instead of the good ones.

 

[LaBelette]

Very dear LaBelette...

Are you a moron, LaBelette?

Seriously, I found the problem, so you can now all return to your normal occupations and stop working on it.

NEVER use combobox.Recordset("fieldname").value . Use combobox.Column(index, [row]) .

The recordset does not always returns the good values as its position is not synchronised with the combobox's selected value. It sometimes locks, it sometimes points to the first record as it shouldn't. It is, somehow, retarded. It's not his fault, he's borned this way, just leave im alone and use combobox.Column(index, [row]) .

Ho, and after a Form.Recordset.Add / .Edit, a Me.Requery helps.