Build a form on the fly. 13

[dewildpup]

Hi Guys I have a table where columns are added on the fly and I would like to create a for on the fly which will then be used to add information to this table.








regards

LeVere

 

[kjv1611]

Could you expand a little on what you are saying? I know you cannot add a form to a table, but you could add a form name to a table, for instance.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dewildpup]

Ok!!!!!!!!

I am writing a payroll program and by nature some earnings are subject to certain deductions. Now here is what I have done. The user creates the deduction code and a three (3) digit for its short name. That short name when create is added as a column to a table. This table is link to a form but when the form is open again it will not contains the new column which was added to the table using vb code. So I wanted to write code which will generate a new form on the fly base on the columns in the table.

Hope this explains it better.

regards

Levere

 

[kjv1611]

Are you sure you won't reach the limit of how many columns can be added?

Are you positive you're not talking about rows instead of columns (records)?

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

I think you should consider the design of your tables. For example:

[tt]Table Wages
PayKey

Table Deductions
PayKey
DeductionCode
Amount

Table Deduction Codes
DeductionCode
Description[/tt]

 

[fneily]

Still not clear. Also, that's not how you use Access - creating columns on the fly. Why can't you have a deductions table with the codes?
A form doesn't automatically create textboxes for new fields in a bound table.
Please supply your table names, field names and what you want to do.
You must NORMALIZE your tables first, then create the RELATIONSHIPS between them.
Or maybe someone else out here understands what you're trying to do.

 

[dewildpup]

Listen Guys I am not going crazy.

Consider the following

Salary: is subject to NIS deduction and other deductions.
I have a table called tblEarnings which contains the earnings code and description. In am attempt to make it easier for the user, when the user adds a new deduction code to the deduction table he has to state wether the system computes the deduction or not if the answer is yes. The short name for that deduction is added as a column to the earnings table. In the earnings table the user can put a Y or N in the square where the Earnings code and the Short Name intercept.

Before the payroll is run a matrix table is build (Normalize table) having the earning code and the deduction code (the code associated with the short name not the name itself) as primary keys.

EarCode EarName NIS PAYE PEN
100 Salarie Y Y Y
105 Travel N N N


Rgeards

LeVere

 

[kjv1611]

Where does the "new column" come into all of this?

--

"If to err is human, then I must be some kind of human!" -Me

 

[dewildpup]

Let say you add a new code in the deduction table call taxes on car with a short name TOC. Then the earnings table would now look like this. Let say the code associated with TOC is 035 in the deduction table.

EarCode EarName NIS PAYE PEN TOC
100 Salarie Y Y Y Y
105 Travel N N N N

Then where the column is TOC and there is a Y i can build my table as follows:

EarCode DedCode
100 035

That is woking fine what I need is code to add the new field TOC to the form which is used to update the earnings table.


Regards

LeVere

 

[fneily]

If this is your tblEarnings -
EarCode EarName NIS PAYE PEN
100 Salarie Y Y Y
105 Travel N N N

then this IS NOT NORMALIZED! It violates the first Normal form - you have repetitive column headings (deduction deduction deduction etc.).
Mr. Remou has shown the correct way to set up a JUNCTION table for your database.
The first Normal form states you CAN NOT have variable length records which in your case you would have.
That is WHY you are having form problems.

 

[dewildpup]

OK Big Guy!!!!!!!!!!!!!!.

The Normalized table is built on the fly. Don't any one uderstand that. Ok lets go back to Mr. Remou example

Table Wages
PayKey

Table Deductions
PayKey
DeductionCode
Amount

Table Deduction Codes
DeductionCode
Description

Now in his first table wages(tblearnings) is the code for each earnings and its description fine. Now this means that if a user sets up a new earnings code he would then have to either go to another form and select the deduction which that earnings (wages) is subject to.

What i wanted was for him to do it there and then simply by placing a Y or N in the appropriate column. Then I was able to build Mr. Remou table (Table Deductions)on the fly. So yes I have acheive the three tables that Mr. Remou indicates.

I see two problems (1) I need to add a column to a table on the fly that is currently being done and the other is to add a new label and a field to the form on the fly. It can be done.

Now do you guys always listen to the teacher after all he was a pupil too. So stop thinking in the box. Here is how it should work.

1. Get all the field names from the appropriate table.
2. For each name assign a label and a text box to the form using VB code certainly this is possible.

Now who is willing to take the challenge and help me acheive this. Believe me it will be done!!!!!!!!!!


LeVere

 

[JoeAtWork]

The Normalized table is built on the fly.

That's actually an oxy-moron. If in the process of changing your data you need to change the schema of your database, that by definition is non-normalized. I.e. "data entry" should not require changes in database design.

The fields you want to "add on the fly" are actually data. It's data because it's entered by your users.

Might I suggest a design that I think will work? It's only a variation form Remou's original reply.

EarnCodes
EarCode
EarName

Deductions
EarCode---------|-- these two fields are the primary key
DeductionID-----|
Apply (boolean)

DeductionCodes
DeductionID
DeductionCode
Description

So you tables with data would be:

EarCode EarName
100 Salary
105 Travel

DeductionID DeductionCode Description
035 TOC Taxes on Car

EarCode DeductionID Apply
100 035 Y
105 035 N

One thing you need to do is when the user creates a new DeductionCode record, you need to add one new Deduction record for each existing EarningCode (conversely you would need to delete related Deductions records if a DeductionCode is deleted).

One of the nice things about using this design is you don't have to create controls on the fly - an ordinary bound form will do it for you. I can envision having a main form for the Earning Codes, and a subform for the Deductions records. Since "Apply" is a boolean field, Access would automatically render a checkbox for you (but if you really want the user to pick "Yes" or "No" you could also make a dropdown). If the user creates (or deletes) a deduction on the fly, you would need to requery your subform.

If you really want your deduction codes to appear like columns you could probably do that with a pivot table.

Regarding "thinking inside the box", encouraging you to follow proven best practices does not mean we are lacking in imagination. I know exactly what you are trying to achieve, and why, and I could tell you how to do it, and it would even work. But it would be a poorer, less flexible design than the relational/normalized schema we are trying to encourage you to adopt.

 

[Remou]

That is a very neat explanation JoeAtWork. It deserves a star.

 

[TheAceMan1]

JoeAtWork . . .

[blue]Excellent![/blue] . . . [blue]Poetry In Motion![/blue] . . . Have another [purple]pinky [/purple]

See Ya! . . . . . .

 

[kjv1611]

JoeAtWork,

Great work, showing much thought and patience, me thinks.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dewildpup]

Hi JoeAtWork,

Thank you.

Regards


LeVere