Re: If... Then... Statement

[Terri Hdokins]

Re: If... Then... Statement

I am a little stuck right and came seem to get my VBA code to work. Let me see if I can explain.
I have a master table called "tblWeeks" which contains all sales information by weeks. Then I have a temp table called "tblTemp" in which I use to bring in my week working on. I then created a unmatched query that I want to use to see if I have already brought in a week. In my VBA basic I am trying to say that if the value is "", then msgbox.... Else go ahead and do the append query.
Here is my code that I have so far. I really hope someone can help me.
Thanks in advance,
Terri Hodkins

Private Sub cmdImportPlWk_Click()

On Error GoTo cmdImportPlWk_Err

DoCmd.Echo True, ""
DoCmd.OpenTable "tblTemp", acNormal, acEdit
DoCmd.RunMacro "macDelete", , ""
DoCmd.TransferSpreadsheet acImport, 8, "tblTemp", "U:\DATAFILE\Fresh Kist Produce\DATABASE\MASTER POOL WEEK", _
True, "tblWeeks!A1:I9999"
DoCmd.Close acTable, "tblTemp"

DoCmd.OpenQuery "qryUnmatchedPoWk", acViewNormal, acEdit

If IsNull(Query![qryUnmatchedPoWk]![PoWk]) Then
msgbox ("***** YOU HAVE ALREADY IMPORTED THIS POOL WEEK *****")
DoCmd.Close acQuery, "qryUnmatchedPoWk"

Else:
DoCmd.Close acQuery, "qryUnmatchedPoWk"
DoCmd.RunMacro "macAppendPoWk", , ""
Beep
msgbox "***** IMPORT OF POOL WEEK IS COMPLETE *****", vbInformation, ""

End If

Exit Sub

 

[DALeffler]

Hi Terri,

What I think you're asking is how you can tell with VB code whether or not you're query is returning any records and dispalying a message if the query does not return any records.

I'm no expert, but the only "easy" way I know how to do that is by using Access "record set" objects and manipulating the properties and methods of those record set objects via code.

IMHO, what I think you have to do is set a record set object equal to your query record set result. If your query doesn't contain any records, then the Excel table has already been imported, right? If so, try this (using Access 97):

Private Sub cmdImportPlWk_Click()

On Error GoTo cmdImportPlWk_Err

DoCmd.Echo True, ""
DoCmd.OpenTable "tblTemp", acNormal, acEdit
DoCmd.RunMacro "macDelete", , ""
DoCmd.TransferSpreadsheet acImport, 8, "tblTemp", "U:\DATAFILE\Fresh Kist Produce\DATABASE\MASTER POOL WEEK", _
True, "tblWeeks!A1:I9999"
DoCmd.Close acTable, "tblTemp

Dim dbs as DataBase
Dim rstQryRslt as RecordSet

Set dbs = CurrentDb
Set rstQryRslt = ("qryUnmatchedPoWk",dbOpenSnapshot)

If rstQryRslt.EOF Then
'no records in rstQryRslt query...
msgbox ("***** YOU HAVE ALREADY IMPORTED THIS POOL WEEK *****")
Set rstQryRslt = Nothing
Set dbs = Nothing
Exit Sub

Else

Set rstQryRslt = Nothing
Set dbs = Nothing
DoCmd.RunMacro "macAppendPoWk", , ""
Beep
msgbox "***** IMPORT OF POOL WEEK IS COMPLETE *****", vbInformation, ""

End If

Exit Sub

This is by no means "bullet proof" - try this on a back up copy of your db first!

Ya' still interested?

Hope so,

Doug.

[This message has been edited by DALeffler (edited 06-21-2001).]

 

[Terri Hdokins]

Thank you so much!!! It worked.