Matching vba recordset to form recordset

ClaraBarton

Registered User.
Local time
Today, 10:07
Joined
Oct 14, 2019
Messages
742
I have the following code:
Code:
Public Sub AddItem(ID As Long, strITPath As String)
    Dim rst                 As DAO.Recordset
    Dim strSql              As String
    Dim lngDocNo            As Integer
    Dim bkmrk               As Variant
    
strSql = "SELECT * from tblItems WHERE flocID = " & ID
Set rst = CurrentDb.OpenRecordset(strSql)
lngDocNo = AddDocNo(ID)

    With rst
        .FindFirst lngDocNo
        If .NoMatch Then
            .AddNew
        Else: .Edit
        bkmrk = .Bookmark
        End If
        .Fields!ITPath = strITPath
        .Fields!InUse = True
    End With
   Forms.frmDetail.ItemDetail.Form.Recordset.Bookmark = bkmrk
End Sub
I would like to go to the form recordset at this record ... either new record or a record to be further edited.
How do I tie these two recordsets together and get the proper record?

I read that recordset bookmarks don't necessarily match form bookmarks unless they're a clone which this is not.
 
No Update in that code?
 
I'm confused. If you are working interactively on a form, why are you looking up a record in a separate recordset? Why are you not using the built in search features or a custom search of the form's recordset clone?

BTW, binding a form to a table or unfiltered query is the least efficient method of searching. What you are doing is even worse since you are brining down the exact same set of records TWICE. You are forcing Access to load every record in the table first and THEN search. If your row count is small, how you code your process is pretty irrelevant but when the recordset is large, typically more than 100,000 records, then you should think about efficiency. Since you have the ID which I assume is unique to the record or set of records you want, then the best method is to use a query with a where clause that references the ID field on the form. That returns ONLY the requested record if it is found or an empty recordset into which you can add a new record if no record is found.
 
eh... it's a subform, so it's limited records. I want to find any empty records and fill them in. I know you don't approve but the DocNo is not the ID. It is only a numerical list within certain bounds. I'll go back and rethink it.
I wanted to do it from a module because it gets called from 2 different places.
 
eh... it's a subform, so it's limited records. I want to find any empty records and fill them in. I know you don't approve but the DocNo is not the ID. It is only a numerical list within certain bounds. I'll go back and rethink it.
I wanted to do it from a module because it gets called from 2 different places.

What if you make a recordset with flocID = ID AND DocID = ...?

If (rst.EOF) Then
rst.AddNew
Else
rst.Edit
End If
 
@lmb's suggestion should work to identify if a record exists.

But I would still rethink this. Records should only be updated in ONE form. When you update records in multiple forms, you must repeat your validation logic assuming you bother with it. On the rare occasions I agree to do this, it is usually because the main update form has a lot of data fields and in a slightly different context, a subset of those fields are listed (always including ALL the required fields). In that case, I put the validation for the common fields in a procedure in a standard code module (can't go in the form's class module for the obvious reason) and I pass in a reference to the form. So instead of using Me.fieldname, the validation logic uses frm.fieldname and the frm is an argument that references the form or subform. It doesn't matter which.

But, the other logical question is why doesn't the query look for gaps? Seems like a lot of work to look for specific Id's. You have to manually locate the gap and then one at a time search for it. And finally, "keys" should never be reused. If you are talking about sequence numbers, that is something different. Here's a link to a db that shows how to handle sequence numbers in a subform and renumber them to remove gaps if you want.

 
@ClaraBarton , if you can match the records in the form "somehow" then you could also use the same matching strategy in a query and then simply use the query as recordsource of the sub form.
 
The following is a simple query for identifying gaps in a sequence:

SQL:
SELECT
    (P1.ProductID + 1) AS GapStart,
    (MIN(P2.ProductID) -1) AS GapEnd
FROM
    Products AS P1,
    Products AS P2
WHERE
    P1.ProductID < P2.ProductID
GROUP BY
    P1.ProductID
HAVING
    (MIN(P2.ProductID) - P1.ProductID) > 1;

If you want to find gaps in a subset of rows numbered independently you would simply restrict both instances of the table on whatever column determines the subset.
 
