Best way to handle a code composed of two parts?

[Guest]

I have a master table of college courses. The key field is a combination of
the rubric (department abbreviation) and course number (text field). The
rubric and course number are also in separate fields.

When someone enters a course that's not in the table, I want the user to
enter EITHER the code (which I will split into rubric and course number) OR
the rubric and course number.

I would prefer that they enter the rubric and course number separately.
However, I can't seem to enter the rubric and leave the field because the
code field is blank. I have tried to create an eventprocedure that is
triggered when you leave the rubric field and write rubric&course into the
key field.

How can I write an entry into the key field ([CourseCode] using values typed
into [Rubric] and [CourseNo]?

Alternatively, I can have the user enter the course code and split it into
parts IF I can figure out how to identify the first position in the string
that is a digit. Course codes are composed of two to as many as eight or ten
letters and three or four digits. If I can determine the position of the
first digit, I can use Len, Left, and Right to slice and dice to get the
separate rubric and course number.

 

[BruceM]

If CourseCode is a combination of Rubric and CourseNo, you should not create
a separate field. Instead, make the primary key a combination of Rubric and
CourseNo. In table design view, hold the Ctrl key while clicking the two
fields, then click the key icon on the toolbar (or click Edit --> Primary
Key). You can easily combine the fields for display purposes.

 

[Guest]

Bruce,

Thanks for the very quick response!

How's this going to affect the table to which this one is linked? All those
records use course code. I'm going to have to modify that table and the links.

Guess I'll back up the database and see what happens.

liz

If CourseCode is a combination of Rubric and CourseNo, you should not create
a separate field. Instead, make the primary key a combination of Rubric and
CourseNo. In table design view, hold the Ctrl key while clicking the two
fields, then click the key icon on the toolbar (or click Edit --> Primary
Key). You can easily combine the fields for display purposes.

I have a master table of college courses. The key field is a combination of
the rubric (department abbreviation) and course number (text field). The
rubric and course number are also in separate fields.

When someone enters a course that's not in the table, I want the user to
enter EITHER the code (which I will split into rubric and course number)
OR
the rubric and course number.

I would prefer that they enter the rubric and course number separately.
However, I can't seem to enter the rubric and leave the field because the
code field is blank. I have tried to create an eventprocedure that is
triggered when you leave the rubric field and write rubric&course into the
key field.

How can I write an entry into the key field ([CourseCode] using values
typed
into [Rubric] and [CourseNo]?

Alternatively, I can have the user enter the course code and split it into
parts IF I can figure out how to identify the first position in the string
that is a digit. Course codes are composed of two to as many as eight or
ten
letters and three or four digits. If I can determine the position of the
first digit, I can use Len, Left, and Right to slice and dice to get the
separate rubric and course number.

 

[BruceM]

You would need to redefine the relationships. Unfortunately I don't have
time to go into it in more detail today, but I will take another look
tomorrow. There is a way of breaking up the text and numbers, but I can't
work out the details just now. Maybe somebody will jump into the thread.
In any case, duplicating the information is best avoided.

Bruce,

Thanks for the very quick response!

How's this going to affect the table to which this one is linked? All
those
records use course code. I'm going to have to modify that table and the
links.

Guess I'll back up the database and see what happens.

liz

If CourseCode is a combination of Rubric and CourseNo, you should not
create
a separate field. Instead, make the primary key a combination of Rubric
and
CourseNo. In table design view, hold the Ctrl key while clicking the two
fields, then click the key icon on the toolbar (or click Edit --> Primary
Key). You can easily combine the fields for display purposes.

I have a master table of college courses. The key field is a combination
of
the rubric (department abbreviation) and course number (text field).
The
rubric and course number are also in separate fields.

When someone enters a course that's not in the table, I want the user
to
enter EITHER the code (which I will split into rubric and course
number)
OR
the rubric and course number.

I would prefer that they enter the rubric and course number separately.
However, I can't seem to enter the rubric and leave the field because
the
code field is blank. I have tried to create an eventprocedure that is
triggered when you leave the rubric field and write rubric&course into
the
key field.

How can I write an entry into the key field ([CourseCode] using values
typed
into [Rubric] and [CourseNo]?

Alternatively, I can have the user enter the course code and split it
into
parts IF I can figure out how to identify the first position in the
string
that is a digit. Course codes are composed of two to as many as eight
or
ten
letters and three or four digits. If I can determine the position of
the
first digit, I can use Len, Left, and Right to slice and dice to get
the
separate rubric and course number.

 

[Guest]

Bruce,

Thanks again for your quick response.

I understand about duplicating data, but the problem goes beyond redefining
relationships and duplicating data.

If I have a single key field ([CourseCode], which includes both the
abbreviation and course number, I can easily populate a list box to validate
the course info when someone is entering courses for each student. It won't
be nearly as easy if the code is split into two parts.

I'd rather violate the rules of normalization (which I'm doing anyway,
because I'm filling in the course description and credit hours in the
student/courses table because course titles sometimes change and some courses
are variable hours credit. Much better for simplicity's sake to just write
the data "permanently" into the record associated with the student).

Parsing the code field would probably be best if it's doable because that
would mean that the data entry person would always enter just the
single-field code. However, I can just keep doing what I'm doing now, which
is have her enter the code and then the separate parts. I just hate to do
that.

Again, thanks for your fast responses.

Dij

P.S. Surely do wish someone had an idea about my non-scrolling continuous
form! Even my second request hasn't gotten a response.

You would need to redefine the relationships. Unfortunately I don't have
time to go into it in more detail today, but I will take another look
tomorrow. There is a way of breaking up the text and numbers, but I can't
work out the details just now. Maybe somebody will jump into the thread.
In any case, duplicating the information is best avoided.

Bruce,

Thanks for the very quick response!

How's this going to affect the table to which this one is linked? All
those
records use course code. I'm going to have to modify that table and the
links.

Guess I'll back up the database and see what happens.

liz

If CourseCode is a combination of Rubric and CourseNo, you should not
create
a separate field. Instead, make the primary key a combination of Rubric
and
CourseNo. In table design view, hold the Ctrl key while clicking the two
fields, then click the key icon on the toolbar (or click Edit --> Primary
Key). You can easily combine the fields for display purposes.

"Dij" <dijatcox.net> wrote in message
I have a master table of college courses. The key field is a combination
of
the rubric (department abbreviation) and course number (text field).
The
rubric and course number are also in separate fields.

When someone enters a course that's not in the table, I want the user
to
enter EITHER the code (which I will split into rubric and course
number)
OR
the rubric and course number.

I would prefer that they enter the rubric and course number separately.
However, I can't seem to enter the rubric and leave the field because
the
code field is blank. I have tried to create an eventprocedure that is
triggered when you leave the rubric field and write rubric&course into
the
key field.

How can I write an entry into the key field ([CourseCode] using values
typed
into [Rubric] and [CourseNo]?

Alternatively, I can have the user enter the course code and split it
into
parts IF I can figure out how to identify the first position in the
string
that is a digit. Course codes are composed of two to as many as eight
or
ten
letters and three or four digits. If I can determine the position of
the
first digit, I can use Len, Left, and Right to slice and dice to get
the
separate rubric and course number.

 

[BruceM]

Sometimes it is necessary to store data rather than linking to it. We have
a situation where a name needs to be stored as it was when the record was
created. Things like that come up sometimes. If a course description is
variable you could have two or more descriptions for the course in the
Course table. A Yes/No field could activate the course that is being used
at the moment, so that the source query for a combo box or list box could
exclude records in which the box is not checked, or something like that. Or
you could store the information. I would prefer the first choice if
possible, but reworking an existing database could be troublesome.
As for combining two separate fields in a list box (or combo box) you can
combine the fields in the row source query:
FullNumber: [DeptCode] & [CourseCode]
That sort of thing is done often for combining name fields into a full name.

I have poked around a bit, and I know there is code for parsing a
letter/number string, but the variable lengths of the letter and number
strings makes it more difficult.

Here is one thing I have played around with a bit. If the number portion
(on the right end of CourseCode) is either three or four digits, you could
do something like this in VBA (untested):

Dim strCodeLength as String
Dim strDeptCode as String
Dim strCourseNo as String
Dim intLetterPart as Integer

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode), 4)) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strDeptCode = Left(Me.CourseCode), intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strDeptCode = Left(Me.CourseCode, intLetterPart)
End If

The idea is that if the course code is HIST123, the rightmost 4 characters
are T123. By using Val, this evaluates to 0. In that case, the length of
the string minus 3 (in this case, 4) is used in the Left function; otherwise
the length of the string minus 4 is used in the left function. This works
only if the numeric portion of CourseCode is either 3 or 4. As I said, I
haven't tested the code except in abbreviated fashion in an unbound text
box, but I would like to do so when I have a little more time.

For the other part of your post, what is this about a non-scrolling
continuous form?

Bruce,

Thanks again for your quick response.

I understand about duplicating data, but the problem goes beyond
redefining
relationships and duplicating data.

If I have a single key field ([CourseCode], which includes both the
abbreviation and course number, I can easily populate a list box to
validate
the course info when someone is entering courses for each student. It
won't
be nearly as easy if the code is split into two parts.

I'd rather violate the rules of normalization (which I'm doing anyway,
because I'm filling in the course description and credit hours in the
student/courses table because course titles sometimes change and some
courses
are variable hours credit. Much better for simplicity's sake to just write
the data "permanently" into the record associated with the student).

Parsing the code field would probably be best if it's doable because that
would mean that the data entry person would always enter just the
single-field code. However, I can just keep doing what I'm doing now,
which
is have her enter the code and then the separate parts. I just hate to do
that.

Again, thanks for your fast responses.

Dij

P.S. Surely do wish someone had an idea about my non-scrolling continuous
form! Even my second request hasn't gotten a response.

You would need to redefine the relationships. Unfortunately I don't have
time to go into it in more detail today, but I will take another look
tomorrow. There is a way of breaking up the text and numbers, but I
can't
work out the details just now. Maybe somebody will jump into the thread.
In any case, duplicating the information is best avoided.

Bruce,

Thanks for the very quick response!

How's this going to affect the table to which this one is linked? All
those
records use course code. I'm going to have to modify that table and the
links.

Guess I'll back up the database and see what happens.

liz

:

If CourseCode is a combination of Rubric and CourseNo, you should not
create
a separate field. Instead, make the primary key a combination of
Rubric
and
CourseNo. In table design view, hold the Ctrl key while clicking the
two
fields, then click the key icon on the toolbar (or click Edit -->
Primary
Key). You can easily combine the fields for display purposes.

"Dij" <dijatcox.net> wrote in message
I have a master table of college courses. The key field is a
combination
of
the rubric (department abbreviation) and course number (text field).
The
rubric and course number are also in separate fields.

When someone enters a course that's not in the table, I want the
user
to
enter EITHER the code (which I will split into rubric and course
number)
OR
the rubric and course number.

I would prefer that they enter the rubric and course number
separately.
However, I can't seem to enter the rubric and leave the field
because
the
code field is blank. I have tried to create an eventprocedure that
is
triggered when you leave the rubric field and write rubric&course
into
the
key field.

How can I write an entry into the key field ([CourseCode] using
values
typed
into [Rubric] and [CourseNo]?

Alternatively, I can have the user enter the course code and split
it
into
parts IF I can figure out how to identify the first position in the
string
that is a digit. Course codes are composed of two to as many as
eight
or
ten
letters and three or four digits. If I can determine the position of
the
first digit, I can use Len, Left, and Right to slice and dice to get
the
separate rubric and course number.

 

[Guest]

Bruce,

Thanks once again.

The non-scrolling continuous form problem is described in the Access forms
area under the title "SECOND POST--continuous form scroll problem". Fly in
the ointment, but frustrating when entering courses for a new student because
only the course record you are entering is visible.

I will try your code in a little while. As far as I know, there are no
courses with anything less than three digits. LSU courses all have four, but
students do sometimes transfer courses from schools that use three digits.
Thanks also for explaining the underlying logic behind the code.

This is a database that is used to track our department's graduate students
and their programs of study. It's not used that much and it's not that big so
I'm not too worried about it not being perfectly normalized. I haven't done
any programming in a long time, either. I'm just trying to get this simple
system worked out so that the person who is using it is doing the minimum
typing possible. The person who set up the database didn't have a clue and
I've spent a fair amount of time reworking it but I'm just about at a point
where much more work isn't worth the time and effort. We're just about to
"good enough"...

Liz

Sometimes it is necessary to store data rather than linking to it. We have
a situation where a name needs to be stored as it was when the record was
created. Things like that come up sometimes. If a course description is
variable you could have two or more descriptions for the course in the
Course table. A Yes/No field could activate the course that is being used
at the moment, so that the source query for a combo box or list box could
exclude records in which the box is not checked, or something like that. Or
you could store the information. I would prefer the first choice if
possible, but reworking an existing database could be troublesome.
As for combining two separate fields in a list box (or combo box) you can
combine the fields in the row source query:
FullNumber: [DeptCode] & [CourseCode]
That sort of thing is done often for combining name fields into a full name.

I have poked around a bit, and I know there is code for parsing a
letter/number string, but the variable lengths of the letter and number
strings makes it more difficult.

Here is one thing I have played around with a bit. If the number portion
(on the right end of CourseCode) is either three or four digits, you could
do something like this in VBA (untested):

Dim strCodeLength as String
Dim strDeptCode as String
Dim strCourseNo as String
Dim intLetterPart as Integer

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode), 4)) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strDeptCode = Left(Me.CourseCode), intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strDeptCode = Left(Me.CourseCode, intLetterPart)
End If

