subform sizing problem?

[Guest]

I have a form that displays a single record at a time, info for one student.

I have a subform (continuous) that displays all the courses that a student
has taken or will take.

If a student has 20 courses on his/her degree program, I can see all 20 on
the subform.

However, if I enter a new student's record, that student has no courses, so
when I start entering courses, I see only one at a time. Each time I enter a
new course, the previous one scrolls off the screen even though there is room
for the new record to be added below the existing one.

If I view a student with five course records, I see all five, but as I enter
new courses, I can only see five at a time.

It appears that I will only ever see as many courses as the student had when
I access his/her record even though there's lots of room for more courses
below the existing records.

Does anyone have any idea what I can do to fix this so that new courses are
added at the bottom of the list and records don't scroll off the top of the
form until the subform area is full? This really makes data entry of new
students and courses difficult. I've seen a subform similar to mine where new
courses were added to the bottom without anything scrolling but I can't tell
what's different about mine.

 

[Guest]

Hi

You need to alter the form's properties. As an example you could set the
form to AutoResize = Yes, etc.

To select the subform - open the "main" form in design view so you can see
both main and sub. Click the square in the top left of the sub (a black
quare will appear) to select the sub. Open the properties box and go to the
format column.

Hope this helps

 

[Guest]

Wayne,

Thanks for your reply.

The properties for both the form and the subform were already set to Yes for
AutoResize. I even tried setting the property for the subform to No then
resetting it to Yes and nothing changed.

You'd think that Autoresize would be the answer, but it's not. Any more ideas?

Thanks,

liz

 

[Graham Mandeno]

Hi Liz

No idea, sorry, without some more info.

1. Are you able to scroll back up and thus see all the records that were
previously visible.

2. Do you have any code attached to your form, particularly in the area of
navigating to the new record? If so, can you please post it?

 

[Graham Mandeno]

BTW, Autoresize affects only main forms, not subforms.

 

[Guest]

Graham,

I can navigate back up to see the other records, but I have to use Tab or a
cursor key. The cursor keys act like Tab or Shift-Tab. That is, if I press
the up arrow, I go to the next field to the left, not to the previous record.

Here's something that may be a clue. Whenever I access a new main record,
the vertical scroll bar on the subform is the full height of the subform and
that never changes.

There's something else that's strange. When I type the data for the first
new record and then exit from the record (by just tabbing through the last
couple of fields), instead of the cursor going to a new blank record, it goes
back to the first field in the first record on the screen.

I have events on the subform AfterUpdate and After DelConfirm. (Those are
the ones for updating the Update date on the main form that you just helped
me with and the subform was misbehaving before those were in place.) I also
have an On Exit event for the first field on the subform (SemesterCode) and
On Not in List and On Exit codes for the CourseCode (which you also helped me
with.) I don't think there's anything in there about navigating to a new
record.

Here's all the code associated with that form:

Private Sub cboSemesterCode_Exit(Cancel As Integer)
Dim varClassSemKey, varClassSemDesc As Variant
varClassSemKey = DLookup("[SemKey]", "[tblSemesters]", "[SemCode] = '" & _
[cboSemesterCode] & "'")
varClassSemDesc = DLookup("[SemDesc]", "[tblSemesters]", "[SemCode] = '" _
& [cboSemesterCode] & "'")
If (Not IsNull(varClassSemKey)) Then Me![ClassSemKey] = varClassSemKey
If (Not IsNull(varClassSemDesc)) Then Me![ClassSemDesc] = varClassSemDesc
varClassSemKey = ""
varClassSemDesc = ""
cboSemesterCode = ""
cboCourseCode = ""


End Sub

