Small problem with the following code.

[fatboy95]

Someone here helped me with this code before but I am still having a small problem with it. If I only have one record for the PartName or nothing at all it errors out with something about EOF or BOF. I am not sure what to change to keep it from doing this.

On Error GoTo Err_Handler


Dim s As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rsFind As New ADODB.Recordset
Dim Icount As Double
Dim Rcount As Double

Dim EngineID As Double
Dim NewPartName As String

'Open db connection
Set db = CurrentProject.Connection

'open recordet to tblCPartOnOrder
 s = "Select * from tblCPartOnOrder"
 
Set rsFind = New ADODB.Recordset
rsFind.Open s, db, adOpenDynamic, adLockOptimistic

'open recordet to tblCPartOnOrderSort
 s = "tblCPartOnOrderSort"
 
Set rs = New ADODB.Recordset
rs.Open s, db, adOpenDynamic, adLockOptimistic
Rcount = 1

Do While Not rsFind.EOF
            
        If Rcount = 1 Then
        EngineID = rsFind.Fields("EngineID")
        NewPartName = rsFind.Fields("PartName")
        rsFind.MoveNext
        Rcount = Rcount + 1
        End If
        
    If rsFind.Fields("EngineID") = EngineID Then
    'same record
    NewPartName = NewPartName & ", " & rsFind.Fields("PartName")
    rsFind.MoveNext
    Rcount = Rcount + 1
    
    Else
    'new record
        
    'Add Current record
        With rs
        .AddNew
        .Fields("EngineID") = EngineID
        .Fields("ComboPartName") = NewPartName
        .Update
        Icount = Icount + 1
        End With
        EngineID = rsFind.Fields("EngineID")
        NewPartName = rsFind.Fields("PartName")
        rsFind.MoveNext
        Rcount = Rcount + 1

    End If
                
Loop
    
        With rs
        .AddNew
        .Fields("EngineID") = EngineID
        .Fields("ComboPartName") = NewPartName
        .Update
        Icount = Icount + 1
        End With
    
MsgBox (Rcount - 1) & " records were combined into " & Icount & " new records."
    
Set rs = Nothing
Set rsFind = Nothing
DoCmd.Requery
Exit_Err_handler:
    Exit Sub

Err_Handler:

Select Case Err.Number
'This checks for Error ______

Case Else
    MsgBox Err.Description
    MsgBox Err.Number
    Resume Exit_Err_handler
    
End Select

Exit_Sub

Thanks for the help.

 

[visualAd]

Which line is causing the error?

 

[fatboy95]

This is exactly what it says:

Either BOF or EOF is True, or the current record has been deleted. Requested opertation requires a current record.

I do have at least one record in the table but for some reason it will not work with only one. As soon as I add a second record it works fine.

Thanks

 

[visualAd]

This is exactly what it says:

Either BOF or EOF is True, or the current record has been deleted. Requested opertation requires a current record.

I do have at least one record in the table but for some reason it will not work with only one. As soon as I add a second record it works fine.

Thanks

To find out which line the errror is occuring on comment out the following line:
On Error GoTo Err_Handler then run your code. It should bring up a box with a debug button on it. If you press that button you will see the line which is causing the error highlighted.

It looks like you are trying to read off an empty recordset. You should test that the recordset contains records before you start reading its feilds. If you have an empty recordsset the Record ccount property will be 0 and both EOF and BOF will be true.

 

[fatboy95]

Ok I remarked the error handler out and when I ran it it gave me 3201 for the code. This is the line it stopped at

If rsFind.Fields("EngineID") = EngineID Then

I don't really understand what is wrong with this code. If I have only one record in my database it errors with the above. If I have no records or two or more records it works fine.

Again thanks for the help.

 

[cable]

Ok I remarked the error handler out and when I ran it it gave me 3201 for the code. This is the line it stopped at

If rsFind.Fields("EngineID") = EngineID Then

I don't really understand what is wrong with this code. If I have only one record in my database it errors with the above. If I have no records or two or more records it works fine.

Again thanks for the help.

its because you have a movenext in the first if.
The do loop starts because you have 1 rec, then the movenext then screws things up.

basically I would say the logic is slightly off I would restate what your trying to achive in this bit and start coding from fresh.

 

[visualAd]

This is why you get an error:

[color=red]'rsFind record pointer pointing to a record set containing 1 record[/color]
[color=red]'rsFind record pointer on first record[/color]

If Rcount = 1 Then [color=red]' always true for first loop iteration[/color]
  EngineID = rsFind.Fields("EngineID")
  NewPartName = rsFind.Fields("PartName")
  rsFind.MoveNext [color=red]' record pointer is now pointing to EOF - as you have moved past 
                             ' the last record[/color]
  Rcount = Rcount + 1
End If

If rsFind.Fields("EngineID") = EngineID Then [color=red]' this line will now throw and error because 
                                                        ' rsFind is not pointing to a record[/color]
  'same record
  NewPartName = NewPartName & ", " & rsFind.Fields("PartName")
  rsFind.MoveNext
  Rcount = Rcount + 1

