?
Solved

Why is my recordset skipping values?

Posted on 2003-03-26
17
Medium Priority
?
203 Views
Last Modified: 2013-11-25
OK guys, you did a GREAT job helping me a couple of weeks ago, and I need your help once again!  I've got two modules that perform the same functions on two different tables.  One of them works great - the other has exactly the same code and doesn't.  I know the code's the same because I pasted the working code into the new module and changed the names.  I've actually done this twice, and both times have had a bizarre problem.

This is what's supposed to happen:  In each table are two fields per record.  The index goes from 01 to 99, while the other field has a user-inputted string.  The user fills the fields, but I need to restrict them to adding data at the first available slot - not just add information wherever they choose.  To control this, I created the table with the string "---Empty---" where there user inputs goes.  So to start with, every record has a field with a sequential number in it, and a field with the string "---Empty---" in it.  When the form opens, a listbox displays all the values, and using a recordset, the user is restricted to enter a text string ONLY in the first record with an "---Empty---" in it.  So if record 01 has the "---Empty---", the user may add to that one.  If 04 is the first "---Empty---" the user may only add to that one.  This method worked very well in the first module I wrote.

OK, so what's happening, now?  Well, like I said above, I copied the code and form from the working module and changed the names where needed.  All of the names and references match between the form and code.  When I open the form, it properly restricts me to record 01 since it's empty.  When I modify it, the table is updated correctly, but instead of restricting me to 02 for my next input, it jumps to 03.  If I enter a value in 03, it goes to 04 (just as it should).  If I continue sequentially, it will generally see that 02 contains "---Empty---" around the time I get to record 05.  So - what in the world am I missing here?  I'm guessing that the recordset reference is throwing it off, but I'll be damned if I can see where.  I also can't see where it could be pulling a recordset twice (and thus skipping a good value).  Here's my code:

Option Compare Database
Private Sub Form_Open(Cancel As Integer)
Me!List22.RowSource = PartFile
Me!Label28.Caption = CustomerName
Me!Label128.Caption = ProgramD
Dim RstBase, Rst, Rst2, Rst3, Rst4, Rst5 As DAO.Recordset
Set RstBase = CurrentDb.OpenRecordset("SELECT * FROM " & PartFile & " WHERE [Part_Description] = '---Empty---'")
   RstBase.FindFirst (Part_Description = "---Empty---")
   Let NewNumber.Value = RstBase![Part_Number]
   Let NewDesc.Value = Nothing
   Let List22.Value = NewNumber.Value
Set RstBase = Nothing
End Sub


Private Sub Create_Button_Click()
   Dim S1 As String
   Dim GoNoGo As Integer
   ' The first recordset finds the first "---Empty---" record
   Set Rst = CurrentDb.OpenRecordset("SELECT * FROM " & PartFile & " WHERE [Part_Description] = '---Empty---'")
   ' The second recordset finds any duplicate records
   If NewDesc.Value = "" Then
       MsgBox ("You MUST enter a program description!")
       GoTo 10
   Else
   Set Rst2 = CurrentDb.OpenRecordset("SELECT * FROM " & PartFile & " WHERE [Part_Description] = '" & NewDesc.Value & "'")

   'If it hits the end of files before it finds a match, it creates the program without question
       If Not Rst2.EOF Then
           GoNoGo = MsgBox("There is already a part with that description in the system.  Do you still want to create this program?", 36)
           'If it finds a duplicate, it still lets you create the program if you wish, but it will ask.
               '6=YES  ie, the user DOES want a part with a duplicate description
               If GoNoGo = 7 Then GoTo 10
               Rst.Edit
               Rst![Part_Number] = NewNumber.Value
               Rst![Part_Description] = NewDesc.Value
               Rst.Update
               Rst.Close
               Set Rst2 = Nothing
       Else
           Rst.Edit
           Rst![Part_Number] = NewNumber.Value
           Rst![Part_Description] = NewDesc.Value
           Rst.Update
           Rst.Close
           Set Rst2 = Nothing
       End If
   End If
   ' It uses RstBase again to find the first "---Empty---" record, so when it updates, it shows the proper number.
5   Set Rst5 = CurrentDb.OpenRecordset("SELECT * FROM " & PartFile & " WHERE [Part_Description] = '---Empty---'")
   Rst5.FindFirst ([Part_Description] = "---Empty---")
   MsgBox ("Rst5!Part_Description = " & Rst5![Part_Description])
   MsgBox ("Rst5!Part_Number = " & Rst5![Part_Number])
   Rst5.Close
   Set Rst5 = Nothing
10  Let NewDesc.Value = Nothing
   Let List22.Value = NewNumber.Value
   Me.Refresh
End Sub


Please keep in mind that I'm new to Access and Visual Basic, so this code may well be sloppy and inefficient, but at least I know what it does.  Since this one REALLY has me stumped, I'm throwing ALL the points I have at it - HELP!!
0
Comment
Question by:Shellhead1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 3

Expert Comment

by:y2ksw
ID: 8211398
Ok, I've seen this one:

