Need help with access vba

A

arian29

Board Regular
Joined
Sep 12, 2011
Messages
64
i am getting error 'there is no current record', when running the code below.
Its getting stuck at - Lrs5.MoveFirst



Code: 
Public Function data_AS()    Dim db As Database
    Dim Lrs As DAO.Recordset
    Dim Lrs1 As DAO.Recordset
    Dim Lrs2 As DAO.Recordset
    Dim Lrs3 As DAO.Recordset
    Dim Lrs4 As DAO.Recordset
    Dim Lrs5 As DAO.Recordset
    Dim Lrs6 As DAO.Recordset
    Dim Lrs7 As DAO.Recordset
    Dim LSQL As String
    Dim LGST As String
    Dim i As Integer, ii As Integer
    
    i = 1
    ii = 1
    DoCmd.SetWarnings False
    
    Set db = CurrentDb()
    LSQL = "Select * from [GE_Turbine_Details]"
    Set Lrs = db.OpenRecordset(LSQL)
    LSQL = "Select * from [Competition Turbines]"
    Set Lrs1 = db.OpenRecordset(LSQL)
    LSQL = "Select * from [Wind Speed]"
    Set Lrs2 = db.OpenRecordset(LSQL)
    LSQL = "Select * from [PPA]"
    Set Lrs3 = db.OpenRecordset(LSQL)
    LSQL = "Select * from [K-Factor]"
    Set Lrs4 = db.OpenRecordset(LSQL)
    LSQL = "Select * from Project_Size"
