Question Export to excel button based on records in current view (1 Viewer)

S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
How do I create a button that exports the filtered data on a form, which is based of a query, to excel.

I have a query that retrieves ALL of the employees in my database but the data if filtered by department. I created buttons for each department that filters the "ALL employees table" by department. I want the export to excel button to only retrieve only the filterd records, not the entire employee query.

PLEASE HELP:mad:
 
V

vbaInet

AWF VIP
Local time
Today, 17:40
Joined
Jan 22, 2010
Messages
26,374
How are you filtering the records? Using the in-built filter function or otherwise?
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
I am using a few macros. The main macro has all the filters in it and then I have Department Macros that set the Temp Vars to a specific number (1-25) that coincide with the main macro. Make sense?
 
V

vbaInet

AWF VIP
Local time
Today, 17:40
Joined
Jan 22, 2010
Messages
26,374
Macros :rolleyes: I'm not a fan. :)

Can you show us the macros or better still convert the macros to code so I can see what you're doing.
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
Option Compare Database
'------------------------------------------------------------
' mcrFilter
'
'------------------------------------------------------------
Function mcrFilter()
On Error GoTo mcrFilter_Err
' FilterType - 1 = AVE, 2 = SCE-CHA, 3 = EPR, 4 = SCE-INT, 5 = SCI, 6 = VEE, 7 = UCN, 8 = SCI-NSA: Restraints, 9 = SCE-ENG, 10 = SCE-ELE, 11 = SCE-BOD, 12 = ALL, 13 = SCI:, 14 = SCI-ARD, 15 = SCI-BPS, 16 = SCI-CPG, 17 = SCI-DEB, 18 = SCI-MFS, 19 = SCI-NSA
If (TempVars!FilterType = 1) Then
DoCmd.SetFilter "", "Left$([FIN],3)=""AVE""", "NavigationSubform"
ElseIf (TempVars!FilterType = 2) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-CHA""", "NavigationSubform"
ElseIf (TempVars!FilterType = 3) Then
DoCmd.SetFilter "", "Left$([FIN],3)=""EPR""", "NavigationSubform"
ElseIf (TempVars!FilterType = 4) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-INT""", "NavigationSubform"
ElseIf (TempVars!FilterType = 5) Then
DoCmd.SetFilter "", "Left$([FIN],3)=""SCI""", "NavigationSubform"
ElseIf (TempVars!FilterType = 6) Then
DoCmd.SetFilter "", "Left$([FIN],3)=""VEE""", "NavigationSubform"
ElseIf (TempVars!FilterType = 7) Then
DoCmd.SetFilter "", "Left$([FIN],3)=""UCN""", "NavigationSubform"
ElseIf (TempVars!FilterType = 8) Then
DoCmd.SetFilter "", "[strLowest_Level_Department]=""SCI-NSA: Restraints""", "NavigationSubform"
ElseIf (TempVars!FilterType = 9) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-ENG""", "NavigationSubform"
ElseIf (TempVars!FilterType = 10) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-ELE""", "NavigationSubform"
ElseIf (TempVars!FilterType = 11) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-BOD""", "NavigationSubform"
ElseIf (TempVars!FilterType = 12) Then
DoCmd.SetFilter "", "", ""
ElseIf (TempVars!FilterType = 13) Then
DoCmd.SetFilter "", "Left$([FIN],4)=""SCI:""", "NavigationSubform"
ElseIf (TempVars!FilterType = 14) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-ARD""", "NavigationSubform"
ElseIf (TempVars!FilterType = 15) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-BPS""", "NavigationSubform"
ElseIf (TempVars!FilterType = 16) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-CPG""", "NavigationSubform"
ElseIf (TempVars!FilterType = 17) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-DEB""", "NavigationSubform"
ElseIf (TempVars!FilterType = 18) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-MFS""", "NavigationSubform"
ElseIf (TempVars!FilterType = 19) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-NSA""", "NavigationSubform"
ElseIf (TempVars!FilterType = 20) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-PCV""", "NavigationSubform"
ElseIf (TempVars!FilterType = 21) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-SRT""", "NavigationSubform"
ElseIf (TempVars!FilterType = 22) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-VIV""", "NavigationSubform"
ElseIf (TempVars!FilterType = 23) Then
DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-VLE""", "NavigationSubform"
End If

mcrFilter_Exit:
Exit Function
mcrFilter_Err:
MsgBox Error$
Resume mcrFilter_Exit
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
I am setting filters based on Department name. For example SCE-BOD is number 12 and it reads like this

[temp vars] ! ]filter type] = 12
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
Function mcrFilter()
On Error GoTo mcrFilter_Err
' FilterType - 1 = AVE, 2 = SCE-CHA, 3 = EPR, 4 = SCE-INT, 5 = SCI, 6 = VEE, 7 = UCN, 8 = SCI-NSA: Restraints, 9 = SCE-ENG, 10 = SCE-ELE, 11 = SCE-BOD, 12 = ALL, 13 = SCI:, 14 = SCI-ARD, 15 = SCI-BPS, 16 = SCI-CPG, 17 = SCI-DEB, 18 = SCI-MFS, 19 = SCI-NSA
Code: 
 If (TempVars!FilterType = 1) Then
DoCmd.SetFilter "", "Left$([FIN],3)=""AVE""", "NavigationSubform" [Code]
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
I have no idea what that means. I am not a VBA programmer. If I were I wouldn't need help. LOL
 
V

vbaInet

AWF VIP
Local time
Today, 17:40
Joined
Jan 22, 2010
Messages
26,374
Even programmers sometimes need help ;)

[C O D E]
Your code here
[/ C O D E]

without the spaces. See the link I provided. I want to see the code with the indentation in-tact.

The code will look like this:
Code: 
Public Function MyString()
    If This Then
        Do this
    End If
End Function
Can you see how the indents are preserved.
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
If [tempVars]![FilterType] = 1 then
SetFilter
wherecondition = Left$([FIN],3) = "AVE"
Control Name = Navigation Subform
Else If [TempVars]![FilterType] = 2

and so on and so forth
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
I'm not sure how you did the indentation but whenever I try to recreate that, Epic fail!
 
V

vbaInet

AWF VIP
Local time
Today, 17:40
Joined
Jan 22, 2010
Messages
26,374
I've explained how we would like to see it a few times already and I can't read code in the format you've presented it.

Hopefully, someone else will be able to help.
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
OK, let's pretend I have no code. How would you export filtered data from form view?
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
Since you don't like Macros how do you suggest that I filter the employee query?
 
V

vbaInet

AWF VIP
Local time
Today, 17:40
Joined
Jan 22, 2010
Messages
26,374
It's not because I don't like macros, the problem is I asked to see your code in a readable format but you've refused to comply. Everyone on this forum expects to see code in the format I specified.
 
S

sladetroityer

Registered User.
Local time
Today, 09:40
Joined
Oct 26, 2011
Messages
149
Code: 
 If (TempVars!FilterType = 1) Then
                  DoCmd.SetFilter "", "Left$([FIN],3)=""AVE""", "NavigationSubform"
           End if

Hopefully this is what your looking for. This is just one of the filter entries that I have in the Macro
 
You must log in or register to reply here.

Similar threads

C
exporting access table data to excel
Replies
10
Views
673
raziel3
raziel3
R
Export a form to excel along with the combo boxes
Replies
5
Views
222
June7
June7
A
Trouble Exporting Sorted Data from Access Form with Subforms to Excel
Replies
2
Views
270
ebs17
E
WineSnob
Extra columns on export query
Replies
11
Views
378
WineSnob
WineSnob
I
Connect different databases
2
Replies
31
Views
840
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom