Problem with my search module

  • Thread starter Thread starter Patrick..
  • Start date Start date
P

Patrick..

HI!!

I hope I'm in the right discussion group.

I've written code to help me gather info from a few tables.
Here's an example:

tempTableName = "tempTableAllFY"

Set dbs = CurrentDb

' first, get number of FY's available.
Set rsnew = dbs.OpenRecordset(" SELECT * FROM
YearPicker")
**** Will cycle through about 6-7 tables representing
the Fiscal Years currently available.

Example: fiscal Year 97/98 his in this table,threfore a
97/98 table exits as well.(filed with different info)
same thing for the other available fiscal year.

With rsnew

While Not .EOF
strAbr = .Fields("Abreviation")
If Not strAbr = "--------" Then ' it just skips-it
tmpT = "t " & strAbr & " Data"
' transfer data to temp table.

Select Case TypeOfRep
Case "Building"
strsql = "INSERT INTO " & tempTableName & " " _
& " SELECT * from [" & tmpT & "] WHERE [del
Bldg] like '" & reponse & "'"

Case "IO"
strsql = "INSERT INTO " & tempTableName & " " _
& " SELECT * from [" & tmpT & "] WHERE IO
like '" & reponse & "'"
..
..
..
endif
dbs.execute strsql

..moveNext
End With


Etc...
this code cycles through all the fiscal year tables,one by
one and inserts into a temp table the appropriate data
matching the reponse value.

Up to here,thing are OK.
The thing thats not working to good is this:
Lets say that on the first loop,no data exits matching the
Reponses Value,I should continue to the next and so on.
But if a value of zero is returned and then
the "dbs.execute strsql" is executed, it leaves the with
without checking the rest of my fiscal tables..
And the end result his,no data stored in my temp table.
Can't create my report.

can I hide an option to my "dbs.execute strsql" that
prevent him from leaving when no data his found.

I've also tried querry definition method,but it doesn't
execute-it,its like he justs skips over my code.

DO you have any idea on how to make this work..

If you need more explaination or code examples don't
exitate to ask..

PAtrick
 
I'm not sure you need this given your later post. However...

As it seems the problem occurs when your SQL string
produces no records, perhaps you could open a recordset
based on the SQL string and test to see if the recordset
contains records, with:

if rs.BOF and rs.EOF then
' no records in recordset - so do nothing.
else
dbs.execute strsql
end if

I assume you've posted a code fragment. It doesn't
show the END SELECT statement.

Good luck with your project.
Geoff
 
Back
Top