Is it possible to create new input objects on a form during runtime?

[JGalletta]

I have the following form:

Is it possible to have the form duplicate each data entry object when the + button is clicked at the bottom right of each 'subform' (not actually subforms)?

I have some data entry that I'd like to manage in this manner. I'd like to be able to make a flexible entry form for this data and hopefully, I can get to the point where clicking either of the + buttons will add another "record" for each "subform." This seems like its going to be code-heavy, but I am prepared and have the time, if this is possible. All I need to know is whether it is possible and how to do it.

If you have some sort of other profound method of accomplishing what I'm trying to do, please feel free to share. Hopefully you can imagine the details of what I'm trying to accomplish. Perhaps its a design flaw? Someone please help! It's been a year since my last project and I'm a little rusty.

 

[JGalletta]

Can anyone help me with this?

 

[JHB]

To add controls to a form at runtime, you've to put the form in design view.

 

[Isskint]

You can hide controls and make them visible at run time.

 

[JGalletta]

To add controls to a form at runtime, you've to put the form in design view.

What is the method for adding controls, positioning, naming, etc.? This is the response I was looking for, btw.

 

[JHB]

In the attached pdf-file, have I made a print from the Help-File for CreateControl.
It has all the answer you were asking for.

And here one example:

[B]CreateControl,  CreateReportControl Methods Example[/B]
 The following example first creates a new form based on an Orders  table. It then uses the [B]CreateControl[/B] method to create a text box control  and an attached label control on the form.
Sub NewControls()
    Dim frm As Form
    Dim ctlLabel As Control, ctlText As Control
    Dim intDataX As Integer, intDataY As Integer
    Dim intLabelX As Integer, intLabelY As Integer

    ' Create new form with Orders table as its record source.
    Set frm = CreateForm
    frm.RecordSource = "Orders"
    ' Set positioning values for new controls.
    intLabelX = 100
    intLabelY = 100
    intDataX = 1000
    intDataY = 100
    ' Create unbound default-size text box in detail section.
    Set ctlText = [B]CreateControl([/B]frm.Name, acTextBox, , "", "", _
        intDataX, intDataY[B])[/B]
    ' Create child label control for text box.
    Set ctlLabel = [B]CreateControl([/B]frm.Name, acLabel, , _
         ctlText.Name, "NewLabel", intLabelX, intLabelY[B])[/B]
    ' Restore form.
    DoCmd.Restore
End Sub

 

[JGalletta]

Thanks a bunch!

 

[mdlueck]

Excellent pointer, JHB.

I will point out from the MS Access help file for that function:

You can use the CreateControl and CreateReportControl methods only in form Design view or report Design view , respectively.

Also I will point out that I have read that Access controls are disposable but their slots come out of a fixed pool size and are unrecoverable. See the post here...

Create Check Boxes/Option buttons/Option Group using VBA (MaxBPs issue in Access)
http://www.access-programmers.co.uk/forums/showthread.php?p=1121359

 

[JGalletta]

Excellent pointer, JHB.

I will point out from the MS Access help file for that function:

Also I will point out that I have read that Access controls are disposable but their slots come out of a fixed pool size and are unrecoverable. See the post here...

Create Check Boxes/Option buttons/Option Group using VBA (MaxBPs issue in Access)
http://www.access-programmers.co.uk/forums/showthread.php?p=1121359

Is it possible to reuse the same additional controls each time - retrieving them from their slot in the fixed pool? Or, is that what you mean by unrecoverable -- they cannot be retrieved? Thereby, disposable as opposed to recyclable?

I know I will not have 754 controls on the form - does this "counter"/"pool" get reset for each instance of the form? Does this mean the form has to be created/deleted each time it is opened/closed?

.... am I understanding this properly?

 

[mdlueck]

You may hide controls you do not need at certain times. That will not waste control slots.

Creating / Deleting controls will chew through control slots.

I am quite sure this is a per-Form pool.

For one part of the application I at run-time rename bunches of label controls to morph a certain named portion of the application over to another name. This process also involves defining a custom SQL to populate the Multiple-Items form with in the area of the application. I do not need to bring the form into Design mode to accomplish this.

So if you could statically place all of the controls you will ever need, name them all, then you could change their visibility / active state, and thus avoid wasting control slots available.

 

[JGalletta]

Ok, great. This seems like the route for me; great explanation. Looks like it's back to the salt mine for me!

 

[JHB]

Thank you for the fine words, mdlueck.

In my first replay I pointed it out:

