Autopopulate field in details form

[hamilton_kane]

Hi, I work for a cement contracting company and I've built a perfect program using Microsoft Access' Asset Tracking Template. I like how when you assign an asset to a contact(employee), it shows up in that contacts(employees) details form. I've also added a vehicle list and a job site list. I've made it auto populate to the asset list where it has a drop down, same as the owner dropdown for the contact list. I've tried messing around with the contact assets datasheet subform, and I can't seem to get the asset to populate into the vehicle and job site detail forms like it does with the contact details form. This is the only thing I need before it ready for launch. Any help would be appreciated as I have seemingly tried everything. Thank you!
Original post found here: UtterAccess.com

[mike60smart]

Hi
Can you upload your database

[hamilton_kane]

Of course. It seems to be working weird also. When you assign an item to certain employees, it auto populates to the vehicles detail form as that employee instead of the vehicle.

<attachment removed per OP request>

[mike60smart]

Hi Hamilton
You have made the mistake of using the Microsoft Template as is.
It is meant to be used as a guide and not to be used as an actual working database.
If you are going to use this database as a working database then it needs some modifications.

I only looked at your Asset's table and these are my observations:-

1. Assets should become tblAssets
2. ID should become AssetID -
3. Vehicle Lookup - should not have spaces in any name and this is also set as a Lookup Field. Search
Google for "The Evils of Lookup Fields in Access database". It will list why you should not use them.
4. Category - you have Hard Coded a list of Categories in the table field which again is a Big NO NO. Thisa list of
Categories should be in a separate table.
5. Type - same as Category - Types should be in a sperate table
6. Employee - Lookup Field - Big No No
7. Job Site - space needs deleting and the field is set as a Lookup
8. Vehicle - field is set as a Lookup

[hamilton_kane]

Okay thank you! I will make those changes but the question still remains as my office team need to look up assets by job site and vehicle. I haven't used the vehicle lookup I will probably delete it as I made a vehicle list form for them. If you assign an asset to an employee, you can go to the employee list, click on the particular employee and on the subtab see what assets are all assigned to that particular employee. How do I get that information to pop up when I click on a certain vehicle or job site in their particular detail subtabs?

[mike60smart]

Hi
Currently on the Assets List when you double click on an Employee there is an Event called
"OnDblClick" which opens up the "Employee Details" Form

You would need to apply the same Double Click Event on the Vehicle Field to open the "Vehicle details" Form
Same applies for the "Job Site"

[hamilton_kane]

Okay great, thank you.

[hamilton_kane]

Hi, that did not work as that only lets the user double click on an item and go to its details. What I need is for the user to be able to go onto the job site and vehicle lists, click on a certain vehicle or job site and see which assets are assigned to it after you assign them in the asset list. Here are some pictures to try and clarify, thank you.

[mike60smart]

I believe your table structure is wrong for what you want to be able to see.

You are saying that when you select a Job Site you want to see 1 or more Employees who are currently on this site.

Or are you saying that when you select a Job Site you want to see 1 or more Assets that are currently in use on this site.

[hamilton_kane]

I want to see 1 or more assets that are currently in use on this site

[mike60smart]

See the modified attached Job Site field in the Assets Form

Now when you double Click the Jobsite Name the form will display the Job Site Plus all Assets

[hamilton_kane]

Perfect, it works as intended. It throws an error while opening and closing an asset though. What all did you change?

[mike60smart]

Hi

See the attached

I modified the code and the form now closes as it should with no error.

[hamilton_kane]

Okay thank you, you are awesome. You saved me lol, what did you change so I can change the vehicle field and so I know for future reference?

[mike60smart]

Hi

I just changed the Open VB from a Macro to an OnClick Event as shown below:-

Private Sub txtOpen_Click()

10        On Error GoTo txtOpen_Click_Error
         
20        DoCmd.OpenForm "Asset Details", acNormal, "", "[ID]=" & Nz(ID, 0), , acDialog
          


          
30        On Error GoTo 0
40        Exit Sub


txtOpen_Click_Error:


50        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure txtOpen_Click, line " & Erl & "."


End Sub