This is a filing system. Numerical items are culled, "inUse" is unchecked and the record no longer shows in the list. When a new item is added, it uses the first culled number or is added at the end of the list. It has worked for years but I recently upgraded to Majp's treeview and I'm rethinking everything. I'll go back and redo this... Thank you for your suggestions.
Code:
Public Function AddDocNo(ID As Long)
    Dim strFind         As String
    Dim lngDoc          As Long
    
strFind = "InUse = False AND fLocID = " & ID
lngDoc = Nz(DMin("DocNo", "tblItems", strFind), 0)

    If lngDoc > 0 Then
        AddDocNo = lngDoc
    Else: AddDocNo = Nz(DMax("DocNo", "tblItems", "fLocID =" & ID), 0) + 1
    End If
End Function
 
Last edited:
If you're using MajP's masterful Treeview then I believe his clever Update & Sort method does all the heavy lifting, so presumably you have a reference field for your own needs.

Not crystal clear but I get your gist.

You could work with the recordset directly which is bound to the form with:

If you are handling an update to the references only - I think you could be better off handling the update in your unsaved QueryDef allowing multiple execution in a single pass. Or if you feel more comfortable you can create a saved Update Query with Parameters & execute the update query from VBA. Though I believe hardcoding the QueryDef may be more efficient/ same. If choosing this route you need to ensure your recordsource is based on a query (so it updates itself); also best to have option dbSeeChanges (not sure if this is required as I think I got some inaccurate info on Youtube with the effect of this). I think I prohibits changes being made if another user has made them changes since you opened the record; where the Youtube insinuated that this is required to enable other objects which have the same recordsource update themselves. I don't think it's the case but i could be wrong. Certainly best to put it there.
THEN to select the record work with the Form's Recordset property itself which I learned from MajP (thank you) & FindFirst automatically selects & goes to record in the form by:

Code:
Sub blah()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("QueryName")
  Forms("LibResourceDetailsF").Recordset.FindFirst ("FieldID = " & NumericCriteria)
 
  If rs.NoMatch Then
    'Add a new record & populate controls with already know values as you have in code already
  End If
 
  rs.Close   'IMPORTANT
End Sub

Be sure to close the recordset; VERY IMPORTANT.

eh... it's a subform, so it's limited records
Just to clarify a SubForm can contain many, many records. It is basically a form, but as a control; so this means nothing.

@ClaraBarton , if you can match the records in the form "somehow" then you could also use the same matching strategy in a query and then simply use the query as recordsource of the sub form.
Not to pick faults but I'm not sure this is super advice. You'd then be presenting the user with a incomplete data-set. Possibly causing them to unknowingly recreate records which already exist. Sure you could open the form again based on the new criteria you have & would be a soultion if the OP was wanting to drill-down on a single record but I don't really think this is applicable as:
go to the form recordset at this record ... either new record or a record to be further edited.
Stating that you're not opening a new form/ providing the SubForm with restricted data of a particular record. I get the impression this is for your main records which you are viewing on a subform; which you're updating references...

MajP's Treeview is a complex work of art, well written by an expert. I do not want to discourage you from this at all, as I am learning much myself with the help of others here (sorry @sonic8 do not mean to pick faults as you've helped me a bit in the past but inaccurate input without clear warning of important consequences can really put a learner behind massively [speaking from experience & is not a dig at anyone here]).
Subform syntax can be tricky, but you're about to enter a world of User Defined Class Objects replicating inheritance (inheritance in VBA is not intuitive) with User-Declared Events & ActiveX Controls; further obfuscating what's going on. A different league of coding. Brace yourself for a lot of learning. I'm not wording this right as it's coming across arrogant (my apologies) & I don't mean this to be the case. What I mean is if you're trying to edit MajP's work of art, this will be tricky. I personally became obsessed with how it works :love: so beware.
 
