Copying Form from one Access db to another.

[amg0657]

Hello,

I'm new to VBA Access. I have 2 databases that have the same information.
One db is a staging db and the other db is production. I created a form in
the staging db and it successfully executes everything I want it to do.
However, when I copy the form to production, I get a compile error,
"User-defined type not defined". When I try to execute the form in
production, the first variable declaration "wb As Workbook" is highlighted.

Here is the first part of the code:

*************************************************
Public Sub Command0_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim db As DAO.Database

Set wb = Workbooks.Open("R:\Adrian\YTD DB\Weekly Open Positions
Report_Current.xls")
Set db = CurrentDb

'Deletes the previous table that exists
DoCmd.DeleteObject acTable, "Details"

strWbName = "R:\Adrian\YTD DB\Weekly Open Positions Report_Current.xls"

For Each ws In wb.Worksheets
If InStr(ws.Name, "Details") Then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
ws.Name, _
strWbName, True, ws.Name & "$"

End If

Next ws
***************************************************

Is this a problem that can be easily solved? I've gone to Tools/Options and
selected "Require Variable Declaration" but I still get the compile error.
Please advise. Thanks.

AMG

 

[Steve Sanford]

It sounds like you have a reference problem. In the staging db, open the IDE
and goto 'TOOLS/REFERENCES". Note the references that are selected,
specifically the ones for Excel. Close the database.

Now open the production db. Open the IDE and goto 'TOOLS/REFERENCES". Are
the references selected the same as the staging db references?


Another option: You could import the forms/queries/tables from the
production db into the staging db and use a switchboard (menu form) to select
which form you want to see. You could change the background colors of the
forms to help indicate which form you were in. Lots of things you could do to
ensure you were using the correct form (staging or production).


HTH

 

[Jim Burke in Novi]

The fact that you're declaring a workbook and a worksheet leads me to believe
that you must have a reference to Excel in the staging DB and not in the
production DB. Based on your description, I would create an Excel reference
in the production DB and see if that doesn't fix the problem.

 

[amg0657]

Got it working by ensuring references in both db's work the same. Thanks.

 

[amg0657]

Got it working by ensuring references were the same in both dbs. Thanks.