The idea is that if the course code is HIST123, the rightmost 4 characters
are T123. By using Val, this evaluates to 0. In that case, the length of
the string minus 3 (in this case, 4) is used in the Left function; otherwise
the length of the string minus 4 is used in the left function. This works
only if the numeric portion of CourseCode is either 3 or 4. As I said, I
haven't tested the code except in abbreviated fashion in an unbound text
box, but I would like to do so when I have a little more time.

For the other part of your post, what is this about a non-scrolling
continuous form?

 

[BruceM]

I posted a reply in that other thread, but I'm afraid it's not much help.

I have seen (and inherited) rather a number of databases that look nice, but
that are chaotic behind the scenes and clunky to use. Some people are
impressed by bells and whistles, and will accept that, for instance, you
need to enter the employee's name over and over. As long as the interface
has nice colors and pictures they're impressed.

I will continue to experiment with my parsing system. It seems to be an
unusual approach, which means either that I'm innovative or that there's
something wrong with it. I hope it's the former.

Bruce,

Thanks once again.

The non-scrolling continuous form problem is described in the Access forms
area under the title "SECOND POST--continuous form scroll problem". Fly in
the ointment, but frustrating when entering courses for a new student
because
only the course record you are entering is visible.

I will try your code in a little while. As far as I know, there are no
courses with anything less than three digits. LSU courses all have four,
but
students do sometimes transfer courses from schools that use three digits.
Thanks also for explaining the underlying logic behind the code.

