Bulk Create Records and Append to Table

[biganthony via AccessMonster.com]

Hi,

I have three questions related to an issue I am working through with someone.
I list them below but will provide information about the issue.

I am currently helping some one with an Access 2003 DB that helps her store
the classes needed for a college timetable. I am hoping to provide her with
an opportunity to create some classes in bulk and append them to an existing
table. She has a number of tables representing the faculties eg math, history,
social_science, all with the same structure.

In the math table, classes could appear as: 9matha, 9 mathb, 9mathc or
11math1, 11math2, 11math3 etc

Instead of creating each class individually in the math table, I thought I
could help her create a form where she enters the minimum details and have
Access create the classes and append the 'a', 'b' , 'c' or whatever suffix to
the class name.

This is what I did so you can help me (so bear with me - thanks!):

I created an unbounded form with an option group listing her faculties. The
user chooses the faculty they want to create classes for and this
automatically enters the faculty into the faculty field. This is then used to
determine what faculty table to append the data to (see code below)

I then created unbounded fields that correspond to the number of fields in
her faculty tables. These fields are as follows: ClassID (autonumber),
YearGroup, ClassPrefix, Faculty, Teacher, LessonValue. The class field in her
faculty tables is set to no duplicates as there cannot be two classes called
the same thing in the same college year level.

I then created an unbounded text box called: "number of classes to create"
(NumberToCreate). I am setting this to a maximum of 10.

I then created ten textboxes that will hold one character that represents the
suffix to attach to the end of the class name entered above. In these boxes,
the user would enter 'a', or 'b' or 'c' or '1' or '2' or '3' etc and this
would be attached to the class to produce the class name matha, mathb, mathc,
math1, math2 etc

So, if she wanted to create the math classes for year group 11, she would
enter a value for the autonumber, the year group level, eg 11, the class
prefix, eg 11math, the faculty is automatically entered from the option group
and she could leave the teacher field blank and enter a lesson value. So the
test data could be something like: 140, 11, 11Math, Math, , 10. In the
suffixes textboxes, she could enter the values A,B,C,D,E,F that would be
appended to the class '11Math' to create the unique classes.

So I created a command button on the form and with the help of this forum,
found and added the following code:

****************************************
Dim db As dao.Database
Dim rs As dao.Recordset
Dim NumberofClasses As Integer
Dim Table_Name As String

Table_Name = Me.Faculty

Set rs = DBEngine(0)(0).OpenRecordset(Table_Name, dbOpenTable,
dbAppendOnly)

For NumberofClasses = 1 To Me.NumberToCreate

rs.AddNew
rs.Fields("ClassID") = Me.ClassID
rs.Fields("YearGroup") = Me.YearGroup
rs.Fields("Class") = Me.ClassPrefix + Suffix <<-------- I
Need Help Here !!!!!! *****
rs.Fields("Faculty") = Me.Faculty
rs.Fields("Teacher") = Me.Teacher
rs.Fields("LessonValuel") = Me.LessonValue
rs.Update

Me.ClassID = Me.ClassID.Value + 1

Next NumberofClasses

rs.Close
Set rs = Nothing
*************************************************

My three problems are:

How do I get Access to add the suffix to the Class prefix to produce the full
class name eg 11MathA, 11MathB, 11MathC etc as it goes through the loop above?
?

How do I determine that the autonumber value for classID that the user enters
in the form above is not already used in the destination table? Or, how can I
design it so the user does not have to enter a value for classID field - they
could leave it blank and Access could append the data and automatically
continue the autonumber sequence in the table?

If the user wants ten classes created in bulk, I would like to have all ten
suffix text boxes enabled. If the user only wants four classes created, how
do I enable the first four text boxes and disable the other six etc? I am
sure the answer involves looping through the ten textbox controls and setting
their enabled values to true/false, but i have no idea how to write that code.


I appreciate any advice and thank you in advance.

regards
Anthony

 

[banem2]

Hi,

