Adding fields to an existing subform

[Guest]

H

I have a subform, based on a query that references table TransportCosts. Given the clients desire to view this information as a matrix, the table has a field for Customers (destinations) and all other fields refer to an Origin (of which there are about 10. Therefore 11 columns in this table). The query allows the user to do a search for a specific Customer. Given the nature of the table, when creating the subform I chose to have all fields displayed to the user so when executing this query, the subform displays transport costs from all origins to this customer

Now I pose my problem

It is possible that in the future an Origin (ie. another field) may be added to this table. How can I update the subform to include this new Origin from VBA without recreating the subform from scratch

I look forward to a reply

Thank
Jade

 

[Allen Browne]

After adding the field to the table, open the query in design view, and drag
it from the table into the query grid. Save the query.

Now choose the Forms tab of the Database windows, and open the subform in
design view. Open the Field list (View menu). Drag the new field onto the
Detail section of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a subform, based on a query that references table TransportCosts.

Given the clients desire to view this information as a matrix, the table has
a field for Customers (destinations) and all other fields refer to an Origin
(of which there are about 10. Therefore 11 columns in this table). The query
allows the user to do a search for a specific Customer. Given the nature of
the table, when creating the subform I chose to have all fields displayed to
the user so when executing this query, the subform displays transport costs
from all origins to this customer.

Now I pose my problem:

It is possible that in the future an Origin (ie. another field) may be

added to this table. How can I update the subform to include this new Origin
from VBA without recreating the subform from scratch?

 

[Guest]

Alan,

Thanks for this. Is there any way I can do it in VBA though?

Jade

----- Allen Browne wrote: -----

After adding the field to the table, open the query in design view, and drag
it from the table into the query grid. Save the query.

Now choose the Forms tab of the Database windows, and open the subform in
design view. Open the Field list (View menu). Drag the new field onto the
Detail section of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Given the clients desire to view this information as a matrix, the table has
a field for Customers (destinations) and all other fields refer to an Origin
(of which there are about 10. Therefore 11 columns in this table). The query
allows the user to do a search for a specific Customer. Given the nature of
the table, when creating the subform I chose to have all fields displayed to
the user so when executing this query, the subform displays transport costs
from all origins to this customer.added to this table. How can I update the subform to include this new Origin
from VBA without recreating the subform from scratch?

 

[Allen Browne]

Yes.

You can programmatically add a field to the table with the CreateField()
method.

You can modify the query by setting the SQL property of the QueryDef.

You can modify the form if you open it in Design view (hidden if you wish),
and CreateControl, adjusting the Left and Top properties of the existing
controls (in twips) so the new one fits. You can't do this step in an MDE.

 

[Guest]

Hi Allen

Have taken your advice and have done the following:

New Origin = cmbOrigin

Set fld = CurrentDb.CreateTableDef("Transport").CreateField(NewOrigin, Number)

CurrentDb.CreateTableDef("Transport").Fields.Append fld
CurrentDb.TableDefs.Refresh

I keep getting an error: "Data type conversion error", which appears to be due to my field type being 'Number'. The field type must be Number as the field lists the transport costs from an origin to destination. Do you have any suggestions?

Thanks
Jade

 

[Allen Browne]

Replied to your new thread.