Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 29th, 2003, 09:52 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default record source

hi,

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

pls advice
nono

 
Old October 30th, 2003, 10:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old October 30th, 2003, 10:52 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:(

 
Old October 31st, 2003, 09:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old November 2nd, 2003, 09:37 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ;)
 
Old November 3rd, 2003, 09:14 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old November 10th, 2003, 10:32 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi Mixirica,

thanks, your method really works! bravo!!!

cheers
nono





Similar Threads
Thread Thread Starter Forum Replies Last Post
SOLVED - VBA Help For Row Source and Record Source eusanpe Access VBA 4 May 13th, 2008 11:58 AM
record.source WHERE date comparison Richard Lally Access 3 January 5th, 2006 08:03 PM
Update Source Record After Insert Duncan Allen SQL Server DTS 1 October 25th, 2004 10:44 AM
Record source ... does not exist tunsted Access 2 April 1st, 2004 10:03 AM
Report's record source too long Mitch Access 7 January 26th, 2004 04:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.