I have three questions related to an issue I am working through with someone.
I list them below but will provide information about the issue.

I am currently helping some one with an Access 2003 DB that helps her store
the classes needed for a college timetable. I am hoping to provide her with
an opportunity to create some classes in bulk and append them to an existing
table. She has a number of tables representing the faculties eg math, history,
social_science, all with the same structure.

In the math table, classes could appear as: 9matha, 9 mathb, 9mathc or
11math1, 11math2, 11math3 etc

Instead of creating each class individually in the math table, I thought I
could help her create a form where she enters the minimum details and have
Access create the classes and append the 'a', 'b' , 'c' or whatever suffix to
the class name.

This is what I did so you can help me (so bear with me - thanks!):

I created an unbounded form with an option group listing her faculties. The
user chooses the faculty they want to create classes for and this
automatically enters the faculty into the faculty field. This is then used to
determine what faculty table to append the data to (see code below)

I then created unbounded fields that correspond to the number of fields in
her faculty tables. These fields are as follows: ClassID (autonumber),
YearGroup, ClassPrefix, Faculty, Teacher, LessonValue. The class field in her
faculty tables is set to no duplicates as there cannot be two classes called
the same thing in the same college year level.

I then created an unbounded text box called: "number of classes to create"
(NumberToCreate). I am setting this to a maximum of 10.

I then created ten textboxes that will hold one character that represents the
suffix to attach to the end of the class name entered above. In these boxes,
the user would enter 'a', or 'b' or 'c' or '1' or '2' or '3' etc and this
would be attached to the class to produce the class name matha, mathb, mathc,
math1, math2 etc

So, if she wanted to create the math classes for year group 11, she would
enter a value for the autonumber, the year group level, eg 11, the class
prefix, eg 11math, the faculty is automatically entered from the option group
and she could leave the teacher field blank and enter a lesson value. So the
test data could be something like: 140, 11, 11Math, Math, , 10. In the
suffixes textboxes, she could enter the values A,B,C,D,E,F that would be
appended to the class '11Math' to create the unique classes.

So I created a command button on the form and with the help of this forum,
found and added the following code:

****************************************
Dim db As dao.Database
Dim rs As dao.Recordset
Dim NumberofClasses As Integer
Dim Table_Name As String

Table_Name = Me.Faculty

Set rs = DBEngine(0)(0).OpenRecordset(Table_Name, dbOpenTable,
dbAppendOnly)

For NumberofClasses = 1 To Me.NumberToCreate

rs.AddNew
rs.Fields("ClassID") = Me.ClassID
rs.Fields("YearGroup") = Me.YearGroup
rs.Fields("Class") = Me.ClassPrefix + Suffix <<-------- I
Need Help Here !!!!!! *****
rs.Fields("Faculty") = Me.Faculty
rs.Fields("Teacher") = Me.Teacher
rs.Fields("LessonValuel") = Me.LessonValue
rs.Update

Me.ClassID = Me.ClassID.Value + 1

Next NumberofClasses

rs.Close
Set rs = Nothing
*************************************************

My three problems are:

How do I get Access to add the suffix to the Class prefix to produce the full
class name eg 11MathA, 11MathB, 11MathC etc as it goes through the loop above?
?

How do I determine that the autonumber value for classID that the user enters
in the form above is not already used in the destination table? Or, how can I
design it so the user does not have to enter a value for classID field - they
could leave it blank and Access could append the data and automatically
continue the autonumber sequence in the table?

If the user wants ten classes created in bulk, I would like to have all ten
suffix text boxes enabled. If the user only wants four classes created, how
do I enable the first four text boxes and disable the other six etc? I am
sure the answer involves looping through the ten textbox controls and setting
their enabled values to true/false, but i have no idea how to write that code.


I appreciate any advice and thank you in advance.

regards
Anthony

Hi Anthony,

Instead:
rs.Fields("Class") = Me.ClassPrefix + Suffix

Try:
rs.Fields("Class") = Me.ClassPrefix & Suffix