'    Set Lrs5 = db.OpenRecordset(LSQL)
'    LSQL = "Select * from Project_Constraint"
    
    Set Lrs5 = db.OpenRecordset(LSQL)
    LSQL = "Select * from Project_Cons"
    Set Lrs6 = db.OpenRecordset(LSQL)
    LSQL = "select * from shear_factor"
    Set Lrs7 = db.OpenRecordset(LSQL)
    DoCmd.RunSQL "delete from test1"
    If Lrs.EOF = False Then
        Do While Not Lrs1.EOF
            'MsgBox Lrs("GE Turbines")
            'Lrs.MoveFirst
            'Do While Not Lrs1.EOF
                'MsgBox Lrs1("GE Turbines")
                Lrs2.MoveFirst
                Do While Not Lrs2.EOF
                    'MsgBox Lrs2("GE Turbines")
                    Lrs3.MoveFirst
                    Do Until Lrs3.EOF
                        Lrs4.MoveFirst
                        Do Until Lrs4.EOF
                                Lrs5.MoveFirst
                                'Lrs6.MoveFirst
                                Do Until Lrs5.EOF
                                    Lrs6.MoveFirst
                                        Do Until Lrs6.EOF
                                            Lrs7.MoveFirst
                                                Do Until Lrs7.EOF
                                                    Lrs.MoveFirst
                                                    Do Until Lrs.EOF
                                                    LSQL = "Insert into Test1 values('" & Lrs("GeTurbine") & "'"
                                                    LSQL = LSQL & " ,'" & Lrs1("Competitors turbines") & "'," & IIf(IsNull(Lrs("GE_Price")), 0, Lrs("GE_Price")) & "," & IIf(IsNull(Lrs("GE_TT_Transportation")), 0, Lrs("GE_TT_Transportation")) & "," & IIf(IsNull(Lrs("GE_CE_Installation")), 0, Lrs("GE_CE_Installation")) & "," & IIf(IsNull(Lrs("GE_BPO")), 0, Lrs("GE_BPO")) & "," & IIf(IsNull(Lrs("GE_Other_Capital_Cost")), 0, Lrs("GE_Other_Capital_Cost")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Escalation")), 0, Lrs("GE_Planned_FSA_Escalation")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Year")), 0, Lrs("GE_Planned_FSA_Contract_Year")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Periond")), 0, Lrs("GE_Planned_FSA_Contract_Periond")) & "," & IIf(IsNull(Lrs("GE_Planned_Unplanned_ONM")), 0, Lrs("GE_Planned_Unplanned_ONM")) & "," & IIf(IsNull(Lrs("GE_Hub_Height_1")), 0, Lrs("GE_Hub_Height_1"))
                                                    LSQL = LSQL & " ,'" & Lrs("GeTurbine_2") & "'," & IIf(IsNull(Lrs("GE_Price_2")), 0, Lrs("GE_Price_2")) & "," & IIf(IsNull(Lrs("GE_TT_Transportation_2")), 0, Lrs("GE_TT_Transportation_2")) & "," & IIf(IsNull(Lrs("GE_CE_Installation_2")), 0, Lrs("GE_CE_Installation_2")) & "," & IIf(IsNull(Lrs("GE_BPO_2")), 0, Lrs("GE_BPO_2")) & "," & IIf(IsNull(Lrs("GE_Other_Capital_Cost_2")), 0, Lrs("GE_Other_Capital_Cost_2")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Escalation_2")), 0, Lrs("GE_Planned_FSA_Escalation_2")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Year_2")), 0, Lrs("GE_Planned_FSA_Contract_Year_2")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Periond_2")), 0, Lrs("GE_Planned_FSA_Contract_Periond_2")) & "," & IIf(IsNull(Lrs("GE_Planned_Unplanned_ONM_2")), 0, Lrs("GE_Planned_Unplanned_ONM_2")) & "," & IIf(IsNull(Lrs("GE_Hub_Height_2")), 0, Lrs("GE_Hub_Height_2"))
                                                    LSQL = LSQL & " ,'" & Lrs("GeTurbine_3") & "'," & IIf(IsNull(Lrs("GE_Price_3")), 0, Lrs("GE_Price_3")) & "," & IIf(IsNull(Lrs("GE_TT_Transportation_3")), 0, Lrs("GE_TT_Transportation_3")) & "," & IIf(IsNull(Lrs("GE_CE_Installation_3")), 0, Lrs("GE_CE_Installation_3")) & "," & IIf(IsNull(Lrs("GE_BPO_3")), 0, Lrs("GE_BPO_3")) & "," & IIf(IsNull(Lrs("GE_Other_Capital_Cost_3")), 0, Lrs("GE_Other_Capital_Cost_3")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Escalation_3")), 0, Lrs("GE_Planned_FSA_Escalation_3")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Year_3")), 0, Lrs("GE_Planned_FSA_Contract_Year_3")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Periond_3")), 0, Lrs("GE_Planned_FSA_Contract_Periond_3")) & "," & IIf(IsNull(Lrs("GE_Planned_Unplanned_ONM_3")), 0, Lrs("GE_Planned_Unplanned_ONM_3")) & "," & IIf(IsNull(Lrs("GE_Hub_Height_3")), 0, Lrs("GE_Hub_Height_3"))
                                                    LSQL = LSQL & " ,'" & Lrs("GeTurbine_4") & "'," & IIf(IsNull(Lrs("GE_Price_4")), 0, Lrs("GE_Price_4")) & "," & IIf(IsNull(Lrs("GE_TT_Transportation_4")), 0, Lrs("GE_TT_Transportation_4")) & "," & IIf(IsNull(Lrs("GE_CE_Installation_4")), 0, Lrs("GE_CE_Installation_4")) & "," & IIf(IsNull(Lrs("GE_BPO_4")), 0, Lrs("GE_BPO_4")) & "," & IIf(IsNull(Lrs("GE_Other_Capital_Cost_4")), 0, Lrs("GE_Other_Capital_Cost_4")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Escalation_4")), 0, Lrs("GE_Planned_FSA_Escalation_4")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Year_4")), 0, Lrs("GE_Planned_FSA_Contract_Year_4")) & "," & IIf(IsNull(Lrs("GE_Planned_FSA_Contract_Periond_4")), 0, Lrs("GE_Planned_FSA_Contract_Periond_4")) & "," & IIf(IsNull(Lrs("GE_Planned_Unplanned_ONM_4")), 0, Lrs("GE_Planned_Unplanned_ONM_4")) & "," & IIf(IsNull(Lrs("GE_Hub_Height_4")), 0, Lrs("GE_Hub_Height_4"))
                                                    LSQL = LSQL & " ," & IIf(IsNull(Lrs1("Price")), 0, Lrs1("Price")) & "," & IIf(IsNull(Lrs1("TT_Transportation")), 0, Lrs1("TT_Transportation")) & "," & IIf(IsNull(Lrs1("CE_Installation")), 0, Lrs1("CE_Installation")) & "," & IIf(IsNull(Lrs1("BPO")), 0, Lrs1("BPO")) & "," & IIf(IsNull(Lrs1("Other_Capital_Cost")), 0, Lrs1("Other_Capital_Cost")) & "," & IIf(IsNull(Lrs1("Planned_FSA_Escalation")), 0, Lrs1("Planned_FSA_Escalation")) & "," & IIf(IsNull(Lrs1("Planned_FSA_Contract_Year")), 0, Lrs1("Planned_FSA_Contract_Year")) & "," & IIf(IsNull(Lrs1("Planned_FSA_Contract_Period")), 0, Lrs1("Planned_FSA_Contract_Period")) & "," & IIf(IsNull(Lrs1("Planned_Unplanned_ONM")), 0, Lrs1("Planned_Unplanned_ONM")) & "," & IIf(IsNull(Lrs1("Com_Hub_Height")), 0, Lrs1("Com_Hub_Height"))
                                                    
                                                    LSQL = LSQL & " ," & Lrs2("Speed")
                                                    LSQL = LSQL & " ," & Lrs3("PPA")
                                                    LSQL = LSQL & " ," & Lrs4("K-Factor")
                                                    LSQL = LSQL & " ," & Lrs5("project_size")
                                                    LSQL = LSQL & " ,'" & Lrs6("project_constraint") & "'"
                                                    LSQL = LSQL & "," & Lrs7("Shear_Factor") & ");"
                                                    DoCmd.RunSQL LSQL
                                                    Lrs.MoveNext
                                                    i = i + 1
                                                    ii = ii + 1
                                                    Loop
                                                    If ii > 2 Then
                                                        Lrs7.MoveNext
                                                        ii = 1
                                                    End If
                                                    Loop
                                            If i > 2 Then
                                                Lrs6.MoveNext
                                                Lrs5.MoveNext
                                                i = 1
                                            End If
                                        Loop
                                        'Lrs6.MoveNext
                                    'Lrs5.MoveNext
                                Loop
                            Lrs4.MoveNext
                        Loop
                    Lrs3.MoveNext
                Loop
            Lrs2.MoveNext
        Loop
        Lrs1.MoveNext
      Loop
    End If