This is a database that is used to track our department's graduate
students
and their programs of study. It's not used that much and it's not that big
so
I'm not too worried about it not being perfectly normalized. I haven't
done
any programming in a long time, either. I'm just trying to get this simple
system worked out so that the person who is using it is doing the minimum
typing possible. The person who set up the database didn't have a clue and
I've spent a fair amount of time reworking it but I'm just about at a
point
where much more work isn't worth the time and effort. We're just about to
"good enough"...

Liz

Sometimes it is necessary to store data rather than linking to it. We
have
a situation where a name needs to be stored as it was when the record was
created. Things like that come up sometimes. If a course description is
variable you could have two or more descriptions for the course in the
Course table. A Yes/No field could activate the course that is being
used
at the moment, so that the source query for a combo box or list box could
exclude records in which the box is not checked, or something like that.
Or
you could store the information. I would prefer the first choice if
possible, but reworking an existing database could be troublesome.
As for combining two separate fields in a list box (or combo box) you can
combine the fields in the row source query:
FullNumber: [DeptCode] & [CourseCode]
That sort of thing is done often for combining name fields into a full
name.

I have poked around a bit, and I know there is code for parsing a
letter/number string, but the variable lengths of the letter and number
strings makes it more difficult.

Here is one thing I have played around with a bit. If the number portion
(on the right end of CourseCode) is either three or four digits, you
could
do something like this in VBA (untested):