Private Sub cboCourseCode_NotInList(NewData As String, Response As Integer)
If MsgBox("That course is not in the list. " & _
"Would you like to add this course to the master course
database?", vbYesNo) = vbYes Then

'User said yes, open the form to add the new course...
DoCmd.OpenForm "frm2CourseMaster", , , , acFormAdd, acDialog,
NewData

'User didn't actually add the record
'If IsNull(DLookup("CourseID", "tblCourseMaster", "CourseID
= """ & _
NewData & """")) Then
'Response = acDataErrContinue
'Else
'User added a record and the combo box will show the new value
Response = acDataErrAdded

'End If
Else
Response = acDataErrContinue
End If


End Sub


Private Sub cboCourseCode_Exit(Cancel As Integer)
Dim varRubric, varCourseNo, varCourseTitle, varCredit As Variant
varRubric = DLookup("[Rubric]", "[tblCourseMaster]", "[CourseCode] = '"
& _
[cboCourseCode] & "'")
varCourseNo = DLookup("[CourseNo]", "[tblCourseMaster]", "[CourseCode] =
'"_
& [cboCourseCode] & "'")
varCourseTitle = DLookup("[CourseTitle]", "[tblCourseMaster]",
"[CourseCode] _
= '" & [cboCourseCode] & "'")
varCredit = DLookup("[Credit]", "[tblCourseMaster]", "[CourseCode] = '"
& _
[cboCourseCode] & "'")

If (Not IsNull(varRubric)) Then Me![CourseID] = [varRubric] & " " &
[varCourseNo]
If (Not IsNull(varCourseTitle)) Then Me![CourseTitle] = varCourseTitle
If (Not IsNull(varCredit)) Then Me![Credit] = varCredit

cboSemesterCode = ""
cboCourseCode = ""

End Sub

Private Sub frm2CourseMaster_Load()
If Not IsNull(Me.OpenArgs) Then
Me!CourseCode = Me.OpenArgs

End If
End Sub

Private Function UpdateChangeDate()
Dim strSQL As String
strSQL = "Update [tblStudents] set [Updated]=Date() " & "where [ID]='" &
Me!_
[ID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
End Function

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then UpdateChangeDate
End Sub

Private Sub Form_AfterUpdate()
UpdateChangeDate
End Sub

 

[Graham Mandeno]

Hi Liz

This is very curious! Usually, repositioning to the first record in the
form happens when the form is requeried, but I can't see anything that is
causing that to happen.

When you start to enter data for a new record, does a new, blank record with
a * in the record selector appear below?

I'll have a look at it if you like, provided your database is not too big to
email.
You could send it to me at ng1.g.mandeno at xoxy.net.

I see now what you are doing with the DLookups. This can be greatly
simplified if you base your form on a query joining all three tables, as I
suggested in the other post.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I can navigate back up to see the other records, but I have to use Tab or
a
cursor key. The cursor keys act like Tab or Shift-Tab. That is, if I press
the up arrow, I go to the next field to the left, not to the previous
record.

Here's something that may be a clue. Whenever I access a new main record,
the vertical scroll bar on the subform is the full height of the subform
and
that never changes.

There's something else that's strange. When I type the data for the first
new record and then exit from the record (by just tabbing through the last
couple of fields), instead of the cursor going to a new blank record, it
goes
back to the first field in the first record on the screen.

I have events on the subform AfterUpdate and After DelConfirm. (Those are
the ones for updating the Update date on the main form that you just
helped
me with and the subform was misbehaving before those were in place.) I
also
have an On Exit event for the first field on the subform (SemesterCode)
and
On Not in List and On Exit codes for the CourseCode (which you also helped
me
with.) I don't think there's anything in there about navigating to a new
record.

Here's all the code associated with that form:

Private Sub cboSemesterCode_Exit(Cancel As Integer)
Dim varClassSemKey, varClassSemDesc As Variant
varClassSemKey = DLookup("[SemKey]", "[tblSemesters]", "[SemCode] = '"
& _
[cboSemesterCode] & "'")
varClassSemDesc = DLookup("[SemDesc]", "[tblSemesters]", "[SemCode] =
'" _
& [cboSemesterCode] & "'")
If (Not IsNull(varClassSemKey)) Then Me![ClassSemKey] = varClassSemKey
If (Not IsNull(varClassSemDesc)) Then Me![ClassSemDesc] =
varClassSemDesc
varClassSemKey = ""
varClassSemDesc = ""
cboSemesterCode = ""
cboCourseCode = ""


End Sub

Private Sub cboCourseCode_NotInList(NewData As String, Response As
Integer)
If MsgBox("That course is not in the list. " & _
"Would you like to add this course to the master course
database?", vbYesNo) = vbYes Then

'User said yes, open the form to add the new course...
DoCmd.OpenForm "frm2CourseMaster", , , , acFormAdd, acDialog,
NewData

'User didn't actually add the record
'If IsNull(DLookup("CourseID", "tblCourseMaster", "CourseID
= """ & _
NewData & """")) Then
'Response = acDataErrContinue
'Else
'User added a record and the combo box will show the new
value
Response = acDataErrAdded

'End If
Else
Response = acDataErrContinue
End If


End Sub


Private Sub cboCourseCode_Exit(Cancel As Integer)
Dim varRubric, varCourseNo, varCourseTitle, varCredit As Variant
varRubric = DLookup("[Rubric]", "[tblCourseMaster]", "[CourseCode] = '"
& _
[cboCourseCode] & "'")
varCourseNo = DLookup("[CourseNo]", "[tblCourseMaster]", "[CourseCode]
=
'"_
& [cboCourseCode] & "'")
varCourseTitle = DLookup("[CourseTitle]", "[tblCourseMaster]",
"[CourseCode] _
= '" & [cboCourseCode] & "'")
varCredit = DLookup("[Credit]", "[tblCourseMaster]", "[CourseCode] = '"
& _
[cboCourseCode] & "'")

If (Not IsNull(varRubric)) Then Me![CourseID] = [varRubric] & " " &
[varCourseNo]
If (Not IsNull(varCourseTitle)) Then Me![CourseTitle] = varCourseTitle
If (Not IsNull(varCredit)) Then Me![Credit] = varCredit

cboSemesterCode = ""
cboCourseCode = ""

End Sub

Private Sub frm2CourseMaster_Load()
If Not IsNull(Me.OpenArgs) Then
Me!CourseCode = Me.OpenArgs

End If
End Sub

Private Function UpdateChangeDate()
Dim strSQL As String
strSQL = "Update [tblStudents] set [Updated]=Date() " & "where [ID]='" &
Me!_
[ID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
End Function

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then UpdateChangeDate
End Sub

Private Sub Form_AfterUpdate()
UpdateChangeDate
End Sub

 

[Guest]

Graham,

Since this is student info, I'm going to have to "sanitize" the database.
I'll delete most of the student records and substitute some dummy IDs and
names (you'll be seeing Bill the Cat and Opus the Penguin, among others. It
will probably be sometime tomorrow before I get that done. I REALLY
appreciate all your help!

liz