tables

  • Thread starter Thread starter Jason Frazer
  • Start date Start date
J

Jason Frazer

The form name is "Customers" It has a SQL statement that
refernances 2 tables. Table [Table_Tax_Rate] has 5
fields. [Zip code] which is the primary key, [Inside City
Limits], [Inside City Limits tax rate], [Outside City
Limits] &[Outside City Limits tax rate]. Table
[Table_Customer] has [Zipcode], [incitylimits],
[Customer_Tax Rate] "incitylimits" is a pickbox field.

What I need this to do is the follow:
When I click on button "Update tax" It would need to run a
VB routine that will look at the zip code in field
[Zipcode] and look in the table [Table_Tax_Rate] and go to
the record with that zipcode and then go to field [Inside
City Limits tax rate] if [incitylimits] is checked. If
[incitylimits] is not checked go to [Outside City Limits
tax rate] and set that value to [Customer_Tax Rate]. How
can I do this?

Thanks for your time and help

Jason Frazer
 
-----Original Message-----
The form name is "Customers" It has a SQL statement that
refernances 2 tables. Table [Table_Tax_Rate] has 5
fields. [Zip code] which is the primary key, [Inside City
Limits], [Inside City Limits tax rate], [Outside City
Limits] &[Outside City Limits tax rate]. Table
[Table_Customer] has [Zipcode], [incitylimits],
[Customer_Tax Rate] "incitylimits" is a pickbox field.

What I need this to do is the follow:
When I click on button "Update tax" It would need to run a
VB routine that will look at the zip code in field
[Zipcode] and look in the table [Table_Tax_Rate] and go to
the record with that zipcode and then go to field [Inside
City Limits tax rate] if [incitylimits] is checked. If
[incitylimits] is not checked go to [Outside City Limits
tax rate] and set that value to [Customer_Tax Rate]. How
can I do this?

Thanks for your time and help

Jason Frazer
Click to expand...
Hi Jason, use online help for info on the function
dlookup. Use the following as an example...

private sub cmdUpdatetax_Click()
dim strCriteria as string
dim strField as string
dim curTaxRate as currency

if chkInCityLimits then
strField="Inside City Limits tax rate"
else
strField="Outside City Limits tax rate"
end if

strCriteria="[Zipcode]=" & chr(34) & txtZipcode & chr(34)

curTaxRate=dlookup(strfield,"Table_Tax_Rate",strCriteria)

txtCustomer_Tax Rate=curTaxRate

end sub

the above assumes that zip codes are alphanumeric. If they
are numeric then leave off the chr(34).

Luck & Happy New Year
Jonathan
 
I'm having trouble getting this code to work.
I get the following error "Data type mismatch in criteria
expression" I know the error is created during the
dlookup operation. The data type
for "OutsideCityLimitsTaxRate"
and "InsideCityLimitsTaxRate" is currency with a standard
format. The data type for "ZipCode" and "PostalCode" is
text.

Here is the code I have so far:

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click
Dim strCriteria As String
Dim strField As String
Dim curTaxRate As Currency

Rem DoCmd.RunCommand acCmdSaveRecord

If chkInsideCityLimits Then
strField = "InsideCityLimitsTaxRate"
Else
strField = "OutsideCityLimitsTaxRate"
End If

strCriteria = "ZipCode=" & [PostalCode]

curTaxRate = DLookup(strField, "Table_Tax_Rate",
strCriteria)

[Sales Tax Rate] = curTaxRate

Me.Refresh
MsgBox "Tax Information was updated."

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.Description
Resume Exit_Command69_Click

End Sub

Thanks for you help and time

Jason Frazer
-----Original Message-----
-----Original Message-----
The form name is "Customers" It has a SQL statement that
refernances 2 tables. Table [Table_Tax_Rate] has 5
fields. [Zip code] which is the primary key, [Inside City
Limits], [Inside City Limits tax rate], [Outside City
Limits] &[Outside City Limits tax rate]. Table
[Table_Customer] has [Zipcode], [incitylimits],
[Customer_Tax Rate] "incitylimits" is a pickbox field.

What I need this to do is the follow:
When I click on button "Update tax" It would need to run a
VB routine that will look at the zip code in field
[Zipcode] and look in the table [Table_Tax_Rate] and go to
the record with that zipcode and then go to field [Inside
City Limits tax rate] if [incitylimits] is checked. If
[incitylimits] is not checked go to [Outside City Limits
tax rate] and set that value to [Customer_Tax Rate]. How
can I do this?

Thanks for your time and help

Jason Frazer
Click to expand...
Hi Jason, use online help for info on the function
dlookup. Use the following as an example...

private sub cmdUpdatetax_Click()
dim strCriteria as string
dim strField as string
dim curTaxRate as currency

if chkInCityLimits then
strField="Inside City Limits tax rate"
else
strField="Outside City Limits tax rate"
end if

strCriteria="[Zipcode]=" & chr(34) & txtZipcode & chr(34)

curTaxRate=dlookup(strfield,"Table_Tax_Rate",strCriteria)

txtCustomer_Tax Rate=curTaxRate

end sub

the above assumes that zip codes are alphanumeric. If they
are numeric then leave off the chr(34).

Luck & Happy New Year
Jonathan
.
Click to expand...
 
Back
Top