By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Access 2003 issue Default Value--HELP

P: 9
Boy I cant believe I have not been on these forms for a long time. But, I found this site again.
This site has not let me down before and so here goes it again.

I have a POS Access 2003 POS system. I need a default value changed in 2 locations based upon 1 table/form.

So I am currently in this old POS system using the default value for Sales Tax in a table and also in a form.
So here are the exact details:

Running Access 2003 with all service packs installed.

--Item 1--
Table Name: Orders
Table Field Name: TaxRate-Input
Current Default Value: 0.08

--Item 2--
Form Name:Orders
Form Field Name:TaxRate-Input
Current Default Value: 0.08

Other fields are dependent upon this field to have the default data in it.

Now I have another Table that someone can open up so they can possibly do a change.

Table Name: CurrentTax TBL
Table Field Name: TaxRate

I use a form called TaxRateForm that can be opened, update the value and saved and closed

This form TaxRateForm has a field called TaxRate.

What I want to do is someone be able to open the TaxRateForm input the current tax rate and would then update by either a macro or VBA (not great at VBA but can get by if I have help) the default value in the 2 other locations (listed above as Item 1 and Item 2)once the value in that form was changed and the user clicks update or something that would save the current form, close the form and update the default value in the table Orders, field name TaxRate-Input, default value to the new value.
This also needs to update the form name Orders, field name: TaxRate-Input, default value with the new value.

This way the new value will take place going forward for new records in either table or form. (not changing old records)

Now how can I do this????? Is this able to be done????
PLEASE, PLEASE! If someone can help me (steps, examples would be great help) I have to get this changed like yesterday.
Thanx in advance to anyone who helps and PS sorry for being so long winded, but I wanted to be detailed as much as possible.
Wespw1
Oct 24 '13 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,221
Make the default value for TaxRate-Input be like this
Expand|Select|Wrap|Line Numbers
  1. =Dlookup("TaxRate","CurrentTax TBL",strCriteria)
The strCriteria needs to have a where string that tells Dlookup how to find the correct row in the table.

I'm not sure you should try to change the default in the table definition dynamically. I'd do that manually.

Jim
Oct 24 '13 #2

P: 9
Jimatqsi,

Thank you so much for your help. I will give it a try.
Oct 25 '13 #3

P: 9
Jimatqsi,

I tried your method and can seem to get it to work.
I used:
Expand|Select|Wrap|Line Numbers
  1. =Dlookup("TaxRate", "CurrentTax TBL", "TaxPrimaryID = 1")
The Orders table has several other fields
The Form Orders also has several fields
The CurrentTax TBL only has 2 fields Field 1 TaxPrimaryID and Field 2 TaxRate
What is in these fields

TaxPrimaryID
1

TaxRate
0.08

What am I missing?

Also, Why would you not change them dynamically?
Oct 25 '13 #4

P: 9
More exact details to try to get help with this issue. I cant get the lookup to work or do a way to change the default value by either a macro or VBA.

I have a Table called: Orders
Has a Field called: TaxRate-Input
The current Default Value: 0.08

I have a Form called: Orders
Has a Field called: TaxRate-Input
The current Default Value: 0.08
This form is based upon a Query based upon a Table called: Orders Qry

I have another Table called: CurrentTaxTBL
I have a Field called: TaxPrimaryId
Has a current value of: 1
I have a Field called: TaxRate
has a current value of: 0.08

I have another Form called: TaxRateForm
I have a Field called: TaxRate
Has a current value of: 0.08
This is based off the Table: CurrentTaxTBL

What I am looking for is from the
Table: TaxRateForm
and after updating
Field: TaxRate
that a button is then clicked that will run a Macro that will take the value inputted in the TaxRate Field and update the Default Values on
Table: Orders
Field: Tax-Rate Input

I also want to update the Default Values on
Form: Orders
Field: TaxRate-Input

I believe I need to make sure those tables and forms are not open first. Now I am sure this could be done as a Dlookup but I have not been able to get that to work. So I want the Default Values to be updated with what ever is inputted into the TaxRateForm. I also know it could be done in VBA. But, I am not the greatest at VBA.
If someone could help me get this to work I would be very appreciated.
The access is 2003 with all svc pack installed.
Nov 15 '13 #5

NeoPa
Expert Mod 15k+
P: 31,186
The DefaultValue property requires that the value stored represents a formula that returns a string. Thus, the DefaultValue value itself should be something like :
Expand|Select|Wrap|Line Numbers
  1. Me.XXX.DefaultValue = "=""" & Dlookup("[TaxRate]", "[CurrentTax TBL]", "[TaxPrimaryID] = 1") & """"
I've fallen foul of that one on a number of occasions.
Nov 15 '13 #6

P: 9
NeoPa,

Thank you for your help. I put this in the DefaultValue:
Expand|Select|Wrap|Line Numbers
  1. = "=""" & Dlookup("[TaxRate]", "[CurrentTax TBL]", "[TaxPrimaryID] = 1") & """"
When I clicked Save I got this message:
Unknown function 'DLookUp' in validation expression or default value on 'Orders.TaxRate-Input'.

I don't get what I am doing wrong. The fields on each table are the same size type decimal places.

Am I missing something in the translation of your answer?
Nov 18 '13 #7

NeoPa
Expert Mod 15k+
P: 31,186
I wasn't clear enough it seems. Please look at it again (as I have now updated it to make it clear that it is a line of VBA code and not a value to enter manually into the property at design time.
Nov 19 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.