exporting data to project

[Guest]

I have the database designed by someone with the module which should export
data from the database to Microsoft Project. My problem is that it does not
work the same way on different systems, results are not the same. On some
systems filtering of the data does not work, on others exporting stops if the
date value is more than one year in the future. Could someone look into the
code of this module and tell me where are the problems coming from. Code
below:

*************************************

Attribute VB_Name = "modReportToProject"
Option Compare Database
Option Explicit

Sub ReportToProject(Optional lngGroupID As Long, Optional lngSubGroupID As
Long)
Dim cnn As Object
Dim rsExport As Object, rsExportRes As Object
Dim pj As Object, apj As Object
Dim intCounter As Integer, lngTotalRecords As Long
Dim strProjectName As String, strPath As String
Dim strCurrentAsset As String, intCreateproject As Integer
Dim strGroupName As String
Dim blnExists As Boolean


On Error GoTo Err_ReportToProject

'set variables
Set cnn = CurrentProject.Connection
Set rsExport = CreateObject("ADODB.Recordset")
Set rsExportRes = CreateObject("ADODB.Recordset")

If lngGroupID = 0 Then
rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources", cnn, 1
Else
If lngSubGroupID = 0 Then
rsExport.Open "SELECT * FROM qryExportToProject WHERE
lngGeneralGroupsID = " & lngGroupID, cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources WHERE
[tblGeneralGroups].[lngGeneralGroupsID] = " & lngGroupID, cnn, 1
Else
rsExport.Open "SELECT * FROM qryExportToProject WHERE
lngAssetGroupID = " & lngSubGroupID, cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources WHERE
lngAssetGroupID = " & lngSubGroupID, cnn, 1
End If
End If

Set pj = CreateObject("msProject.Application")

strPath = CurrentProject.Path & "\"
strProjectName = "hire"

