Shellhead1
asked on
Why is my recordset skipping values?
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("S
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("S
' 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("S
'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("S
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!!
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("S
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("S
' 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("S
'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("S
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!!
ASKER
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?
ASKER
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?
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 :-)
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 :-)
ASKER
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.
Ok, I'll try the code this evening at home. I'll see if I get it run...
ASKER
Would it help any if I posted the code that I copied and is working fine?
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
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
ASKER
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.
ASKER
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.
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-
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("S
' The second recordset finds any duplicate records
If NewDesc.Text = "" Then
Call MsgBox("You MUST enter a program description!")
Else
Set Rst2 = CurrentDb.OpenRecordset("S
'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("S
Rst5.FindFirst ("[Part_Description] = '---Empty---'") '???
If Not Rst5.NoMatch Then '???
Call MsgBox("Rst5!Part_Descript
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("S
''' 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
This works as far as it can. Potential errors are marked with '???
VERSION 5.00
Object = "{FAEEE763-117E-101B-8933-
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("S
' The second recordset finds any duplicate records
If NewDesc.Text = "" Then
Call MsgBox("You MUST enter a program description!")
Else
Set Rst2 = CurrentDb.OpenRecordset("S
'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("S
Rst5.FindFirst ("[Part_Description] = '---Empty---'") '???
If Not Rst5.NoMatch Then '???
Call MsgBox("Rst5!Part_Descript
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("S
''' 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
ASKER
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.
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.
ASKER
What's wrong guys? Not enough points? Is this unsolveable? Why so quiet?
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.
ASKER
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.
ASKER
Incidentally, this code was written in VBA, specifically for MS Access. Does that help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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? :-)