Dim strCodeLength as String
Dim strDeptCode as String
Dim strCourseNo as String
Dim intLetterPart as Integer

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode), 4)) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strDeptCode = Left(Me.CourseCode), intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strDeptCode = Left(Me.CourseCode, intLetterPart)
End If

The idea is that if the course code is HIST123, the rightmost 4
characters
are T123. By using Val, this evaluates to 0. In that case, the length
of
the string minus 3 (in this case, 4) is used in the Left function;
otherwise
the length of the string minus 4 is used in the left function. This
works
only if the numeric portion of CourseCode is either 3 or 4. As I said, I
haven't tested the code except in abbreviated fashion in an unbound text
box, but I would like to do so when I have a little more time.

For the other part of your post, what is this about a non-scrolling
continuous form?

 

[Guest]

Bruce,

Your code worked, with minor modifications (had to add a paren, subtract
one, piddly stuff that Access prompted me to do, then add the code to write
the two variables into the table).

I have even set the properties for the two generated fields so they don't
have tab stops and are color-coded (the user knows that green fields are
filled in automatically so she can see them but the cursor doesn't go there).
I could remove them from the form altogether, but I think it serves as a
double-check for her to be able to see them without having to bounce through
them.

There's just one tiny (but very annoying) problem. When I open the form,
CourseCode in the first record is highlighted. When you click the "add
record" button, nothing happens. However, if you press Enter, then you can
click the "add record" button and everything works great.

I know there's bound to be some little something that I'm doing wrong, but I
am working nearly blind here. What do I need to do to avoid that extra
confusing step? I want the user to be able to open the form and click the
"add record" button without having to press Enter.

BTW, your input is GREATLY appreciated! Your solution seems very elegant to
me.

liz




Here's what I'm using:

--------------

Private Sub CourseCode_Exit(Cancel As Integer)
Dim strCodeLength As String
Dim strRubric As String
Dim strCourseNo As String
Dim intLetterPart As Integer

If Not IsNull(Len(Me.CourseCode)) Then

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode, 4))) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strRubric = Left(Me.CourseCode, intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strRubric = Left(Me.CourseCode, intLetterPart)
End If

If (Not IsNull(strRubric)) Then Me![Rubric] = strRubric
If (Not IsNull(strCourseNo)) Then Me![CourseNo] = strCourseNo

End If

End Sub

------------

I posted a reply in that other thread, but I'm afraid it's not much help.

I have seen (and inherited) rather a number of databases that look nice, but
that are chaotic behind the scenes and clunky to use. Some people are
impressed by bells and whistles, and will accept that, for instance, you
need to enter the employee's name over and over. As long as the interface
has nice colors and pictures they're impressed.

I will continue to experiment with my parsing system. It seems to be an
unusual approach, which means either that I'm innovative or that there's
something wrong with it. I hope it's the former.

Bruce,

Thanks once again.

The non-scrolling continuous form problem is described in the Access forms
area under the title "SECOND POST--continuous form scroll problem". Fly in
the ointment, but frustrating when entering courses for a new student
because
only the course record you are entering is visible.

I will try your code in a little while. As far as I know, there are no
courses with anything less than three digits. LSU courses all have four,
but
students do sometimes transfer courses from schools that use three digits.
Thanks also for explaining the underlying logic behind the code.

This is a database that is used to track our department's graduate
students
and their programs of study. It's not used that much and it's not that big
so
I'm not too worried about it not being perfectly normalized. I haven't
done
any programming in a long time, either. I'm just trying to get this simple
system worked out so that the person who is using it is doing the minimum
typing possible. The person who set up the database didn't have a clue and
I've spent a fair amount of time reworking it but I'm just about at a
point
where much more work isn't worth the time and effort. We're just about to
"good enough"...

Liz

Sometimes it is necessary to store data rather than linking to it. We
have
a situation where a name needs to be stored as it was when the record was
created. Things like that come up sometimes. If a course description is
variable you could have two or more descriptions for the course in the
Course table. A Yes/No field could activate the course that is being
used
at the moment, so that the source query for a combo box or list box could
exclude records in which the box is not checked, or something like that.
Or
you could store the information. I would prefer the first choice if
possible, but reworking an existing database could be troublesome.
As for combining two separate fields in a list box (or combo box) you can
combine the fields in the row source query:
FullNumber: [DeptCode] & [CourseCode]
That sort of thing is done often for combining name fields into a full
name.

I have poked around a bit, and I know there is code for parsing a
letter/number string, but the variable lengths of the letter and number
strings makes it more difficult.

Here is one thing I have played around with a bit. If the number portion
(on the right end of CourseCode) is either three or four digits, you
could
do something like this in VBA (untested):

Dim strCodeLength as String
Dim strDeptCode as String
Dim strCourseNo as String
Dim intLetterPart as Integer

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode), 4)) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strDeptCode = Left(Me.CourseCode), intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strDeptCode = Left(Me.CourseCode, intLetterPart)
End If

