Default value on form using conditional expression?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to assign a default value based on a conditional expression. e.g. For
the textbox control [TaskDueDate] I want the default value to be calculated
as follows: =IIf([TaskType] = "Set-Up", [EventDate],Null) A default value is
never returned with this expression even when the [TaskType] = Set-Up.
 
DebsMo,

You won't be able to use a Default Value for this purpose. The Default
Value is assigned at the point where a new record is started, and at
that point in your data entry, Access doesn't know the TaskType, so it
can't assign the TaskDueDate. You could, however, use a VBA procedure
on the After Update event of the control (combobox?) where you enter the
TaskType, something along these lines...
If Me.TaskType = "Set-Up" Then
Me.TaskDueDate = Me.EventDate
Else
Me.TaskDueDate = Null
End If
 
Thanks so much! I was tearing my hair out and couldn't find this explained
in any help section or in the number of books I have on access.

Steve Schapel said:
DebsMo,

You won't be able to use a Default Value for this purpose. The Default
Value is assigned at the point where a new record is started, and at
that point in your data entry, Access doesn't know the TaskType, so it
can't assign the TaskDueDate. You could, however, use a VBA procedure
on the After Update event of the control (combobox?) where you enter the
TaskType, something along these lines...
If Me.TaskType = "Set-Up" Then
Me.TaskDueDate = Me.EventDate
Else
Me.TaskDueDate = Null
End If

--
Steve Schapel, Microsoft Access MVP

I want to assign a default value based on a conditional expression. e.g. For
the textbox control [TaskDueDate] I want the default value to be calculated
as follows: =IIf([TaskType] = "Set-Up", [EventDate],Null) A default value is
never returned with this expression even when the [TaskType] = Set-Up.
 
Steve,

Can you help me with a similar problem? I took a class in VB but I'm
completely useless working in it. I haven't even thought about it for 6
years.

Anyway, I have a similar problem. I have a form with two different types of
data that can be entered: Lumpmisc Credits or Lump Sum Charges. There is a
combo box for each type of entry (multiple values for each one). I have a
field called "Adjustment Type" that I want to enter either "lm" or "ls"
depending on which type of adjustment is input. I understand I cannot use a
default value, so VB is the only option I see for this. Using what you gave
to DebsMo, I think I need something like this:
Private Sub Adj_Type_AfterUpdate()
If Me.[Lumpmisc Credit Codes] = " " Then
Me.[Lumpmisc Credit Codes] = "ls"
Else
Me.[Lumpmisc Credit Codes] = "lm"
End If

But, this doesn't work. How should I define the variable "Me.[Lumpmisc
Credit Codes]? Shoud it be something like 'Me.[Lumpmisc Credit Codes] =
Text'? "Lumpmisc Credit Codes' is the name of the field in which the user
would enter the type of adjustment. I want the form to enter "ls" if that
field is empty (meaning they entered a lumpsum instead of lumpmisc
adjustment) and "lm" if there is data in that field.


Steve Schapel said:
DebsMo,

You won't be able to use a Default Value for this purpose. The Default
Value is assigned at the point where a new record is started, and at
that point in your data entry, Access doesn't know the TaskType, so it
can't assign the TaskDueDate. You could, however, use a VBA procedure
on the After Update event of the control (combobox?) where you enter the
TaskType, something along these lines...
If Me.TaskType = "Set-Up" Then
Me.TaskDueDate = Me.EventDate
Else
Me.TaskDueDate = Null
End If

--
Steve Schapel, Microsoft Access MVP

I want to assign a default value based on a conditional expression. e.g. For
the textbox control [TaskDueDate] I want the default value to be calculated
as follows: =IIf([TaskType] = "Set-Up", [EventDate],Null) A default value is
never returned with this expression even when the [TaskType] = Set-Up.
 
Hi Steave

Canu help me with a similar problem?

i was trying to create flying logging database using ACCESS in which i have
to calculate a default value for field DURATION (of flight) by subtracting
Flight take-landing time and Filght take-off time .

i tried a few time it is not working out well .
it will be great full if u can give solution for this...


with regards

joe

Steve,
Can you help me with a similar problem? I took a class in VB but I'm
completely useless working in it. I haven't even thought about it for 6
years.