I am not sure what you mean that user enters AutoNumber? AutoNumber is
- Auto generated unique number as user adds records to table with
AutoNumber field.

To find last used number you should use VBA function, but DMax
(slooow) could help if there aren't many records to search.

If this is your record:

140, 11, 11Math, Math, , 10

Then you could use:

DMax ("[SecondField]", "TableName", "[Class] = '" & Me.txtClass & "'")

This will return last used number for class (in this case 11 for class
"Math"). All you need to do is to use that number and add it in your
function loop ThatNumber = ThatNumber + 1.

Last (air code, possible errors):

Private Sub intClassNumber_AfterUpdate()
Dim ctl As Control

'Set all checkboxes to False
For Each Ctl In Me.Controls
If Ctl.Type = acCheckBox then Ctl.Enabled = False
Next Ctl

'Determine value entered between 1-10
'Jump to part to make checkboxes enabled
Select Case Me.intClassNumber
Case 1
GoTo ContCase1
Case 2
GoTo ContCase2
'... and so on
Case 10
GoTo ContCase10
End Select
Exit Sub

ContCase10:
chkBox10.Enabled = True
ContCase9:
chkBox9.Enabled = True
'... and so on
ContCase1:
chkBox1.Enabled = True

End Sub

There might be better solutions from other readers.

This code first sets all checkboxes on form to false, then it reads
value entered and, depending on value makes first 'n' checkboxes
enabled.

Hope this helps.

Regards,
Branislav Mihaljev

 

[biganthony via AccessMonster.com]

Hi Branislav,

Thanks for your reply. In reference to what you wrote below, does that mean
the auto number field (which is the ClassID) can be left blank by my user and
when she clicks the create button, the classes are created and automatically
appended, for example, to the Math table? Will Access automatically assign
autonumbers to the appended records? If the ClassID field can be left blank,
I will set its visible property to false so she doesn't enter anything into
it.

I will try your other ideas and report back.

Many thanks
Anthony

 

[banem2]

Hi Branislav,

Thanks for your reply. In reference to what you wrote below, does that mean
the auto number field (which is the ClassID) can be left blank by my user and
when she clicks the create button, the classes are created and automatically
appended, for example, to the Math table? Will Access automatically assign
autonumbers to the appended records? If the ClassID field can be left blank,
I will set its visible property to false so she doesn't enter anything into
it.

I will try your other ideas and report back.

Many thanks
Anthony

Hi Anthony,

Hold a sec - when we speak about "auto number" in Access everyone
thinks about it as AutoNumber field used in table. This is field where
Access store for each record unique number. This value cannot be
edited, deleted or retyped. I think you speak about field where
program should store generated numbers for your classes?

If above is true, you can use DMax (Note: slow, use only if there is
small amount of records) to find max number for Class and populate
table with max number +1, +2, etc.

Basically I think I have gave you all ideas and solutions, so you need
to put all together.

Good luck,
Branislav Mihaljev

 

[biganthony via AccessMonster.com]

Hi,

With the line of code quoted below, how can I get the loop to loop through
the ten text boxes that hold the suffixes that will be added to the class?

I have ten unbounded textboxes called suffix1, suffix2, ... suffix10, set to
accept only one character. Into these textboxes, the user would add the
character that she wants attached to the end of the class name. This allows
the user to create the following classes (10 at a time):

11MathA
11MathB
11MathC

 

[biganthony via AccessMonster.com]

Hi Branislav

Thanks for your ideas. I have worked this out and this is the code I used. I
have been able to solve all three problems in my original post.


Dim db As dao.Database
Dim rs As dao.Recordset
Dim NumberofClasses As Integer
Dim Table_Name As String
Dim ClassID As Integer
Dim Suffix As String

Table_Name = Me.Faculty

Set rs = DBEngine(0)(0).OpenRecordset(Table_Name, dbOpenTable,
dbAppendOnly)

