Code stops working despite correct References

[iknowkungfu]

When I import all of the objects in a sample database, the code that worked before no longer works. I have been through and checked the References in the editor, and they are identical.

I am working in Access 2002, and I think the sample database was made in 2003. They are both in Access 2000 format and always have been.

If I import all objects from the sample database into my own, and reference the appropriate libraries, it does not work. If I import the appropriate objects (which I made/wrote) from my database into the sample one it works. The code is pretty much identical.

If I create a blank database and import everything from either my own or the sample, and reference the libraries, it does not work.

I know this is probably something simple...

 

[alcifer_c]

Compile the code then

do a compact and repair.

After having my hard drive replaced and certain files recovered, I ended up with two sets of settings for Access and actually all Office applications. This was causing all kinds of weird behaviour. Same symptoms you describe.

You can check if you have duplicate settings by going in the control panel, admin tools, computer management and then opening system information folder. I had an entry for both Office10 applications and office2003. Office10 is the old version and 2003 the new.

Good luck... very frustrating problem to figure out for me.

Al

 

[iknowkungfu]

I'm on the work network machines. We have Office 10 and nothing else. I have just tried Compact and Repair.

Specifically, the error is: "Run-time error '13' type mismatch"

Private Sub Export_Test_Crosstab_Click()
    Dim xlApp As Excel.Application
    Dim xlSheet As Excel.Worksheet
    Dim xlWorkbook As Excel.Workbook
    Dim acQuery As QueryDef
    Dim objRST As Recordset
    Dim strQueryName As String
    Dim strSheetName As String
    strQueryName = "employee"
    strSheetName = Left(strQueryName, 31)
    strSheetName = Trim(strSheetName)
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWorkbook = xlApp.Workbooks.Add
    Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
    
    Set xlSheet = xlWorkbook.Sheets(1)
    With xlSheet
        '.Cells.CopyFromRecordset objRST
        '.Cells(A.1).Value = "Hello World"
        .name = strSheetName
    End With
      
    Set objRST = Nothing
    Set xlSheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing
    
End Sub

It runs until:
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
It opens excel, but does nothing else.

I get an identical error for exporting to powerpoint.

This is an example literally out of the book. You can download the working version supplied by the authors here It is the mdb called 'Sample'.

 

[alcifer_c]

Hello,

The problem might be with the data. Maybe one machine has different settings for date or something? In your sql, try formatting the date to the format that you want, just to make sure.

Al

 

[Bat17]

probably the order of the references
just to make sure be specific.

Dim acQuery As DAO.QueryDef
Dim objRST As DAO.Recordset

HTH

Peter

 

[iknowkungfu]

Ah.... ambiguous code. Thanks Bat.

WHY ON EARTH doesn't VB/VBA pick up on this? I'm really beginning to hate the loose, silent-error-handling, "RAD" environment! Apparently VB.NET/VB7 is different...