Continous Form Vertical Scrolling Problem

A

alhollis21

I am fairly knowledgeable at DB development and have developed dozens
but this problem is aggravating. I have a main form with a continuous
subform within it. I do not allow users to add new records directly
into the subform but by using insert statements from the main form
selections I "insert" records into a table and refresh the form to
show the new corresponding records in the subform and it all works
just fine. However, when the records begin to flow beyond the bottom
of the allotted height of the subform, the subform does not recognize
the user cannot see all corresponding records and the need to scroll
vertically. I have changed multiple properties to try to combat the
issue but to no avail. Is anyone familiar with the problem?

Thanks in advance.
 
D

Dirk Goldgar

alhollis21 said:
I am fairly knowledgeable at DB development and have developed dozens
but this problem is aggravating. I have a main form with a continuous
subform within it. I do not allow users to add new records directly
into the subform but by using insert statements from the main form
selections I "insert" records into a table and refresh the form to
show the new corresponding records in the subform and it all works
just fine. However, when the records begin to flow beyond the bottom
of the allotted height of the subform, the subform does not recognize
the user cannot see all corresponding records and the need to scroll
vertically. I have changed multiple properties to try to combat the
issue but to no avail. Is anyone familiar with the problem?
Click to expand...


What version of Access are you using, and what code are you using to add
records and refresh the form? When I use code like this in a simple test
main-form/subform arrangement, the subform's scroll bar updates
appropriately.

Private Sub cmdAddRecord_Click()

CurrentDb.Execute _
"INSERT INTO tSub (MainID, SubValue) " & _
"VALUES(" & Me.ID & ", 'foo')", _
dbFailOnError

Me.tSub.Requery

End Sub

You do have the subform's Scroll Bars property set to Both or to Vertical,
right?
 
L

Lemmen

alhollis21 said:
I am fairly knowledgeable at DB development and have developed dozens
but this problem is aggravating. I have a main form with a continuous
subform within it. I do not allow users to add new records directly
into the subform but by using insert statements from the main form
selections I "insert" records into a table and refresh the form to
show the new corresponding records in the subform and it all works
just fine. However, when the records begin to flow beyond the bottom
of the allotted height of the subform, the subform does not recognize
the user cannot see all corresponding records and the need to scroll
vertically. I have changed multiple properties to try to combat the
issue but to no avail. Is anyone familiar with the problem?

Thanks in advance.
Click to expand...
 
A

alhollis21

What version of Access are you using, and what code are you using to add
records and refresh the form?  When I use code like this in a simple test
main-form/subform arrangement, the subform's scroll bar updates
appropriately.

    Private Sub cmdAddRecord_Click()

        CurrentDb.Execute _
            "INSERT INTO tSub (MainID, SubValue) " & _
                    "VALUES(" & Me.ID & ", 'foo')", _
            dbFailOnError

        Me.tSub.Requery

    End Sub

You do have the subform's Scroll Bars property set to Both or to Vertical,
right?
Click to expand...

Here is the code used in the form to add records to the subform.

Private Sub cmdAddTest_Click()
Dim curRecord As Integer

On Error GoTo Err_cmdAddTest_Click

curRecord = Me.CurrentRecord

'Must force a record save to ensure data integrity of the new record
DoCmd.RunCommand acCmdSaveRecord


If RTrim(Me.TestConditions.Column(1)) = "Special Test" Then
'insert new test conditions for this test from special tests
pubReliabilityDBCon.Execute ("INSERT INTO dbo.tblReliabilityTests
(ReliabilityRequestID, TestTypeID, TestConditions, NoOfSamples,
TestDuration) " & _
"VALUES (" &
Me.ReliabilityRequestID.value & "," & Me.SpecialTests.Column(4) & ","
& Me.SpecialTests.Column(0) & "," & Me.SampleNo.value & ",'" &
Me.txtDuration.value & "')")

Else
'insert new test conditions for this test from standard tests
pubReliabilityDBCon.Execute ("INSERT INTO dbo.tblReliabilityTests
(ReliabilityRequestID, TestTypeID, TestConditions, NoOfSamples,
TestDuration) " & _
"VALUES (" &
Me.ReliabilityRequestID.value & "," & Me.TestConditions.Column(4) &
"," & Me.TestConditions.Column(0) & "," & Me.SampleNo.value & ",'" &
Me.txtDuration.value & "')")
End If

Me.Requery

DoCmd.GoToRecord acDataForm, "frmReliabilityRequests", acGoTo,
curRecord

Exit Sub

Err_cmdAddTest_Click:
If Err = 91 Then
MsgBox "Re-establishing link to SQL server"
If LinkSQL Then
Resume
End If
Else
Debug.Print vbTab & "Error Occurred: " & Err.Description
Resume Next
End If
End Sub

I don't have problems with the vertical bar showing, I can even
control that myself by a count of records in the subform but for some
reason the form believes it is taller than it is because the scroll
down arrow is not visible and even if it was the scrolling bar will
not move up or down like it has room to move. I can snap some screen
shots but do now see a place to post an attachment here. If you email
me directly I can attach a screen shot or two.

Thanks.
 
D

Dirk Goldgar

Hmm. I see some things in your posted code that lead to questions.

Is this an .mdb or .accdb with linked SQL Server tables, or is it an .adp?

Is the object pubReliabilityDBCon an ADO Connection object?

If pubReliabilityDBCon is an ADO Connection object, how did you initially
set it? Did you set it from CurrentProject.Connection or .AccessConnection,
or did you open the connection using a connection string? Please post the
code -- you can mung any credentials or sensitive information that might
appear in the connection string.

Where is this command button, "cmdAddTest", on the main form or on the
subform?

What is the name of the subform control (on the main form)?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


What version of Access are you using, and what code are you using to add
records and refresh the form? When I use code like this in a simple test
main-form/subform arrangement, the subform's scroll bar updates
appropriately.

Private Sub cmdAddRecord_Click()

CurrentDb.Execute _
"INSERT INTO tSub (MainID, SubValue) " & _
"VALUES(" & Me.ID & ", 'foo')", _
dbFailOnError

Me.tSub.Requery

End Sub

You do have the subform's Scroll Bars property set to Both or to Vertical,
right?
Click to expand...

Here is the code used in the form to add records to the subform.

Private Sub cmdAddTest_Click()
Dim curRecord As Integer

On Error GoTo Err_cmdAddTest_Click

curRecord = Me.CurrentRecord

'Must force a record save to ensure data integrity of the new record
DoCmd.RunCommand acCmdSaveRecord


If RTrim(Me.TestConditions.Column(1)) = "Special Test" Then
'insert new test conditions for this test from special tests
pubReliabilityDBCon.Execute ("INSERT INTO dbo.tblReliabilityTests
(ReliabilityRequestID, TestTypeID, TestConditions, NoOfSamples,
TestDuration) " & _
"VALUES (" &
Me.ReliabilityRequestID.value & "," & Me.SpecialTests.Column(4) & ","
& Me.SpecialTests.Column(0) & "," & Me.SampleNo.value & ",'" &
Me.txtDuration.value & "')")

Else
'insert new test conditions for this test from standard tests
pubReliabilityDBCon.Execute ("INSERT INTO dbo.tblReliabilityTests
(ReliabilityRequestID, TestTypeID, TestConditions, NoOfSamples,
TestDuration) " & _
"VALUES (" &
Me.ReliabilityRequestID.value & "," & Me.TestConditions.Column(4) &
"," & Me.TestConditions.Column(0) & "," & Me.SampleNo.value & ",'" &
Me.txtDuration.value & "')")
End If

Me.Requery

DoCmd.GoToRecord acDataForm, "frmReliabilityRequests", acGoTo,
curRecord

Exit Sub

Err_cmdAddTest_Click:
If Err = 91 Then
MsgBox "Re-establishing link to SQL server"
If LinkSQL Then
Resume
End If
Else
Debug.Print vbTab & "Error Occurred: " & Err.Description
Resume Next
End If
End Sub