To add controls to a form at runtime, you've to put the form in design view.

One way I bypassed it at once, was to have a "Master" form which I copied and created the desired controls at this copy.
Another way it can be made if they wish to retain the established control elements is to copy the form - then the pool resets again.
Both solution through code ofcause.

 

[JGalletta]

Good stuff. Thanks guys.

 

[Pat Hartman]

If you must open any object in design view, you will NOT be able to distribute the application as an .mde or .accde and you will NOT be able to use the Access Runtime engine to run it. You will always need to distribute an .mdb or .accdb and the user will always need the full retail version of Access installed to run your application. And besides, this is not the Access way. If you use a continuous subform, you won't need code at all. Using the tools Access gives you for rapid application development will result in less code, less testing, more stability, etc. Subforms are "infinately" expandable. That is the technique you need to employ.

 

[tfurnivall]

One thing is not quite clear form the 'specifications' that you gave. (Well, two things, actually).

First - you seem to have a bunch of scouts who are going into a bunch of fields to find a bunch of pests, and then create a bunch of notes about what they've done.
In other words we have:

Scout => Field => Pest = > Note

What is not clear is if the Field and Pest information are tightly tied together. Instinct says that Fields and Pests are quite separate - any given Pest can be in any number of fields. IF this is the case, then I think I would have expected to see another "+" to the right of "Search Sample". This means that you work with a Scount by starting with one field, and when you are done with that field you move on to the next.
You deal with a field by starting with one pest, and when you are done with that pest you move on to the next.
You deal with a pest by starting with the hard data, and then starting with one note ad then move on to the next.

IF the Fields and Pests are indeed bound together, then your form makes a bit more sense.

Second, buried away almost invisible, in your description is the use of the word Record. I'm wondering if you want to see a procession of Fields, or Pests or Notes marching down the screen (in which case a lot of the very esoteric description of memory management is very useful), or whether pressing a "+" simply clears the screen fields at the appropriate level (and presumably those below it) so that you can add a new Record/Entry to an underlying table.

It looks like the responses you have already have been helpful, and I'm hesitant to re-open the issue, but I'd surely love to know the answers to my questions!

Happy Holidays,

Tony

 

[JGalletta]

Hey Tony, Gladly!

(I will be using the terms 'farm-field' and 'entry-field' to differentiate.)

1. Scouts go into => Farm-fields performing either a beating tray search or a sample search for => Pests

Our IPM manager wants the notes entry-field to be relevant to each farm-field as opposed to either the pest or the date. i.e. if a scout finds an unusual condition, the scout would record these conditions by farm-field and enter them in the Notes entry-field.

The "+" in the light blue rectangle will add another pest found in the particular field.
The "+" in the dark blue rectangle will add another farm-field to the scouts entry, another drop down for farm-field will appear, time in and time out, search method (beating tray, search sample, or both) pest entry form, and notes for the new field. So, to answer your second question partially, I'd like to see a procession of farm-fields down the screen, but each farm-field would have the light blue pests sub-form within its dark blue bounds.

I see how this can be a little confusing, but it is laid out much like the notebooks they take into the farm-fields, so they'd understand it.

The 'submit record' button would then enter the data into the underlying tables iff I cannot do this with prebuilt access subform type design.

Please, If you think I'm doing this wrong, or have something that would save me a ton of headaches, don't feel like you'll hurt my feelings. This is only my second DB, so there's no inflated ego-bubble to burst here.

I have a second form with a subform within one of its subforms in an attempt to do it the easy way (have access worry about binding controls to tables), but it doesn't allow to have continuous form view with a subform form that contains another subform. That is what I'd ultimately like to accomplish, but if I can't, that's fine.. but what do others normally do when this is their goal?

Sorry if I'm not being clear -- I'm a bit tired and it's been a year since I last touched access. Hope this helps!

 

[tfurnivall]

Thanks for your response.

If I understand you now, the 'relationships' are more like this:

Scout => Field =>Pest
                    ||
                    || => Note

(We can have more than one pest entry for a field, and more than one Note entry for a field, and there is NO RELATIONSHIP between the Pests and the Notes)

I'm intrigued why you want to have the procession of Pests down the screen. From what you've described, all you really need is to be able to create a set of entries where a Pest is related to a Field, and an independent set of entries where a Note is related to a Field.

Specifically, unless you have been specifically instructed to create this strange sort of hybrid visual representation, I don't see why you need to keep one Pest around while you work on another. (From experience of being a father, I find that you can substitute the word "child" for "pest"in that previous sentence without changing the meaning too much, or without changing the thinking behind it).