The idea is that if the course code is HIST123, the rightmost 4
characters
are T123. By using Val, this evaluates to 0. In that case, the length
of
the string minus 3 (in this case, 4) is used in the Left function;
otherwise
the length of the string minus 4 is used in the left function. This
works
only if the numeric portion of CourseCode is either 3 or 4. As I said, I
haven't tested the code except in abbreviated fashion in an unbound text
box, but I would like to do so when I have a little more time.

For the other part of your post, what is this about a non-scrolling
continuous form?

 

[BruceM]

Glad to hear it worked. One observation is that this line:
If Not IsNull(Len(Me.CourseCode)) Then
could probably be
If IsNull(Me.CourseCode) Then
The Nz function can also be helpful:
If Nz(Len(Me.CourseCode),0) = 0 Then
Just mentioning these things. I don't see a problem
I'm not sure why you need the If(Not IsNull parts of the lines at the end of
the code. As long as CourseCode is not null, the code wil run and CourseNo
will not be null. Similarly, as long as CourseCode is longer than three
characters, intLetterPart will be 1 or greater, so strRubric will not be
null. Shorter versions should work:
Me![Rubric] = strRubric
Another point is that unless you have the same name for the control and the
field (I think), or are using a reserved word, you can use the dot (.)
rather than the bang (!). Makes coding a bit easier.
Me.Rubric = strRubric

Again, I don't see these as problems. It's more about convenience.

For you other question, this code should work for the command button:
DoCmd.GoToRecord , , acNewRec
What code is behind your button? Or are you describing the built-in new
record button?
You can open to a new record, if you wish. In the form's Open event:
Me.Recordset.AddNew

The situation you describe is ringing a bell, but I can't quite place it.
Is there any code in the form's Open event (or other event such as Activate
that runs before the form is ready to use)?

Bruce,

Your code worked, with minor modifications (had to add a paren, subtract
one, piddly stuff that Access prompted me to do, then add the code to
write
the two variables into the table).

I have even set the properties for the two generated fields so they don't
have tab stops and are color-coded (the user knows that green fields are
filled in automatically so she can see them but the cursor doesn't go
there).
I could remove them from the form altogether, but I think it serves as a
double-check for her to be able to see them without having to bounce
through
them.

There's just one tiny (but very annoying) problem. When I open the form,
CourseCode in the first record is highlighted. When you click the "add
record" button, nothing happens. However, if you press Enter, then you can
click the "add record" button and everything works great.

I know there's bound to be some little something that I'm doing wrong, but
I
am working nearly blind here. What do I need to do to avoid that extra
confusing step? I want the user to be able to open the form and click the
"add record" button without having to press Enter.

BTW, your input is GREATLY appreciated! Your solution seems very elegant
to
me.

liz




Here's what I'm using:

--------------

Private Sub CourseCode_Exit(Cancel As Integer)
Dim strCodeLength As String
Dim strRubric As String
Dim strCourseNo As String
Dim intLetterPart As Integer

If Not IsNull(Len(Me.CourseCode)) Then

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode, 4))) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strRubric = Left(Me.CourseCode, intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strRubric = Left(Me.CourseCode, intLetterPart)
End If

If (Not IsNull(strRubric)) Then Me![Rubric] = strRubric
If (Not IsNull(strCourseNo)) Then Me![CourseNo] = strCourseNo

End If

End Sub

------------

I posted a reply in that other thread, but I'm afraid it's not much help.

I have seen (and inherited) rather a number of databases that look nice,
but
that are chaotic behind the scenes and clunky to use. Some people are
impressed by bells and whistles, and will accept that, for instance, you
need to enter the employee's name over and over. As long as the
interface
has nice colors and pictures they're impressed.

I will continue to experiment with my parsing system. It seems to be an
unusual approach, which means either that I'm innovative or that there's
something wrong with it. I hope it's the former.

Bruce,

Thanks once again.

The non-scrolling continuous form problem is described in the Access
forms
area under the title "SECOND POST--continuous form scroll problem". Fly
in
the ointment, but frustrating when entering courses for a new student
because
only the course record you are entering is visible.

I will try your code in a little while. As far as I know, there are no
courses with anything less than three digits. LSU courses all have
four,
but
students do sometimes transfer courses from schools that use three
digits.
Thanks also for explaining the underlying logic behind the code.

This is a database that is used to track our department's graduate
students
and their programs of study. It's not used that much and it's not that
big
so
I'm not too worried about it not being perfectly normalized. I haven't
done
any programming in a long time, either. I'm just trying to get this
simple
system worked out so that the person who is using it is doing the
minimum
typing possible. The person who set up the database didn't have a clue
and
I've spent a fair amount of time reworking it but I'm just about at a
point
where much more work isn't worth the time and effort. We're just about
to
"good enough"...

Liz


:

Sometimes it is necessary to store data rather than linking to it. We
have
a situation where a name needs to be stored as it was when the record
was
created. Things like that come up sometimes. If a course description
is
variable you could have two or more descriptions for the course in the
Course table. A Yes/No field could activate the course that is being
used
at the moment, so that the source query for a combo box or list box
could
exclude records in which the box is not checked, or something like
that.
Or
you could store the information. I would prefer the first choice if
possible, but reworking an existing database could be troublesome.
As for combining two separate fields in a list box (or combo box) you
can
combine the fields in the row source query:
FullNumber: [DeptCode] & [CourseCode]
That sort of thing is done often for combining name fields into a full
name.

I have poked around a bit, and I know there is code for parsing a
letter/number string, but the variable lengths of the letter and
number
strings makes it more difficult.

Here is one thing I have played around with a bit. If the number
portion
(on the right end of CourseCode) is either three or four digits, you
could
do something like this in VBA (untested):

Dim strCodeLength as String
Dim strDeptCode as String
Dim strCourseNo as String
Dim intLetterPart as Integer

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode), 4)) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strDeptCode = Left(Me.CourseCode), intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strDeptCode = Left(Me.CourseCode, intLetterPart)
End If