Else
  'new record

  'Add Current record
  With rs
    .AddNew
    .Fields("EngineID") = EngineID
    .Fields("ComboPartName") = NewPartName
    .Update
    Icount = Icount + 1
  End With
  
  EngineID = rsFind.Fields("EngineID")
  NewPartName = rsFind.Fields("PartName")
  rsFind.MoveNext
  Rcount = Rcount + 1
End If

I would suggest a modification but I am really not sure what your code is doing. Hopefully the above will show you exactly what is causing the error.

 

[fatboy95]

When I order parts for a engine it goes into a seperate table from the engine table EnginePartsOnOrder. I have a couple of query's that make a new table to put the EngineID with the PartName and then this code copies each EngineID and all its parts on order into one cell letting me be able to display the data on my main page for parts on order for an engine. In the bellow example is basically how it works.

I have a query that makes this table
tblPartsOnOrder
EngineID PartOnOrder
1 Gasket
1 Intake
1 Anit-Freeze
2 Transmission
2 U-joints

then the code takes this info and makes this table
tblComboPartsOnOrder
EngineID ComboPartOnOrder
1 Gasket, Intake, Anit-Freeze
2 Transmission, U-joints


I do understand what you are saying my error is but I am not sure how to fix this problem with it.

 

[cable]

mmm, why do you need that 2nd table?
It doesn't 'do' anything, its the same as the first just displayed differently.

 

[fatboy95]

One table inputs the data and the other sorts in into on box per the engine id so that I am able to show it on a seperate from. I am really frustrated about this because this is the last thing I need to fix for my database to have it operational for use...

 

[visualAd]

I think I understand your problem. You want to use the data in a table and concatenate all the parts on order into one string for each engine.

I don't think using two tables is the best way of doing it. I assume what you are trying to do is display all parts on order for one engine in the same form. Have you looked into using subforms?

 

[cable]

yea, as your not storing 'new', 'original' data you need to look at using a query to change the 'view' of your current data.

 

[fatboy95]

Basically I am concantenating the field and it is working great with the code above unless I only have one partonorder. IE

Lets say I have only engineid 3 as and that is all I have for engines in my database and then I only order one part for it lets say a intake. When I run the above code it will error out but if I add another part it works just great or if I add another engine and part on order for it the coder works just fine.

I do thank you for all your help.

 

[visualAd]

The way you are doing this is not ideal as you are causing data repetition Why concatenate all the parts on order? Where is the string going to be used? - Is it just for display purposes on a form or report?

If this is the case then you shouldn't be duplicating this data in another table and should generate it when the form or report is activated.

Anyway I've re written your VBA for you. I assume as you mention engine ID that you have a table containing a list of Engines and their EngineId's. I've used this table (named it engines - you'll need to change this to reflect the name of your table).

The code goes through each engine in the engines table and executes a query on the PartsOnOrder table to pull out a recordset containing all the parts on order for that engine. It then builds a string from all the parts found (if any) and creates a new record in your PartsOnOrderSort table.

Sub combine()
    Dim con As ADODB.Connection
    Dim rsEngines As New ADODB.Recordset
    Dim rsParts As New ADODB.Recordset
    Dim rsComParts As New ADODB.Recordset

    Dim strPartsSql as String
    Dim strParts As String
    
    Set con = CurrentProject.Connection
    ' open engines table
    rsEngines.Open "engine", con, adOpenForwardOnly, adLockReadOnly, adCmdTable
    
    'open combined parts table
    rsComParts.Open "tblCPartOnOrderSort", con, adOpenStatic, adLockOptimistic, adCmdTable
    
    While (rsEngines.EOF = False)
        strPartsSql = "SELECT PartName FROM tblCPartOnOrder WHERE EngineId=" & _
            rsEngines!EngineID & ";"
        
        rsParts.Open strPartsSql, con, adOpenStatic, adLockReadOnly, adCmdText
        
        strParts = ""
        
        While (rsParts.EOF = False)
            strParts = strParts & "," & rsParts!PartName
            
            rsParts.MoveNext
        Wend
        
        ' remove first ,
        strparts = Mid(strparts, 2)
        
        ' check the engine has parts on order
        If rsParts.RecordCount > 0 Then
            ' add new record
            rsComParts.AddNew
            rsComParts!EngineID = rsEngines!EngineID
            rsComParts!ComboPartName = strparts
            rsComParts.Update
        End If
        
        ' close this recordset
        rsParts.Close
        
        rsEngines.MoveNext
    Wend
    
    rsEngines.Close
    rsComParts.Close
End Sub

 

[fatboy95]

I tried to concantenate the field but for some reason I couldn't get it to work with a single field. I have a form for engine information then I have a form that shows all the parts on order for the engine and its pertinate info. I want to be able to see just the part name on order on m form with the engine data so that I have a quick reference to it.