rs3 wont go to cell 18 on Excel from Access

[DMT Dave]

Hey guy's sorry I think another user has changed the Status field, I also added Dim db as DAO.Database and now used Set db = CurrentDb()



The Set rs = db.Openrecordset etc

I am going to change the status to the correct status and try it....

Will post if it goes wrong but much appreciate your help, you are great

[DMT Dave]

Hi Guy's it all works, thank you all so much for your help

[accesstos]

Hi Dave!

You have to ensure that the recordsets returns records.
Replace the line

xLWB.Worksheets(1).Cells(14, 1).Offset((i - 1) * 2, 0).CopyFromRecordset rs

with this block of code and test it again:

With xLWB.Worksheets(1).Cells(14, 1).Offset((i - 1) * 2, 0)
    If rs.RecordCount > 0 Then
        .CopyFromRecordset rs
    Else
        .Value = "No records!"
    End If
End With

In addition, you say:

With rs3
.AddNew
!RunNo = Ref
.Update
.Close
End With

but the Ref is an empty string(!).

Genenarly, for this job you can say:

db.Execute "INSERT INTO tblRunSheetNo (RunNo) SELECT " & Ref & ";",dbFailOnError

but make sure that the Ref is the correct value/variable for this field.

Finaly, You don't have to close the book and reopen it if the answer for check data is Yes. Just show the Excel App that is allready open, otherwise just quit in background.

I 'll be back later with other revisions.

[DMT Dave]

Hi Guys, back to this shortened code similar to accesstos in post 7, I have used this method to shorten another procedure, all works well apart from there may well be more than one record that matches the criterias, how do i add a GROUPBY to the following code ?

With xLWB
For i = 1 To mDelCount
        'Open recordset with the current DelCount.
        Set rs = db.OpenRecordset("SELECT tblAssign.DelNo, tblAssign.DelTo, tblAssign.Town, tblAssign.PostCode FROM tblAssign " _
                                & "WHERE Driver = '" & mDriver & "' And DeliveryDate = #" & mDelDate & "# And Vehicle = '" & VehReg & "' And DelNo = " & i, dbOpenSnapshot)
        'Export data to worksheet.
        xLWB.Worksheets(1).Cells(2, 3).Offset((i - 1) * 2, 0).CopyFromRecordset rs
        'xLWB.Worksheets(1).Cells(14, 1).Offset((i - 1) * 2, 0).CopyFromRecordset rs2
        rs.Close
    Next i

[CJ_London]

GROUP BY won't necessarily reduce the number of records returned - you could have many records that meet the criteria but have different values in other fields

But try it as see - suggest replace SELECT with SELECT DISTINCT which you use when not summing/counting etc

If you only want a single record, use TOP 1

[DMT Dave]

Absolutly brilliant Ajax, thank you, net question, which is the easiest way to add another rs2 where there is an entrycode and exitcode field in tblContacts that would coincide with the PostCode in rs ?

ie in the rs fields there may be an EntryCode and ExitCode for DelNo 1 and not for 2 and 3 but there is for 4,5 and 6 for example

So I need rs2 to look in tblCOntacts based on rs Postcode and add them to the excel output on cells(2,4) etc..

I am thinking can I add a criteria in rs2 from rs maybe: WHERE PostCode = & rs.Fields("PostCode")

Does this make sense ?

[DMT Dave]

I was think something like this but this code does debug

        Set rs2 = db.OpenRecordset("SELECT DISTINCT tblDealers.PostCode, tblDealers.EntryCode, tblDealers.ExitCode FROM tblDealers " _
                                & "WHERE PostCode = " & rs.Fields("PostCode") & i, dbOpenSnapshot)

[CJ_London]

postcode is presumably text so should be

"WHERE PostCode = '" & rs.Fields("PostCode") & i & "'"

Otherwise what is the error description you get?

[accesstos]

Hi Guys, back to this shortened code similar to accesstos in post 7, I have used this method to shorten another procedure, all works well apart from there may well be more than one record that matches the criterias, how do i add a GROUPBY to the following code ?

