Compile Errors

[benkingery]

I transferred the contents of my .mdb 2003 version of access to the newer standard of .accdb. Most everything is working except a few pieces of code. I think it has something to do with object reference libraries but I'm getting an error when I try to add which ones I think will fix the problem.

I'm getting an error saying "Compile error: User-defined type not defined" when trying to run the following code:

Public Function ImportUSPS()
'Import Shipment Data onto Shipments_USPS table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM ORDERS_Shipments_USPS"
DoCmd.SetWarnings True
Dim dlgOpen As FileDialog
  Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
  dlgOpen.InitialFileName = "Y:\Shipments\USPS"
  dlgOpen.Show
  DoCmd.TransferText acImportDelim, "Shipments_USPS", "ORDERS_Shipments_USPS", dlgOpen.SelectedItems(1)
'Update Shipping Table with USPS Records
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE ORDERS_Shipments_USPS INNER JOIN Shipping ON ORDERS_Shipments_USPS.[Reference ID] = Shipping.Order_Number SET Shipping.Tracking_Number = [ORDERS_Shipments_USPS].[Tracking_ID], Shipping.Cost = [ORDERS_Shipments_USPS].[Postage ($)], Shipping.Ship_Date = [ORDERS_Shipments_USPS].[Date_Time] WHERE (((Shipping.Tracking_Number) Is Null))"
DoCmd.SetWarnings True
MsgBox ("USPS records now imported and updated")
End Function

Its specifically choking up on the part that says:

Dim dlgOpen As FileDialog

I'm also getting the same error on the following code:

Private Sub CountGroup_AfterUpdate()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
Dim mySql As String
mySql = "SELECT TOP " & Me.Number & " SafetyCount.Warehouse_Location, SafetyCount.Master_Child, SafetyCount.CountGroup FROM SafetyCount WHERE (((SafetyCount.CountGroup) Is Null)) ORDER BY SafetyCount.Warehouse_Location, SafetyCount.Master_Child"

DoCmd.SetWarnings False
myRecordSet.Open (mySql), , , adLockOptimistic
While Not myRecordSet.EOF
    myRecordSet("CountGroup") = Me.CountGroup
    myRecordSet.Update
    myRecordSet.MoveNext
Wend
DoCmd.SetWarnings True
Set myRecordSet = Nothing
Set cnn1 = Nothing
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Master_Child INNER JOIN SafetyCount ON Master_Child.Master_Child = SafetyCount.Master_Child SET Master_Child.Inv_Count_Group = [SafetyCount].[CountGroup] WHERE (((SafetyCount.CountGroup) Is Not Null))"
DoCmd.RunSQL "DELETE SafetyCount.Master_Child, SafetyCount.CountGroup, SafetyCount.Warehouse_Location FROM SafetyCount WHERE (((SafetyCount.CountGroup) Is Not Null))"
DoCmd.SetWarnings True
MsgBox ("You have added " & Me.Number & " records to " & Me.CountGroup & " !  Please return count for entering when done.")
DoCmd.Close acForm, "frm_CountGroup_MassAssignSafety", acSavePrompt
End Sub

Its getting stopped up on this part with this piece:

Dim cnn1 As ADODB.Connection

Searching for a solution leads me to believe it has something to do with object library references being different between versions of Access. I then tried to add "Microsoft DAO 3.6 Object Library" but I get an error saying: "Name conflicts with existing module, project, or object library".

I'm a little stuck. Can anyone point me in the right direction?

Thanks in advance.

 

[RuralGuy]

You want to add a reference to the ADO library, not the DAO library. Microsoft ActiveX Data Objects x.x Library.

 

[benkingery]

No luck. I added the Microsoft ActiveX Data Objects 2.8 Library. Any other ideas? There are many other library versions of ActiveX Data Objects (2.0, 2.1, 2.5, 2.6, 2.7).

I also tried adding Microsoft ActiveX Data Objects Recordset 2.8 Library. Also no luck there. I'm still getting the same compile errors.

 

[RuralGuy]

It should not be stopping at the same place in that code.

 

[spikepl]

FileDialog is in Microsoft Office 1x.x Object Library

 

[benkingery]

I think that was it, thanks spikepl

 

[gemma-the-husky]

perhaps filedialog is in a code module ..

put your cursor on filedialog, and press shift-F2, and see where it takes you ...

(in the original, i mean)

 

[boblarson]

perhaps filedialog is in a code module ..

No, spikepl had it on the money.