Adding controls dynamically

[msmayhew]

I have a form that has several rows of controls (comboboxes, textboxes, checkboxes, etc)

Each row is exactly the same. There is one row for every 'driver' in the 'SalesDrivers' Table.

Instead of manually adding a row when we add a new driver I would like to form to dynamically create the form based on the drivers in the table.

Any suggestions?

 

[gromit]

Hi -

It certainly would be possible to add controls dynamically.

1. You would need to detect when a new SalesDriver is added. If the drivers are only added via a combo box on this form, then you can detect them with the NotInList event. If the SalesDrivers can be added on another form, then you would need to store the number of drivers somewhere (like in a reference table) and compare the number of drivers set up on the form.

2. Dynamically changing the form would consist of looping through the SalesDrivers and replicating controls. You would need to calculate the correct positions and offsets so that each time through the loop, the new row of controls is located appropriately.

3. One issue that will inevitably arise is expandability. I.e. how many SalesDrivers will you allow room for? Planning would be the key - and good error checking.

4. A few alternate thoughts - You could design a form with a fixed number of rows (say ten) for each of the controls and just toggle whether they are visible. If you have more than ten, you could have a button set up to start scrolling the controls through the SalesDrivers. (Not sure how smoothly that might work... but interesting to try). Also, look at the implementation of the Switchboard Manager, it does something similar to what you are describing.

Another approach might be to design a subform for each sales driver. If you display the form in continuous forms, you might be able to get Access to handle all the dynamic issues. There might be some VBA magic needed to handle the results, but it might be time better invested.

Just thinking out loud. The devil would be in the details...

hth,

- g

 

[Pat Hartman]

Instead of manually adding a row when we add a new driver I would like to form to dynamically create the form based on the drivers in the table.

You are making a spreadsheet not a database application. Your table is not normalized and that is causing the problem with the form. If you fix your tables, adding a new sales driver will not involve changing any objects at all. Tell us what your tables look like now and we'll offer suggestions.

 

[msmayhew]

I don't think I explained the situation well. Let me try again, I'll try to stay brief:

As far as this issue goes, I have to tables: SalesDrivers and SalesDriversData.

The sales drivers table just has two fields. DriverNumber and DriverName. The data table has an ID field (Primary Key), Driver Number, PalletsIn, PalletsOut, CratesIn, CratesOut, etc....

I know, traditionally, the way to update this data table with a form is one record at a time. But the client has requested to have one form with a row of controls for each driver. I have the form set up and it works fine. It has a submit button that executes a SQL statement for each driver (or row of controls).

The problem is adding/removing drivers. Right now it would take forever to add a row of controls and name each control, then add the code to execute the SQL statement.

I want to be able to cycle through the drivers in the SalesDrivers table and add each row dynamically.

I hope this better explains the situation.

Michael

 

[ray147]

It certainly would be possible to add controls dynamically.

2. Dynamically changing the form would consist of looping through the SalesDrivers and replicating controls. You would need to calculate the correct positions and offsets so that each time through the loop, the new row of controls is located appropriately.

hi guys,

i'm doing something very similar...gromit stated above that it's possible to create controls dynamically...i can't figure out how...i've seen the CreateObject method and tried it out, but it seems to work only in design time not at run time...i've thought abt having a standard list of ten and hide/unhide as you go along, but is there a cleaner way?

 

[ray147]

It certainly would be possible to add controls dynamically.

2. Dynamically changing the form would consist of looping through the SalesDrivers and replicating controls. You would need to calculate the correct positions and offsets so that each time through the loop, the new row of controls is located appropriately.

hi guys,

i'm doing something very similar...gromit stated above that it's possible to create controls dynamically...i can't figure out how...i've seen the CreateObject method and tried it out, but it seems to work only in design time not at run time...i've thought abt having a standard list of ten and hide/unhide as you go along, but is there a cleaner way?

what i'm doing is trying to transfer the results from an SQL query into a form output, but i need to include four new blank fields for each row of output for the user to enter and then update a table accordingly.

 

[neileg]

It sounds as if all you need is a continuous form. This has as many rows as there are records to display. Your headings will relate to each column in the form and the first field(s) in the row would identify the driver.

If there are more rows than space on the form then you simply scroll up and down the list.