Default value for field not appearing in Form. also, how to have the Default value for one of the field to "draw" from another related-table. (1 Viewer)

tcneo

Member
Local time
Tomorrow, 05:21
Joined
Dec 7, 2021
Messages
68
[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!
 

Attachments

  • Test Planner.accdb
    3.1 MB · Views: 343
Last edited:

June7

AWF VIP
Local time
Today, 13:21
Joined
Mar 9, 2014
Messages
5,423
Need to set the DefaultValue property on the textbox. Yes, adding default value to table after textbox was created would not automatically modify the textbox property. All setting these properties in table does is allow for the properties to carry over to control when field is dragged from Field List or when using wizard to build form.

Include Duration_of_test as a column in combobox RowSource. Pull value from that column by referencing its index. Index begins with 0 so if it is column 4 its index is 3. Then code in combobox AfterUpdate event:

If IsNull(Me!Duration_per_qty) Then Me.Duration_per_qty = Me.1cboAnalyticalMethod.Column(3)
 

tcneo

Member
Local time
Tomorrow, 05:21
Joined
Dec 7, 2021
Messages
68
Need to set the DefaultValue property on the textbox. Yes, adding default value to table after textbox was created would not automatically modify the textbox property. All setting these properties in table does is allow for the properties to carry over to control when field is dragged from Field List or when using wizard to build form.

Include Duration_of_test as a column in combobox RowSource. Pull value from that column by referencing its index. Index begins with 0 so if it is column 4 its index is 3. Then code in combobox AfterUpdate event:

If IsNull(Me!Duration_per_qty) Then Me.Duration_per_qty = Me.1cboAnalyticalMethod.Column(3)
which combobox should I put the above in, the combobox where I make my selection of Analytical Method?

What does "Me" refer to? Do I need to change it to something else specific to my database, or is it a keyword in VBA?
 

June7

AWF VIP
Local time
Today, 13:21
Joined
Mar 9, 2014
Messages
5,423
Me. is alias reference to the form or report the code is behind. Yes, it is valid use in VBA.

Code goes where it makes most sense which I think is for choosing Analytical Method since you said that is the value dictating duration.
 

oleronesoftwares

Passionate Learner
Local time
Today, 14:21
Joined
Sep 22, 2014
Messages
1,159
What does "Me" refer to? Do I need to change it to something else specific to my database, or is it a keyword in VBA?
it's used to refer to a control in the current record on a Form. i would suggest you google for an introductory book on ms access.
 

June7

AWF VIP
Local time
Today, 13:21
Joined
Mar 9, 2014
Messages
5,423
Me. does not refer to control, it is alias for name of form or report the code is behind. It is good practice to prefix control reference with form/report name ('qualifies' the control reference). Me. is just a shorthand for the form/report name.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:21
Joined
Feb 28, 2001
Messages
27,001
What does "Me" refer to?

"Me" is ONLY useful with forms and reports that have a "Class Module" attached to the form/report. When used, "Me" appears ONLY in the class-module's VBA code and refers to the object that provides context of the class module. So for example, if you have a form A with a control called Fred, the code in form A's class module can refer to Me.Fred in order to find that control. Note that if you have a different form B with a control called Sue, in module B the reference Me.Sue works - but Me.Fred (referencing form A's control) will not work.

Functionally, "Me" is a shortcut keyword in Access - like "ActiveSheet" in Excel VBA code or "ActiveDocument" in Word VBA code or like a single dot in a file-system reference. It can be used in place of a longer syntax such as Forms("A").control-name or Forms.Form_A.control-name or even this syntax: Set form-object = Me (to create an object pointer to the associated form).

Also note that "Me" in a GENERAL module does not work because there is no object attached to the module and therefore no associated object context.
 

oleronesoftwares

Passionate Learner
Local time
Today, 14:21
Joined
Sep 22, 2014
Messages
1,159
Me. does not refer to control, it is alias for name of form or report the code is behind
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:21
Joined
Feb 28, 2001
Messages
27,001
Second comment: Remember that default values declared as properties for something are applied only when a record is created (i.e. is a new record). Once the record is created, default values specified as properties have little or no meaning. However, on forms, there is nothing to stop you from putting code into a Form_Current event to test controls and assert defaults as needed.

The question of asserting a default from a separate table is that, technically as a property, this doesn't work well. But again, in a Form_Current routine, VBA can do things involving any table that Access cannot do via properties.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:21
Joined
Oct 29, 2018
Messages
21,358
[apologies that i posted this in Reports instead of Forms by mistake. But I do not know how to move or delete this thread.]
Just FYI. I moved your thread out of the Reports forum.
 

tcneo

Member
Local time
Tomorrow, 05:21
Joined
Dec 7, 2021
Messages
68
Need to set the DefaultValue property on the textbox. Yes, adding default value to table after textbox was created would not automatically modify the textbox property. All setting these properties in table does is allow for the properties to carry over to control when field is dragged from Field List or when using wizard to build form.

Include Duration_of_test as a column in combobox RowSource. Pull value from that column by referencing its index. Index begins with 0 so if it is column 4 its index is 3. Then code in combobox AfterUpdate event:

If IsNull(Me!Duration_per_qty) Then Me.Duration_per_qty = Me.1cboAnalyticalMethod.Column(3)
In the row source query for the 1cboAnalyticalMethod control (in the Task_Detail_Add form), I added the Duration_of_test field from the Analytical_Method table as the fourth column (from the left). I unchecked the Show box.

In the AfterUpdate event of the 1cboAnalyticcalMethod combo control, I clicked on the Code Builder and have the following:

Private Sub 1cboAnalyticalMethod_AfterUpdate()
If IsNull(Me.Duration_per_Qty) Then
Me.Duration_per_Qty = Me.1cboAnalyticalMethod.Column(3)
End If
End Sub

When I tried it, it didn't work. The Duration_per_qty textbox remains blank.

Did I do something wrong?
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:21
Joined
Sep 21, 2011
Messages
14,047
Walk through the code with F8.
Your statement infers Duration_per_Qty is not Null but a ZLS ?
 

June7

AWF VIP
Local time
Today, 13:21
Joined
Mar 9, 2014
Messages
5,423
Why are you prefixing combobox names with 1? Access won't like that and will have to enclose in [ ]. VBA will adjust the procedure name to use "ctl" prefix.

Probably have to change the combobox ColumnCount to 4 and ColumnWidths to 0";1";0";0"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:21
Joined
Feb 19, 2002
Messages
42,976
In the AfterUpdate event of the 1cboAnalyticcalMethod combo control, I clicked on the Code Builder and have the following:
Why are you storing the data redundantly? There are reasons to store data where it is used rather than where it is defined but make sure you understand the ramifications. I'm not sure this field fits but I'm just making the assessment based on the name of the field. For example, when you add an item to an order, you want the price at the time the order was placed. If you don't store it in the order, you need a more complex solution because if you look at this order next year, you don't want next year's price. Storying the price in the Order does not violate Normalization rules since you need the value of price at the time of the order. You want the price at the time the order was placed. A field named "qty" doesn't seem to fit into the "time dependent" reason class for storing the field in more than one place.
 

tcneo

Member
Local time
Tomorrow, 05:21
Joined
Dec 7, 2021
Messages
68
Why are you storing the data redundantly? There are reasons to store data where it is used rather than where it is defined but make sure you understand the ramifications. I'm not sure this field fits but I'm just making the assessment based on the name of the field. For example, when you add an item to an order, you want the price at the time the order was placed. If you don't store it in the order, you need a more complex solution because if you look at this order next year, you don't want next year's price. Storying the price in the Order does not violate Normalization rules since you need the value of price at the time of the order. You want the price at the time the order was placed. A field named "qty" doesn't seem to fit into the "time dependent" reason class for storing the field in more than one place.
as explained in the original post, the Analytical method has a default duration. So the Bio test usually takes 100mins for example. But after assigning the task to an analyst, in real time, things can happen and the actual duration may differ from the typical duration. Say for example, the actual test for a particular sample and day may take 120 mins. If the task record simply takes the value of the duration from the Analytical Method table, then there is no way to reflect the actual duration. That is why the task has its own duration.
 

tcneo

Member
Local time
Tomorrow, 05:21
Joined
Dec 7, 2021
Messages
68
Why are you prefixing combobox names with 1? Access won't like that and will have to enclose in [ ]. VBA will adjust the procedure name to use "ctl" prefix.

Probably have to change the combobox ColumnCount to 4 and ColumnWidths to 0";1";0";0"
noted. will remove numerals from now on. but even after renaming it without the "1", the above still didn't work.
 

tcneo

Member
Local time
Tomorrow, 05:21
Joined
Dec 7, 2021
Messages
68
Walk through the code with F8.
Your statement infers Duration_per_Qty is not Null but a ZLS ?
the field is currently blank at the moment, so the field is NULL? it can't be a ZLS since it is a number? right?
 

oleronesoftwares

Passionate Learner
Local time
Today, 14:21
Joined
Sep 22, 2014
Messages
1,159
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
Pick yes in the property "allow value list edits"
 

Attachments

  • allow value list edit.PNG
    allow value list edit.PNG
    15.3 KB · Views: 336

Users who are viewing this thread

Top Bottom