Why the records fetching multiple times (1 Viewer)

Ashfaque

Student
Local time
Today, 14:15
Joined
Sep 6, 2004
Messages
894
Hi,

I am simply trying to export the records of tbl T_VehExp to Excel sheets in one single file kept at the specific destination but it appears multiple time same sheets.

I was trying to give vehicle number to each sheet instead of Sheet1, Sheet 2...

There is something wrong in code or I not able to find the control of repeating vehicle details.

Some of DIM statements are some other purpose.

:banghead:
Code Tags Added by UG
Please use Code Tags when posting VBA Code

https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Code:
Public Sub InsertDates():
  Dim objXl As Excel.Application
  Dim objWkb As Excel.Workbook
  Dim objSht As Excel.Worksheet
  Dim rstNames As DAO.Recordset, rst As DAO.Recordset
  Dim iRow As Integer, x As Long
  Dim PreviousDate As Date, MonthEndDate As Date
 
  '==
Dim TempDate, TTempDate, DummyDate, CheckTime, DCheckTime
Dim MyDay As Integer
Dim MAM, MPM
Dim InTime, OutTime
Dim DAccNo, TAccNo As Double
Dim Cmprdate
Dim CmprInTime
Dim CmprOutTime
Dim DEmp, TEmp As String
Dim MN As String
Dim Yr As Variant
Dim Interval As Variant
Dim dblNumHours As Double

Dim Q As String
  '==
 
 
  Set rstNames = CurrentDb.OpenRecordset("SELECT DISTINCT VehicleNo, VEDate FROM Q1;")
  If rstNames.EOF And rstNames.BOF Then
    MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
  Else
    Set objXl = New Excel.Application
    objXl.Visible = True
    Set objWkb = objXl.Workbooks.Open("D:\VehicleDB\VehExpReport\VehExpReport.xlsx")
   
    Do
      Set objSht = objWkb.Worksheets.Add()
      objWkb.ActiveSheet.Name = rstNames![VehicleNo]
     
      Set rst = CurrentDb.OpenRecordset("SELECT [T_VehExp.VehicleNo], [T_VehExp.VEDate], [T_VehExp.JobNo], " & _
      "[T_VehExp.InvoiceNo], [T_VehExp.VehicleModel], [T_VehExp.VDate], [T_VehExp.Expense Description], [T_VehExp.Amount], " & _
      "[T_VehExp.KM], [T_VehExp.Remarks], [T_VehExp.Part], [T_VehExp.MechanicName] FROM T_VehExp " & _
      "WHERE [T_VehExp.VehicleNo] ='" & rstNames![VehicleNo] & "' ;")

Q = rstNames![VehicleNo]

     
      If rst.EOF And rst.BOF Then
        MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
      Else
Call FormatSheet(objSht, "VehicleNo:" & " - " & rst![T_VehExp.VehicleNo])
       
        iRow = 5
        rst.MoveFirst
       
        Do While Not rst.EOF
         
'
          objSht.Cells(iRow, 1).Value = DateValue(rst![T_VehExp.VEDate])
          objSht.Cells(iRow, 1).HorizontalAlignment = xlCenter
          objSht.Cells(iRow, 1).Borders.Color = vbBlack
          objSht.Cells(iRow, 2).Value = rst![T_VehExp.JobNo]
          objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
          objSht.Cells(iRow, 2).Borders.Color = vbBlack
          If (IsNull(rst![T_VehExp.InvoiceNo])) Then
          Dim Z As Integer
          Z = 0
          objSht.Cells(iRow, 3).Value = Z
          Else
          objSht.Cells(iRow, 3).Value = TimeValue(rst![T_VehExp.InvoiceNo])
          End If
          objSht.Cells(iRow, 3).Borders.Color = vbBlack


'===
          iRow = iRow + 1
          rst.MoveNext
        Loop
'   objWkb.ActiveSheet.Name = rstNames![VehicleNo]
       
        rst.MoveLast
''====
'            'objSht.Cells(iRow, 7).Value = "Date Created"
'
'            x = x + 1
'            iRow = iRow + 1
'        End If
       