'Lrs1.MoveNext
'Loop
    
    'MsgBox "Done"
    DoCmd.SetWarnings True
End Function
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
1. You should not need to do this they way you did. There's no need for all these record sets. This is overkill.

2. What ARE you trying to do?
 
Upvote 0
I agree that most likely this is not how an experienced programmer would go about this and it will be difficult to debug it. That said, one obvious place to start is to see if there are any of your recordsets that have no records in them. After opening the recordsets insert a check:

Code: 
	Dim checkForRecords As String
	If Lrs.EOF Then
		checkForRecords = "Recordset 0 returned now records." & vbnewline
	End If
	If Lrs1.EOF Then
		checkForRecords = "Recordset 1 returned now records." & vbnewline
	End If
	If Lrs2.EOF Then
		checkForRecords = "Recordset 2 returned now records." & vbnewline
	End If
	If Lrs3.EOF Then
		checkForRecords = "Recordset 3 returned now records." & vbnewline
	End If
	If Lrs4.EOF Then
		checkForRecords = "Recordset 4 returned now records." & vbnewline
	End If
	If Lrs5.EOF Then
		checkForRecords = "Recordset 5 returned now records." & vbnewline
	End If
	If Lrs6.EOF Then
		checkForRecords = "Recordset 6 returned now records." & vbnewline
	End If
	If Lrs7.EOF Then
		checkForRecords = "Recordset 7 returned now records." & vbnewline
	End If
	If checkForRecords <> "" Then
	    Msgbox checkForRecords
		Exit Sub
	End If
 
Upvote 0
If you are trying to count recordsets, build a query for each
then count it

select count("[field]") as RecCount from table

put them all in a macro. Zero code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Access application gives 'Not responding' at this code line. Having difficulty in resolving
Replies
6
Views
414
chazrab
C
C
  • Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
450
welshgasman
welshgasman
S
  • Question
Exporting 2 tables using 2 queries into one Excel file (2 different tabs)
Replies
0
Views
356
santa12345
S
Guinaba
  • Question
Writing back from recordset to table or query
Replies
7
Views
4K
Guinaba
Guinaba
P
  • Question
Copy Command in Access 2013 VB Issues
Replies
1
Views
2K
welshgasman
welshgasman

Forum statistics

Threads
1,221,590
Messages
6,160,668
Members
451,662
Latest member
reelspike

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top