Dynamic Form

[Joseph]

I would like to be able to create a form(s) dynamically.

Table One: Topics
Table Two: subTopics

Create Form with a tab-control with data from Topics that has subforms
(datasheet view) from subTopics.

I can do this with php, and I understand that it can be done with vba, but I
do not have the knowledge to do it.

 

[Douglas J. Steele]

Creating forms dynamically can be done, but it's an extremely unusual
requirement (the reason the capability exists in VBA is to allow the
creation of wizards)

 

[Joseph]

I know, but I need to Topics and subTopics to be customizable. If I set the
Topics now, then changing them later will be a pain.

PS. I will need reports made also.

 

[Douglas J. Steele]

Perhaps if you provide some details of what you're trying to accomplish,
someone can suggest a better design.

In general, you'd simply present the details of from the Topics table as
rows on a form, with the subTopics presented on a subform.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I know, but I need to Topics and subTopics to be customizable. If I set
the
Topics now, then changing them later will be a pain.

PS. I will need reports made also.

 

[Joseph]

Ok, the Juvenile Boot Camp that I work at has a spreadsheet of that we use
daily as a form to show the juveniles the areas that they need to work on.
This form is then compiled weekly to calculate merits and demerits which
allows extra privileges or not. These weekly forms are then calculated
monthly to for an award. Also for a juvenile to advance to the next phase of
the program, the juvenile has to maintain an average of x merits per week for
y amount of weeks. Then to determine if a juvenile has collected and
maintained enough merits over their stay, another count is done to see if the
juvenile has reached the minimum amount of merits.

And all of that is being done by hand, per juvenile, per day.

Needless to say, it takes allot of time to make this happen. I have
already come up with the way you mentioned, but broke it down further as each
"topic" has its own table with the corresponding "subtopics" as fields. The
problem is that we have changed the "topics" 3 times in the past 4 years.
And the "subtopics" 5 times in the past 3 years.

My question is: Is there a way to do it dynamically? Broken down, it is
simply a nested loop on a multi-dimensional array.

 

[Douglas J. Steele]

Can you give an example of what the fields in the two tables are like? It
sounds to me as though your tables may not be properly normalized.

 

[Joseph]

