Solved

Programmatically create a form using VBA

Posted on 2003-11-10
31
1,479 Views
Last Modified: 2008-03-06
Hi,

I'm using MS Access 2000, and I need to a form that consists of dynamic objects (i.e., the objects (text boxes) in the form are dependent upon data, and may need to change)

Example- The form is illustrating the client's current currency positions. I may need more text boxes in the form if its recordsource increases that types of currency the client possesses. Thus, it the client decides to increase the types of currency's he/she trades, I need the form to dynamically create a new field illustrating that currency.

Using VBA, is there a way to programmatically create a form, so it can store dynamic objects/content?

Please let me know if I have not explained this in great enough detail

I'd appreciate any help at all,

Thanks
0
Comment
Question by:nickg96
  • 15
  • 13
  • +2
31 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 9714954
How are you storing this data?  This does not look good.  Why do you not create a table (tblCustomerCurrency) and use a sub-form based on this table?

Richard
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9715023
Hey nickg96!

 It sounds like you need to be applying this "dynamic" aspect to the data tables rather than the form.  The process you are describing should be stored in a table set the makes the client one table and the currencies they trade a child table with one row for each currency type.

 Currency type traded as seen from the perspective of the overall system should be store in a reference table as "validation" data. to assure you don't end up with 'unknown' currency types related to client records.

 So while it is possible to use VBA to create, place control on, adjust, tweek, bend, fold, spindle and the dreaded "Mutilate", a form!
 I don't think this is where you will want to go after you find out how long its going to take to get all that code in order...
 It's far better and easier to get your data sources properly normalized before designing "Wonder Toys"

regards
Jack
0
 

Author Comment

by:nickg96
ID: 9715258
Jack and Richard,

Thanks, but let me explain in more detail

"This does not look good.  Why do you not create a table (tblCustomerCurrency) and use a sub-form based on this table?"

"The process you are describing should be stored in a table set the makes the client one table and the currencies they trade a child table with one row for each currency type."