Anyway, I have a similar problem. I have a form with two different types of
data that can be entered: Lumpmisc Credits or Lump Sum Charges. There is a
combo box for each type of entry (multiple values for each one). I have a
field called "Adjustment Type" that I want to enter either "lm" or "ls"
depending on which type of adjustment is input. I understand I cannot use a
default value, so VB is the only option I see for this. Using what you gave
to DebsMo, I think I need something like this:
Private Sub Adj_Type_AfterUpdate()
If Me.[Lumpmisc Credit Codes] = " " Then
Me.[Lumpmisc Credit Codes] = "ls"
Else
Me.[Lumpmisc Credit Codes] = "lm"
End If

But, this doesn't work. How should I define the variable "Me.[Lumpmisc
Credit Codes]? Shoud it be something like 'Me.[Lumpmisc Credit Codes] =
Text'? "Lumpmisc Credit Codes' is the name of the field in which the user
would enter the type of adjustment. I want the form to enter "ls" if that
field is empty (meaning they entered a lumpsum instead of lumpmisc
adjustment) and "lm" if there is data in that field.

Shaun Rucker said:
Steve,

Can you help me with a similar problem? I took a class in VB but I'm
completely useless working in it. I haven't even thought about it for 6
years.

Anyway, I have a similar problem. I have a form with two different types of
data that can be entered: Lumpmisc Credits or Lump Sum Charges. There is a
combo box for each type of entry (multiple values for each one). I have a
field called "Adjustment Type" that I want to enter either "lm" or "ls"
depending on which type of adjustment is input. I understand I cannot use a
default value, so VB is the only option I see for this. Using what you gave
to DebsMo, I think I need something like this:
Private Sub Adj_Type_AfterUpdate()
If Me.[Lumpmisc Credit Codes] = " " Then
Me.[Lumpmisc Credit Codes] = "ls"
Else
Me.[Lumpmisc Credit Codes] = "lm"
End If

But, this doesn't work. How should I define the variable "Me.[Lumpmisc
Credit Codes]? Shoud it be something like 'Me.[Lumpmisc Credit Codes] =
Text'? "Lumpmisc Credit Codes' is the name of the field in which the user
would enter the type of adjustment. I want the form to enter "ls" if that
field is empty (meaning they entered a lumpsum instead of lumpmisc
adjustment) and "lm" if there is data in that field.


Steve Schapel said:
DebsMo,

You won't be able to use a Default Value for this purpose. The Default
Value is assigned at the point where a new record is started, and at
that point in your data entry, Access doesn't know the TaskType, so it
can't assign the TaskDueDate. You could, however, use a VBA procedure
on the After Update event of the control (combobox?) where you enter the
TaskType, something along these lines...
If Me.TaskType = "Set-Up" Then
Me.TaskDueDate = Me.EventDate
Else
Me.TaskDueDate = Null
End If

--
Steve Schapel, Microsoft Access MVP

I want to assign a default value based on a conditional expression. e.g. For
the textbox control [TaskDueDate] I want the default value to be calculated
as follows: =IIf([TaskType] = "Set-Up", [EventDate],Null) A default value is
never returned with this expression even when the [TaskType] = Set-Up.
 
I have a related syntax question. I have a Student combo box and a Class
combo box that display the student name and class name, respectively, and
actually store IDs for these entities in the table. The Students table has a
Class field where the student's default class is referenced. When I input a
student, I want the Class combo box to be automatically updated to show the
default class designation. I tried the following, but am getting Run-time
error 2465 (can't find the field '|' in expression):

Private Sub Student_Name_AfterUpdate()
Set [Input Student Attendance]![Class] = "select [Students]![Class] from
[Students] where [Students]![ID]=[Input Student Attendance]![Student]"
End Sub

Thanks for taking a look at this.
 
You could also assign the DLookup command as your ControlSource for each of
the text boxes showing several different related things about your selection.
Just assign a Combo to your form, linking it to the desired field of the
table (i.e. Students). Every time a student's name is selected in the combo,
all of the remaining text boxes change to display different things about that
student. The number of text boxes is not limited. In the following example,
when a student's name is selected from an alphabetical dropdown of students,
his/her age is automatically displayed in the text box:

The Combo Box would have the following as its ControlSource:
StudentName

....and the following as its RowSource:
SELECT [StudentName].[ID], [Students].[StudentName] FROM [Students] ORDER BY
[StudentName];

Each Text box would have the following as its ControlSource:
=DLookUp("[Age]","[Students]","[ID]=" & [Forms]![StudentLookup]![Age])
 
Back
Top