strProjectName & """ in the path " & strPath & ". " & _
"The project will replace the existing project and take a few
minutes. " & Chr(10) & _
"Do you wish to create the project now?", _
buttons:=vbYesNo + vbInformation, _
Title:=APP_TITLE)

With pj
strGroupName = rsExportRes!strGeneralGroup
If lngGroupID = 0 Then
For Each apj In .Projects
If apj.Name = strProjectName & ".mpp" Then
apj.Activate
.docClose
End If
Next

Else
For Each apj In .Projects
If apj.Name = strProjectName & "-" & strGroupName & ".mpp"
Then
apj.Activate
.docClose
End If
Next

End If


.Visible = True
.Calculation 0 'pjManual
.FileOpen Name:=strPath & strProjectName & ".mpt"

.ViewApply Name:="Resource &Sheet"
'create the resource table
intCounter = 1
rsExportRes.MoveLast
rsExportRes.MoveFirst
lngTotalRecords = rsExportRes.RecordCount

Do While Not rsExportRes.EOF
.SetResourceField "Name", rsExportRes!AssetName.Value, , ,
intCounter
.SetResourceField "Initials", rsExportRes!lngAssetCode.Value, ,
, intCounter
.SetResourceField "Code", rsExportRes!lngAssetCode.Value, , ,
intCounter
.SetResourceField "Group", rsExportRes!strGeneralGroup.Value, ,
, intCounter

rsExportRes.MoveNext
Forms!Switchboard.Option3.StatusBarText = "Creating Project:
Loading Resources " & Format(intCounter / lngTotalRecords, "0%") & "
complete!"
intCounter = intCounter + 1

Loop
rsExportRes.Close

'for each asset in the database
.ViewApply "&Gantt Chart" 'Name:="&Gantt Chart"
strCurrentAsset = ""
intCounter = 1
rsExport.MoveLast
lngTotalRecords = rsExport.RecordCount
rsExport.MoveFirst

Do While Not rsExport.EOF
If strCurrentAsset <> rsExport!strName.Value Then
.SetTaskField "Name", rsExport!strName.Value, , , intCounter
.SetTaskField "Resource Names", rsExport!strName.Value, , ,
intCounter

If .Projects(strProjectName).Tasks(intCounter).OutlineLevel
= 2 Then
.Projects(strProjectName).Tasks(intCounter).OutlineOutdent
End If
strCurrentAsset = rsExport!strName.Value
intCounter = intCounter + 1
lngTotalRecords = lngTotalRecords + 1
End If

If Not IsNull(rsExport!strJobName.Value) Then
If rsExport!dteHireStart.Value <
..Projects(strProjectName).ProjectStart Then
.Projects(strProjectName).ProjectStart =
rsExport!dteHireStart.Value
End If

.SetTaskField "Start", rsExport!dteHireStart.Value, , ,
intCounter
.SetTaskField "Name", rsExport!strJobName.Value, , ,
intCounter
.SetTaskField "Duration", rsExport!dteHireFinish.Value -
rsExport!dteHireStart.Value, , , intCounter
If Not IsNull(rsExport!intServiceDuration.Value) Then
.SetTaskField "Finish10", rsExport!dteHireFinish.Value +
rsExport!intServiceDuration.Value, , , intCounter
Else
.SetTaskField "Finish10", rsExport!dteHireFinish.Value,
, , intCounter
End If

If Not IsNull(rsExport!WShopLoc.Value) Then .SetTaskField
"Text10", rsExport!WShopLoc.Value, , , intCounter

If rsExport!StatusID = 2 Then 'confirmed
.SetTaskField "Flag2", 1, , , intCounter
ElseIf rsExport!StatusID = 1 Then 'potential
.SetTaskField "Flag1", 1, , , intCounter
ElseIf rsExport!StatusID = 3 Then 'go line
.SetTaskField "Flag3", 1, , , intCounter
End If

.SetTaskField "Rollup", "Yes", , , intCounter
.SetTaskField "Text1", rsExport!strCustomerName.Value, , ,
intCounter
.SetTaskField "Resource Names", rsExport!strName.Value, , ,
intCounter

If .Projects(strProjectName).Tasks(intCounter).OutlineLevel
= 1 Then
.Projects(strProjectName).Tasks(intCounter).OutlineIndent
End If
intCounter = intCounter + 1
End If
rsExport.MoveNext
Forms!Switchboard.Option3.StatusBarText = "Creating Project:
Loading Tasks " & Format(intCounter / lngTotalRecords, "0%") & " complete!"

Loop
rsExport.Close

.Alerts False
.Calculation -1 'pjAutomatic
.EditGoTo 1, Format(Now(), "d/mm/yyyy hh:mm")

If lngGroupID = 0 Then
.FileSaveAs Name:=strPath & strProjectName
Else
.FileSaveAs Name:=strPath & strProjectName & "-" & strGroupName
End If
End With

Forms!Switchboard.Option3.StatusBarText = ""


Exit_ReportToProject:
Set rsExport = Nothing
Set rsExportRes = Nothing
cnn.Close
Set cnn = Nothing
Set pj = Nothing
Exit Sub

Err_ReportToProject:
MsgBox Err.Number & ": " & Err.Description

Resume Exit_ReportToProject
End Sub

*************************************

Thanks for help.

 

[TC]

Tony, these newsgroups really work best when you ask specific
questions, that people can answer "off the cuff".

You say that someone else designed it for you. Shouldn't they debug it
also? Can't you get them back, to fix it?

HTH,
TC

 

[Guest]

Hi TC,

Thank you for your response. The problem is that the author of this module
is not contactable and I need to figure out the problem myself, hopely with
help from this newsgroup. That is why I posted my question. I hope that
someone will find the error I can not see. It is strange that the same
database run on computers with the same version of Windows, Access and
Project gives different results. If error will be consistant it may be
easier, but it is not and that makes things difficult.
Once again thank you for your response.

Regards,

Tony

 

[TC]

Ok. The problem, from our viewpoint, is that the error is not a simple
one (like a compile error) that we could help you quickly with. The
problem, from our viewpoint, is that the code does not match your
expectations of how it should work. But we don't /know/ those
expectations, so it will be very difficult for us to say what might be
wrong with the code.

Let's start here:

(1) What do you mean when you say that on some systems, "filtering of
the data does not work"? What /specifically/ does or does not happen to
caus ou to say that? Can you give some specific examples (including
data values) that illustrate this problem (whatever it is)?

(2) Similarly, what exactly do you mean when you say that "exporting
stops if the date value is more than one year in the future"? What
specifically does or does not happen? What is a specific date which
causes that to happen or not happen?

HTH,
TC

 

[Guest]

Thank you for your response again. To answer your questions:

1. Then number of exported items is different and the difference is large.
On one system it is exporting 290 records while on the second it is exporting
807 records. On first system it is exporting records as selected in selection
form while on second system it is exporting all records, selection is ignored.

2. On one of the systems, the ones which export less records, exporting
stops once the date selected is more than year in advance. On the other
systems changing of the dates in some records does not have any impact on
results.

I have tried to do filtering in the query outside this module but this was
giving me errors in this module in the If area with rsExport.Open. At the
moment I am going to try to setup the query which will prepare the data and
once it will be right I will try to export it to Project. I need to replace
this trouble If area. What I am looking for is what is the correct syntax to
replace the lines:

rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1

so that I will just open the modified qryExportToProject. Should I just use

rsExpor.Open

without any parameters ?

Thank you for your help.

Regards,

Tony

 

[TC]

You say that it works differently on different systems. Are you
/absolutely positive/ that you are running the /exactly same data/ on
each PC?

The only way that you could do that, would be to create two identical
copies of the database, and run one of them on each PC. It's no good to
run the database on one PC, then take it to another PC and run it on
that one. The first run might change the data, causing a different
result on the next run.

Tony, I really think that this will be too hard to do, over the net,
for free. Do you have any local Access people that could look at it for
you?

HTH,
TC

 

[Guest]

Thank you once again for your responses.

Yes, the data is the same, versions of Windows, Access and Projects are the
same. But I have not checked the systems settings yet. It could be that the
date formats are different and this is the source of the different results. I
will check all and should find what is happening. I have already fixed many
bugs in this database and I hope I will manage to fix this one too. I was
just hoping that maybe someone had similar problem / experience in the future
and this will point me to the right direction. Looks like I have to spend
more time and check all settings.

Thanks for help.

Tony

 

[TC]

Yes - dates can be tricky. Maybe that could be it!

When you reference dates in SQL statements, they have to be enclosed in
cross-hatches, for example:

WHERE DateDue=#6/9/2006#

The trick is, that these "cross-hatched" dates must be specified in
American (month/day/year) format, << regardless of the PC's locale
settings >>. So the example date above, would be taken as June 9 2006 -
not 6 September 2006 - << regardless of the PCs locale settings. >>

Of course, you do not often have fixed dates, in SQL statements. But
you often /do/ have dates as parameters, or obtained from a form
control, or a global variable, or whatever. Or, you might build-up an
SQL string at runtime. So things like this can be very risky, unless
you know /for certain/, how the date will be formatted:

sql = sql & "#" & me![txtDateDue] & "#"

HTH,
TC

 

[TC]

Purely by coincidence, I just saw this thread; see if it helps:


http://groups.google.com/group/microsoft.public.access/msg/d98bf8059a444e86

HTH,
TC

 

[Guest]

Hi TC,

Trying to solve my problem I am trying to put filtering into the queries
before exporting results to MS Project. But this is giving me the error:

-2147217904
No value given for one or more required parameters

in the line:

rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1

When I will run this query it has correct data. What parameters are missing,
how I should modify this line.

When I remove filters in query error dissapears.

Regards,

Tony

 

[TC]

Sorry, I only just saw your reply. If you're still around, can you show
me the code pertaining to rsExport? How do you declare it & put
something into it?

You say that you are "filtering", but there is no "filtering" evident
in the statement you've shown.

TC [MVP Access]

 

[Guest]

Hi TC, thank you for your reply again.

As you could see in my original message the filtering of the data should be
done by:

*****************************************

If lngGroupID = 0 Then
rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources", cnn, 1
Else
If lngSubGroupID = 0 Then
rsExport.Open "SELECT * FROM qryExportToProject WHERE
lngGeneralGroupsID = " & lngGroupID, cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources WHERE
[tblGeneralGroups].[lngGeneralGroupsID] = " & lngGroupID, cnn, 1
Else
rsExport.Open "SELECT * FROM qryExportToProject WHERE
lngAssetGroupID = " & lngSubGroupID, cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources WHERE
lngAssetGroupID = " & lngSubGroupID, cnn, 1
End If
End If

***************************************

lngGroupID and lngSubGroupID should be used for selection and filtering of
rsEsport and rsExportRes. As I have indicated I have tried to put filtering
into the source queries instead but this was not working too. I am still
trying.

Regards,

 

[TC]

Hi TC, thank you for your reply again.

No probs. But unfortunately I am quickly running out of ideas! Also I
do not know ADODB. But let's take one more try in any case.

rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources", cnn, 1

Those look ok to me. But you say that you are getting error -2147217904
"No value given for one or more required parameters" on the first of
those statements. So, does qryExportToProject require parameters? If
so, shouldn't you provide the relevant parameter value, using the
appropriate ADODB syntax? Maybe a "parameters" method, or somesuch?

rsExport.Open "SELECT * FROM qryExportToProject WHERE
lngGeneralGroupsID = " & lngGroupID, cnn, 1

That looks ok to me.

rsExportRes.Open "SELECT * FROM qryExportResources WHERE
[tblGeneralGroups].[lngGeneralGroupsID] = " & lngGroupID, cnn, 1

Are you sure that this query does actuall expose the table/alias name
"tblGeneralGroups"?

rsExport.Open "SELECT * FROM qryExportToProject WHERE
lngAssetGroupID = " & lngSubGroupID, cnn, 1
rsExportRes.Open "SELECT * FROM qryExportResources WHERE
lngAssetGroupID = " & lngSubGroupID, cnn, 1

Those look ok to me.

HTH,
TC [MVP Access]