CREATE TABLE `BootCampMerits`.`BCMeritTopics` (
`idMeritTopics` int(10) NOT NULL auto_increment,
`Topics` varchar(255) default NULL,
`Magnitude` int(11) default NULL,
`Valid` tinyint(1) default NULL,
PRIMARY KEY USING BTREE (`idMeritTopics`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

CREATE TABLE `BootCampMerits`.`BCMeritsubTopics` (
`idMeritsubTopics` int(10) NOT NULL auto_increment,
`SubTopic` varchar(255) default NULL,
`idMeritTopics` int(11) default NULL,
`Magnitude` int(11) default NULL,
`Valid` tinyint(1) NOT NULL,
PRIMARY KEY USING BTREE (`idMeritsubTopics`),
KEY `TopicID` (`idMeritTopics`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1

CREATE TABLE `BootCampMerits`.`MeritsMain` (
`idMerits` int(10) unsigned NOT NULL auto_increment,
`DTGCreated` datetime NOT NULL,
`User` varchar(45) NOT NULL,
`Computer` varchar(45) NOT NULL,
`CadetID` int(10) unsigned NOT NULL,
`AMStaffID` int(10) unsigned NOT NULL,
`PMStaffID` int(10) unsigned NOT NULL,
`AMStaffRemarks` mediumtext NOT NULL,
`PMStaffRemarks` mediumtext NOT NULL,
`AMSSRemarks` mediumtext NOT NULL,
`PMSSRemarks` mediumtext NOT NULL,
PRIMARY KEY (`idMerits`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `BootCampMerits`.`MeritsMain` (
`idMerits` int(10) unsigned NOT NULL auto_increment,
`DTGCreated` datetime NOT NULL,
`User` varchar(45) NOT NULL,
`Computer` varchar(45) NOT NULL,
`CadetID` int(10) unsigned NOT NULL,
`AMStaffID` int(10) unsigned NOT NULL,
`PMStaffID` int(10) unsigned NOT NULL,
`AMStaffRemarks` mediumtext NOT NULL,
`PMStaffRemarks` mediumtext NOT NULL,
`AMSSRemarks` mediumtext NOT NULL,
`PMSSRemarks` mediumtext NOT NULL,
PRIMARY KEY (`idMerits`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Theses are my tables, I will post the actual topic/subtopic this evening.

 

[Douglas J. Steele]

As I suspected, your tables are not properly normalized. Names like
AMStaffID and PMStaffID, AMStaffRemarks and PMStaffRemarks and AMSSRemarks
and PMSSRemarks are what's known as repeating groups.

 

[Joseph]

I forgot to mention that there are two shifts of staff each day that have to
assess the juvenile's behavior per day, hence the AM(0600-1400) and
PM(1400-2200) fields. There should also be a third shift(Night = 2200-0600),
but as the juveniles are not assessed when they are sleeping as that is the
only thing that they should be doing is sleeping.

Now I could do this, and have a record for each shift, but I thought that
would be allot harder to create the forms and reports dynamically:

DROP TABLE IF EXISTS `BootCampMerits`.`MeritsMain2`;
CREATE TABLE `BootCampMerits`.`MeritsMain2` (
`idMeritsMain2` int(10) unsigned NOT NULL auto_increment,
`DTGCreated` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`User` varchar(45) NOT NULL,
`Computer` varchar(45) NOT NULL,
`DTGofMerits` datetime NOT NULL,
`ShiftID` int(10) unsigned NOT NULL COMMENT 'Link to Passdown.Shift Table',
`CadetID` int(10) unsigned NOT NULL,
`StaffID` int(10) unsigned NOT NULL,
`StaffRemarks` mediumtext NOT NULL,
`SSRemarks` mediumtext NOT NULL COMMENT 'Shift Supervisor Comments',
PRIMARY KEY (`idMeritsMain2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `Passdown`.`Shift`;
CREATE TABLE `Passdown`.`Shift` (
`ShiftID` int(10) NOT NULL auto_increment,
`Shift` varchar(50) default NULL,
`ShiftStart` datetime default NULL,
`ShiftEnd` datetime default NULL,
PRIMARY KEY (`ShiftID`),
KEY `ShiftID` (`ShiftID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

 

[Joseph]

I posted duplicate for one table, here is the script for the other table:

DROP TABLE IF EXISTS `BootCampMerits`.`MeritsMerits`;
CREATE TABLE `BootCampMerits`.`MeritsMerits` (
`idMM` int(11) NOT NULL auto_increment,
`DTGCreated` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`User` varchar(45) NOT NULL,
`Computer` varchar(45) NOT NULL,
`idMeritMain` int(10) unsigned NOT NULL,
`idMeritSubTopics` int(11) NOT NULL,
`Value` int(11) NOT NULL,
PRIMARY KEY (`idMM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

[Joseph]

Is it feasible?

 

[Joseph]

Here is the data from the tables Topics and subTopics:

INSERT INTO `BCMeritTopics` (`idMeritTopics`,`Topics`,`Magnitude`,`Valid`)
VALUES
(1,'Wake Up',1,-1),
(2,'Physical Fitness',2,-1),
(3,'Chowhall',3,-1),
(4,'Cleaning',4,-1),
(5,'Rack/Wall Locker',5,-1),
(6,'Inspection',6,-1),
(7,'Classroom Performance',7,-1),
(8,'Staff Interaction',8,-1),
(9,'Peer Interaction',9,-1),
(10,'Outdoor Activities',10,-1),
(11,'Military Bearing',11,-1);


INSERT INTO `BCMeritsubTopics`
(`idMeritsubTopics`,`SubTopic`,`idMeritTopics`,`Magnitude`,`Valid`) VALUES
(1,'Getting Up',1,1,-1),
(2,'Behavior',1,2,-1),
(3,'Motivation',1,3,-1),
(4,'Other',1,4,-1),
(5,'Horseplay',2,1,-1),
(6,'Participates',2,2,-1),
(7,'Follows Instructions',2,3,-1),
(8,'Motivation',2,4,-1),
(9,'Involvement',3,1,-1),
(10,'Behavior',3,2,-1),
(11,'Follows Instructions',3,3,-1),
(12,'Involvement',4,1,-1),
(13,'Behavior',4,2,-1),
(14,'Follows Instructions',4,3,-1),
(15,'Rack ',5,1,-1),
(16,'Display ',5,2,-1),
(17,'Other',5,3,-1),
(18,'Knowledge',6,1,-1),
(19,'Appearance',6,2,-1),
(20,'Wall Locker',6,3,-1),
(21,'Rack',6,4,-1),
(22,'Shoe Display',6,5,-1),
(23,'Inspection Merit/Demerit',6,6,-1),
(24,'Respect',7,1,-1),
(25,'Distracts / Interrupts',7,2,-1),
(26,'Involvement',7,3,-1),
(27,'Follows Instructions / Rules',7,4,-1),
(28,'Copies',7,5,-1),
(29,'Addressing Staff',8,1,-1),
(30,'Needs Warnings',8,2,-1),
(31,'Follows Instructions',8,3,-1),
(32,'Going Into Room',8,4,-1),
(33,'Under-breath Comment',8,5,-1),
(34,'Degrades',8,6,-1),
(35,'Threatens',8,7,-1),
(36,'Addressing Peers',9,1,-1),
(37,'Conversation',9,2,-1),
(38,'Language',9,3,-1),
(39,'Threatens',9,4,-1),
(40,'Distracts / Loud',9,5,-1),
(41,'Behavior',9,6,-1),
(42,'Motivation',10,1,-1),
(43,'Safety Conscious',10,2,-1),
(44,'Use Of Tools',10,3,-1),
(45,'Conversation',10,4,-1),
(46,'Moving W/O Permission',11,1,-1),
(47,'Smiling/Laughing/Smirking',11,2,-1),
(48,'Sounding Off',11,3,-1);

 

[Douglas J. Steele]

As far as I'm concerned, you haven't explained what it is you're trying to
do that you feel a dynamic form is required, as opposed to the standard
form/subform configuration.

 

[Joseph]

I just do not want to have to modify the forms/subforms every time that there
is a change in the way we assess the juveniles.

Is is feasible? Yes.
Is it worth all the trouble of having to write several hundred/thousand
lines of code? Probably not.

I guess that I will have to make static tables until I can get my framework
running to make all my Access front-ends into web pages, with php.

thanks anyways.

 

[Douglas J. Steele]

With a properly designed form/subform, you wouldn't have to change anything
about your forms. You'd make changes to the data in the tables to which the
two forms are bound, and everything would happen automatically.

I'm sorry, but you're really not providing enough information. I'm sure
you're getting frustrated with this thread of notes: I know I am!

At least you have something in mind that will meet your current needs.

Good luck!