Setting table field default property in VBA

[David Thorpe]

Can anybody advise me how to go about setting the default property of a (text) field in a table from VBA?

A database I have been using for years to run a local charity is now to be used by a group of other similar charities in the area. I have added a table that will hold details of each new charity and one of its text fields is "Charity Name".

My aim is that on using the database for the first time, each new charity will input its Name (via an appropriate Form) and that name will become the default value of a similar text field in another table.

Can this be done and, if so, how?

[Uncle Gizmo]

>>> Can anybody advise me how to go about setting the default property of a (text) field in a table from VBA? <<<<

That is the wrong way to go about it! You should not be changing the design of your database on the fly to accommodate your users. You should be able to find a way to do this without going to these extremes.

As I have said before, you are of course entitled to design your database in any way you see fit, however you will eventually run into problems that have not been solved before, and you may find it difficult to solve them yourself.

However if you follow the basic design guidelines for databases, then most of the problems you run into will have already been solved.

>>> will become the default value of a similar text field in another table <<<
Why?
I am not sure exactly what needs to be done to your database from your question, please could you provide more information.

[David Thorpe]

Had I been starting from scratch, your comments would be perfectly valid. However, a database that I have developed for my sole use (over the past 6 years) is now desired by a number of other users. I am very happy to give it to them for free, but they need to be able to change some key parameters to reflect their particular requirements. I don't want the chore of going back to square one and developing the thing all over again for multiple users!

The key parameters are (1) Scheme Name (text that appears as a heading on all screens (Forms) and reports and (2) Rate (an integer that is used as the basis for calculating various costs).

I have modified the database to include a PARAMETERS table that users will input (via a set-up form) on first use and have arranged things so that the table will never hold more than a single record. I thought it would be a simple matter to transfer this inititial information as defaults to identical fields in the main records table.

From your comments I guess two questions arise. (1) can the method I have chosen be made to work (if so, how) and (2) is there is a better way of doing it (if so, what is the method).

I'm not expecting anybody to spend oodles of time doing the work for me - but a bit of guidance to point me in the right direction would be greatly appreciated.

[Uncle Gizmo]

Well I think the Irish have a saying "if I was going there, I wouldn't start from here" which causes much amusement to people when being given directions by an Irish person, however when you think about it it is a very sensible idea.

So my instinct is to say, the same as the Irish giving directions, I wouldn't start from here, but then you are going to ask where to start from? And I don't think I can answer that question either.

Your parameters table, from what you have said, I think you are creating a flat table with a parameter in each field. You would be much better off creating a two column table with the names of the parameters in one column and the parameter value in the Adjacent column. This would be the correct way to do it.

[seth_belgium]

Create a new form based on your Parameters-table. Lets call it frmParams.

The form that contains the header and the integer-value fields, we'll call that one frmScheme.

In the OnLoad() property of frmScheme, use this code:

DoCmd.OpenForm "frmParams"
Me.txtHeader.Value = [Forms]![frmParams]![txtHeader].Value
Me.txtInteger.Value = [Forms]![frmParams]![txtInteger].Value
DoCmd.Close acForm, "frmParams", False

I know it's not fancy programming, but it works fine for me...

PS : If the 'header' of your form is a label-control and not a textbox, use Me.lblHeader.Caption = [Forms]...

Hope it works!

Greetz,

Seth