[apologies that i posted this in Reports instead of Forms by mistake. But I do not know how to move or delete this thread.]
Hi everyone,
But before I describe the Current Issue, let me describe my database first (attached). (note: dummy data is fed into this database)
Tables:
Analyst_Availability: Each record has a date, analyst_ID and number of mins the analyst is available for that day.
Analyst_Details: Analyst_ID, initials, name and email address
Analytical_Method: analytical_method_ID, name of analytical method, type of instrument that this test method uses, typical duration of test
AnalyticalMethod_for_each_Product: junction table that matches analytical method to each product, each product has one or more test methods.
Competency_Analyst_AnalyticalMethod: junction table that matches analytical method to each analyst, basically, this list the analytical methods that each analyst is qualified to perform
Instrument_Table: Instrument_ID, Instrument Type, name, brand, model serial number
Instrument_Type: Instrument_Type, each type of instrument will have one or more of that type of instrument
Product_Table: Product_ID and names
Task_Detail_Table: Task_DetailID, Task_ID, Date of Task Detail, Analyst assigned, Instrument assigned, Analytical method to be used, quantity of test, actual duration of test, status and remarks
Task_Table: Task_ID, Product_ID, Date task is created, Date task is due
General work flow:
Supervisor creates a Task, which is basically specifies which Product is to be tested and when it is due.
Planning staff will then create Task Details. Each Product has to be tested using one or more analytical methods. That is why each Task Detail is mapped to a Task. The analytical method to be used, the instrument to be used and the analyst performing the task is specified in this task detail. If the actual duration of the test is different from the typical value specified in the Analytical_Methods table, the user should be able to modify it here.
Current issue:
Current issues are happening in the form, Task_Detail_Add.
The field, Qty, for Task_Details table has a default value of 1. But it does not show up in the form, Task_Detail_Add. How do I make it appear? (It does appear if I create a new form based on Task_Detail_Table though). I vaguely recall thatI set the default value of 1 in the table after i created this form. I’m not sure if this is related to the issue.
I also would like the Duration_per_qty to be populated with the Duration value based on the Analytical_Method chosen in the combo box (1cboAnalyticalMethod). But I also want the user to be able to edit it if they want. i.e. if user selected Bio_1_Test as the Analytical Method, the Duration_per_qty should show the number 100. But the user should be able to change it to a different value, say, 85.
Side note: I have implemented cascading combo boxes in this form. The values that appear in 1cboAnalyticalMethod are based on the AnalyticalMethods that are matched to the Product_Name specified in the Task_ID. Likewise, in 1cboInstrumentID, the Instrument_ID is matched to the Analytical_Method. The list of Analyst_IDs available in 1cboAnalystID is depend on the availability of the Analyst and AnalyticalMethods. The cascading combos are working fine based on my needs.
How to use my file:
Task_Detail_Add can be accessed from the navigation pane.
As mentioned in my intro post, this is the first database I have created. And I only started using Access since last October'2021. I'm sure I have made many mistakes in the database that I create. I thank everyone in advance for reading this post, looking at my database and for your patience and helpfulness!
Hi everyone,
But before I describe the Current Issue, let me describe my database first (attached). (note: dummy data is fed into this database)
Tables:
Analyst_Availability: Each record has a date, analyst_ID and number of mins the analyst is available for that day.
Analyst_Details: Analyst_ID, initials, name and email address
Analytical_Method: analytical_method_ID, name of analytical method, type of instrument that this test method uses, typical duration of test
AnalyticalMethod_for_each_Product: junction table that matches analytical method to each product, each product has one or more test methods.
Competency_Analyst_AnalyticalMethod: junction table that matches analytical method to each analyst, basically, this list the analytical methods that each analyst is qualified to perform
Instrument_Table: Instrument_ID, Instrument Type, name, brand, model serial number
Instrument_Type: Instrument_Type, each type of instrument will have one or more of that type of instrument
Product_Table: Product_ID and names
Task_Detail_Table: Task_DetailID, Task_ID, Date of Task Detail, Analyst assigned, Instrument assigned, Analytical method to be used, quantity of test, actual duration of test, status and remarks
Task_Table: Task_ID, Product_ID, Date task is created, Date task is due
General work flow:
Supervisor creates a Task, which is basically specifies which Product is to be tested and when it is due.
Planning staff will then create Task Details. Each Product has to be tested using one or more analytical methods. That is why each Task Detail is mapped to a Task. The analytical method to be used, the instrument to be used and the analyst performing the task is specified in this task detail. If the actual duration of the test is different from the typical value specified in the Analytical_Methods table, the user should be able to modify it here.
Current issue:
Current issues are happening in the form, Task_Detail_Add.
The field, Qty, for Task_Details table has a default value of 1. But it does not show up in the form, Task_Detail_Add. How do I make it appear? (It does appear if I create a new form based on Task_Detail_Table though). I vaguely recall thatI set the default value of 1 in the table after i created this form. I’m not sure if this is related to the issue.
I also would like the Duration_per_qty to be populated with the Duration value based on the Analytical_Method chosen in the combo box (1cboAnalyticalMethod). But I also want the user to be able to edit it if they want. i.e. if user selected Bio_1_Test as the Analytical Method, the Duration_per_qty should show the number 100. But the user should be able to change it to a different value, say, 85.
Side note: I have implemented cascading combo boxes in this form. The values that appear in 1cboAnalyticalMethod are based on the AnalyticalMethods that are matched to the Product_Name specified in the Task_ID. Likewise, in 1cboInstrumentID, the Instrument_ID is matched to the Analytical_Method. The list of Analyst_IDs available in 1cboAnalystID is depend on the availability of the Analyst and AnalyticalMethods. The cascading combos are working fine based on my needs.
How to use my file:
Task_Detail_Add can be accessed from the navigation pane.
As mentioned in my intro post, this is the first database I have created. And I only started using Access since last October'2021. I'm sure I have made many mistakes in the database that I create. I thank everyone in advance for reading this post, looking at my database and for your patience and helpfulness!
Attachments
Last edited: