Need Advice for Coding an Asset Tracking Database

[Tyrone Glover]

I am trying to add on to an already complex Asset Tracking Database that
I've created and I'm going to try to explain what I'm trying to do.

The main form is a Continuous Form called frmAssets. The following is an
example of how that data is entered:

Asset Name: Windows XP Professional
Category: Software
Subcategory: Operating System
Make: Microsoft Corporation

Asset Name: Canon PowerShot S60
Category: Hardware
Subcategory: Digital Camera
Make: Canon

How it is setup currently is that if you Double-Click on a specific Asset
Name, it will open up a form called frmAssetDetails. Basically, it repeats
the Asset Name, Category, Subcategory, Make fields but includes a picture
and some other extra details, but in a Single Form view. frmAssetDetails
includes a subform called, AssetInventory, so if you Double-Click Windows XP
Professional in frmAssets it will give you all the serial numbers and
authorization codes in a subform included in the frmAssetDetails table.

What I would like to do is when you Double-Click on the Asset Name field in
frmAssets, it will take you to different forms according to its Category or
Subcategory. So if I select Windows XP, it will open up a
frmSoftwareDetails form that will display fields only related to software or
if I select Canon PowerShot S60, it will open up a frmDigitalCamera. I also
may want to add a frmPrinters and things of that nature.

Please advise on the best way to accomplish this. I will also attach the
code I'm currently using in frmAsset for the Event Procedure in the Asset
Name field which takes me to frmAssetDetails.

Private Sub AssetName_DblClick(Cancel As Integer)
On Error GoTo Err_AssetName_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAssetDetails"

stLinkCriteria = "[AssetID]=" & Me![AssetID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AssetName_Click:
Exit Sub

Err_AssetName_Click:
MsgBox Err.Description
Resume Exit_AssetName_Click
End Sub

Thanks for any help or advice!

 

[Nikos Yannacopoulos]

Tyrone,

I suppose you must have a lookup table for Categories? I'll assume it's
named tblCategories for the example below.

Add another field to it ("DetailForm"), to hold the name of the detail
form to be opened for each category. Then, in your AssetName_DblClick
procedure, get the name of the form to be opened by looking it up in the
table, based on the current category. So, your modified code should look
something like:

Private Sub AssetName_DblClick(Cancel As Integer)
On Error GoTo Err_AssetName_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stCategory As String

stCategory = Me.Category
stDocName = DLookup("DetailForm", "tblCategories", _
"Category = '" & stCategory & "'")

stLinkCriteria = "[AssetID]=" & Me![AssetID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AssetName_Click:
Exit Sub

Err_AssetName_Click:
MsgBox Err.Description
Resume Exit_AssetName_Click
End Sub

HTH,
Nikos

 

[Tyrone Glover]

Thanks a lot, that actually works out pretty well. The only odd thing it is
doing is that it disables the Close Button. The only way it closes is if
you switch to Design View, then back to Form View then close it using the
windows Close Button. If you try to create a new Close Button, it'll give
you an error message saying, "Subscript out of range". If you open up the
form through the Database window, it Closes just fine. Other than that, it
does what I want it to do.

 

[Nikos Yannacopoulos]

Tyrone,

This is indeed odd! I can't imagine why this should happen, this code
has absolutely nothing to do with any close button! What button are you
referring to? The standard Windows close button, or a command button
that you added? On which form?

Nikos

 

[Tyrone Glover]

I'm sorry, it was that particular form that was corrupt or something, it had
nothing to do with the code. I just deleted it and created a new
frmSoftwareDetails. Everything works fine now. It does exactly what I need
it to do.

Thanks you very much!

 

[Nikos Yannacopoulos]

Good! By the way, if you're starting to get funny stuff in your
development copy (which tends to happen when heavy development is done),
it's a good idea to create a new, blank database and import all your
objects from the development copy, thus creating an fresh, clean copy of it.

Nikos