Dim RstBase, Rst, Rst2, Rst3, Rst4, Rst5 As DAO.Recordset

Only Rst5 will be a recordset, the others variants. Was this your intention? Well, ok, variants cannot be recordsets, your choice? :-)
0
 

Author Comment

by:Shellhead1
ID: 8211451
Hmmm...my intention was for all of them to be recordsets.  That begs another question, is it possible to use multiple recordsets in a single procedure?  And if so, am I using them to correctly edit and search for data?
0
 

Author Comment

by:Shellhead1
ID: 8211461
Oh, one more thing - am I implying correctly that the reason for my other module to work properly might have to do with the order of declaration in the DIM statement?  Might that code be working alright because something other than Rst5 is the recordset?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:y2ksw
ID: 8214515
I didn't try the code, but the problem is, as far as I can parse and interpret from here, the missing recordset declarations. Funny enough your procedure didn't come up with an error 91.

It's no order problem, but you need to declare each variable explicitly recordset, long, string ecc, except for variants, which are the predefined type since VB3.

You may have many recordsets in one procedure, VB4 had a limit of 5 or 6, but due to some limits of the DAO 2.0.

Hope this helped. BTW: Copy and paste is not always the fastest way to program :-)
0
 

Author Comment

by:Shellhead1
ID: 8217782
I just tried declaring all of the recordsets individually and it made no difference.  The program still skips 02 and goes directly to 03 after creating 01.
0
 
LVL 3

Expert Comment

by:y2ksw
ID: 8218168
Ok, I'll try the code this evening at home. I'll see if I get it run...
0
 

Author Comment

by:Shellhead1
ID: 8218333
Would it help any if I posted the code that I copied and is working fine?
0
 
LVL 3

Expert Comment

by:n_narayanan
ID: 8218352
Are you sure that the 2nd record has the value ---Empty---

Can you recheck it again. Even if there is an additional space in that it might give a problem.

So, open the access database, goto 2nd record, delete the value by pressing ctrl+0 (this will put the Null) and save it.

Then again open the table, go to 2nd record insert the value ---Empty---

Now check your program.

I am really doubtful about the data inside the table.

Cheers

Narayanan
0
 

Author Comment

by:Shellhead1
ID: 8218479
I've checked the data a couple of times, but I'll recheck it again.  I hadn't thought there might be a space after the "---Empty---".  Although, if that were the problem, wouldn't the program bypass it all the time?  Instead the program just bypasses it for a couple of entries and ultimately gets around to changing it.
0
 

Author Comment

by:Shellhead1
ID: 8218682
I did as you suggested and replaced the "---Empty---" in 02 with the "---Empty---" in 03, and it didn't make any difference.  I actually copied the value from 03 to 02, so I know that it was/is the same.
0
 
LVL 3

Expert Comment

