Hi,
I'm creating a little Access 2002 database and I have a few questions on how to do it.
The main purpose of the database is to store defects we found in our production. So when a defect is detected, we add an entry to the database specifying the defect, the machine causing the defect, the problem in that machine, and an optional comment.
The defect will be selected from a combobox bound to a lookup table containing all the types of defects (ofcourse we can add new defect types). The machine is also selected from a lookup table, and the same with the machine problem.
Since we have 4 production lines, the machine table has a field specifying which line does this machine belong to, so when entering a new defect the user will select the line first, just to limit the number of machines displayed in the machine combobox(there are over 20 machines per line).
Once the machine is selected, the "Machine problem" combobox needs to be filled with all the problems that apply to that kind of machine, executing a query like "SELECT problemId FROM problems WHERE machineType = someType", where someType is a value found at runtime (from the Machine combobox).
So my first question arrises from the fact that I think I won't be able to use the standard methods of creating the forms in access: I won't be able to directly bind the controls to the lookup tables, since I'm doind some filtering to limit the amount of records displayed (If I show all the machines from the machines table in a combobox, the user will have to select between 100 different values. The same will happen with the "problem" combobox). So I was thinking of creating all my controls (comboboxes mainly) as "unbound" and then populate them from VBA modules as the user starts to select the filtering options.
Then, when all the data are specified, I would construct an insert query like "INSERT INTO Defects VALUES(defId, machineId, problemId, date(), comment)"
Do you think this is a good way to do approach this ?
Second, how do I specify the value for an autonumeric value in an insert query ?
Thanks in advance for any advice on this issues.
I'm creating a little Access 2002 database and I have a few questions on how to do it.
The main purpose of the database is to store defects we found in our production. So when a defect is detected, we add an entry to the database specifying the defect, the machine causing the defect, the problem in that machine, and an optional comment.
The defect will be selected from a combobox bound to a lookup table containing all the types of defects (ofcourse we can add new defect types). The machine is also selected from a lookup table, and the same with the machine problem.
Since we have 4 production lines, the machine table has a field specifying which line does this machine belong to, so when entering a new defect the user will select the line first, just to limit the number of machines displayed in the machine combobox(there are over 20 machines per line).
Once the machine is selected, the "Machine problem" combobox needs to be filled with all the problems that apply to that kind of machine, executing a query like "SELECT problemId FROM problems WHERE machineType = someType", where someType is a value found at runtime (from the Machine combobox).
So my first question arrises from the fact that I think I won't be able to use the standard methods of creating the forms in access: I won't be able to directly bind the controls to the lookup tables, since I'm doind some filtering to limit the amount of records displayed (If I show all the machines from the machines table in a combobox, the user will have to select between 100 different values. The same will happen with the "problem" combobox). So I was thinking of creating all my controls (comboboxes mainly) as "unbound" and then populate them from VBA modules as the user starts to select the filtering options.
Then, when all the data are specified, I would construct an insert query like "INSERT INTO Defects VALUES(defId, machineId, problemId, date(), comment)"
Do you think this is a good way to do approach this ?
Second, how do I specify the value for an autonumeric value in an insert query ?
Thanks in advance for any advice on this issues.
