Changing Default Table Value Using VBA/Macro

[tomself1]

Hi,

I'm doing a shop database, and I'm using the default value to store VAT values.

Is there a way to change the default value on a number of tables using VBA code or macros?

Cheers,

Tom.

 

[JamesMcS]

Have a look at this:

http://msdn.microsoft.com/en-us/library/aa195837(office.11).aspx

 

[tomself1]

Oh that's absolutely brilliant!

Thank you

 

[JamesMcS]

No probs, have fun!

 

[vbaInet]

NB: Bear in mind that the default value you're changing in code will only affect the Control's default value and not affect the field's default value in the table. So for everytime you open the form, you must run that code.

 

[tomself1]

Is there a way to change the field's default value straight off then?

 

[vbaInet]

You would use the TableDefs collection to change a field's property. In order to effectively do this you must have exclusive access to that table (i.e. the table must not be in use or open).

If this is going to be a regular occurence, then the TableDefs option wouldn't be suitable.

 

[tomself1]

Exclusive access isn't a problem! VAT only changes every few years.

So something like this?

currentdb.TableDefs("myTable").Fields(0).DefaultValue = "NewDefaultValue"

 

[vbaInet]

VAT you say? You're better off creating a table to handle vat rates.

Yes that's it. However, you would need the double quotes on either side of the NewDefaultValue for it to work. So:


currentdb.TableDefs("myTable").Fields(0).DefaultVa lue = """NewDefaultValue"""

 

[tomself1]

Okay, I'll try that.

And yeah, it's for a shop system. Every transaction has to record the current VAT rate for transaction purposes.

 

[vbaInet]

And yeah, it's for a shop system.

I hope there are SALES going on so I can get some discount!

Good luck!

 

[tomself1]

Ahha, we'll see!

And cheers for your help! Hopefully it should work perfectly