by:y2ksw
ID: 8219805
Spaghetti-Code (I'm from Italy) :-)

This works as far as it can. Potential errors are marked with '???

VERSION 5.00
Object = "{FAEEE763-117E-101B-8933-08002B2F4F5A}#1.1#0"; "DBLIST32.OCX"
Begin VB.Form Form1
   Caption         =   "Form1"
   ClientHeight    =   2790
   ClientLeft      =   3675
   ClientTop       =   2805
   ClientWidth     =   4290
   LinkTopic       =   "Form1"
   ScaleHeight     =   2790
   ScaleWidth      =   4290
   Begin VB.TextBox NewNumber
      Height          =   285
      Left            =   240
      TabIndex        =   5
      Text            =   "NewNumber"
      Top             =   2220
      Width           =   1245
   End
   Begin VB.TextBox NewDesc
      Height          =   285
      Left            =   210
      TabIndex        =   4
      Text            =   "NewDesc"
      Top             =   1860
      Width           =   3885
   End
   Begin MSDBCtls.DBList List22
      Height          =   645
      Left            =   180
      TabIndex        =   1
      Top             =   30
      Width           =   1515
      _ExtentX        =   2672
      _ExtentY        =   1138
      _Version        =   393216
   End
   Begin VB.CommandButton Create_Button
      Caption         =   "Create_Button"
      Height          =   525
      Left            =   2160
      TabIndex        =   0
      Top             =   150
      Width           =   1245
   End
   Begin VB.Label Label128
      Caption         =   "Label128"
      Height          =   435
      Left            =   210
      TabIndex        =   3
      Top             =   1260
      Width           =   2115
   End
   Begin VB.Label Label28
      Caption         =   "Label28"
      Height          =   375
      Left            =   240
      TabIndex        =   2
      Top             =   810
      Width           =   2085
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Dim CurrentDb As DAO.Database

Private Sub Create_Button_Click()
   
    Dim S1 As String
    Dim GoNoGo As VbMsgBoxResult
    Dim Rst As DAO.Recordset
    Dim Rst2 As DAO.Recordset
    Dim Rst5 As DAO.Recordset
   
    ' The first recordset finds the first "---Empty---" record
    Set Rst = CurrentDb.OpenRecordset("SELECT * FROM PartFile WHERE [Part_Description] = '---Empty---'")
   
    ' The second recordset finds any duplicate records
    If NewDesc.Text = "" Then
        Call MsgBox("You MUST enter a program description!")
    Else
        Set Rst2 = CurrentDb.OpenRecordset("SELECT * FROM PartFile WHERE [Part_Description] = '" & NewDesc.Text & "'")
        'If it hits the end of files before it finds a match, it creates the program without question
        If Not Rst2.EOF Then
            GoNoGo = MsgBox("There is already a part with that description in the system. Do you still want to create this program?", vbYesNoCancel)
            'If it finds a duplicate, it still lets you create the program if you wish, but it will ask.
            '6=YES ie, the user DOES want a part with a duplicate description
            If GoNoGo <> vbYes Then GoTo 10
            Rst.Edit
            Rst![Part_Number] = NewNumber.Text
            Rst![Part_Description] = NewDesc.Text
            Rst.Update
            Rst.Close
            Set Rst2 = Nothing
        Else
            Rst.AddNew                                  '???
            Rst![Part_Number] = Val(NewNumber.Text)     '???
            Rst![Part_Description] = NewDesc.Text
            Rst.Update
            Rst.Close
            Set Rst2 = Nothing
        End If
        'It uses RstBase again to find the first "---Empty---" record, so when it updates, it shows the proper number.
        Set Rst5 = CurrentDb.OpenRecordset("SELECT * FROM PartFile WHERE [Part_Description] = '---Empty---'")
        Rst5.FindFirst ("[Part_Description] = '---Empty---'")   '???
        If Not Rst5.NoMatch Then                        '???
            Call MsgBox("Rst5!Part_Description = " & Rst5![Part_Description])
            Call MsgBox("Rst5!Part_Number = " & Rst5![Part_Number])
        End If
        Rst5.Close
        Set Rst5 = Nothing
    End If
   
10
    NewDesc.Text = ""
    List22.Text = NewNumber.Text
    Me.Refresh
   
End Sub

Private Sub Form_Load()

    Dim RstBase As DAO.Recordset
'    Dim Rst As DAO.Recordset
'    Dim Rst2 As DAO.Recordset
'    Dim Rst3 As DAO.Recordset
'    Dim Rst4 As DAO.Recordset
'    Dim Rst5 As DAO.Recordset
   
    Set CurrentDb = OpenDatabase(App.Path & "\db1.mdb", False, False)
   
'    List22.RowSource = "PartFile"                   '???
    Label28.Caption = "CustomerName"
    Label128.Caption = "ProgramD"
    Set RstBase = CurrentDb.OpenRecordset("SELECT * FROM PartFile WHERE [Part_Description] = '---Empty---'")
'''    RstBase.FindFirst ("Part_Description = '---Empty---'") '???
'''    If Not RstBase.NoMatch Then
'''        NewNumber.Text = RstBase![Part_Number]
'''    End If
   
    'same as:
'''    If Not RstBase.EOF Then
'''        NewNumber.Text = RstBase![Part_Number]
'''    End If

    'or
    If RstBase.RecordCount Then
        NewNumber.Text = RstBase![Part_Number]
    End If
   
    NewDesc.Text = ""
    List22.Text = NewNumber.Text
    Set RstBase = Nothing

End Sub

Private Sub Form_Unload(Cancel As Integer)

    On Error Resume Next
   
    Dim rs As DAO.Recordset
   
    For Each rs In CurrentDb.Recordsets
        rs.Close
    Next
   
    CurrentDb.Close
   
    Set CurrentDb = Nothing

End Sub
0
 

Author Comment

by:Shellhead1
ID: 8219958
OK, I copied all of the code into a new code window attached to a new form, and I got this error: "The expression On Load you entered as the event property setting produced the following error: Invalid outside procedure."

As an aside, why is such a drastic re-write of the code I started with needed to solve this issue?  I realize my code's not very professional, but I would have thought that changing a few lines would be enough to solve this, instead of starting from scratch.
0
 

Author Comment

by:Shellhead1
ID: 8270165
What's wrong guys?  Not enough points?  Is this unsolveable?  Why so quiet?
0
 
LVL 3

Expert Comment

by:y2ksw
ID: 8273449
No, it's not a question of points. Not for me. For the last error I have no solution. On my PC the sample worked, and I tried it with the last VB6SP5, but have no DotNet. So the only thing I can imagine that some calling conventions are not the same. There should be a wizard to find out what's wrong, but as I told, I don't know anything about .Net. Sorry.
0
 

Author Comment

by:Shellhead1
ID: 8423185
C'mon guys - 215 points!!  Can this possibly be THAT hard?  I mean, I'm just a novice at this - but I've read some GREAT stuff out here, and you guys should be able to handle this.
0
 

Author Comment

by:Shellhead1
ID: 8423200
Incidentally, this code was written in VBA, specifically for MS Access.  Does that help?
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8527984
PAQ'd and all 290 points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month13 days, 3 hours left to enroll

777 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