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.
Don't add a new record to recordset, go to new record row on form. And use RecordsetClone. Only use bookmark if there is a match.
Show code calling this procedure. How are form/subform related?
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.
@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.
Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below
@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
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
@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:
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 so beware.
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
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
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?