Quick question DoCmd.OpenTable

[stephaniem]

Trying to open a table I have populated through code... Cant figure out why it is snarking at me that it wants an = sign for this command?

DoCmd.OpenTable(strTable, acViewPivotChart, acEdit)

Anyone have an idea?

 

[jdraw]

I suggest you tell us in English what you are trying to do. We only know what you tell us, so it helps if we understand your environment, a bit of your business, your database and what you are trying to achieve.

DoCmd.OpenTable is not the most common command.

 

[stephaniem]

I was trying to avoid that, since it has to do with another post on here. But, I'll give it a shot. I plan on going back to my original post and posting what I did, but I had this hiccup.


-----
I have two recordsets. One has all of the records with a classification of "Development" and "New Business". The other starts blank.

The input is a date range (DateStart, DateEnd)

The code takes the date range and populates the blank table (ProjectCount) with a record for each month within the given date range.

Then the code compares each month to the start and end date of each project and creates a tally for both "Development" and "New Business" records in each month. The ProjectCount table now looks like this:

ProjectMonth ProdDevCount NewBusCount
1/1/2011 3 2
2/1/2011 5 0
3/1/2011 2 6
etc



What I'm trying to do is now take that blank table that is now fully populated and open it in pivotchart mode.

I though DoCmd.OpenTable would work, but it won't accept it, saying:

Compile Error: Expected "="

Code below: warning - this is very rough... I'm still figuring out the kinks. The approach probably isn't ideal either.

 Dim rs As DAO.Recordset
 Dim temptable As DAO.Recordset
 Dim intMonthEntered As Integer
 Dim PDcount As Long
 Dim NBcount As Long
 
'set Project Query as rs
'set tblTempProjectCount as temptable. data is populated and deleted during code.
Set rs = CurrentDb.OpenRecordset("Count of Projects Query")
Set temptable = CurrentDb.OpenRecordset("tblTempProjectCount")
'determine the number of months of data requested. Populate temptable with a record for each month in the given range.
For intMonthEntered = 0 To DateDiff("m", Forms!frmOpenProjectCount.DateStart, Forms!frmOpenProjectCount.DateEnd)
    temptable.AddNew
    temptable.Fields("ProjectMonth") = DateAdd("m", intMonthEntered, Forms!frmOpenProjectCount.DateStart)
    temptable.Update
Next
'Loop through temp table (which has a record for each month requested).
Do While Not temptable.EOF
    PDcount = 0
    NBcount = 0
    rs.MoveFirst
    'Loop through query that returned all product development and new business records.
    Do While Not rs.EOF
    'check entry data and last update or open/closed status
    If rs.Fields("EntryDate") <= temptable.Fields("ProjectMonth") And (rs.Fields("MaxofDate") >= temptable.Fields("ProjectMonth") Or rs.Fields("Closed") = "False") Then
        'If classified as development, add a count to Product Development tally
        If rs.Fields("Status") = "Development" Then PDcount = PDcount + 1
        'If classified as new business, add a count to New Business tally
            If rs.Fields("Status") = "New Business" Then NBcount = NBcount + 1
    End If
    'grab the next project record
    rs.MoveNext
    Loop
    'Edit the temporary table for selected month. Insert PDcount and NBcount as values in the record for month in temptable
    temptable.Edit
    temptable.Fields("ProdDevCount") = PDcount
    temptable.Fields("NewBusCount") = NBcount
    temptable.Update
'Grab the next month in temptable
temptable.MoveNext
Loop
 
DoCmd.OpenTable (temptable, acViewPivotChart, acEdit)

 

[jdraw]

Can you post a zip of your tables and relationships?

 

[stephaniem]

Really can't... a lot of it is intellectual property with Product Development.

I know, that would make life a lot easier.

 

[stephaniem]

Finally tried about everything I could think of and this worked

DoCmd.OpenTable "TableName", acViewPivotChart

For some reason, it really didn't want parentheses.

 

[gemma-the-husky]

it was syntax. either

call DoCmd.OpenTable(strTable, acViewPivotChart, acEdit)

or

DoCmd.OpenTable strTable, acViewPivotChart, acEdit