The idea is that if the course code is HIST123, the rightmost 4
characters
are T123. By using Val, this evaluates to 0. In that case, the
length
of
the string minus 3 (in this case, 4) is used in the Left function;
otherwise
the length of the string minus 4 is used in the left function. This
works
only if the numeric portion of CourseCode is either 3 or 4. As I
said, I
haven't tested the code except in abbreviated fashion in an unbound
text
box, but I would like to do so when I have a little more time.

For the other part of your post, what is this about a non-scrolling
continuous form?

 

[Guest]

Bruce,

Thanks for all the tips. Much appreciated. As you can tell, I just don't
have the foundation I need to figure this stuff out.

I suspect that the checking-for-null had to do with my trying to get the
darned thing to let me add a new record. I finally figured out that I had to
press Enter and didn't remove the code.

There are no events associated with the form, but that's a good tip about
opening in add mode. I will try that.

Oh, I'm using the built-in add-record button at the bottom of the form (the
one down there with the record-forward, record-back buttons). I suppose I
could add a button of my own but the user is familiar with the regular Access
button.

liz

P.S. I surely wish I could figure out what is causing that continuous form
problem. I haven't taken the time to check to see what events might be
associated with the old form. The big difference between it and my form is
that the one and the many forms on my form are visible at the same time. On
the old form, there are two tabs (like tabs in file folders) and you click on
one tab to get to the one form and the other tab to get to the many form. But
that's a different question....

Glad to hear it worked. One observation is that this line:
If Not IsNull(Len(Me.CourseCode)) Then
could probably be
If IsNull(Me.CourseCode) Then
The Nz function can also be helpful:
If Nz(Len(Me.CourseCode),0) = 0 Then
Just mentioning these things. I don't see a problem
I'm not sure why you need the If(Not IsNull parts of the lines at the end of
the code. As long as CourseCode is not null, the code wil run and CourseNo
will not be null. Similarly, as long as CourseCode is longer than three
characters, intLetterPart will be 1 or greater, so strRubric will not be
null. Shorter versions should work:
Me![Rubric] = strRubric
Another point is that unless you have the same name for the control and the
field (I think), or are using a reserved word, you can use the dot (.)
rather than the bang (!). Makes coding a bit easier.
Me.Rubric = strRubric

Again, I don't see these as problems. It's more about convenience.

For you other question, this code should work for the command button:
DoCmd.GoToRecord , , acNewRec
What code is behind your button? Or are you describing the built-in new
record button?
You can open to a new record, if you wish. In the form's Open event:
Me.Recordset.AddNew

The situation you describe is ringing a bell, but I can't quite place it.
Is there any code in the form's Open event (or other event such as Activate
that runs before the form is ready to use)?

Bruce,

Your code worked, with minor modifications (had to add a paren, subtract
one, piddly stuff that Access prompted me to do, then add the code to
write
the two variables into the table).

I have even set the properties for the two generated fields so they don't
have tab stops and are color-coded (the user knows that green fields are
filled in automatically so she can see them but the cursor doesn't go
there).
I could remove them from the form altogether, but I think it serves as a
double-check for her to be able to see them without having to bounce
through
them.

There's just one tiny (but very annoying) problem. When I open the form,
CourseCode in the first record is highlighted. When you click the "add
record" button, nothing happens. However, if you press Enter, then you can
click the "add record" button and everything works great.

I know there's bound to be some little something that I'm doing wrong, but
I
am working nearly blind here. What do I need to do to avoid that extra
confusing step? I want the user to be able to open the form and click the
"add record" button without having to press Enter.

BTW, your input is GREATLY appreciated! Your solution seems very elegant
to
me.

liz




Here's what I'm using:

--------------

Private Sub CourseCode_Exit(Cancel As Integer)
Dim strCodeLength As String
Dim strRubric As String
Dim strCourseNo As String
Dim intLetterPart As Integer

