Code to Changing a property in the table design view

[David Deacon]

Hi i was given the following advise,below my OriginalQuestion

I am a little new to ADOX can you direct me to the following
Do i place the code behind a button on a form?
Or do i place it in the On Open event and have a the default 10% and
give the user the option to change it to 0%

I have referenced th appropriate library and the default value of the
field to change is 0.1

My original question is

I want to do the following

A user decides if they want all orders reports etc to include 10% tax or
0% tax

Now i have set the default value in my [TAX]field in the tblOrders to .1
(10%) if i go back to the tbl in design view and change this to 0% all
works well.
I now want to somehow; allow the user to change this from a form as the
user has no access to the design view.

Can i call the default value in the tblOrders design view or any ideas

Regards

DD
This was the advise given
Posting Via Google so not sure if someone's already answered...

Using DAO

check out the defaultvalue property of the field object.

eg
in the debug window

?Currentdb.Tabledefs("Orders").Fields("Tax").Defau ltValue

Using ADOX
dim cat as ADOX.Catalog
'open your catalog

Set cat = New ADOx.Catalog
cat.ActiveConnection = strConn

catalog.tables("Orders").columns("Tax").Properties ("Default").Value

Reference the relevant library and hit F2 and check out the various
objects you can use.

Peter
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pink Panther]

Hi David,

I'm a little confused by what you are trying to do.

Is the tax a global setting for the application (with or without GST I
presume) or is it applied to each transaction? I presume it's global.

If applied to each transaction why not use the default property of the
control (assuming you are displaying the tax on the user interace).
Store this value in a table or registry (using SaveSettings|GetSetting
or somesuch).

Using ADOX (you must be using MDAC 2.6 or later otherwise err 3241
appears).

On a form not bound to Orders
Put a command button on the form

Option Compare Database
Option Explicit

Public sub ChangeDefault_Click()

Dim cat As ADOX.Catalog

Set cat = New ADOX.Catalog
'change the conn string or use the current connection string
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\YourMDB.mdb;Persist Security Info=False"

cat.Tables("Orders").Columns("Tax").Properties("De fault").Value = 0

End Sub

Of course the table cannot be locked or opened by any users to do
this.

You don't need to go into design view to manipulate the database|table
schema.

Anyways that's the basics of how to do it. I am sure you can expand
upon it.

It should be realitively straightforward to work out that
?cat.Tables("Orders").Columns("Tax").Properties("D efault").Value
is a give away that the assignment
cat.Tables("Orders").Columns("Tax").Properties("De fault").Value = 0
is possible.

Peter

David Deacon <da**********@bigpond.com.au> wrote in message news:<40***********************@news.frii.net>...

Hi i was given the following advise,below my OriginalQuestion

I am a little new to ADOX can you direct me to the following
Do i place the code behind a button on a form?
Or do i place it in the On Open event and have a the default 10% and
give the user the option to change it to 0%

I have referenced th appropriate library and the default value of the
field to change is 0.1

My original question is

I want to do the following

A user decides if they want all orders reports etc to include 10% tax or
0% tax

Now i have set the default value in my [TAX]field in the tblOrders to .1
(10%) if i go back to the tbl in design view and change this to 0% all
works well.
I now want to somehow; allow the user to change this from a form as the
user has no access to the design view.

Can i call the default value in the tblOrders design view or any ideas

Regards

DD
This was the advise given

[David Deacon]

Hi Peter
thanks for all your help
I have to go back and re-think as i know find out that the GST can vary
on items from 10% to 0% so i will need to have a global setting that
then if a item is 0% then i can override the global setting in my Order
form
My tblOrdersDetail at this stage calculates the GST after the Extended
amount, i will need to have the GST in each linetotal and then i am
going to want it to default to 10% and maybe work it so if GST is 0 then
you place a -10% or something like this

As for ADOX what is MDAC is this a developers edition?? or ??
I am yet to buy the DEdition but will be!

Can i get back to you if i get stuck as you seem to know alot about this
side of things
DD

DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pink Panther]

Hi David,

You're welcome.

MDAC is the anacronym for Microsoft Data Access Components.
Essentially these components are the MS librarys for database
connectivity.

It is part of the MS operating system (well the recent ones anyhow).

