If IsNull DLookup Else Use this Field

  • Thread starter Thread starter JK
  • Start date Start date
J

JK

I'm using Access03 and I would be greatful if someone would help me program
the default value of a field.

I have a workorders form much like the one in the microsoft example. I have
a sales tax field with it's default value set to
"=DLookUp("[SalesTaxRate]","My Company Information")"

I also have a tab for outside vendors. The vendors are scattered all over
the country. I have a pull down menu so the user can select the vendor and
then enter any parts or services we hired the vendor to do. Each vendor can
have its own tax rate. So I have a combo box (VendorID) with VendorTaxRate as
one of the columns. When a user selects a vendor that vendors tax rate
populates a hidden field.

Now, I would like for the main Sales Tax % field with the default value
(specificed above) to say, if VendorID IsNull, then use the DLookup Function
above - if not - use the hidden vendor tax rate field.

Hope that makes sense.

I have to start taking VBA classes... Any help you could provide... Thx...
 
I'm not an MVP but I'm pretty sure your field would have to say something like

=IIF(IsNull(Me.comboboxVendor), DLOOKUP("[SalesTaxRate]","[My Company
Information]", "[SalesTaxID] = " & [SalesTaxID]), me.txtboxVendorTaxRate)

Preston
 
Sorry, also remember that access closes and opens quotes that are individual.
I.E. when you type "my name is " Preston " thank you " access sees (my name
is ) preston ( thank you )
you have to use double quotes to get a single quote inside of single quotes
I.E.

"my name is "" Preston "" thank you " access sees (my name is " Preston "
thank you)

hope this helps
 
I wasn't able to get either suggestion to work. It's probably me - I'm sure.
I tried entering your suggested code in both the before update and after
update events. When I wasn't able to make either of those options work, I
tried entering it directly in the default value field in the properties
window. When I create a new record #NAME appears in the SalesTaxRate field;
it's not pulling the default value or the rate assigned to a vendor.

What would be the basic steps for implimenting your suggestion?
Thx........

Preston said:
Sorry, also remember that access closes and opens quotes that are individual.
I.E. when you type "my name is " Preston " thank you " access sees (my name
is ) preston ( thank you )
you have to use double quotes to get a single quote inside of single quotes
I.E.

"my name is "" Preston "" thank you " access sees (my name is " Preston "
thank you)

hope this helps

JK said:
I'm using Access03 and I would be greatful if someone would help me program
the default value of a field.

I have a workorders form much like the one in the microsoft example. I have
a sales tax field with it's default value set to
"=DLookUp("[SalesTaxRate]","My Company Information")"

I also have a tab for outside vendors. The vendors are scattered all over
the country. I have a pull down menu so the user can select the vendor and
then enter any parts or services we hired the vendor to do. Each vendor can
have its own tax rate. So I have a combo box (VendorID) with VendorTaxRate as
one of the columns. When a user selects a vendor that vendors tax rate
populates a hidden field.

Now, I would like for the main Sales Tax % field with the default value
(specificed above) to say, if VendorID IsNull, then use the DLookup Function
above - if not - use the hidden vendor tax rate field.

Hope that makes sense.

I have to start taking VBA classes... Any help you could provide... Thx...
Click to expand...
Click to expand...
 
This at least picks up the default value in the My Company Info table.
However, it does not pick up the vendor tax rate when one is selected (when
txtSupplierID is not null).

=IIf(IsNull([txtSupplierID]),DLookUp("[SalesTaxRate]","My Company
Information"),[VendorTaxRate])



ruralguy via AccessMonster.com said:
You can use an Immediate IF (IIF()) for that:

=IIF(IsNull([VendorID]),DLookUp("[SalesTaxRate]","My Company Information")" ,
Me.HiddenBox)

I'm using Access03 and I would be greatful if someone would help me program
the default value of a field.

I have a workorders form much like the one in the microsoft example. I have
a sales tax field with it's default value set to
"=DLookUp("[SalesTaxRate]","My Company Information")"

I also have a tab for outside vendors. The vendors are scattered all over
the country. I have a pull down menu so the user can select the vendor and
then enter any parts or services we hired the vendor to do. Each vendor can
have its own tax rate. So I have a combo box (VendorID) with VendorTaxRate as
one of the columns. When a user selects a vendor that vendors tax rate
populates a hidden field.

Now, I would like for the main Sales Tax % field with the default value
(specificed above) to say, if VendorID IsNull, then use the DLookup Function
above - if not - use the hidden vendor tax rate field.

Hope that makes sense.

I have to start taking VBA classes... Any help you could provide... Thx...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
The control name is "VendorTaxRate." The control is visible and is being
udpated with the correct vendor tax rate when a vendor is selected. Could the
problem have to do with the percent formatting I have on these fields? Or,
could the tab control be affecting this?

VendorTaxRate is simply formatted in the Prop Window as Percent and in the
workorder table I have the Input Mask set to 9.00;1;"_"

SalesTaxRate has the same Input Mask but I'm also using Allen Brown's (I
think) MakePercent Mod.

I've pasted that function below:
Public Function MakePercent(txt As TextBox)
On Error GoTo Err_Handler
'Purpose: Divide the value by 100 if no percent sign found.
'Usage: Set the After Update property of a text box named Text23 to:
' =MakePercent([Text23])

If Not IsNull(txt) Then
If InStr(txt.Text, "%") = 0 Then
txt = txt / 100
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 2185 Then 'No Text property unless control has focus.
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
Resume Exit_Handler
End Function





ruralguy via AccessMonster.com said:
What is the name of the Control that contains the [VendorTaxRate]? Make this
control visible while you troubleshoot.
This at least picks up the default value in the My Company Info table.
However, it does not pick up the vendor tax rate when one is selected (when
txtSupplierID is not null).

=IIf(IsNull([txtSupplierID]),DLookUp("[SalesTaxRate]","My Company
Information"),[VendorTaxRate])
You can use an Immediate IF (IIF()) for that:
Click to expand...
[quoted text clipped - 22 lines]
I have to start taking VBA classes... Any help you could provide... Thx...
Click to expand...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
That statement should work if the field is truly Null. It may be that it is a
null string or a blank character, which are different from the Access value
of Null. Try replacing IsNull([txtSupplierID]) with Len(txtSupplierID) = 0
and see if that works.
The control name is "VendorTaxRate." The control is visible and is being
udpated with the correct vendor tax rate when a vendor is selected. Could the
problem have to do with the percent formatting I have on these fields? Or,
could the tab control be affecting this?

VendorTaxRate is simply formatted in the Prop Window as Percent and in the
workorder table I have the Input Mask set to 9.00;1;"_"

SalesTaxRate has the same Input Mask but I'm also using Allen Brown's (I
think) MakePercent Mod.

I've pasted that function below:
Public Function MakePercent(txt As TextBox)
On Error GoTo Err_Handler
'Purpose: Divide the value by 100 if no percent sign found.
'Usage: Set the After Update property of a text box named Text23 to:
' =MakePercent([Text23])

If Not IsNull(txt) Then
If InStr(txt.Text, "%") = 0 Then
txt = txt / 100
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 2185 Then 'No Text property unless control has focus.
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
Resume Exit_Handler
End Function





ruralguy via AccessMonster.com said:
What is the name of the Control that contains the [VendorTaxRate]? Make this
control visible while you troubleshoot.
This at least picks up the default value in the My Company Info table.
However, it does not pick up the vendor tax rate when one is selected (when
txtSupplierID is not null).

=IIf(IsNull([txtSupplierID]),DLookUp("[SalesTaxRate]","My Company
Information"),[VendorTaxRate])

You can use an Immediate IF (IIF()) for that:

[quoted text clipped - 22 lines]

I have to start taking VBA classes... Any help you could provide... Thx...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Back
Top