If Not IsNull(Len(Me.CourseCode)) Then

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode, 4))) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strRubric = Left(Me.CourseCode, intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strRubric = Left(Me.CourseCode, intLetterPart)
End If

If (Not IsNull(strRubric)) Then Me![Rubric] = strRubric
If (Not IsNull(strCourseNo)) Then Me![CourseNo] = strCourseNo

End If

End Sub

------------

I posted a reply in that other thread, but I'm afraid it's not much help.

I have seen (and inherited) rather a number of databases that look nice,
but
that are chaotic behind the scenes and clunky to use. Some people are
impressed by bells and whistles, and will accept that, for instance, you
need to enter the employee's name over and over. As long as the
interface
has nice colors and pictures they're impressed.

I will continue to experiment with my parsing system. It seems to be an
unusual approach, which means either that I'm innovative or that there's
something wrong with it. I hope it's the former.

"Dij" <dijatcox.net> wrote in message
Bruce,

Thanks once again.

The non-scrolling continuous form problem is described in the Access
forms
area under the title "SECOND POST--continuous form scroll problem". Fly
in
the ointment, but frustrating when entering courses for a new student
because
only the course record you are entering is visible.

I will try your code in a little while. As far as I know, there are no
courses with anything less than three digits. LSU courses all have
four,
but
students do sometimes transfer courses from schools that use three
digits.
Thanks also for explaining the underlying logic behind the code.

This is a database that is used to track our department's graduate
students
and their programs of study. It's not used that much and it's not that
big
so
I'm not too worried about it not being perfectly normalized. I haven't
done
any programming in a long time, either. I'm just trying to get this
simple
system worked out so that the person who is using it is doing the
minimum
typing possible. The person who set up the database didn't have a clue
and
I've spent a fair amount of time reworking it but I'm just about at a
point
where much more work isn't worth the time and effort. We're just about
to
"good enough"...

Liz


:

Sometimes it is necessary to store data rather than linking to it. We
have
a situation where a name needs to be stored as it was when the record
was
created. Things like that come up sometimes. If a course description
is
variable you could have two or more descriptions for the course in the
Course table. A Yes/No field could activate the course that is being
used
at the moment, so that the source query for a combo box or list box
could
exclude records in which the box is not checked, or something like
that.
Or
you could store the information. I would prefer the first choice if
possible, but reworking an existing database could be troublesome.
As for combining two separate fields in a list box (or combo box) you
can
combine the fields in the row source query:
FullNumber: [DeptCode] & [CourseCode]
That sort of thing is done often for combining name fields into a full
name.

I have poked around a bit, and I know there is code for parsing a
letter/number string, but the variable lengths of the letter and
number
strings makes it more difficult.

Here is one thing I have played around with a bit. If the number
portion
(on the right end of CourseCode) is either three or four digits, you
could
do something like this in VBA (untested):

Dim strCodeLength as String
Dim strDeptCode as String
Dim strCourseNo as String
Dim intLetterPart as Integer

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode), 4)) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strDeptCode = Left(Me.CourseCode), intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strDeptCode = Left(Me.CourseCode, intLetterPart)
End If

The idea is that if the course code is HIST123, the rightmost 4
characters
are T123. By using Val, this evaluates to 0. In that case, the
length
of
the string minus 3 (in this case, 4) is used in the Left function;
otherwise
the length of the string minus 4 is used in the left function. This
works
only if the numeric portion of CourseCode is either 3 or 4. As I
said, I
haven't tested the code except in abbreviated fashion in an unbound
text
box, but I would like to do so when I have a little more time.

For the other part of your post, what is this about a non-scrolling
continuous form?

 

[BruceM]

I don't know what to say about the command button, other than to suggest you
start a new thread in the forms group. This far down in the thread the
question may not get much attention.

For the continuous subform, same suggestion. I will have to say I am not
entirely clear about the nature of the problem. It would be best if it was
something like:
"When I add a new record to a continuous subform, I can only add it to the
bottom of the list. However, in another database [describe behavior]. I
can't figure out how to make my database behave like the other one."

Bruce,

Thanks for all the tips. Much appreciated. As you can tell, I just don't
have the foundation I need to figure this stuff out.

I suspect that the checking-for-null had to do with my trying to get the
darned thing to let me add a new record. I finally figured out that I had
to
press Enter and didn't remove the code.

There are no events associated with the form, but that's a good tip about
opening in add mode. I will try that.

Oh, I'm using the built-in add-record button at the bottom of the form
(the
one down there with the record-forward, record-back buttons). I suppose I
could add a button of my own but the user is familiar with the regular
Access
button.

liz

P.S. I surely wish I could figure out what is causing that continuous form
problem. I haven't taken the time to check to see what events might be
associated with the old form. The big difference between it and my form is
that the one and the many forms on my form are visible at the same time.
On
the old form, there are two tabs (like tabs in file folders) and you click
on
one tab to get to the one form and the other tab to get to the many form.
But
that's a different question....

Glad to hear it worked. One observation is that this line:
If Not IsNull(Len(Me.CourseCode)) Then
could probably be
If IsNull(Me.CourseCode) Then
The Nz function can also be helpful:
If Nz(Len(Me.CourseCode),0) = 0 Then
Just mentioning these things. I don't see a problem
I'm not sure why you need the If(Not IsNull parts of the lines at the end
of
the code. As long as CourseCode is not null, the code wil run and
CourseNo
will not be null. Similarly, as long as CourseCode is longer than three
characters, intLetterPart will be 1 or greater, so strRubric will not be
null. Shorter versions should work:
Me![Rubric] = strRubric
Another point is that unless you have the same name for the control and
the
field (I think), or are using a reserved word, you can use the dot (.)
rather than the bang (!). Makes coding a bit easier.
Me.Rubric = strRubric

Again, I don't see these as problems. It's more about convenience.

For you other question, this code should work for the command button:
DoCmd.GoToRecord , , acNewRec
What code is behind your button? Or are you describing the built-in new
record button?
You can open to a new record, if you wish. In the form's Open event:
Me.Recordset.AddNew

