I am a novice in both Access and VBA. I have been assign to build an Inventory Control for the company. I got the data structure work pretty well by trial and errors. Now, it's time to build the analysis for it; and I ran into some difficulties.
I use all the fields as combo box on a tab of the main form, and put expression "=DCount() as controlSource; most of them return correct value, but when there is no record found, the return value is wierd and I don't understand why. For example, when I want to count the parts that are in "Returned for Repair", result shows #Name? instead of 0. Could somebody explain why, and how can I fix it?
Also, I like to build a public function for this, so I can pass the table/query name along with the "search-target" as String, then it could return the right value.
Thanks a lots Sinndho. I got most of it.
There is another issue related to this. There are two categories that need qry to find the search targets, how can I use Dcount with them.
For example, asset tables have one field name hierarchyID, which stands for a configuration of system from the top down, such as router main module, then interface board/power board/,and OS, etc.
To count hardware, you just helped me all, but for software (OS), I need to go back to to the hierarchy table to find the component category, which will have "OS" or "Application" listing. How can I do this? If not, can you shed sone light? Thanks again
Not sure to fully understand the problem, mainly because I don't know how your database is organised, but DCount() can accept three parameters:
- The first is a column or the derived expression from a column.
- The second is the table or query (domain) to work with.
- The third contains criteria that you apply to the defined domain.
In your case this third parameter should be something like:
<Column> = 'OS' OR <Column> = 'Application'
As an alternative, you could also build a SELECT query with a WHERE clause:
SELECT ... FROM ... WHERE <Column> = 'OS' OR <Column> = 'Application'
and call the DCount function using this query as the domain. This could be quicker, as domain functions (DCount, DMin, DMax, DLookup etc.) are known to be rather slow.
I knew that, but I have to build an unbound tabbed page for data analysis, and I don't know how to make the controlsource for the textbox in the page to do so. I also tried and failed to pass value from one pahe to another.
Can you tel me how. Thanks