Last edited:
maybe change it to like this:
Code:
Public Sub AddItem(ID As Long, strITPath As String)
    Dim rst                 As DAO.Recordset
    Dim strCriteria         As String
    Dim lngDocNo            As Integer
    
    strCriteria = "flocID = " & ID
    Set rst = Forms!frmDetail!ItemDetail.Form.RecordsetClone
    lngDocNo = AddDocNo(ID)
    strCriteria = strCriteria & " And DocNo = " & lngDocNo
    With rst
        .FindFirst strCriteria
        If .NoMatch Then
            .AddNew
            !fLocID = ID
            !DocNo = lngDocNo
        Else:
            .Edit
        End If
        .Fields!ITPath = strITPath
        .Fields!InUse = True
        .Update
        Forms.frmDetail.ItemDetail.Form.Bookmark = .LastModified
    End With
   Set rst = Nothing
End Sub

Public Function AddDocNo(ByVal ID As Long)
    Dim qry        As String
    Dim lngDoc     As Long
    Dim strFind    As String
    Dim rst As DAO.Recordset
    strFind = "fLocID = " & ID & " And InUse = 0"
    AddDocNo = 1
    Set rst = CurrentDb.OpenRecordset("select Top 1 DocNo From tblItems Where " & strFind & " Order by DocNo Asc;")
    If Not rst.EOF Then
        AddDocNo = rst!DocNo
        rst.Close: Set rst = Nothing
        Exit Function
    End If
    Set rst = CurrentDb.OpenRecordset("select Top 1 DocNo from tblItems where fLocID = " & ID & " Order by DocNo Desc;")
    If Not rst.EOF Then
        AddDocNo = Val(rst!DocNo & "") + 1
    End If
    rst.Close: Set rst = Nothing
End Function
 
I wouldn't reach down into a child object, pull out its data source, apply a bunch of logic, and then stuff that data source back into the child...
Set rst = Forms!frmDetail!ItemDetail.Form.RecordsetClone
...
Forms.frmDetail.ItemDetail.Form.Bookmark = .LastModified
Rather, the child object should expose a method, like...
Code:
Forms!frmDetail!ItemDetail.Form.AddItem ID, Path
The object that owns the data should also own the logic that modifies it.
 
as suggested, put this code in subform ItemDetail:

Code:
Public Sub AddItem(ID As Long, strITPath As String)
    Dim rst             As DAO.Recordset
    Dim strCriteria     As String
    Dim lngDocNo        As Integer
    
    strCriteria = "flocID = " & ID
    Set rst = Me.RecordsetClone
    lngDocNo = AddDocNo(ID)
    strCriteria = strCriteria & " And DocNo = " & lngDocNo
    With rst
        .FindFirst strCriteria
        If .NoMatch Then
            .AddNew
            !FlocID = ID
            !DocNo = lngDocNo
        Else:
            .Edit
        End If
        .Fields!ITPath = strITPath
        .Fields!InUse = True
        .Update
        Me.Bookmark = .LastModified
    End With
   Set rst = Nothing
End Sub

Private Function AddDocNo(ByVal ID As Long) As Long
    Dim qry         As String
    Dim lngDoc      As Long
    Dim strFind     As String
    Dim rst         As DAO.Recordset
    strFind = "fLocID = " & ID & " And InUse = 0"
    AddDocNo = 1
    Set rst = CurrentDb.OpenRecordset("select Top 1 DocNo From tblItems Where " & strFind & " Order by DocNo Asc;")
    If Not rst.EOF Then
        AddDocNo = rst!DocNo
        rst.Close: Set rst = Nothing
        Exit Function
    End If
    Set rst = CurrentDb.OpenRecordset("select Top 1 DocNo from tblItems where fLocID = " & ID & " Order by DocNo Desc;")
    If Not rst.EOF Then
        AddDocNo = Val(rst!DocNo & "") + 1
    End If
    rst.Close: Set rst = Nothing
End Function

you call it like this:
Code:
[Forms]![frmDetail]![ItemDetail].Form.AddItem theIDNumberHere, theITPathHere
 
Whoa... @arnelgp!!!! You did my work for me and it works like a charm. Thank you so much.
When I compare code I run it side by side in a spreadsheet. How does everyone else do this? Surely there's some secret here.
 
I tend to walk the code, line by line, if it does not do what I think/want it to do.
If you had done that, you would have noticed that the record never got updated?, as the .UpDate keyword was missing from your code?
 

Users who are viewing this thread

Back
Top Bottom