'Determine the last number in the autonumner field of the destination
table.
'New value will be one greater than it.

ClassID = DMax("[ClassID]", Table_Name) + 1

For NumberofClasses = 1 To Me.NumberToCreate

Select Case NumberofClasses
Case 1
Suffix = Me.Suffix1.Value
Case 2
Suffix = Me.Suffix2.Value
Case 3
Suffix = Me.Suffix3.Value
Case 4
Suffix = Me.Suffix4.Value
Case 5
Suffix = Me.Suffix5.Value
Case 6
Suffix = Me.Suffix6.Value
Case 7
Suffix = Me.Suffix7.Value
Case 8
Suffix = Me.Suffix8.Value
Case 9
Suffix = Me.Suffix9.Value
Case 10
Suffix = Me.Suffix10.Value
End Select

rs.AddNew

rs.Fields("ClassID") = ClassID
rs.Fields("Year") = Me.Year
rs.Fields("Class") = Me.ClassPrefix + Suffix
rs.Fields("Faculty") = Me.Faculty
rs.Fields("Teacher") = Me.Teacher
rs.Fields("LessonValue") = Me.LessonValue
rs.Update

ClassID = ClassID + 1

Next NumberofClasses

rs.Close
Set rs = Nothing

MsgBox "Classes have been successfully created.", vbOKOnly +
vbInformation, "Success!"

 

[Marshall Barton]

I have three questions related to an issue I am working through with someone.
I list them below but will provide information about the issue.

I am currently helping some one with an Access 2003 DB that helps her store
the classes needed for a college timetable. I am hoping to provide her with
an opportunity to create some classes in bulk and append them to an existing
table. She has a number of tables representing the faculties eg math, history,
social_science, all with the same structure.

In the math table, classes could appear as: 9matha, 9 mathb, 9mathc or
11math1, 11math2, 11math3 etc

Instead of creating each class individually in the math table, I thought I
could help her create a form where she enters the minimum details and have
Access create the classes and append the 'a', 'b' , 'c' or whatever suffix to
the class name.

This is what I did so you can help me (so bear with me - thanks!):

I created an unbounded form with an option group listing her faculties. The
user chooses the faculty they want to create classes for and this
automatically enters the faculty into the faculty field. This is then used to
determine what faculty table to append the data to (see code below)

I then created unbounded fields that correspond to the number of fields in
her faculty tables. These fields are as follows: ClassID (autonumber),
YearGroup, ClassPrefix, Faculty, Teacher, LessonValue. The class field in her
faculty tables is set to no duplicates as there cannot be two classes called
the same thing in the same college year level.

I then created an unbounded text box called: "number of classes to create"
(NumberToCreate). I am setting this to a maximum of 10.

I then created ten textboxes that will hold one character that represents the
suffix to attach to the end of the class name entered above. In these boxes,
the user would enter 'a', or 'b' or 'c' or '1' or '2' or '3' etc and this
would be attached to the class to produce the class name matha, mathb, mathc,
math1, math2 etc

So, if she wanted to create the math classes for year group 11, she would
enter a value for the autonumber, the year group level, eg 11, the class
prefix, eg 11math, the faculty is automatically entered from the option group
and she could leave the teacher field blank and enter a lesson value. So the
test data could be something like: 140, 11, 11Math, Math, , 10. In the
suffixes textboxes, she could enter the values A,B,C,D,E,F that would be
appended to the class '11Math' to create the unique classes.

So I created a command button on the form and with the help of this forum,
found and added the following code:

****************************************
Dim db As dao.Database
Dim rs As dao.Recordset
Dim NumberofClasses As Integer
Dim Table_Name As String

Table_Name = Me.Faculty

Set rs = DBEngine(0)(0).OpenRecordset(Table_Name, dbOpenTable,
dbAppendOnly)

For NumberofClasses = 1 To Me.NumberToCreate