If you want to export all the records replace the line:

xLWB.Worksheets(1).Cells(2, 3).Offset((i - 1) * 2, 0).CopyFromRecordset rs

with this block of code:

With xLWB.Worksheets(1)
    With .Cells(.Rows.Count, 3).End(-4162).Offset(2, 0)
        If rs.RecordCount > 0 Then
            .CopyFromRecordset rs
        Else
            .Value = "No records for DelNo(" & i & ")"
        End If
    End With
End With

Why you export the data to Excel? Is that necessary?
Maybe an Access report make your life easier.

[DMT Dave]

Hi Guy's, this is weird as it gets!!! by the way i haven't had chance to adapt post 24 code as that's a different db procedure

Going back to the shortened code that works brilliant on the laptop for testing, when i have copied the code to work desktop db and replaced, i have checked that i have got x 6 RemNo's 1,2,3,4,5 and 6 all for the same driver (engineer) all for 25/07/2019, i get a runtime error 424 Object required

The debug highlights the rs

    With xLWB
For i = 1 To RemQty
        'Open recordset with the current RemQty.
        Set rs = Db.OpenRecordset("SELECT tblRemovals.RemNo, tblRemovals.Client, tblRemovals.Add1, tblRemovals.Add2, tblRemovals.Town, tblRemovals.Postcode, " _
                               & "tblRemovals.Tel, tblRemovals.Mobile, tblRemovals.LiftMake, tblRemovals.LiftModel FROM tblRemovals " _
                                & "WHERE Engineer = '" & mDriver & "' And RemovalDate = #" & RemDate & "# And RemNo = " & i, dbOpenSnapshot)
         
        'Export data to worksheet.
        xLWB.Worksheets(1).Cells(13, 1).Offset((i - 1) * 2, 0).CopyFromRecordset rs


        rs.Close
    Next i

[accesstos]

Hi Dave!

Some mistake in copy-paste, I suppose.
I see a "With xLWB" at the top of the code and no one "End With" at the bottom.
Take care to compile your code after each editing and always keep at the top of the module those lines:

Option Compare Database
Option Explicit

Furthermore, if you don't mind to export all returned records, of each RemQty value, to Excel in one step as a solid block of data, give a try to the code bellow:

Set rs = db.OpenRecordset("SELECT tblRemovals.RemNo, tblRemovals.Client, tblRemovals.Add1, tblRemovals.Add2, tblRemovals.Town, tblRemovals.Postcode, " _
                        & "tblRemovals.Tel, tblRemovals.Mobile, tblRemovals.LiftMake, tblRemovals.LiftModel FROM tblRemovals " _
                        & "WHERE Engineer = '" & mDriver & "' And RemovalDate = #" & RemDate & "# And (RemNo > 0) AND (RemNo <=" & RemQty & ")", dbOpenSnapshot)
         
'Export all records to worksheet in one step.
xLWB.Worksheets(1).Cells(14, 1).CopyFromRecordset rs

This approach eliminates the necessity of a loop.

Good luck!

[CJ_London]

@accessto

I was considering making that suggestionl, but look like OP wants a line in Excel between each remNo

If the suggestion is adopted, recommend include an ORDER BY remNo in the sql

[accesstos]

Ajax, of course, this is the most efficient approach, but,
We still ignore the reason for exporting to Excel.
It's up to Dave to decide and take care for the necessary modifications, if need.

[DMT Dave]

Hi accesstos and Ajax, thank you so much for your input, this one works perfect now again thank you all so much Dave

[DMT Dave]

Hi Ajx and accesstos, i din't answer, 80% of reports are either printed or output as PDF on a server, Customer updates are prefered by Excel and 20% are output as Excel so that mobile devices can read them and users may need to add something extra manually in which it can be done outside of Access....

I am going to look at another procedure that is miles long because of lack of knowledge to make it shorter so i will will post again shortly as this has helped massivley thanks to you guy's all thebest Dave