compile error

[graveyard]

dear all, i was using a code to automate export from access to excel (_ConsolidatedCCRraw).

ive had no problem till recently when i activate it and it returns me a compile error message - variable not declared. I shut down my database and revive it and notice sometimes the code works after that while it doesnt work the other times.

can anyone advise me what went wrong here? Thanks - psl find code below



Private Sub Command58_Click()
Dim rsCountries As DAO.Recordset
Dim rsCountry As DAO.Recordset
Dim dDao As DAO.QueryDef
Dim lblStatus_OrigCaption As String
Dim strFile As String
Dim lblstatus As String
Dim lblstatus As String

With lblstatus
.Visible = True
lblStatus_OrigCaption = .Caption
.Caption = "Status: Extraction in progress ... "
Me.Repaint

Set dDao = CurrentDb.QueryDefs("ConsolidatedCCRraw By Country")

Set rsCountry = CurrentDb.OpenRecordset("SELECT distinct(ConsolidatedCCRraw.[country]) from ConsolidatedCCRraw order by 1")

If rsCountry.EOF And rsCountry.BOF Then
Exit Sub
End If

While Not rsCountry.EOF
.Caption = "Status: Extraction in progress ... " & rsCountry.Fields(0)
Me.Repaint

strFile = GetDBPath & rsCountry.Fields(0) & "_ConsolidatedCCRraw.xls"
If File_Exists(strFile) Then
Kill strFile
End If
dDao.SQL = "SELECT * from ConsolidatedCCRraw where ConsolidatedCCRraw.[country] = '" & rsCountry.Fields(0) & "'"
DoCmd.TransferSpreadsheet 1, 8, "ConsolidatedCCRraw By Country", _
GetDBPath & rsCountry.Fields(0) & "_ConsolidatedCCRraw.xls", True

rsCountry.MoveNext


Wend
.Caption = "Status: All extract completed"
Me.Repaint
Set rsCountries = Nothing
Set rsCountry = Nothing

MsgBox "ConsolidatedCCRraw By Country - Complete!", vbInformation + vbOKOnly, "ConsolidatedCCRraw Extract"
.Caption = lblStatus_OrigCaption
Me.Repaint
.Visible = False
End With
End Sub

 

[Beetle]

Which line is highlighted when you go into debug mode?

Have you tried stepping through the code?

You might want to have a look at this thread.

 

[ChrisO]

Dim lblstatus As String
Dim lblstatus As String

Please compile your code.

 

[graveyard]

Dim lblstatus As String
Dim lblstatus As String

Please compile your code.

hi beetle, the "With lblStatus" (the code right after the initial "dim" section) get highlighted. sorry i am not good with vba and this code was done up by a frenz whom i cannot reach now. i will try to step in but if u have a quick solution, kindly advise me.


hi ChrisO, i have realized that was duplicated when i ran through the code and system prompted me i had entered same. i have taken this off before i post this - sorry for not giving the latest revised code. Still i am having the compile error

 

[thechazm]

Your lblStatus looks like you are trying to reference a control and not use a string variable. Remove both of the lblStatus dims and make a direct call to the form setting the visible property that way. Eg.

Forms![Your form name].lblStatus.Visible = True

Put your form name in between the brackets.

 

[graveyard]

Your lblStatus looks like you are trying to reference a control and not use a string variable. Remove both of the lblStatus dims and make a direct call to the form setting the visible property that way. Eg.

Forms![Your form name].lblStatus.Visible = True

Put your form name in between the brackets.

hi thechazm, it works!! thank you so much for your advice!!!