rs.AddNew
rs.Fields("ClassID") = Me.ClassID
rs.Fields("YearGroup") = Me.YearGroup
rs.Fields("Class") = Me.ClassPrefix + Suffix <<-------- I
Need Help Here !!!!!! *****
rs.Fields("Faculty") = Me.Faculty
rs.Fields("Teacher") = Me.Teacher
rs.Fields("LessonValuel") = Me.LessonValue
rs.Update

Me.ClassID = Me.ClassID.Value + 1

Next NumberofClasses

rs.Close
Set rs = Nothing
*************************************************

My three problems are:

How do I get Access to add the suffix to the Class prefix to produce the full
class name eg 11MathA, 11MathB, 11MathC etc as it goes through the loop above?
?

How do I determine that the autonumber value for classID that the user enters
in the form above is not already used in the destination table? Or, how can I
design it so the user does not have to enter a value for classID field - they
could leave it blank and Access could append the data and automatically
continue the autonumber sequence in the table?

If the user wants ten classes created in bulk, I would like to have all ten
suffix text boxes enabled. If the user only wants four classes created, how
do I enable the first four text boxes and disable the other six etc? I am
sure the answer involves looping through the ten textbox controls and setting
their enabled values to true/false, but i have no idea how to write that code.

From a database perspective, you are creating a spreadsheet
the hard way ;-)

Seriously, you should never have separate tables for each
group of classes. Instead, there should be one table with a
field that identifies the class group. Best to correct
things now before you dig the hole so deep you may never get
out of it.

To answer your question, name the 10 suffix text boxes
something like sfx1, sfx2, ..., sfx10. Then your loop can
refer to them using syntax like:

rs.Fields("Class") = Me.ClassPrefix & _
Me("sfx" & NumberofClasses)

You can use a similar approach to enable/disable the suffix
text boxes in the NumberToCreate text box's After Update
event:

For k = 1 To 10
Me("sfx" & k).Enabled = (k <= Me.NumberToCreate)
Next k

 

[banem2]

Hi,

With the line of code quoted below, how can I get the loop to loop through
the ten text boxes that hold the suffixes that will be added to the class?

I have ten unbounded textboxes called suffix1, suffix2, ... suffix10, set to
accept only one character. Into these textboxes, the user would add the
character that she wants attached to the end of the class name. This allows
the user to create the following classes (10 at a time):

11MathA
11MathB
11MathC

Hi,

Maybe you could use something like this:

Dim i As Integer, strFieldName As String
Dim strClass As String

i = 1: strFieldName = "suffix"
strClass = "11Math"

strFieldName = strFieldName & i
Do While Me(strFieldName).Value <> ""
strClass = Me(strFieldName).Value
i = i + 1
Loop

Now adopt it to your code.

Regards,
Branislav Mihaljev

 

[biganthony via AccessMonster.com]

Thanks Marshall,

I have adapted my code using your suggestions.

My friend who has the database inherited it where there is a table for each
faculty. These tables are then brought together in a Union query to produce a
report listing the classes that each member of the teaching faculties are
teaching. Many of them teach across different faculties.

Thanks for your help
Anthony

 

[Klatuu]

The database is even worse than first described. If there are multiple
teachers who can teach classes in one or more faculties you need an additonal
table. You may, in fact need more.
For example, in a typical educational model, a class defines a course of
work. There can be multiple classes - an instance of a class. So you need a
table that describes a class. You need a table that describes the instances
of the class (date, time, location, teacher). And since you have your
teacher separated into faculties, you need a table of facualties, a table of
teachers, and a table that resolves the one to many relationship between
faculties and teachers.

 

[Marshall Barton]

I understand the situation, been there, done that, paid the
price. But, like I said, the problems caused by an
unnormalized table structure will only become more and more
complex as you add more features to your app. The use of a
UNION query to fake a properly normalized table is just an
extra layer that deals with one symptom without addressing
the disease. Of course you are free to ignore my advice,
but, if you do, be prepared to deal with all the
issues/complications/problems that will eventually distort
your app into an unwieldy monster.