Solved: How to promt message for NIL data

[winxmun]

Hi,
I hv a VBA code that is running a query & thereafter export to an Excel File. However there will be an error message pop up if is a NIL record. May I know how to prompt a message with "No Record" instead of an error message? Tks a lot!:dunno

DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_Expected_No_of_Redemp_CO"
DoCmd.SetWarnings True

'---------------------Export AccessQuery_Qry_Expected_No_of_Redemp_CO---------------
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset("Expected_No_of_Redemp_CO", dbOpenSnapshot)
Rst.MoveLast
intRow = Rst.RecordCount
Rst.MoveFirst
initRowExcel = 2
x = 1
y = initRowExcel
avarRecords = Rst.GetRows(intRow)
intRow = UBound(avarRecords, 2) + 1
intCol = UBound(avarRecords, 1) + 1
'-------------------open excel----------------------------------------
Set appXL = CreateObject("excel.application")
appXL.Visible = True
Set wbk = GetObject("d:\HDB_Expected_No_of_Redemp_CO.xls")
Set wbs1 = wbk.Worksheets(1)
wbk.Parent.Windows(1).Visible = True
For intRow = 0 To intRow - 1
For intCol = 0 To intCol - 1
wbs1.Cells(y, x).Value = avarRecords(intCol, intRow)
x = x + 1
Next intCol
x = 1
y = y + 1
Next intRow
Rst.Close

Happy New Year!

[winxmun]

Hi Everybody,

Anyone can help? Thanks a lot! :help

[CreganTur]

When you say "Nil" 'm guessing you mean NULL? Is the error because the recordset itself contains no records (in which case you can test for Rst.EOF. If it's true right after you open the recordset, then it contains to records). Or is the error specific to the value of a record field within the recordset?

[winxmun]

Hi CreganTur (http://www.vbaexpress.com/forum/member.php?u=14154),
Yes, I meant null record. Yes, the VBA will run the make table query and populate the result to excel file. If the query return with no record, an error message will be pop up (user will not know this is due to no record with the system error message). In this case, can Rst.EOF help? Actually I’ve tried to add the Rst.EOF before the Rst.Close, but was prompt with error message.

Pls pardon me if I understand your question wrongly as I am not good in VBA. Thanks!

[HiTechCoach]

DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_Expected_No_of_Redemp_CO"
DoCmd.SetWarnings True

'---------------------Export AccessQuery_Qry_Expected_No_of_Redemp_CO---------------
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset("Expected_No_of_Redemp_CO", dbOpenSnapshot)

if Rst.RecordCount < 1 then

Msgbox "Nothing found."
Go To CleanUp
End If

Rst.MoveLast
intRow = Rst.RecordCount



Rst.MoveFirst
initRowExcel = 2
x = 1
y = initRowExcel
avarRecords = Rst.GetRows(intRow)
intRow = UBound(avarRecords, 2) + 1
intCol = UBound(avarRecords, 1) + 1
'-------------------open excel----------------------------------------
Set appXL = CreateObject("excel.application")
appXL.Visible = True
Set wbk = GetObject("d:\HDB_Expected_No_of_Redemp_CO.xls")
Set wbs1 = wbk.Worksheets(1)
wbk.Parent.Windows(1).Visible = True
For intRow = 0 To intRow - 1
For intCol = 0 To intCol - 1
wbs1.Cells(y, x).Value = avarRecords(intCol, intRow)
x = x + 1
Next intCol
x = 1
y = y + 1
Next intRow

CleanUp:

Rst.Close
set Rst = Nothing

[winxmun]

Hi HiTechCoach,

Thank you very very much...it work perfectly fine. :friends:

[HiTechCoach]

You're welcome.

Glad we could assist.

[winxmun]

Hi,
One more question, how to prompt another message after the query/ module has completed?

Eg, if is NIL record, to prompt "No Record" but if is with record and has completed the run, to prompt "Completed".

Tks.

[HiTechCoach]

Try adding this line with MsgBox ...:

Next intRow

' Add the following
Msgbox "Complete."

CleanUp: