Search code examples
vbaformsms-access

Why can't I get the current record ID from a multi-instance form in VBA

I have a database I'm working on with a number of forms, some of them have to be updated when something in a different form is changed. I've set it up so that they're multi-instance forms, which works something like this:

Set frm = New Form_Name
frm.RecordSource = "select * from Table_Name where id = " & ID
colForms.Add Item:=frm, Key:=frm.hwnd & ""
mintForm = mintForm + 1
frm.SetFocus
frm.Visible = True

colForms is a collection. To refresh a form, I've added a function that refreshes the form that matches the name and ID passed:

Function RefreshForm(RForm As String, ID As Integer)
    If Developer = False Then On Error GoTo Fehler
    Dim F As Form
    On Error Resume Next
    For i = 1 To mintForm
        Set F = colForms(i)
        If F.CurrentRecord = ID And F.Name = RForm Then F.Refresh
    Next i
'Error handling logic
Done:
    Exit Function
Fehler:
    RuntimeError "MultiInstance: RefreshForm", Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext, Err.LastDllError
    Err.Raise 1000, , "Folgefehler"
End Function

Problem is, F.CurrentRecord is always 1 for some reason, even though the ID of the record in the form is definitely not 1. Any idea what I'm doing wrong, or how I could properly get the record ID (primary key) from the form? The form is definitely bound (at least the ones where I'm trying to get the ID).

Solution

  • CurrentRecord is not pulling record's ID from unique identifier field. CurrentRecord is a sequential number assigned by the form and has no relationship to the record unique identifier saved in field.

    If focus is on fifth record then it is record 5 and if that record has a unique ID field value of 5 then they are equivalent. Doing this comparison seldom makes sense. A form always sequentially numbers records starting with 1 regardless of filtering/sorting yet the form could be filtered to display a subset of records and/or records could be sorted so ID's are not sequential nor start with 1.

    If you want to know ID of record that has focus then reference field name holding that data.

    If F!fieldname = ID And F.Name = RForm Then F.Refresh