Indeed - I'll go further and say that even if you have been told that it must be done that way, you should be asking what sort of benefit will accrue to doing it that way. Remember we are simply gathering data - not reporting it out!

I'm imagining a table called ScoutActivity which has the Date, the ScoutID, the Field ID and a PestID; then separate tables for Scouts, Fields and Pests (which presumably are predefined, right?) and then a table for Notes (identified by date, scout and field), which is really the data that you are gathering. OK - there is some other data about the pest that the scout found in the field on that date, which would also be in that ScoutActivity table. But everything else is in separate tables:

[U]ScoutTable[/U]
ScoutID
ScoutName
[I]other data about the scout

[/I][U]FieldTable[/U]FieldID
FieldLocation
FieldSize
LastDateFieldWasUsedForAScoutingExercise
[I]and other data about the field

[/I][U]PestTable
[/U]PestID
PestName
PestSpecies
LastDatePestWasSpotted[I]Other data about the pest

[/I][U]ScoutingActivityTable
[/U]Date
ScoutID
FieldID
PestID
PestCount
PestCountingMethod
[I]and other data that you may want to record[/I]

[U]Notes
[/U]Date
ScoutID
FieldID
NoteText

Notice we've done nothing at all about Forms, but we have (I think) pretty well defined the way you want your data to be laid out. Now let's suppose that someone says "But of course the note needs to reflect the pest that was involved".

We simply modify the Note table:

[U]Notes
[/U]Date
ScoutID
FieldID
PestID
NoteText
[

and we now have that extra relationship between the Pest and the Note defined.

It's only at this point that you should even begin to think about what your form will look like.

True confessions time - I can't find that form example, so I'll leave you to think about this, which is now a pretty common sort of problem - the sort that Access is well-equipped to deal with, and one that does not seem to require any exotic techniques at all to deal with the problem of entering data.

Tony

 

[JGalletta]

These are my relationships:

One note per field in the same table. I think your missing something -- clicking the bottom right "+" button on the dark blue area duplicates everything in the dark blue area (i.e. a new record in tblFieldRecJoin and therefore a new list of pests as a new set of records in tblFieldRecInfo) -- not just the notes, and all while keeping this data linked to the original record in tblScoutingRecords. And, the notes are about the fields, not the pests.

 

[JGalletta]

This whole design works - functionally and hierarchically. The only thing I don't like about it is that the subform and the subform within that subform cannot be viewed as continuous forms, so the data entry person(s) would not be able to see all of their entries at once to double check their work, prevent duplicates or add missing records. I'm designing this for people who have no knowledge of access and its record navigation, so instead of forcing them to navigate to make multiple entries, I'd rather have it all up on the screen so they don't get lost in the program. Chances are they wouldn't be able to keep track of what gets entered and which field it's applied to with single view subforms, because of the break in the workflow created by navigation. I don't mean to demean my users, but that's the nature of the beast; I'd like this to be bulletproof.

Here's a view of the form with all navigation buttons visible, if this helps you get a better grasp on my concept:

 

[Pat Hartman]

If you want the subforms to be in continuous or datasheet view, you will need to make them equal level rather than dependent on each other. tblFieldRecJoin data will be shown on one continuous subform and tblFieldRecInfo will be shown in one to the right of it. In the Current event of the first subform, you would requery the second subform to sync it. The RecordSource query of the tblFieldRecInfo subform will reference the Record Number of the first subform for criteria.
Select ...
From ...
Where [Record Number] = Forms!mainform!sfrm1.Form![Record Number]

Now for the problems.
1. Column names should not contain embedded spaces or special characters.
2. The relationship between scouts nd scouting records should be on ScoutID (an autonumber field) rather than a name field.
3. Enforce RI should be checked for all relationships.
4. tblFieldRecJoin should have an autonumber PK and that PK should be used in tblFieldRecInfo rather than the two fields that are there now. Although it isn't required that you use simple autonumbers for primary keys, you will find it easier creating queries and using tables in combos if you do. If you switch to an autonumber PK for this table, you will need to add a compound unique index to enforce the business rule that the combination of Record Number and Field Number be unique.
5. tblFieldRecInfo needs a PK. Again, Access is too flexibile in this regard. good practice dictates that every table have a unique PK.
6. Although I can't say for certain that weather conditions and temperature are in the wrong table, I can remind you that they are variable during the day so if readings are taken at different times, those two variables may need to be different so they probably should be moved to tblFieldRecJoin.