Inserting a column into a table using VBA?

[Sniper-BoOyA-]

Good Afternoon,

I was wondering if it was possible to insert a column into the table which is the source of the form im working on, using VBA?

The idea is to save the value of a textbox, and use it as defaultvalue of the column.

So that the user only has to enter the value once..

Any thoughts?

 

[DCrake]

When you say Column do you mean Field? If so, I would shy away from doing this. What is the logic of letting users add fields to tables willy nilly.

 

[Sniper-BoOyA-]

Good point.

Its jut that,

When i first made the database, the user had to enter the Diameter for every sample. (samplenr is an autonumbering field)

Which works, but the Diameter is the same for every sample. So if lets say the diameter is 150,7, and i have 10 samples.

Then i'd have to enter 150,7 x 10 times.

Due to the fact that the diameter is not always 150,7 i can not set the default value of the field to 150,7.

I solved it partially by adding a textbox on the form. And i made the query refer to the textbox on the form. So i could use it in formulas.

All this works great, as long as the form is active. If the form is closed, or you close the database and reopen it, the value of the textbox is gone.

So up to this point, i am doing a little brainstorming what the best idea would be to save the value of the textbox.

Apperently, inserting a field/column into the table is bad idea.

Back to the drawing board

 

[CBrighton]

Create a lookup table with the defaults diameter for each sample type. Then set the default value of the control to a DLookup e.g. DLookup("DefaultDiameter","tblSampleDefaults","SampleType = '" & txtSampleType).

This assuming there are multiple types of sample, each with a different default diameter. It also assumes that a control named txtSampleType is on the form and populated with which type of sample it is.

:edit:

Better yet, add an control (on txtSampleType, which may be better as a combobox to be honest) after update / change event to set the value of the diameter to the DLookup value.

This will mean that the VBA is called to get the default diameter each time the sample type is changed.

 

[Sniper-BoOyA-]

cheers. Worked like a charm