how to export from access to excel /overwrite

[sspreyer]

thanks I have now put the sql in place

but still getting error run time 424 object required

here the code now

Private Sub Command77_Click()
       Dim strSQL As String
 
    strSQL = "SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname " & _
         " FROM tblExposure " & _
         " INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID "
    If Not IsNull(Me.employeeId) Then
        strWhere = strWhere & "([employeeId] = " & Me.employeeId & ") AND "
    End If
 
lngLen = Len(strWhere) - 5
 
        DoCmd.Hourglass True
 
        If lngLen <= 0 Then
            strSQL = strSQL
            Set qryDef = dbs.CreateQueryDef("Exposure Grapth1", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Grapth1"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Grapth1"
        Else
            strWhere = Left$(strWhere, lngLen)
 
            strSQL = strSQL & " WHERE " & strWhere
         [COLOR=red][B]  Set qryDef = dbs.CreateQueryDef("Exposure Graph1", strSQL)[/B][/COLOR]
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Grapth1"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Graph1"
        End If
 
        DoCmd.Hourglass False
 
    
        DoCmd.CancelEvent
    
End Sub

thanks

shane

 

[CJ_London]

sorry to interject but just a quick comment - is this correct? unusual to link ID to ID. would have expected something like .EmployeeID

INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID

 

[namliam]

You didnt "DIM" the qryDef anywhere

 

[GinaWhipp]

Hmm, that's going to need some tweaking with the GROUP BY statement which must fall *after* the WHERE line. So, let's try...

   Dim strSQL As String
   Dim strGroupBy As String
 
    strSQL = "SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname " & _
         " FROM tblExposure " & _
         " INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID"
 
    strGroupBy = " GROUP BY tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure], tblemployee.firstname, tblemployee.lastname"
 
    'Numeric
    If Not IsNull(Me.employeeId) Then
        strWhere = strWhere & "([employeeId] = " & Me.employeeId & ") AND "
    End If
 
lngLen = Len(strWhere) - 5
 
        DoCmd.Hourglass True
 
        If lngLen <= 0 Then
            strSQL = strSQL & strGroupBy
            Set qryDef = dbs.CreateQueryDef("Exposure Graph", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Graph"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Graph"
        Else
            strWhere = Left$(strWhere, lngLen)
 
            strSQL = strSQL & " WHERE " & strWhere & strGroupBy
            Set qryDef = dbs.CreateQueryDef("Exposure Graph", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Graph"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Graph"
        End If
 
        DoCmd.Hourglass False
 
 
        DoCmd.CancelEvent
 
End Sub

 

[sspreyer]

so would qryDef be

Dim qryDef As String

cheers
shane

 

[GinaWhipp]

Oh dear, forgot a few lines (thanks namliam, my bad! Please add to the top!

Dim dbs As DAO.Database
Dim qryDef As DAO.QueryDef
Dim strWhere As String
Dim lngLen As Long
Set dbs = CurrentDb

 

[sspreyer]

right I have manage to get it to work BUT! its still export the whole query not filtering by employee. id I'm on I have attach a strip down version of my database so you all can have a look see if you can make out what's going wrong thanks again you all have been so helpful

View attachment trial.zip

sorry the format not mdb

cheers

shane

 

[namliam]

Oh dear, forgot a few lines (thanks namliam, my bad! Please add to the top!

Dim dbs As DAO.Database
Dim qryDef As DAO.QueryDef
Dim strWhere As String
Dim lngLen As Long
Set dbs = CurrentDb

Try adding Option Explicit as well

 

[sspreyer]

hi

it still exporting all the records e.g my trail database has 2 employee's the id's are 1 and 2 say i'm on employee 1 i only want to export the date linked on the exposure records that are linked to employee 1 but it still exporting all exposure record of all employee's
View attachment trial2.zip

thanks again

shane

 

[GinaWhipp]

Okay, try this one...

 

[GinaWhipp]

@CJ_London

Nope, it is correct. The Employee ID field if named *ID* in both Tables, very confusing.

 

[sspreyer]

thanks, gina

thats not my database. Mine Called "Database trail "

or was i ment to be look at this one

cheers

shane

 

[GinaWhipp]

Nope, I fixed it...

EDIT: Very strange, I had to do it twice for it to *stick*!

 

[sspreyer]

right there error message See Pic. im getting it because it doesnt seem to be generating the "temp" Query it will only work if i make query call it exposure Grapth1 but does not filter the id it just copy over the query i generate manually



thanks Again

Shane

 

[GinaWhipp]

Please take the t out of Graph. I thought I fixed them all must have missed one. Note: You can leave your file name as it is.

 

[sspreyer]

Thanks!!!!!!!!!! Gina Works Great!!! sorry if i have been bugging you to death!!!!!

Thanks again

Shane

 

[GinaWhipp]

Nope... don't feel bugged, glad to do it!