'          Loop Until rstNames![VehicleNo] = rst![T_VehExp.VehicleNo]
'        End If
      End If
      rstNames.MoveNext
    Loop Until rstNames.EOF
      
    objXl.Application.DisplayAlerts = False
    objWkb.Worksheets("Sheet1").Delete
    objXl.Application.DisplayAlerts = True
  End If
End Sub

Can someone help me?

I was trying to attach zip file of my db with less weight but failed.

Thanks in advance
Ashfaque
 

Attachments

  • INAT_VEHICLES_FE.zip
    453.8 KB · Views: 77
Last edited by a moderator:

Ranman256

Well-known member
Local time
Today, 04:45
Joined
Apr 9, 2015
Messages
4,339
you shouldnt loop thru data to post. (too long)
instead, post ALL the data at once via
range("a1").copyFromREcordset rst

THEN format the sheet.
 

Ashfaque

Student
Local time
Today, 14:15
Joined
Sep 6, 2004
Messages
894
Thanks RunMan,

That I would probably do it later but the present issue is repeating data sheet.

Thanks for understanding me.
 

isladogs

MVP / VIP
Local time
Today, 09:45
Joined
Jan 14, 2017
Messages
18,209
I haven't read your code in detail.
However one thing stood out on a quick skim...

The first SQL statement uses SELECT DISTINCT to get unique records
Change the 2nd SQL statement to also use SELECT DISTINCT
 

Ashfaque

Student
Local time
Today, 14:15
Joined
Sep 6, 2004
Messages
894
I believe there should be LOOP UNTIL somewhere in between to control the repeating vehicle sheet.

But where?
 

Ashfaque

Student
Local time
Today, 14:15
Joined
Sep 6, 2004
Messages
894
I have attached the fresh db for your ready ref.
 

Attachments

  • INAT_VEHICLES_FE-new.zip
    474 KB · Views: 78

Ashfaque

Student
Local time
Today, 14:15
Joined
Sep 6, 2004
Messages
894
SOLVED : Re: Why the records fetching multiple times

Thanks to all,

I have managed it. It is working well now.

Ashfaque:)
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:45
Joined
Sep 21, 2011
Messages
14,232
Re: SOLVED : Re: Why the records fetching multiple times

Thanks to all,

I have managed it. It is working well now.

Ashfaque:)

So could you post the finished code (but within code tags)?
 

Ashfaque

Student
Local time
Today, 14:15
Joined
Sep 6, 2004
Messages
894
Gasman,

I have added extra code lines particularly for Output on Excel sheet designing purpose.

Moreover, additional code lines will generate a summary of all sheets in one single file that is a good outcome of my code lines. (Although almost codes are taken from this forum's genius members..thanks to all of them. I only modified a little)

If I simply put lines in tags, it may confuse. Therefore, I am attaching the whole db for you and others as well.

You need to place an one empty Excel file by name "VehExpReport" in the folder named "Vehilcedb"

Just click the btn Export Expense on main form.

Hope it will work smoothly

Now I am looking to provide Hyperlink to all vehicle numbers on the summary sheet thru the vba code so that if I want to see details of expenses, the control could reach to the particular sheet instead of searching the sheet.

Is there anyone to help me out over this ?


Thanks,
Ashfaque
 

Attachments

  • INAT_VEHICLES_FE.zip
    485.7 KB · Views: 90

Gasman

Enthusiastic Amateur
Local time
Today, 09:45
Joined
Sep 21, 2011
Messages
14,232
Thank you.

Just one observation.

You are using an integer for the excel row allocation.
I'm not sure how much data you will be exporting, but that would be max 32767 as it stands.

I use Long in VBA for Excel rows just in case.

Also how have you hidden the menu options?
 

Ashfaque

Student
Local time
Today, 14:15
Joined
Sep 6, 2004
Messages
894
Thanks Gasman,

I changed it to Long.

Anyone there to guide me for Hyperlink ?

Thanks in Advance.
Ashfaque
 

Users who are viewing this thread

Top Bottom