Default Value for field in Table

[Guest]

Hi, I have two tables "Client" and "Projects" that are related by ID number.
In the Client table there is a field named "TaxForm".

In the Projects table, I have a field named "TaxDeptNotified", which is a
text field. Normally I would create a combo box on a form to select the
answer from a table that contains the values: Yes, No, NA.

Is it possible to have the Project\TaxDeptNotified field default to a
certain value (NA)based on the value in the Client\TaxForm field? (If the
Client Form is NOT a 990, the Answer s/b NA.)

TIA!

 

[Jason Lopez]

I have actually tried something similar (but with not much success, so this
could help us both out). There is some code that you might be able to enter
that is a SELECT CASE statement where that "TaxDeptNotified" field will be
filled if the condition required is met in the case statement (anyone
correct me if I am wrong). As long as it is on the same form, you should be
okay by using something like this (again, someone correct me as I have not
had much success with the SELECT CASE statement yet):

If Me!TaxForm = (what ever the value is) Then
Me!TaxDeptNotified = "Yes"
ElseIf Me!TaxForm = (Whatever secondary value should be) Then
Me!TaxDeptNotified = "No"
Else
Me!TaxDeptNotified = "N/A"
End If

If someone can translate this into a SELECT CASE statement, that might help
Karin out better.

Where it can help me out is if someone can help me point my statement to the
mainform from a subform. Essentially, the AfterUpdate event will trigger
the code and force data from the subform to the mainform if the conditions
are met.

Jason Lopez

 

[Guest]

Additionally - I could use a form to accomplish the request below. I have a
main form from Table Client and a Subform from Table Projects, both of which
include the fields I talk about below.

 

[louisjohnphillips]

Additionally - I could use a form to accomplish the request below. I have a
main form from Table Client and a Subform from Table Projects, both of which
include the fields I talk about below.








- Show quoted text -

Let's assume that the Clients and Projects tables have a master/detail
relationship. The frmClients is the main form and the frmProjectsSub
is a subform. These forms are linked based on ClientID.

Navigation to a new Clients row causes a new set of Projects rows to
appear. This has triggered the "on current" event in the
frmProjectsSub.

In the frmProjectsSub's "Form_Current" event handler, try

Private Sub Form_Current()
if Parent.ClientID.value = "999" then
Me.TaxDeptNotified.defaultvalue = NULL
else
Me.TaxDeptNotified.defaultvalue = "NA"
end if
end sub

 

[louisjohnphillips]

Additionally - I could use a form to accomplish the request below. I have a
main form from Table Client and a Subform from Table Projects, both of which
include the fields I talk about below.








- Show quoted text -

Let's assume that the Clients and Projects tables have a master/detail
relationship. The frmClients is the main form and the frmProjectsSub
is a subform. These forms are linked based on ClientID.

Navigation to a new Clients row causes a new set of Projects rows to
appear. This has triggered the "on current" event in the
frmProjectsSub.

In the frmProjectsSub's "Form_Current" event handler, try

Private Sub Form_Current()
if Parent.ClientID.value = "999" then
Me.TaxDeptNotified.defaultvalue = NULL
else
Me.TaxDeptNotified.defaultvalue = "NA"
end if
end sub

 

[Guest]

This does not work except for the first "N/A" record it comes to. I'm going
to add more detail to my original message.

 

[Guest]

Yet more detail...

I only want to update the Projects.TaxDeptNotified field if the
Client.TaxForm field is not (<>) 990. I will be manually updating the field
otherwise. (I suppose I could periodically run an update query to handle
this.)

Updating through the Main/Sub Form may not be the most appropriate place for
this update, as I do store the results in the Project Table.

 

[Guest]

Sorry that it's taking a bijillion messages to get my question/needs across.
Per my last I said I could run an update query, which would update the table,
but it doesn't update the combo boxes on the form, so that answer doesn't
really work.

 

[Jason Lopez]

In the field properties is the Default Value selection under the Data tab.
Set that to what you are looking for initially and then all your records
will be marked "N/A" unless you change the value.

Jason Lopez

 

[Jason Lopez]

For something similar that I am working on, how would I write the code to go
reverse? IOW, I have a parent field that needs to put a value in the
control on a subform. I can see the "Parent" and "Me" marks on this set of
code. But would I identify the subform in the code as
Forms.Subform1.Field.defaultvalue = "NA?"

Jason Lopez

 

[John W. Vinson]

For something similar that I am working on, how would I write the code to go
reverse? IOW, I have a parent field that needs to put a value in the
control on a subform. I can see the "Parent" and "Me" marks on this set of
code. But would I identify the subform in the code as
Forms.Subform1.Field.defaultvalue = "NA?"

By far the easiest way to do this involves no code at all. Just include the
mainform field as (or as part of) the subform's Master Link Field property
(separated by a semicolon if there already is a master/child link field), and
the field into which it should be stored likewise in the Child Link Field.

John W. Vinson [MVP]

 

[Guest]

What I finally ended up doing was creating conditional formatting on the
field (Date990) in the subform. I created a text box on the subform and made
its control the field (TaxForm) from the main form. Then I conditionally
formatted the [Date990] so that it is "not visible" if the TaxForm <>990.
This way it won't get filled in when it shouldn't. Thanks to everyone for
the help.