The situation you describe is ringing a bell, but I can't quite place it.
Is there any code in the form's Open event (or other event such as
Activate
that runs before the form is ready to use)?

Bruce,

Your code worked, with minor modifications (had to add a paren,
subtract
one, piddly stuff that Access prompted me to do, then add the code to
write
the two variables into the table).

I have even set the properties for the two generated fields so they
don't
have tab stops and are color-coded (the user knows that green fields
are
filled in automatically so she can see them but the cursor doesn't go
there).
I could remove them from the form altogether, but I think it serves as
a
double-check for her to be able to see them without having to bounce
through
them.

There's just one tiny (but very annoying) problem. When I open the
form,
CourseCode in the first record is highlighted. When you click the "add
record" button, nothing happens. However, if you press Enter, then you
can
click the "add record" button and everything works great.

I know there's bound to be some little something that I'm doing wrong,
but
I
am working nearly blind here. What do I need to do to avoid that extra
confusing step? I want the user to be able to open the form and click
the
"add record" button without having to press Enter.

BTW, your input is GREATLY appreciated! Your solution seems very
elegant
to
me.

liz




Here's what I'm using:

--------------

Private Sub CourseCode_Exit(Cancel As Integer)
Dim strCodeLength As String
Dim strRubric As String
Dim strCourseNo As String
Dim intLetterPart As Integer

If Not IsNull(Len(Me.CourseCode)) Then

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode, 4))) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strRubric = Left(Me.CourseCode, intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strRubric = Left(Me.CourseCode, intLetterPart)
End If

If (Not IsNull(strRubric)) Then Me![Rubric] = strRubric
If (Not IsNull(strCourseNo)) Then Me![CourseNo] = strCourseNo

End If

End Sub

------------



:

I posted a reply in that other thread, but I'm afraid it's not much
help.

I have seen (and inherited) rather a number of databases that look
nice,
but
that are chaotic behind the scenes and clunky to use. Some people are
impressed by bells and whistles, and will accept that, for instance,
you
need to enter the employee's name over and over. As long as the
interface
has nice colors and pictures they're impressed.

I will continue to experiment with my parsing system. It seems to be
an
unusual approach, which means either that I'm innovative or that
there's
something wrong with it. I hope it's the former.

"Dij" <dijatcox.net> wrote in message
Bruce,

Thanks once again.

The non-scrolling continuous form problem is described in the Access
forms
area under the title "SECOND POST--continuous form scroll problem".
Fly
in
the ointment, but frustrating when entering courses for a new
student
because
only the course record you are entering is visible.

I will try your code in a little while. As far as I know, there are
no
courses with anything less than three digits. LSU courses all have
four,
but
students do sometimes transfer courses from schools that use three
digits.
Thanks also for explaining the underlying logic behind the code.

This is a database that is used to track our department's graduate
students
and their programs of study. It's not used that much and it's not
that
big
so
I'm not too worried about it not being perfectly normalized. I
haven't
done
any programming in a long time, either. I'm just trying to get this
simple
system worked out so that the person who is using it is doing the
minimum
typing possible. The person who set up the database didn't have a
clue
and
I've spent a fair amount of time reworking it but I'm just about at
a
point
where much more work isn't worth the time and effort. We're just
about
to
"good enough"...

Liz


:

Sometimes it is necessary to store data rather than linking to it.
We
have
a situation where a name needs to be stored as it was when the
record
was
created. Things like that come up sometimes. If a course
description
is
variable you could have two or more descriptions for the course in
the
Course table. A Yes/No field could activate the course that is
being
used
at the moment, so that the source query for a combo box or list box
could
exclude records in which the box is not checked, or something like
that.
Or
you could store the information. I would prefer the first choice
if
possible, but reworking an existing database could be troublesome.
As for combining two separate fields in a list box (or combo box)
you
can
combine the fields in the row source query:
FullNumber: [DeptCode] & [CourseCode]
That sort of thing is done often for combining name fields into a
full
name.

I have poked around a bit, and I know there is code for parsing a
letter/number string, but the variable lengths of the letter and
number
strings makes it more difficult.

Here is one thing I have played around with a bit. If the number
portion
(on the right end of CourseCode) is either three or four digits,
you
could
do something like this in VBA (untested):

Dim strCodeLength as String
Dim strDeptCode as String
Dim strCourseNo as String
Dim intLetterPart as Integer

strCodeLength = Len(Me.CourseCode)

If (Val(Right(Me.CourseCode), 4)) = 0 Then
intLetterPart = Len(Me.CourseCode) - 3
strCourseNo = Right(Me.CourseCode, 3)
strDeptCode = Left(Me.CourseCode), intLetterPart)
Else
intLetterPart = Len(Me.CourseCode) - 4
strCourseNo = Right(Me.CourseCode, 4)
strDeptCode = Left(Me.CourseCode, intLetterPart)
End If

The idea is that if the course code is HIST123, the rightmost 4
characters
are T123. By using Val, this evaluates to 0. In that case, the
length
of
the string minus 3 (in this case, 4) is used in the Left function;
otherwise
the length of the string minus 4 is used in the left function.
This
works
only if the numeric portion of CourseCode is either 3 or 4. As I
said, I
haven't tested the code except in abbreviated fashion in an unbound
text
box, but I would like to do so when I have a little more time.

For the other part of your post, what is this about a non-scrolling
continuous form?