command button

[awake2424]

I am trying to update a form in access 2003 using excel data. I import the data to the form by a command button and this code:
Option Compare Database
On Error GoTo Err_CmdImportExcel_Click
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="Test", filename:="C:/Documents and Settings/cmccabe/Desktop/Test.xls", HasFieldNames:=True
Exit_CmdImportExcel_Click:
Exit Sub
Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click
End Sub

However I can not save the event procedure without the following:
Private Sub Command8_Click()

but when I add this to the code i get an undefined expression error and the update does not run. Any suggestions? Thanks.

 

[dkinley]

I would think that the bits about ...

On Error GoTo Err_CmdImportExcel_Click

Exit_CmdImportExcel_Click:
Exit Sub

Err_Command8_Click:

Resume Exit_Command8_Click

Are at odds with one another. That is, if error go to cmdImport... and it has nowhere to go because the other reference is for command8...

Check the name of your command button and make all of these reference correctly. For instance, if the name was cmdDoThis then it would need to be ...

Private Sub [B]cmdDoThis[/B]_Click()
On Error GoTo Err_[B]cmdDoThis[/B]_Click
 
...
 
Exit_[B]cmdDoThis[/B]_Click:
Exit Sub
 
Err_[B]cmdDoThis[/B]_Click:
...
Resume Exit_[B]cmdDoThis[/B]_Click

-dK

 

[missinglinq]

Where re you placing this line? Assuming that your command button is, indeed, named Command8, your code should read, including the Option Compare line, should be:

Option Compare Database

Private Sub Command8_Click() 
 On Error GoTo Err_CmdImportExcel_Click
 
 DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="Test", 
 filename:="C:/Documents and Settings/cmccabe/Desktop/Test.xls", 
 HasFieldNames:=True

Exit_CmdImportExcel_Click:

Exit Sub
Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click
End Sub

 

[awake2424]

I am using the code:

Option Compare Database
Private Sub Import_Click()
On Error GoTo Err_CmdImportExcel_Click

DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="Test", filename:="C:/Documents and Settings/cmccabe/Desktop/Test.xls", HasFieldNames:=True
Exit_CmdImportExcel_Click:
Exit Sub
Err_Import_Click:
MsgBox Err.Description
Resume Exit_Import_Click
End Sub

and am getting:
Compile error: Label not defined and Private Sub Import_Click()
is highlighted

I changed the button name to Import. Thanks.

 

[awake2424]

I changed all the names to CmdImportExcel but when I click the button noting happens. Thanks.

 

[dkinley]

Sorry duder ... I can't get past the names bit, yet. It normally isn't an issue but it could be creating some havoc because of scope - so trying to eliminate that part first since it is easy. You have ...

Private Sub Import_Click()
On Error GoTo Err_CmdImportExcel_Click
...
Exit_CmdImportExcel_Click:
Exit Sub

Err_Import_Click:
...
Resume Exit_Import_Click

End Sub

Following the logic if there is an error, it is supposed to go to Err_CmdImportExcel_Click. The statement you have in this subroutine is Err_Import_Click. That is, unless you have a Err_CmdImportExcel_Click somewhere else in your project that has a higher scope then it is getting all confused on what to do.

Another look is during the error, it is supposed to go to (resume) Exit_Import_Click but has no where to go because you have it as Exit_CmdImportExcel_Click.

-dK

 

[vbaInet]

Also, below this line of code "Option Compare Database" type this:

Option Explicit

Now, click the DEBUG menu and COMPILE.