record source

damnnono_86 · October 29th, 2003, 09:52 PM

hi,

how do i check if a record source is blank???

pls advice
nono

SerranoG · October 30th, 2003, 10:24 AM

Well, first your form/subform/report/subreport will come up empty. Also, you can do some error checking by using the RecordSource property. For example, if you have a subform on your main form, you can check it's record source by putting this code in:

Code:

MsgBox Me.sfrMySubForm.Form.RecordSource, vbInformation, "Subform's Record Source"

Where you substitute the real name instead of "sfrMySubform."

In a report, there is a "On No Data" event on the report's (main) properties. You can write code that will trigger if the report is empty.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

damnnono_86 · October 30th, 2003, 10:52 PM

hi, i am not checking if my subform has any data anot. i actually wanted to check if there are any records in my database. if records matches then it will display otherwise an error msg will be prompt.

this is what i have in my code:
....
me.recordsource = "Select table1.name,table1.ID,table1.address from table1 where table1.name = forms!table1!txtID;"

me.requery
.......

'table1.name = forms!table1!txtID' is a textfield wheere user will enter the search criteria. and base on the criteria it will search the database to check if there are any matched data.

i am not using any recordset. so what can i do to check if the data is empty so as to display the msgbox???

pls advice
nono:(

SerranoG · October 31st, 2003, 09:16 AM

Quote:

quote:Originally posted by damnnono_86
me.recordsource = "Select table1.name,table1.ID,table1.address from table1 where table1.name = forms!table1!txtID;"

me.requery

Ohhhhhh... you're not checking to see if there is data in your table; what you're trying to do is filter the data to show only one subset of data from your entire table.

You can leave the record source alone as Table1 and just put this on txtID's AfterUpdate event:

Code:

   Me.FilterOn = True
   Me.Filter = "[name] = '" & Me.txtID & "'"

If there is no data, the form will come up empty. That's evidence enough, you don't need a message! And when you want to reset the form to ALL data (use a button), you can use this on the button's OnClick event:

Code:

   Me.Filter = ""

By the way, be careful with statements like this:

Code:

me.recordsource = "Select table1.name,table1.ID, _
   table1.address from table1 where table1.name = forms!table1!txtID;"

Be sure to separate out the variables from the strings. It should be written like this:

Code:

me.recordsource = "Select table1.name,table1.ID, _
   table1.address from table1 where _
   table1.name = '" & forms!table1!txtID & "';"

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

damnnono_86 · November 2nd, 2003, 09:37 PM

hi i dont see a need in the filter to be place on the after event of my primary key becoz in my situation, the data is already filter properly and the only thing i want to solve is to actually inform the user that there is no such record if they have entered a criteria that does not matches. that is the reason y i wanted to have a msgbox. any idea???

in addition, i have a clear button where i have place the code

me.filter = ""

but to no results as when i click on the clear button, nothing happens. do u mean that the code above actually helps to clear all the fields, to make them empty when click on the clear button? if it is so, pls give me so guidance on that too.

thanks
nono ;)

SerranoG · November 3rd, 2003, 09:14 AM

Nono, the statement Me.Filter = "" or any other assignment will not work unless on the form's On Open Event you have Me.FilterOn = True.

To check for data based on criteria, how about...

Code:

Dim rs as Object

Set rs = Me.Recordset.Clone
rs.FindFirst "enter correct criteria here"
If rs.NoMatch Then
   MsgBox "No match found.", vbExclamation, "Data Search"
Else
   'Do something else, for example.  To synchronize the form to the 
   'searched item if found...
   Me.Bookmark = rs.Bookmark
End If
rs.Close

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

damnnono_86 · November 10th, 2003, 10:32 PM

hi Mixirica,

thanks, your method really works! bravo!!!

cheers
nono