S
Sue
Hi there
I am trying to work out the code to get records stored in Access (but
accessed via an Excel Interface) to open up in a new Excel workbook on
clicking a button (an 'export' or 'extract' function if you like) -
both 2003 versions...
I have successfully gotten them to open up into a new form (Excel
VBA), just can't get this right...(so haven't bothered putting my
sorry excuse for an attempt below, just the SQL stuff that sits behind
the combo boxes and then the code that opens it up in a new form etc)
Note: I do not want to create a query in Access and have Excel tap
into that. I have written an SQL in the Excel form - much
smoother...simply using the Access DB as a storage can..
Really appreciate some [any!] help here...have looked at other
people's spiels and the KB to no avail!
thanks in advance..
Sue
'In a seperate module, along with other public variables I have:
Sub FindDatabasePath()
path1 = "\\xxx\xxx\xxx\xxx\xxx\xxx\xxxx"
path1 = "" & path1 & "" & "\xxx\xxx\xxx\xxx\xxx\xxx\Xxxx.mdb"
End Sub
'(clearly, x reflects the specifics of the path to be found)
--------------------------------------------------------------------------
'In the form itself I have:
Option Explicit
Dim ws As Workspace
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim BHDrop() As Variant
Dim ProgrammeDrop() As Variant
Dim SQL1 As String
Dim rscount As Integer
Dim rscount1 As Integer
Dim gg As Integer
Dim zz As Integer
Private Sub UserForm_Initialize()
Set ws = DBEngine.Workspaces(0)
Call FindDatabasePath
Set db = ws.OpenDatabase(path1)
'open up a dataset
Set rs2 = db.OpenRecordset("SELECT tblProject.[BusinessHead] FROM
tblProject WHERE Not [Phase] = 'cancelled'" _
& " AND Not [Phase] = 'Completed' AND Not [Phase] = 'Delivered' AND
Not [Phase] = 'Value Captured'" _
& " GROUP BY tblProject.[BusinessHead]")
rscount1 = rs2.RecordCount - 1
rs2.MoveFirst
ReDim BHDrop(0 To rscount1, 1)
For zz = 0 To rscount1
BHDrop(zz, 0) = rs2.Fields(0)
rs2.MoveNext
Next zz
cmboxBH.List = BHDrop
cmboxBH.Value = Empty
cmboxAName.Value = Empty
ProjectID = cmboxAName.Text
BusinessHeadID = cmboxBH.Text
End Sub
Private Sub cmboxBH_Change()
cmboxAName.Value = ""
BusinessHeadID = cmboxBH.Text
If BusinessHeadID = "" Then
SQL1 = "SELECT [ProjectName],[BusinessHead] FROM tblProject WHERE Not
[Phase]= 'cancelled'" _
& " AND Not [Phase]= 'Completed' AND Not [Phase]= 'Delivered' AND Not
[Phase]= 'Value Captured'" _
& " GROUP BY [ProjectName], [BusinessHead] HAVING [BusinessHead]Is
Null"
Else
SQL1 = "SELECT [ProjectName],[BusinessHead] FROM tblProject WHERE Not
[Phase]= 'cancelled'" _
& " AND Not [Phase]= 'Completed' AND Not [Phase]= 'Delivered' AND Not
[Phase]= 'Value Captured'" _
& " GROUP BY [ProjectName], [BusinessHead] HAVING [BusinessHead]='" &
BusinessHeadID & "'"
End If
Set rs1 = db.OpenRecordset(SQL1)
If rs1.EOF Then
Else
rs1.MoveLast
rscount = rs1.RecordCount - 1
rs1.MoveFirst
ReDim ProgrammeDrop(0 To rscount, 1)
For gg = 0 To rscount
ProgrammeDrop(gg, 0) = rs1.Fields(0)
rs1.MoveNext
Next gg
cmboxAName.List = ProgrammeDrop
End If
End Sub
----------------------------------------------------------------------------
'To open the records in an Excel form (Input form), I have then
written:
Private Sub cmbInputform_Click()
MsgBox ("Compulsory fields in input forms" & Chr(13) & _
"are highlighted in Blue"), vbInformation, "PLEASE NOTE:"
Unload frmMainMenu
ProjectID = cmboxAName.Text
BusinessHeadID = cmboxBH.Text
AddNewRcrd = False
Completed = False
Call Closeout
With frmInput
..Show
End With
End Sub
---------------------------------------------------------------------
Sub Closeout()
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub
---------------------------------------------------------------------
These all work fine...just need to know how to get it to extract the
records into a new workbook rather than a form...
ta.
I am trying to work out the code to get records stored in Access (but
accessed via an Excel Interface) to open up in a new Excel workbook on
clicking a button (an 'export' or 'extract' function if you like) -
both 2003 versions...
I have successfully gotten them to open up into a new form (Excel
VBA), just can't get this right...(so haven't bothered putting my
sorry excuse for an attempt below, just the SQL stuff that sits behind
the combo boxes and then the code that opens it up in a new form etc)
Note: I do not want to create a query in Access and have Excel tap
into that. I have written an SQL in the Excel form - much
smoother...simply using the Access DB as a storage can..
Really appreciate some [any!] help here...have looked at other
people's spiels and the KB to no avail!
thanks in advance..
Sue
'In a seperate module, along with other public variables I have:
Sub FindDatabasePath()
path1 = "\\xxx\xxx\xxx\xxx\xxx\xxx\xxxx"
path1 = "" & path1 & "" & "\xxx\xxx\xxx\xxx\xxx\xxx\Xxxx.mdb"
End Sub
'(clearly, x reflects the specifics of the path to be found)
--------------------------------------------------------------------------
'In the form itself I have:
Option Explicit
Dim ws As Workspace
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim BHDrop() As Variant
Dim ProgrammeDrop() As Variant
Dim SQL1 As String
Dim rscount As Integer
Dim rscount1 As Integer
Dim gg As Integer
Dim zz As Integer
Private Sub UserForm_Initialize()
Set ws = DBEngine.Workspaces(0)
Call FindDatabasePath
Set db = ws.OpenDatabase(path1)
'open up a dataset
Set rs2 = db.OpenRecordset("SELECT tblProject.[BusinessHead] FROM
tblProject WHERE Not [Phase] = 'cancelled'" _
& " AND Not [Phase] = 'Completed' AND Not [Phase] = 'Delivered' AND
Not [Phase] = 'Value Captured'" _
& " GROUP BY tblProject.[BusinessHead]")
rscount1 = rs2.RecordCount - 1
rs2.MoveFirst
ReDim BHDrop(0 To rscount1, 1)
For zz = 0 To rscount1
BHDrop(zz, 0) = rs2.Fields(0)
rs2.MoveNext
Next zz
cmboxBH.List = BHDrop
cmboxBH.Value = Empty
cmboxAName.Value = Empty
ProjectID = cmboxAName.Text
BusinessHeadID = cmboxBH.Text
End Sub
Private Sub cmboxBH_Change()
cmboxAName.Value = ""
BusinessHeadID = cmboxBH.Text
If BusinessHeadID = "" Then
SQL1 = "SELECT [ProjectName],[BusinessHead] FROM tblProject WHERE Not
[Phase]= 'cancelled'" _
& " AND Not [Phase]= 'Completed' AND Not [Phase]= 'Delivered' AND Not
[Phase]= 'Value Captured'" _
& " GROUP BY [ProjectName], [BusinessHead] HAVING [BusinessHead]Is
Null"
Else
SQL1 = "SELECT [ProjectName],[BusinessHead] FROM tblProject WHERE Not
[Phase]= 'cancelled'" _
& " AND Not [Phase]= 'Completed' AND Not [Phase]= 'Delivered' AND Not
[Phase]= 'Value Captured'" _
& " GROUP BY [ProjectName], [BusinessHead] HAVING [BusinessHead]='" &
BusinessHeadID & "'"
End If
Set rs1 = db.OpenRecordset(SQL1)
If rs1.EOF Then
Else
rs1.MoveLast
rscount = rs1.RecordCount - 1
rs1.MoveFirst
ReDim ProgrammeDrop(0 To rscount, 1)
For gg = 0 To rscount
ProgrammeDrop(gg, 0) = rs1.Fields(0)
rs1.MoveNext
Next gg
cmboxAName.List = ProgrammeDrop
End If
End Sub
----------------------------------------------------------------------------
'To open the records in an Excel form (Input form), I have then
written:
Private Sub cmbInputform_Click()
MsgBox ("Compulsory fields in input forms" & Chr(13) & _
"are highlighted in Blue"), vbInformation, "PLEASE NOTE:"
Unload frmMainMenu
ProjectID = cmboxAName.Text
BusinessHeadID = cmboxBH.Text
AddNewRcrd = False
Completed = False
Call Closeout
With frmInput
..Show
End With
End Sub
---------------------------------------------------------------------
Sub Closeout()
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub
---------------------------------------------------------------------
These all work fine...just need to know how to get it to extract the
records into a new workbook rather than a form...
ta.