I don't have problems with the vertical bar showing, I can even
control that myself by a count of records in the subform but for some
reason the form believes it is taller than it is because the scroll
down arrow is not visible and even if it was the scrolling bar will
not move up or down like it has room to move. I can snap some screen
shots but do now see a place to post an attachment here. If you email
me directly I can attach a screen shot or two.

Thanks.
 
D

Dirk Goldgar

(Re-posting, as my original reply doesn't seem to have appeared.)

Hmm. I see some things in your posted code that lead to questions.

Is this an .mdb or .accdb with linked SQL Server tables, or is it an .adp?

Is the object pubReliabilityDBCon an ADO Connection object?

If pubReliabilityDBCon is an ADO Connection object, how did you initially
set it? Did you set it from CurrentProject.Connection or .AccessConnection,
or did you open the connection using a connection string? Please post the
code -- you can mung any credentials or sensitive information that might
appear in the connection string.

Where is this command button, "cmdAddTest", on the main form or on the
subform?

What is the name of the subform control (on the main form)?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


What version of Access are you using, and what code are you using to add
records and refresh the form? When I use code like this in a simple test
main-form/subform arrangement, the subform's scroll bar updates
appropriately.

Private Sub cmdAddRecord_Click()

CurrentDb.Execute _
"INSERT INTO tSub (MainID, SubValue) " & _
"VALUES(" & Me.ID & ", 'foo')", _
dbFailOnError

Me.tSub.Requery

End Sub

You do have the subform's Scroll Bars property set to Both or to Vertical,
right?
Click to expand...

Here is the code used in the form to add records to the subform.

Private Sub cmdAddTest_Click()
Dim curRecord As Integer

On Error GoTo Err_cmdAddTest_Click

curRecord = Me.CurrentRecord

'Must force a record save to ensure data integrity of the new record
DoCmd.RunCommand acCmdSaveRecord


If RTrim(Me.TestConditions.Column(1)) = "Special Test" Then
'insert new test conditions for this test from special tests
pubReliabilityDBCon.Execute ("INSERT INTO dbo.tblReliabilityTests
(ReliabilityRequestID, TestTypeID, TestConditions, NoOfSamples,
TestDuration) " & _
"VALUES (" &
Me.ReliabilityRequestID.value & "," & Me.SpecialTests.Column(4) & ","
& Me.SpecialTests.Column(0) & "," & Me.SampleNo.value & ",'" &
Me.txtDuration.value & "')")

Else
'insert new test conditions for this test from standard tests
pubReliabilityDBCon.Execute ("INSERT INTO dbo.tblReliabilityTests
(ReliabilityRequestID, TestTypeID, TestConditions, NoOfSamples,
TestDuration) " & _
"VALUES (" &
Me.ReliabilityRequestID.value & "," & Me.TestConditions.Column(4) &
"," & Me.TestConditions.Column(0) & "," & Me.SampleNo.value & ",'" &
Me.txtDuration.value & "')")
End If

Me.Requery

DoCmd.GoToRecord acDataForm, "frmReliabilityRequests", acGoTo,
curRecord

Exit Sub

Err_cmdAddTest_Click:
If Err = 91 Then
MsgBox "Re-establishing link to SQL server"
If LinkSQL Then
Resume
End If
Else
Debug.Print vbTab & "Error Occurred: " & Err.Description
Resume Next
End If
End Sub

I don't have problems with the vertical bar showing, I can even
control that myself by a count of records in the subform but for some
reason the form believes it is taller than it is because the scroll
down arrow is not visible and even if it was the scrolling bar will
not move up or down like it has room to move. I can snap some screen
shots but do now see a place to post an attachment here. If you email
me directly I can attach a screen shot or two.

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Main form/Subform Navigation 0
Continous Forms and preventing a new record being created 2
Problem with form--subform 5
Field referencing - Continous Form 1
How many lines are in a continuous form 2
Mouse wheel 1
autonumber PK scrolling problem 6
Link Two Subforms on a Main Form 1

Top