Form to open to specific record (DB attached) Question

[Welshgasman]

Not really Dave, there are others here much, much better and because that, they are probably much busier in their professional life to give entire solutions; me on the other hand have lots of time on my hands....


Cheers,

A little modest there Vlad?
You have helped me a good few times in the past myself.

[d9pierce1]

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....

[d9pierce1]

Not really Dave, there are others here much, much better and because that, they are probably much busier in their professional life to give entire solutions; me on the other hand have lots of time on my hands....
Cheers,

I dont know, I have seen a lot of mericles come from you just on my stuff!

[d9pierce1]

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

[Welshgasman]

I am interested to see his approach tomorrow.
I myself would have done it with linked forms, the Access way.

[d9pierce1]

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

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

[Gicu]

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:

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

Cheers,

[Gicu]

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,

[d9pierce1]

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

[d9pierce1]

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:

'Vlad - exit sub if form open on its own
If IsNull(Me.txtSubCatID) Then Exit Sub

Cheers,

Had to change this to OpenArgs but that was easy
Thanks Again