Re: Please help with Search+Browse+Edit

From: MN (MN_at_discussions.microsoft.com)
Date: 03/08/05 

Date: Tue, 8 Mar 2005 12:37:01 -0800

Tim Thanks you a lot ...
But could you help me from beginner :-( I am not expert for Access. So
please patient with my dumb question.
My form1 have:
P_ID
Lastname
Firstname
Agency
Record source: dbo_personinfo

So, How can I created a button call Search on click event() and How it will
display in a list box on lower half of the form? The user can search by
Lastname if they could not remember P_ID (?)
Also, I got an error 2465 "Application-defined or Object-defined error" at
command line:
"Forms!SearchViewrecord.getselectrecord = 0 Then"
Hope to hear from you soon,
Regards,
MN

"Tim Ferguson" wrote:
> I think I understand what you want to do here: but forgive me if I have
> got the question wrong. You want to force the user to use a search form
> before opening a form on that one record. What I do is this:
>
> Create a search form: the top half of this has a couple of text boxes for
> the stuff to search on, and a command button called search. The command
> button runs code to create a SELECT query that uses the contents of the
> text boxes, usually with LIKE operators rather than =. The results of
> this query are displayed in a list box in the lower half, along with
> command buttons OK and Cancel. Cancel unloads the form, OK simply hides
> it. Other logic in the form caters for <new> record requests, etc. A
> custom form method returns the selected value from the list box: more on
> this later.
>
> Create a Public Function that calls the search form in dialog mode. When
> this returns, if the form is not loaded, then the user cancelled out, and
> the function returns an error code. If the form is loaded and hidden,
> then use the custom method to retrieve the requested record id, and
> unload the form. If the requested record is a valid record, then return
> that ID from the function. If it is the code for a <new> record, create a
> new record and return a new identifier from the function.
>
> OK so far?
>
> The bit that actually works is calling the function from the Form_Open
> event (of the main form). If the function returns the cancelled error,
> then set the Cancel argument and Access will close the form before it
> appears. If the function returns a valid record, then you create a sql
> command like "select * from mytable where table_id = 10445" and poke it
> into the RecordSource of the form.
>
>
> The whole sequence is something like:
>
> DoCmd.OpenForm MainForm
>
> -> Private sub Form_Open(Cancel as Integer) ' on Main Form
>
> dwRecordNum = GetARecordNumber()
>
> -> public function GetARecordNumber() as Long
>
> docmd.Openform SearchForm
>
> -> SearchForm is eventually hidden by user clicking "OK"
>
> -> return to GetARecordNumber()
>
> If Not IsLoaded("SearchForm") Then
> ' user cancelled
> GetARecordNumber = -1
>
> ElseIf Forms!SearchForm.GetSelectedRecord = 0 Then
> ' <new> record requested
> GetARecordNumber = MakeANewRecord()
> DoCmd.Close SearchForm
>
> Else
> ' return the selected number
> GetARecordNumber = Forms!SearchForm.GetSelectedRecord
> DoCmd.Close SearchForm
>
> End If
>
>
> -> back in Form_Open
>
> If dwRecordNum = -1 Then
> ' user cancelled
> Cancel = True
> Exit Sub
>
> Else
> strSQL = "SELECT * FROM MyTable WHERE TableID = " & dwRecordNum
> Me.RecordSource = strSQL
>
> End If
>
>
> The smart thing to do then is put a command button called OK on the main
> form, which calls the code a bit like this:
>
> ' relaunch the search form and get a new recordsource
> Call Form_Open(Cancel)
>
> ' does the user want to stop doing this now?
> If Cancel = True then DoCmd.Close acForm, Me.Name
>
> ' otherwise it's just reopened on the new record anyway
>
>
> It sounds a lot more complicated than it really is; but looks pretty
> seamless to the user.
>
> Hope it helps
>
>
> Tim F
>
>