- Search a record with P_ID or lastname or firstname (P_ID is Pri key)
called Form1
- Form1 search complete and a browse window pop up let the user select
a record then a msgbox pop up to confirm user open this record or not
called Form2.
- A Form3 open to edit this record from previous search (Form2)
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
