Hi Guys,
Just a quick question - hoping for a quick response
My Access DB is an Asset Management System.
The main table of the DB is Tbl_Asset_Main
One of the fields of this table is Allocation_Destination
Another field is Allocated_To_Name
When recording an asset a user can select an Allocation_Destination of "Site" or "User".
The rowsource of the Allocated_To_Name combo box depends on this selection i.e. if the user selects "Site" it looks at Tbl_Sites, if "User" then Tbl_Users.
These two tables are linked to another DB that I do not manage.
My Problem
The DB search form allows users to search for all assets Allocated to a site or all assets Allocated to a specific user.
You can probably guess what the problem is...
A site can have many users. The users would like to be able to select:
Allocated To: Site
Allocated To Name: Perth
Show Assets for Users at this Site: Yes / No
The yes / no option would be a tick box. The problem is that the Site and User tables that I link to are not related in the other database. Instead, the user table does have a text field "Office" which does show the site name.
I need a way to query all Assets where:
Any help would be appreciated. I can post a cut down version of the DB if this will help.
Thanks
Ben
Just a quick question - hoping for a quick response
My Access DB is an Asset Management System.
The main table of the DB is Tbl_Asset_Main
One of the fields of this table is Allocation_Destination
Another field is Allocated_To_Name
When recording an asset a user can select an Allocation_Destination of "Site" or "User".
The rowsource of the Allocated_To_Name combo box depends on this selection i.e. if the user selects "Site" it looks at Tbl_Sites, if "User" then Tbl_Users.
These two tables are linked to another DB that I do not manage.
My Problem
The DB search form allows users to search for all assets Allocated to a site or all assets Allocated to a specific user.
You can probably guess what the problem is...
A site can have many users. The users would like to be able to select:
Allocated To: Site
Allocated To Name: Perth
Show Assets for Users at this Site: Yes / No
The yes / no option would be a tick box. The problem is that the Site and User tables that I link to are not related in the other database. Instead, the user table does have a text field "Office" which does show the site name.
I need a way to query all Assets where:
Code:
If UsersTooCheckBox = 0 then
'show all assets where allocation dest = "site", allocation name = "sitename"
Else 'UsersTooCheckBox = 1
'show all assets where allocation dest="site", allocation name = "sitename"
AND
'allocation name = Tbl_Users.Username WHERE Tbl_Users.Office = "sitename"
...Any help would be appreciated. I can post a cut down version of the DB if this will help.
Thanks
Ben