you can check you MDAC version using CC.exe from Microsoft.

see http://support.microsoft.com/default.aspx?kbid=301202 on how to do
this.

This will also show you the relevant files involved.

Normally when storing data don't store calculated values (in
transactional system anyways). Instead of storing RawAmount, TaxRate,
BillAmount or somesuch store the RawAmount and TaxRate. Use a
calculation to get the BillAmount when displaying it on the user
interface (forms, reports).

If you are going to use a global default for the application that the
use can manipulate i'd save the value in the registry and make a
wrapper function to retrieve the value. However other may have
different suggestions.

You may wish to get the user to set the default but they need to
select either 0 or 10% for each line of the order, unless the whole
order is GST Inclusive/Exclusive then you will need to store the Tax
Rate on the Order Header and apply it to the order total (ie the sum
of the lines).

I don't check my email account too often (it's a spam trap really, but
feel free to contact me) and the best policy is to post your questions
to the NewsGroup as there is an amazing amount of knowledge in
everyone's collective head.

Regards,

Peter

David Deacon <da**********@bigpond.com.au> wrote in message news:<40***********************@news.frii.net>...

Hi Peter
thanks for all your help
I have to go back and re-think as i know find out that the GST can vary
on items from 10% to 0% so i will need to have a global setting that
then if a item is 0% then i can override the global setting in my Order
form
My tblOrdersDetail at this stage calculates the GST after the Extended
amount, i will need to have the GST in each linetotal and then i am
going to want it to default to 10% and maybe work it so if GST is 0 then
you place a -10% or something like this

As for ADOX what is MDAC is this a developers edition?? or ??
I am yet to buy the DEdition but will be!

Can i get back to you if i get stuck

[David Deacon]

Thanks Peter
I have got your global setting on TAX working great, i could leave the
gst out of the line total but i cant for the long term vision.
I am going to look at the idea of using your suggestion

If you are going to use a global default for the application that the
use can manipulate i'd save the value in the registry and make a
wrapper function to retrieve the value. However other may have
different suggestions.

Could you give me an idea on how to do this

Regards
DD
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pink Panther]

Hi David,

Option Compare Database
Option Explicit

const USER_DEFAULTS = "UserDefaults"

Public Function SaveUserDefault(ByRef pstrDefault As String, ByRef
pvarValue As Variant) As Boolean

On Err GoTo err_Handler

'i'd also make a wrapper for YourAppName
SaveSetting "YourAppName", USER_DEFAULTS, pstrDefault, pvarValue

SaveUserDefault = True

exit_Handler:
Exit Function

err_Handler:
MsgBox Err.Number & " : " & Err.Description, vbCritical
Resume exit_Handler

End Function

Public Function GetUserDefault(ByRef pstrDefault As String) As Variant

On Err GoTo err_Handler

GetUserDefault = GetSetting("YourAppName", USER_DEFAULTS, pstrDefault,
0)

exit_Handler:
Exit Function

err_Handler:
MsgBox Err.Number & " : " & Err.Description, vbCritical
Resume exit_Handler

End Function

' End code

In the debug window to test
?SaveUserDefault("TaxRate", .22)

?GetUserDefault("TaxRate")

is a suggestion. I'd do a search on google and see if you can dig up
any discussion on the pro's and con's of using this method vs. tables
vs. custom database properties (not the way to go IMO for user
settings if you are replacing the front end regularly (ie in a
corporate environment and 'trickling' down changes).

Of course you don't need to make wrapper function, just use
SaveSetting and GetSetting directly in your code. Just if you change
the method (ie decide to use tables or some other method) to save|get
later on it's easy to change the code in the wrapper than have to
change all the code embedded in the rest of your code with sometimes
unpredictable results.

Regards,

Peter

David Deacon <da**********@bigpond.com.au> wrote in message news:<40***********************@news.frii.net>...

Thanks Peter
I have got your global setting on TAX working great, i could leave the
gst out of the line total but i cant for the long term vision.
I am going to look at the idea of using your suggestion

If you are going to use a global default for the application that the
use can manipulate i'd save the value in the registry and make a
wrapper function to retrieve the value. However other may have
different suggestions.

Could you give me an idea on how to do this

Regards
DD
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!