How to figure out what to put into my IF statement to goto or popup?

Author Name Status Posted Time Yamini Josef 1/28/2008 8:58:57 PM Author is Offline Hi all, I have an Access 2003 app where I am searching a table for records with a value (given via an inputbox). If there is only one record, I want to move to it in the current form. If there are multiple records I want to popup a form listing the records found to allow the user to select a record and load it in the first form. I have everything working on the search (meaning I can tell if there is 1 or more than one records matching)...all I need is to figure out what to put into my IF statement to goto or popup. If LnCount > 1 Then Else End If I know these are rudimentary questoins, but I am a MSSQL geek and Access is so different (as far as I can tell) Thanks in advance

Subject Author Status Date Re: How to figure out what to put into my IF state ment to goto or popup? Akshay Devsi 1/29/2008 9:55:04 PM It seems to me your structure would be more like : Code: ( text ) '<<Set variable to found key>> If LnCount > 1 Then   '<<Prepare new form showing relevant records>>   '<<Show form allowing selection of record>>   '<<Set variable to found key>> End If '<<Using variable - show record in main form>> Assuming you agree with me, which, if any, of the se pseudo lines do you think you might have diffi culty with? Post Comments Re: I figured out how to do this Yamini Josef 1/29/2008 9:56:56 PM I figured out how to do this, but I have a related question. After I create my records set, what is the best way to determine the number of records returned (value for LnCount)? I really only need to know if it is 0, 1, or greater than 1. Thanks. Post Comments Re: DCount()? Akshay Devsi 1/29/2008 9:57:39 PM I think what you're looking for is DCount(). Let me know if you need more details on the usage of it and fitting it into your context. Post Comments Re: I used this code Yamini Josef 1/29/2008 9:58:56 PM I used: Code: ( text ) Call FindRec(Me.Name) rs.FindNext strCriteria If rs.NoMatch Then     Exit Sub End If It seems a little slow though. Would using FindRec like this be slower than something like Code: ( text ) strSQL = "Select [Loan Number] from Repurchase where [Loan Number] = '" & LnNum & "' Order By RepID" Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) If Not rs.EOF Then rs.MoveLast LnCount = rs.RecordCount If LnCount > 1 Then     MsgBox ("There are " & LnCount & " records with this loan number...opening list.") Else     Me.Bookmark = rs.Bookmark         End If End If Post Comments Re: Excuse me for asking Akshay Devsi 1/29/2008 9:59:41 PM Excuse me for asking, but why use all that code instead of DCount()? I'm trying to be helpful but you don't seem to respond to what I'm saying :S Post Comments Re: I don't mean to annoy you... Yamini Josef 1/29/2008 10:00:39 PM I don't mean to annoy you...I am asking about a small part of what I am doing so I am trying to avoid changes that will impact the rest of my code. If I use DCount() won't I need to make a second hit on the table if there are more than one reocrd? Post Comments Re: Doing things remotely by web page is always mo re complicated Akshay Devsi 1/29/2008 10:01:19 PM Doing things remotely by web page is always more complicated than discussing things in front of you. That is why it is particularly important to keep a logical conversation going, tidying any loose ends. I can deal with disagreements or differences of opinion (not a problem) but only if I know about them. So I'm not annoyed with you, just wanting to establish a more thorough means of communication. As to DCount(). I can't see that it adds any overhead. If you're determining whether or not to open a form to select an individual record from a group, you have to open a recordset somehow. DCount() is as good a way as any (better than many as it is it is designed for such usage). After that, the form is either opened, or not. If not then no extra load, if it is then it would have had to have been anyway. Post Comments Re: Thanks for the Help Yamini Josef 1/29/2008 10:02:59 PM Thanks for the Help Akshay! I see your point. The other missing info is that I am trying to fix a complete Access app somebody else wrote. Of course no docs or assistance from the "programmer". I have made some progress AND bumped into other issues. I will try and be more precise with my other posts....thanks again. Post Comments Re: You know where to come Akshay Devsi 1/29/2008 10:03:56 PM Well, you know where to come if you find other issues have been left to you Post Comments Have cascaded Combo-Boxes Akshay Devsi 1/29/2008 9:55:52 PM A more typical way of doing something like this in Access would be to have cascaded Combo-Boxes. If you think this may work for you have a look in Example Filtering on a Form. Post Comments

Related Forums Get a Website Template for Free!!! Get a Website Template for Free!!! Reply to Hesus!!! Template Tutorials Any Way to Define a Method from Outside the Class