---I do have separate tables for the clients, the data and the ccys.
The data stored in these tables are systematically downloaded every morning from a separate web based application (obviously pulling data from it's backend DB), stored as .csv files, then uploaded into my database via VBA code I have written.

This db app I am creating will be replacing the current excel based app. The excel based app is overtaxed, outdated and difficult to upgrade/improve.

I need a dynamic form because the client that this db app will be servicing may trade different types of ccys further down the road. Thus, if I only include 5 ccys in this form, but in 3 months the client is trading 3 new ccys, I'll find myself in a boat without a paddle.

The user of this app (not myself) will have no prior knowledge regarding the client trading new ccys. So, he/she will not know that the client is trading new ccys until the morning (when the .csv files are uploaded into the db (our side), and the user receives copies of the clients own daily statement version (clients side))

Basically, this db will be used for balancing. It will be responsible for the reconciliation of data between our side (our records) and the client's side (client records).

Regarding the form- I can't use a list box b/c each ccy needs to have its own dedicated object (field) so adjustments can be allocated. So if we have USD @ 100, but it needs to be @ 250, I need to be able to adjust the USD by +150
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9715343
Excel was NEVER the method that should have been used for this application.  It was fated to be overtaxed from the beginning.

nickg96 said:
"Thus, if I only include 5 ccys in this form, but in 3 months the client is trading 3 new ccys, I'll find myself in a boat without a paddle."

This is EXACTLY why you need the database table schema to be dynamic and NOT the form.  One child table of the Clients table will be the currencies that client trades.  If they trade one today, two tomorrow, three on Friday, the child table will be flexible enough to manage this expansion and contraction.  A subform of the main client form can be used to display and manipulate WHATEVER number of currencies are in effect AT THE MOMENT for that client.  And this is what will make your form dynamic enough to cover all the bases.  1 currency or 100, the subform can be used to cover it.

If you are insisting that the form needs to carry the burden of this dynamic reconfiguration,... I guess I'm not the Expert for this question.  And you should give your client notice that it will take a little longer to put this operation together.

0
 

Author Comment

by:nickg96
ID: 9715355
FYI- i'll be in a meeting for an hour or so. Will be back to check replies then

Thanks,
0
 

Author Comment

by:nickg96
ID: 9715504
Thanks for the reply Jadedata.

I posponed my meeting for a few so I could reply.

I don't think I'm doing a good job of explaining myself.

"This is EXACTLY why you need the database table schema to be dynamic and NOT the form."

The table schema of the database IS dynamic.

"If you are insisting that the form needs to carry the burden of this dynamic reconfiguration"

I'm not well enough educated (on this topic) to "insist" anything. Do you think I WANT to spend the extra (god knows how many hours) to create a form capable of dynamic reconfiguration?

Here is my problem.

I realize that the child table can house EVERY single ccy available in the world (180ish I believe). And it does!

The problem is I don't want to list all 180 ccys in the form. I only want to list the ccys the client is currently trading.

The datasource for this form is sourced from multiple data tables. There are 5 tables (not including client and ccys) that contain data pertinent to the reconciliation process.

I'm pretty sure that: "I can't use a list box" & or a subform based on a query that pulls the data together from the various tables & "b/c each ccy needs to have its own dedicated object (field) so adjustments can be allocated. So if we have USD @ 100, but it needs to be @ 250, I need to be able to adjust the USD by +150"

Please, don't take my response the wrong way. I would love NOTHING more than to be TOTALLY wrong about this. Trust me, I want to make this as easy as possible.

Thanks






0
 
LVL 32

Expert Comment

by:jadedata
ID: 9715585
You need to read up on how subforms work and consider how best to apply this to your issues.  The Master/Child link properties of a subform allow you to limit the display of underlying data to that specific to a current client record without even "pre-filtering" the data before it's given to the subform for presentation.

Each Currency type for that Client (i presume) will have certain other aspects or properties, BUT all currency types will always have certain elements in common
 
  From        To        Conv      FromAmt     ToAmt     EffDateTime
  USDol        Euro     1.21      1.00                1.21    1/21/03 08:00AM
  USDol        Ruble    100       1.00            100.00    1/21/03 08:00AM

re:  "Do you think I WANT to spend the extra (god knows how many hours) to create a form capable of dynamic reconfiguration?"
It's been my experience that some actually DO want to spend the time.  I generally find that I would be wasting my time helping them.
I'm extremely glad we cleared that up.

  To best help you, give me all those examples of those things you DON'T think tables will help control, that lead you to believe that form controls might be your answer.




0
 

Author Comment

by:nickg96
ID: 9715937
It's been my experience that some actually DO want to spend the time.  I generally find that I would be wasting my time helping them.
I'm extremely glad we cleared that up

Why do you have such an attitude?

"If you are insisting that the form needs to carry the burden of this dynamic reconfiguration"

You do realize you wrote the above, correct?

First you call implementing the dynamic reconfiguration form a "burden". And you described my perceived need of it as being "insistent"

I never said I didn't want to spend the time. What I said was I don't want to spend "extra" time- thus wasting my time. And the reason why I said that is you led me to believe (via the above statement) that the dynamic reconfiguration form was not needed.


Listen, I don't want to use this forum to flame, it's ridiculous. I'm just looking for help. That's all. If you don't want to offer that then don't post to the forum.

Also, you didn't address any of my issues. I know how subforms work. I currently have them in the application. It uses the Master/Child link to filter on the account (client) selected.

The subform (on the child side) is based on a qry of the various tables housing the pertinent data. The reason why is based on a qry is because of the data needed is not housed in a single table

The subform obviously lists the desired ccy and balance info based on the account number selected. The problem is being able to adjust the balances of these ccys (as I've mentioned before). How do I do that when the subform is based on a query? The recordset is not updateable. And I can't just add text boxes in the forms (used to enter the adjustment number) b/c I will not know how many ccys will exist for that client.

Also, and I should have mentioned this before (sorry) there is no need for historical data in this db application. Table data will be over written daily. So, I don't need the adjustments and such to be stored.  I just need to adjust the ccy balance so I can rec it to the client's side

Thanks


0
 
LVL 32

Expert Comment

by:jadedata
ID: 9716108
I have that attitude because after 20+ years of programming and teaching, I have discovered that you can't push a rope.  I was not flaming or trying to be unhelpful.  I just want to make absolutely certain that the process you are writing to is going to be flexible enough to handle any future process changes that will eventually happen.  No application is static.  Once a user figures out how much magic you can do with the initial system requirement, THEY WILL be asking for more.  If I give you a solution that is going to prevent that future change, or "paint you into a corner" on future development, then I'm not a very good advisor, am I?

You might notice that Richard also "missed" the part of the question that would have led us both to understand that you are already at the level where subform's have become a benficial part of your applications.  The Experts have no way of knowing these things unless stated.

How exactly does this balancing act take place?  (so far nothing has been mentioned regarding this process...)
If you include addtional fields in the tables for adjustments, will these provide the query with the necessary "extra space" to work out the balances you need?
0
 
LVL 4

Expert Comment

by:inox
ID: 9716194

I'm afraid I can't understand the entire problem, but if you just want to create a dynamic form try (i.e.to edit a subset of a wide table, wide means many,many columns):


'--------------------------------------------------
Private Sub Command4_Click()

Dim ctlText As Control
Dim frm As Form
Dim Rs As DAO.Recordset
Dim I%
Dim Yc%
Dim Sql$
 
  Sql = "SELECT I1,I3,I5 FROM Table1"
 
  Set frm = CreateForm
  Set Rs = CurrentDb.OpenRecordset(Sql)
  Yc = 100
  For I = 0 To Rs.Fields.Count - 1
    Set ctlText = CreateControl(frm.Name, acTextBox, , "", "", 100, Yc)
    ctlText.ControlSource = Rs.Fields(I).Name
    Yc = Yc + 400
  Next
  frm.RecordSource = Sql
  DoCmd.OpenForm frm.Name
 
End Sub

'--------------------------------------------------

PS. this is just a concept it's not ready to use, just for 1. step

0
 

Author Comment

by:nickg96
ID: 9716246
thanks for the reply Jade. good point. Sorry i jumped to conclusions. My fault

Honestly, looking back, I don't think I was very clear w/ my explainations.

I think i'd be best if I explained the entire process/requirement from the start. It's not goign to be extremely long, but let me take my time so i can be thurough. I'll post it in a few minutes.

Thanks,

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9716315
That will be invaluable to our quest... :)
0
 
LVL 3

Expert Comment

by:mccredb
ID: 9716440
nickg96

It might help if you post some details regarding your separate tables for the clients, the data and the ccys, particularly the data table.

Subforms in datasheet view does appear to be the most appropriate method to present/maintain adjustment data in your case.  

I have built several Access applications whose data was dropped and refilled from importing large flat text files.  However I always imported the files into temporary table, then extracted the relevant data into a series of the tables, which allowed me to represent the data without changing the frontend structure.  

0
 

Author Comment

by:nickg96
ID: 9716935
New Explanation:

Hopefully this will better explain what I need. I will explain it from the beginning, please assume the only work I have done on this project is the creation of data tables (mentioned below). I think starting from scratch will be the best approach (at least for me).

I am using MS Access 2000. I need to create a db that will be used for reconciling the difference in Margin Movement Balance for each ccy the client is trading. The reconciliation is between our records and the client's records. The client maintains various accounts with us (50ish). Each one of those accounts needs to be reconciled on an individual account basis.

This MS Access application will be replacing the current Ms Excel based application. I did not develop the original excel based application

No data entry is required. The data is sourced from .csv files that are downloaded from a separate database.

 I created blank data tables. The table format (fields, field types) is modeled after the .csv files. Then those data tables are populated with the .csv files on a daily basis via VBA script.

There are 5 .csv files. Thus there are 5 tables in MS Access:

tblaccounts
tblbalances
tblbasebalances
tblcash
tblfxrates

The (single) user for this application resides within my company. He/She needs to take the clients statements (sent to us via email from client) and reconcile them against our records. He/She needs to balance to the penny. If there are differences between the two sets of data (Us vs. Client) we need to be able to make adjustments to our data.

Example:
Client knows $100 USD Margin Movement Balance
We know $250 USD Margin Movement Balance
Client proves that their figure is correct
We need to decrease our $250 by $150 so we balance with the client ($250-$150=$100)

The Margin Movement Balance totals above (Our side) result from calculations, they do not reside in a single data table. The data used in the calculations resides in various fields within the MS Access data tables (tblaccounts, tblbalances, tblbasebalances, tblcash, tblfxrates).

The data will be uploaded on a daily basis. No historical data will be kept. All data in all tables will be deleted and repopulated with new data on a daily basis.

The user (Our side) would like to use a single MS Access form to handle all reconciliation activities. She would like to type in any needed adjustments right on the MS Access Form. That way He/She can compare the MS Access form directly to the client statement.

Please let me know if you need any other information. I have just been assigned another job that has a higher priority (but it much easier) than this MS Access database project. I will be checking back with this forum in a few hours.

Thanks for ALL of your help. I greatly appreciate it.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9716989
Create a template database which has the fields from the csv, PLUS those fields needed to hold data entered or computed by the folk doing the balance checking.

When a new set of csv's is received, copy the template MDB to a new mdb under a new name. (perhaps including source name/date)  Import the new data into the newly created copy of the template.

Design your forms to include these extra fields as part of the original and permanent design.

To do a balance job on a set of csv's recieved, link to the mdb that has that data in it and the forms should always work without modification.

The second benefit to this is that you can retain the BALANCED data for a period of your choosing, AND relink to it at any time to do rechecks or as a reminder or proof of correct-ness.

This whole process leg can be replicated indefinitely if you use some good naming conventions, and all that data (if desired) can be retained for historical review... (i know you said it's throw-away data...)

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:nickg96
ID: 9718423
Thanks again Jade.

I just need some more detail and clearification for certain points. I want to be able to use your expertise and at the same time make sure I understand you properly

"Create a template database which has the fields from the csv, PLUS those fields needed to hold data entered or computed by the folk doing the balance checking."

-I should create a template database with the following tables:
*tblacounts
*tblbalances
*tblbasebalances
*tblcash
*tblfxates
***these tables should include the "extra" calculation fields (where appropriate/neccessary)

right?

"When a new set of csv's is received, copy the template MDB to a new mdb under a new name. (perhaps including source name/date)  Import the new data into the newly created copy of the template"
-OK, got that one. Good idea, thanks

"Design your forms to include these extra fields as part of the original and permanent design."
-Could you provide detail on how I should go about designing the forms. I want to make sure I'm creating the subforms (including the master/child links) properly. Also, what should I use as the forms recordsource?

"To do a balance job on a set of csv's recieved, link to the mdb that has that data in it and the forms should always work without modification."
-Basically, you're saying that the forms will be designed in a way that it should resolve my previous multiple ccy and adjustment problems. Could you please provide me some more detail concerning this form design?"

"The second benefit to this is that you can retain the BALANCED data for a period of your choosing, AND relink to it at any time to do rechecks or as a reminder or proof of correct-ness."
-OK, got that one too. Another good idea, thanks

"This whole process leg can be replicated indefinitely if you use some good naming conventions, and all that data (if desired) can be retained for historical review... (i know you said it's throw-away data...)"
-I'm confused here. What do you mean "replicated indefinitely" And how will good naming convention aid in that process?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9718706
What additional fields or data elements do you "balancing" crew need to assure that the books balance on this csv's  This is the first thing that needs to be considered.  

  For every entrie in the csv the bean-counters need to ..... (fill in the blank essay question)
0
 

Author Comment

by:nickg96
ID: 9722571
I am going to meet w/ the bean counter to make sure I have the entire process correct and I will get back to you with all of the fields needed for "balancing". Again, thanks for your time Jade
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9722644
I'll be here!
0
 

Author Comment

by:nickg96
ID: 9725859
Jade,

Met w/ bean counter. Need to go through notes and put together calculation list. I will be out of the office tommorrow. I'll have something posted by Thurs/Friday (depends if i have more questions for my associate) Thanks again for your time, i really appreciate your help/patience

Nick
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9725931
again,... I'll be here..! cya then
0
 

Author Comment

by:nickg96
ID: 9750767
Jade,
I'm still putting together the requirements. I've discovered how little I knew about the biz side of this application request. I'll post again on Monday. Many thanks
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9750855
you're welcome,...cya then
0
 

Author Comment

by:nickg96
ID: 9766895
sorry, i'm STILL gathering information. Seems like the more I learn, the more I have to alter. I'll post info soon. Sorry for the long delay
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9766964
I'll stick with you as long as you think necessary...
Let's remember, I've already been where you are now,....learning...
0
 

Author Comment

by:nickg96
ID: 9767098
thank you very much jade
0
 

Author Comment

by:nickg96
ID: 9811827
Jade,

I'm still gathering application requirements. Everyday I find a new nuance I have to accommodate. This application requirements doc will be rather lengthy. Once it is finished, is there a way to post the doc to this site?

Thanks for your patience,
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9811859
That's why you gather the specs before you try to build/code to it all.

posting the specs to EE - I will not want to see these specs because they will probably make little sense to me.  This question should stick to the original issues.  We will not be able to redesign the application at large on this question alone.
0
 

Author Comment

by:nickg96
ID: 9812066
ok, thanks. I won't post specs. I haven't actually started coding/dev yet. Questions above pertain to situations/problems I could see arising in the future (during dev phase). The description of the application was based on how I thought the app would be built. I'll use my specs to assist me when describing problems as they arise. I'll keep you posted. Thanks.
0
 
LVL 32

Accepted Solution

by:
jadedata earned 250 total points
ID: 9812114
As to the question of the development of a "dynamically expanding form"?  Where do  we want to go with that?
0
 

Author Comment

by:nickg96
ID: 9812408
I'll accept your answer as correct since it looks like I will not (hopefully) need a dynamically expanding form if I am able to correctly set up the db.

When I begin developement, if I have further specific questions regarding the dynamic creation of the form (which will probly mean I was unable to set up the tables/queries/subforms properly), I'll post them.

I'll also post any other specific developmental questions as well, as I am sure I will have many of them.

Thanks again for the help

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now