I am getting runtime error 3021 - Either EOF or BOF is true or the current record has been deleted.....
I have 2 combo boxes in a form- One for the client and the other for the project.
When the form loads, the client combo box gets populated with all the clients.
When a particular client is clicked, the project combo box gets populated with all the projects corresponding to that client.
So far, so good. Now when I click a project in the project combo box, I get this runtime error.
Been stuck here for 2 days without a solution. Please help.
This is the code I have - Private Sub Form_Load()
-
-
Call SetupADO
-
Call SetupClients
-
-
End sub
-
-
-
Private Sub SetupADO()
-
-
Dim adoParm As ADODB.Parameter
-
'Create connection object and connect to database
-
Set acnChecklist = New ADODB.Connection
-
-
With acnChecklist
-
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\My Documents\Specifications\Checklist1.mdb;Persist Security Info=False"
-
.Open
-
.CursorLocation = adUseClient
-
End With
-
-
-
' Setup the clients command and record set
-
Set acmClients = New ADODB.Command
-
With acmClients
-
.ActiveConnection = acnChecklist
-
.CommandType = adCmdText
-
.CommandText = "SELECT ClientID, ClientName from tblClients"
-
End With
-
-
Set arsClients = Nothing
-
-
' Setup the projects command and record set
-
Set acmProjects = New ADODB.Command
-
With acmProjects
-
.ActiveConnection = acnChecklist
-
.CommandType = adCmdText
-
.CommandText = "SELECT ProjectID, ProjectName FROM tblProject WHERE ClientID= ?"
-
Set adoParm = .CreateParameter("ClientID", adNumeric, adParamInput, , 1)
-
.Parameters.Append adoParm
-
End With
-
-
End sub
-
-
Populate the Clients Combo box
-
Private Sub SetupClients()
-
'Execute Query
-
Set arsClients = New ADODB.Recordset
-
arsClients.Open acmClients, , adOpenStatic, adLockOptimistic
-
-
' Populate combo-box with query results
-
cboClients.Clear
-
While Not arsClients.EOF
-
cboClients.AddItem arsClients!ClientName
-
arsClients.MoveNext
-
Wend
-
cboClients.Text = ""
-
cboProjects.Text = ""
-
-
End sub
-
-
' Handle change in client selection
-
Private Sub cboClients_Click()
-
Dim strClientName As String
-
Dim iClientID As Integer
-
-
' If nothing selected, then do nothing
-
If cboClients.Text = "" Then
-
Exit Sub
-
End If
-
-
strClientName = cboClients.Text
-
-
arsClients.Find "ClientName= '" & strClientName & "'", , adSearchForward, adBookmarkFirst
-
iClientID = arsClients("ClientID")
-
-
Call SetupProjects(iClientID)
-
-
End Sub
-
-
-
'Populate the projects combo box based on the selected client
-
Private Sub SetupProjects(ByRef iClientID As Integer)
-
-
Call acmProjects.Parameters.Delete("ClientID")
-
Dim adoParm As ADODB.Parameter
-
Set adoParm = acmProjects.CreateParameter("ClientID", adNumeric, adParamInput, , iClientID)
-
Call acmProjects.Parameters.Append(adoParm)
-
-
If arsProjects Is Nothing Then
-
Set arsProjects = New ADODB.Recordset
-
arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
-
Else
-
arsProjects.Requery
-
End If
-
-
cboProjects.Clear
-
While Not arsProjects.EOF
-
cboProjects.AddItem arsProjects!ProjectName
-
arsProjects.MoveNext
-
Wend
-
-
End Sub
-
-
-
Private Sub cboProjects_Click()
-
Dim strProjectName As String
-
Dim iProjectID As Long
-
-
If cboProjects.Text = "" Then
-
Exit Sub
-
End If
-
strProjectName = cboProjects.Text
-
If arsProjects Is Nothing Then
-
Set arsProjects = New ADODB.Recordset
-
arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
-
Else
-
arsProjects.Requery
-
End If
-
With arsProjects
-
If .BOF And .EOF Then
-
.Requery
-
.MoveFirst
-
End If
-
End With
-
-
arsProjects.Find "ProjectName='" & strProjectName & "'", adSearchForward, adBookmarkFirst
-
iProjectID = arsProjects("ProjectID") ------> Error in this line
End Sub
Hope u guys can bail me out.
7 31331
Dear need not to post entire code here just put the code which have th error
use - if Rs0.eof or rs0.bof then
-
'msgbox "No mor records found"
-
else
-
' put ur code here
-
end if
where Rs0 is the Recordset
try it
Good Luck
Have you tried stepping through the cboProjects_Click routine, checking the effects on arsProjects at every point?
Also, I find it odd how you are doing the "If BOF and EOF then ReQuery and MoveFirst" just before the end of this code. Just prior to that, you have either created or requeried arsProjects. So if this condition is true, doesn't that mean the dataset is empty? In which case, another requery doesn't seem as though it will help.
- if Rs0.eof or rs0.bof then
-
'msgbox "No mor records found"
-
else
-
' put ur code here
-
end if
Wouldn't this prevent your code ever being executed? Presumably we'd be starting at the beginning, so BOF would be true.
Thanks for ur time guys.
Strangely I get the runtime error only while clicking on the first project in the combobox, and not while clicking the second or third or fourth project or any subsequent projects. Why is this so?
Also, after Recordset.requery, I am able to print all the records(projects) in
the recordset. It means that the recordset is not empty, isn't it? Then why is it that I am getting the error only while clicking on the first project in the combo box. Is it due to the parametrized SQL statement that I am using to access the projects table?
Help please...
this is the solution to runtime r.eof or r.bof here r is the recordset - do until r.eof
-
if r.eof=true then
-
r.movelast
-
end if
-
r.movenext
-
loop
for bof - do until r.bof
-
if r.bof = true then
-
r.movefirst
-
end if
-
r.moveprevious
-
loop
by sudheer
in code below
when i goto new record and use mouse wheel
i have a runtime error 3021 - Public CurrentBookmark As String
-
Public MouseWheelMoved As Boolean
-
-
Private Sub Form_Current()
-
If MouseWheelMoved = True Then
-
Me.Bookmark = CurrentBookmark
-
End If
-
MouseWheelMoved = False
-
End Sub
-
-
Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
-
CurrentBookmark = Me.Bookmark
-
MouseWheelMoved = True
-
MsgBox "cannot use mouse wheel"
-
End Sub
-
-
please help?
hey any one can help
i found th 70% of soulotion
but when goto new record and move up it work but when move down not work
why??
please need help?? - Public CurrentBookmark As String
-
Public MouseWheelMoved As Boolean
-
-
Private Sub Form_Current()
-
on error resume next
-
If MouseWheelMoved = True Then
-
Me.Bookmark = CurrentBookmark
-
End If
-
MouseWheelMoved = False
-
End Sub
-
-
Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
-
on error resume next
-
CurrentBookmark = Me.Bookmark
-
MouseWheelMoved = True
-
MsgBox "cannot use mouse wheel"
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Polly |
last post by:
I'm trying to write the results of a query, a name, ID number, and
date
out to a notepad .txt file to
print on a "legacy" printer.
I get the output from the first 2 "write" lines over the...
|
by: Jamey |
last post by:
I perused old posts for an answer to this for at least an hour, and
I've found a work-around, but no definitive answer.
Synopsis of the problem:
On NotInList or ctl.Requery commands where a...
|
by: jwa6 |
last post by:
I get an runtime error w/ this code
If Me!Obj = 5100 Then
runtime error 13
type mismatch
what is this testing for? IE: 5100
jim
|
by: Aleksandar |
last post by:
Hi,
I need to convert set of Java classes exported from IBM Modeling environment
to C# for implementation.
When I invoke JCLA conversion from File->Open->Convert in Visual Studio 2005
it starts...
|
by: darrel |
last post by:
Hi there, can someone tell me what is wrong with my code am getting a run time error 3021:
Here my code:
If rs.State = adStateOpen Then rs.Close
rs.Open "Select * from where ID like...
|
by: jmarcrum |
last post by:
i need some help
i have a table with 5 city divisions
1 = D1
2 = D2
3 = D3
4 = D4
and
|
by: bssandeshbs |
last post by:
I am developing a Address Book Database Project using Visual Basic 6...
When i click the delete button the data does'nt get deleted in the FrontEnd ..But it gets deleted in Database when we see in...
|
by: rudivs |
last post by:
I would like to do data validation in the BeforeUpdate procedure, but Access gives me a runtime error when I try to do this:
An example of what I am trying to do is as follows:
Private Sub...
|
by: Ferwayne Yalung |
last post by:
The code works when I logged in as a admin, teacher or student.But when i try an unregistered username, runtime error 3021 (either BOF or EOF is true, or the current record
has been deleted....
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
| | |