A little modest there Vlad?
You have helped me a good few times in the past myself.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Hi Gicu
I found a problem?
If I just open the form frmSystemsCategoryData without going thru the other form
it creates an error.
Is there a way to open this form by itself and with or without the opening arguments? Not sure on this....
I dont know, I have seen a lot of mericles come from you just on my stuff!
Hi Welshgasman,
Thanks, I assure you that I spend at least 2 days working on a solution before I post something on here. I understand how he did it now that it works. I
actually tried something with find first and such but didnt have the necessary skills to complete it correctly. I learned a lot from this
little issue. I am a very visual person so some of these coding things i cant vision at all...
Dave
I am interested to see his approach tomorrow.
I myself would have done it with linked forms, the Access way.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Hi Gicu
I am able to make this work, been trying anyway
Not sure of correct manor but it does work this way
Here is what I added
Code:Private Sub Form_Load() Dim lCatTypeID As Long Dim lCatID As Long Dim rs As DAO.Recordset DoCmd.Echo False Me.txtSubCatID = Me.OpenArgs If Not IsNull(Me.OpenArgs) Then lCatID = DLookup("SystemCategoryID", "tblSystemCategorySub", "SystemCategorySubID=" & Me.OpenArgs) lCatTypeID = DLookup("SystemCategoryTypeID", "tblSystemCategory", "SystemCategoryID=" & lCatID) 'now navigate to desired records 'Type Set rs = Me.sfrmSystemCategoryType.Form.RecordsetClone rs.FindFirst "SystemCategoryTypeID=" & lCatTypeID Me.sfrmSystemCategoryType.Form.Bookmark = rs.Bookmark 'Category Set rs = Me.sfrmSystemCategory.Form.RecordsetClone rs.FindFirst "SystemCategoryID=" & lCatID Me.sfrmSystemCategory.Form.Bookmark = rs.Bookmark 'Subcategory Set rs = Me.sfrmSystemCategorySub.Form.RecordsetClone rs.FindFirst "SystemCategorySubID=" & Me.txtSubCatID Me.sfrmSystemCategorySub.Form.Bookmark = rs.Bookmark Else DoCmd.OpenForm "frmSystemCategoryData" End If DoCmd.Echo True End Sub
The problem with linked (sub)forms is that you get limited subsets for each subsequent subform, so you're loosing the ability to see all records, so it depends on what you want to achieve in the end.
@Dave: just add the new line at the top of the procedure to skip the rest if the ID was not provided:
Cheers,Code:Private Sub Form_Load() Dim lCatTypeID As Long Dim lCatID As Long Dim rs As DAO.Recordset 'Vlad - exit sub if form open on its own If IsNull(Me.txtSubCatID) Then Exit Sub DoCmd.Echo False Me.txtSubCatID = Me.OpenArgs lCatID = DLookup("SystemCategoryID", "tblSystemCategorySub", "SystemCategorySubID=" & Me.OpenArgs) lCatTypeID = DLookup("SystemCategoryTypeID", "tblSystemCategory", "SystemCategoryID=" & lCatID) 'now navigate to desired records 'Type Set rs = Me.sfrmSystemCategoryType.Form.RecordsetClone rs.FindFirst "SystemCategoryTypeID=" & lCatTypeID Me.sfrmSystemCategoryType.Form.Bookmark = rs.Bookmark 'Category Set rs = Me.sfrmSystemCategory.Form.RecordsetClone rs.FindFirst "SystemCategoryID=" & lCatID Me.sfrmSystemCategory.Form.Bookmark = rs.Bookmark 'Subcategory Set rs = Me.sfrmSystemCategorySub.Form.RecordsetClone rs.FindFirst "SystemCategorySubID=" & Me.txtSubCatID Me.sfrmSystemCategorySub.Form.Bookmark = rs.Bookmark DoCmd.Echo True End Sub
Hi Dave,
Almost, you got the right idea, but you're issuing the OpenForm again on the already opened form (it will not open it again so you're OK but just an "eye sore" ) ; you can remove that and the preceding Else lines and it should work fine.
Cheers,
Thanks so much,
I learn so much from you!
That is fantastic and works like a charm!
Dave Marking this as solved!
Again,
Thank you all
Had to change this to OpenArgs but that was easyThe problem with linked (sub)forms is that you get limited subsets for each subsequent subform, so you're loosing the ability to see all records, so it depends on what you want to achieve in the end.
@Dave: just add the new line at the top of the procedure to skip the rest if the ID was not provided:
Cheers,Code:'Vlad - exit sub if form open on its own If IsNull(Me.txtSubCatID) Then Exit Sub
Thanks Again