Solved

Form On Load and DataControl

Posted on 1998-10-02
15
274 Views
Last Modified: 2013-12-25
Hello experts,

When I load my form, I code the data control to move to the last record and then the first record. To show the record count displayed in the taskbar. My problem is that each time the form loads I get a "no current record to display" message. I have read the help on the datacontrol and it specifically says that the data control is loaded before the form load event. So why am I getting this error?

Try this on the form load event

datControl.recordset.movelast
datControl.recordset.movefirst

Any help would be appreciated

Much Thanks
Fred
0
Comment
Question by:gosportlad
  • 4
  • 4
  • 2
  • +3
15 Comments
 
LVL 1

Expert Comment

by:sdbanks
ID: 1496587
One option is to put the code into the form_activate event instead of the form load event.  If you do this the datcontrols will be initialized completely.  If you only want this count refreshed when the form is loaded.  Put some code around the form activate so that the code does not execute each time the form is activated.
0
 

Author Comment

by:gosportlad
ID: 1496588
I get the same result, whether I put the code in the form activate or the form load event.
0
 
LVL 18

Expert Comment

by:deighton
ID: 1496589
'I duplicated your problem then fixed it like this

Private Sub Form_Activate()
   
    If Data1.Recordset.EOF Then
   
        MsgBox "No records"
       
    Else
   
        Data1.Recordset.MoveLast
        Data1.Recordset.MoveFirst
   
        MsgBox CStr(Data1.Recordset.RecordCount)
       
    End If
   
End Sub

0
 
LVL 2

Expert Comment

by:shchuka
ID: 1496590
Try doing this:

dim RecCount as long

dataControl.Refresh
RecCount = 0
if dataControl.RecordSet.RecordCount > 0 then
    dataControl.RecordSet.MoveLast
    dataControl.RecordSet.MoveFirst
    RecCount = dataControl.RecordSet.RecordCount
end if

refresh method explicitely tells data control to open the underlying recordset.
Secondly, you'll get this error even with the data control completely initialized, if there are no records in the recordset.

0
 
LVL 18

Expert Comment

by:deighton
ID: 1496591
You have an empty recordset, I couldn't get the data control to work in form load either.
0
 

Author Comment

by:gosportlad
ID: 1496592
Thanks for trying but the datacontrol **does** contain records that is why I cannot understand why the message comes up.


0
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1496593
Place in the first line:

example:

Private Sub Form_Load()
On Error Resume Next
'your code here
End Sub



0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Expert Comment

by:a111a111a111
ID: 1496594
Try this:


Private Sub Form_Load()

On Error Resume Next

Data1.Refresh

Data1.Recordset.MoveLast
Data1.Recordset.MoveFirst

MsgBox Data1.Recordset.RecordCount

End Sub


If you need me to email you the complete project email to shayplace@hotmail.com
0
 

Author Comment

by:gosportlad
ID: 1496595
Thanks to shchuka for the comment,

I needed to refresh the datacontrol each time, *before* I moved the recordset. If shchuka will post his comment as an answer I'll award the points. Thanks to all who helped. The complete form load event now looks like this.

Private Sub Form_Load
   
    Me.Left = GetSetting(App.Title, "Settings", "MainLeft", 1000)
    Me.Top = GetSetting(App.Title, "Settings", "MainTop", 1000)
    Me.Width = GetSetting(App.Title, "Settings", "MainWidth", 7500)
    Me.Height = GetSetting(App.Title, "Settings", "MainHeight", 6500)
   
On Error GoTo Form_Activate_ErrorHandler

'-------------------- Code Body Starts --------------------
   
    dat1.Refresh 'added thanks to shchuka
    dat1.Recordset.MoveLast
    dat1.Recordset.MoveFirst
   
    Do While Not dat1.Recordset.EOF
    If Not IsNull(dat1.Recordset(0)) Then
        cbo1.AddItem dat1.Recordset(0)
    End If
        dat1.Recordset.MoveNext
    Loop
   
    dat2.Refresh 'added thanks to shchuka
    Do While Not dat2.Recordset.EOF
    If Not IsNull(dat2.Recordset(1)) Then
        cbo2.AddItem dat2.Recordset(1)
    End If

    If Not IsNull(dat2.Recordset(2)) Then
        cbo3.AddItem dat2.Recordset(2)
    End If

    If Not IsNull(dat2.Recordset(3)) Then
        cbo3.AddItem dat2.Recordset(3)
    End If

        dat2.Recordset.MoveNext
    Loop
   
    dat1.Recordset.MoveFirst
   
    Call StatusBarUpdate
    Call UpdateRecords
       
'--------------------- Code Body Ends ---------------------
       Exit Sub
 
   
Form_Activate_ErrorHandler:

    Select Case DisplayMessage(M3021_NO_CURRENT_RECORD, 0, MSG_SYS, 3, , , , " Form_Load ")
    Case vbAbort
      End
    Case vbRetry
      Resume
    Case vbIgnore
      Resume Next
   End Select
End Sub
0
 
LVL 3

Expert Comment

by:TheAnswerMan
ID: 1496596
To speed things along, you should not do the Row Count this way.

Do This instead.
Dim lr_resultset as rdoresultset
dim ls_SQL as string
ls_SQL = "SELECT COUNT(*) FROM TABLKNAME" _
    &  " SELECT * FROM TableName"
set lr_resultset = rdoconnect.OpenResultset(ls_SQL,rdopenstatic)
Label1 = "Count = " & lr_resultset(0) 'label or whatever
lr_resultset.moreresults
set DATA1.Resultset = lr_resultset


and you are off..  you will really notice a big difference when
it is a larger resultset.



0
 
LVL 3

Expert Comment

by:TheAnswerMan
ID: 1496597
Use the NextRecordset Sub of the Resultset object
0
 
LVL 2

Expert Comment

by:shchuka
ID: 1496598
Apparently, I can't report my comments as an answer, since somebody already locked the question :)

-Aleks.
0
 

Author Comment

by:gosportlad
ID: 1496599
Try it now
0
 
LVL 2

Accepted Solution

by:
shchuka earned 50 total points
ID: 1496600
Ok, I'm just reposting my comments :)
Thanks :)



      dim RecCount as long

      dataControl.Refresh
      RecCount = 0
      if dataControl.RecordSet.RecordCount > 0 then
          dataControl.RecordSet.MoveLast
          dataControl.RecordSet.MoveFirst
          RecCount = dataControl.RecordSet.RecordCount
      end if

      refresh method explicitely tells data control to open the underlying recordset.
      Secondly, you'll get this error even with the data control completely initialized, if there are
      no records in the recordset.
0
 
LVL 2

Expert Comment

by:shchuka
ID: 1496601
Thanks :)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now