We help IT Professionals succeed at work.

MS Access 2010 - is it possible to create a button next to a lookup field that jumps to the record of what its looking up

mrroonie
mrroonie used Ask the Experts™
on
hello all

i'm trying to teach myself access to build an asset register for the office - the templates i have found to download all look like they need a massive amount of editing to be how i want them. i have a 1000(!) page reference book on it and it is serving me well but theres one thing i want to do that i can't find in the book, so i'm wondering if its possible or not:

in my database, among other tables that have no relevance, i have an equipment table and an asset number table. there is a field in the equipment table that links to the asset number table - i created the link by using the 'lookup wizard'. this is fine and works well, but i also have a field for entering associations, for example my PC is asset number 0001 and the monitor (an 'associate' of the PC) is asset number 0002 - for the associations field i have used the lookup wizard again that looks up the asset number.

on the equipment form i have fields for serial numbers etc. what i want is a button next to the associations on the equipment form, so if i'm on the record of asset number 0001, when its clicked it autmatically takes me to the asset number 0002 record

is this possible? if so, how can it be done?

thanks in advance for suggestions
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Sure, it is possible.  I assume, since you are storing this [Associate] in the same record as the [Asset] that there will only be one other asset associated with your PC.  If there might be more than that, you should have a seperate AssetAssociations table which contains AssetID and Associate fields.

My recommendation would be:

1. Use the Form_Current event to set the Enabled property of that button based on whether there is an associated record.  Every time you move to  a new record, this would check to see whether that records AssetID is stored in any other records [Associate] field:

Private Sub Form_Current

    Dim strCriteria as string
    strCriteria = "[Associate] = " & me.AssetID
    me.cmd_Associate.Enabled = NOT isnull(DLOOKUP("ID", "yourTable", strCriteria)

End Sub

2. Then, in the click event of that command button, you would do something like the following.

Private sub cmd_Associate_Click

    With me.recordsetclone
        .FindFirst "[AssetID] = " & me.Associate
        if .NoMatch then
            msgbox "No matching record found"
        else
            me.Bookmark = .bookmark
        end if
    end with

End Sub

Author

Commented:
hi there, thanks for the quick reply

>> I assume, since you are storing this [Associate] in the same record as the [Asset] that there will only be one other asset associated with your PC. If there might be more than that, you should have a seperate AssetAssociations table which contains AssetID and Associate fields.


the association has its own record in the same table as the asset record. i have put 4 boxes in for assocations (please see attached). i want the button next to each of the 4 assocations box (in red [i think - i'm colourblind])

for example my PC has 3 assocations - 2 monitors and a printer. are you suggesting i'd be better off putting everything that doesn't have its own IP address in a different table? if its not gonna make too much of a balls up i would rather keep both PCs and peripherals in the same table, otherwise you get questions such as which table does stuff like UPS's and network switches go into?'

please see attached, i think it clears it up a bit more. would that not work if i created that button on all for assoc's?
access.jpg
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
What I'm suggesting is that rather than have separate associates fields, you would have a separate table (tbl_AssetAssociations) which includes two fields (AssetID, AssociateAssetID).  You would still have a record in your main table for each of the associated assets, but by adding this new table you could associate any number of other assets with your primary asset (not just the 4 you currently have).

Another way to accomplish this would be to add a Parent_AssetID column to your main table, and for each of the associated assets, you indicate in that table what the Parent AssetID is.

Then, instead of using 4 separate controls for the associated assets, you would use either a listbox or a continuous form to display the list of assets associated with another asset, and would use a single button to jump to the record of one of the associated assets.

Author

Commented:
>>Another way to accomplish this would be to add a Parent_AssetID column to your main table, and for each of the associated assets, you >>indicate in that table what the Parent AssetID is.

>>Then, instead of using 4 separate controls for the associated assets, you would use either a listbox or a continuous form to display the >>list of assets associated with another asset, and would use a single button to jump to the record of one of the associated assets.

yes - i like that idea, nice and tidy. and the code you've added above will create the single button?

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

Would have to modify your form and the code associated with it to accomodate the Parent_AssetID column.

I'll put a demo together during lunch and post it back.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
See attached.  Very simple example.  I did not add a command button.  Instead, I use the Double Click event of the listbox and the Parent_ID combo boxes to jump back and forth between the two.
Assets.mdb

Author

Commented:
Thanks for this but I've finished work for the weekend now - I'll check it out 1st thing Monday and get back to you. Thanks again

Author

Commented:
Excellent, exactly what i was after, thanks

Author

Commented:
hi fyed, i don't know whats happened but somethings gone wrong overnight. i have checked through the code and everything is as it should be, but now when i double click either the parent_ID or an associate in the listbox i'm getting a runtime error 3070

"Access doesn't recognise 'the asset number' as a valid field name"

whichever asset i double click on the error says 'the asset number' as in the actual number thats in the field, so if im trying to jump to record of asset number 0002 it says "Access doesn't recognise '0002' as a valid field name"

heres my code:  

Private Sub lst_AssociatedAssets_DblClick(Cancel As Integer)

    With Me.RecordsetClone
        .FindFirst "Asset_ID = " & Me.lst_AssociatedAssets
        If .NoMatch Then
            MsgBox "Asset not found"
        Else
            Me.Bookmark = .Bookmark
        End If
    End With
       
End Sub


its exactly the same as the one in your demo. any ideas?

Author

Commented:
FYI, when i go to debug its highlighting

.FindFirst "Asset_ID = " & Me.lst_AssociatedAssets

as the problem line
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Is your Asset_ID field text or numeric?

I've found that even when the fields in the rowsource for a listbox are numeric, it sometimes returns text values.  In this case, since it is returning "0002", that implies a text string not a number.  Try:

        .FindFirst "Asset_ID = " & val(Me.lst_AssociatedAssets)

Author

Commented:
thanks for the quick reply. it is a text field. well, its actually a lookup from another table, but that is a text field. change the line to your suggestion and now i'm getting:

Run-Time error '3464':

Data type mismatch in criteria expression.

if it makes a difference, every time i got back in to check the code it's capitalising Val, instead of val.

do you reckon its because its a lookup?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

as a text field, the syntax should be:

        .FindFirst "Asset_ID = '" & Me.lst_AssociatedAssets & "'"

Notice that I have wrapped the text value in quotes.

Author

Commented:
i could kiss you. thanks so much. don't know how it worked before today

thanks again