"Song Su" wrote in message
I have a continous form frmIncomplete. I put a command button in detailed
section. When clicked, I want to open frmMaster and locate the same
record by ID (primary key AutoNumber).
Private Sub cmdEdit_Click()
DoCmd.OpenForm "frmMaster", , , "[ID] = " & Me![ID]
End Sub
Above code will only display the record and filtered out rest. I don't
want to filter out. I just want to locate and display that record. Before
clicking the button, frmMaster may or may not already open. I use tabbed
browsing and I don't want to hide frmIncomplete form. How to do that?
There are so many scenarios this could need to cover that it's not a
simple piece of code. For example:
- frmMaster could be open, but filtered in such as way that the desired
record is not present.
- frmMaster could be open, and dirty with a record that cannot be saved,
so it cannot move to the desired record.
- The target record may not exist, in which case you have to define the
response you want (e.g. no opening the form, or opening it to a new
record.)
- The target form could be open in DataEntry mode.
- The current record could be a new (unsaved) entry, so it won't be found
in the target unless you explicitly save it first.
Below find some code that addresses some if these things. Paste it into a
new standard module, along with the error handler from here (or substitute
your own):
http://allenbrowne.com/ser-23a.html
Your example would then call the code like this:
Private Sub cmdEdit_Click()
If Me.Dirty Then Me.Dirty = False
Call OpenFormTo("frmMaster", "ID = " & Nz(Me.ID,0), IsNull(Me.ID))
End Sub
This is essentially what the code does:
a) Opens the form if it is not already open.
b) Saves any edits in the target form.
c) Sends the target form to a new record if the 3rd argument is true or
the 2nd argument is blank.
d) Calls Move2Record() to find the desired record.
e) Tries to match the record.
f) If not found, turns the filter off and tries again.
Watch the line breaks: the newsreader will probably mess up the longer
lines.
Hope that's useful for you Song Su. Note that you will need to do your own
debugging and testing here.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
-----------code starts-------------
Public Function OpenFormTo(strForm As String, strWhere As String, Optional
bGotoNewRecord As Boolean, _
Optional strMsg As String, Optional strOpenArgs As String) As Boolean
On Error GoTo Err_OpenFormTo
'Purpose: Open a form loaded with all records, but displaying a
particular one.
'Return: True if successful.
'Arguments: strForm = Name of form.
' strWhere = Where string to match.
' bGotoNewRecord = what to do if not found. False = give
error. True = display new.
' strMsg = string to append any error message to.
'Usage: Call OpenFormTo("frmClient", "ClientID = 64",
IsNull(Me.ClientID))
Dim frm As Form
Dim bFormWasOpen As Boolean
'Get the form open.
If IsLoaded(strForm) Then
bFormWasOpen = True
Else
If strOpenArgs <> vbNullString Then
DoCmd.OpenForm strForm, WindowMode:=acHidden,
OpenArgs:=strOpenArgs
Else
DoCmd.OpenForm strForm, WindowMode:=acHidden
End If
End If
Set frm = Forms(strForm)
'Move to the desired record.
If Len(strWhere) = 0& Then
frm.SetFocus
If HasProperty(frm, "Dirty") Then
If frm.Dirty Then
frm.Dirty = False
End If
End If
If Not frm.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If
OpenFormTo = True
Else
If Move2Record(frm, strWhere, bGotoNewRecord, strMsg) Then
OpenFormTo = True
ElseIf Not bFormWasOpen Then
If strMsg <> vbNullString Then
MsgBox strMsg, vbExclamation, "Not opened."
End If
DoCmd.Close acForm, strForm
End If
End If
Exit_OpenFormTo:
Set frm = Nothing
Exit Function
Err_OpenFormTo:
Select Case Err.Number
Case 2046& 'Can't go to record.
'do nothing
Case Else
Call LogError(Err.Number, Err.Description, conMod & ".OpenFormTo",
Left$(strForm & ": " & strWhere, 255))
End Select
Resume Exit_OpenFormTo
End Function
Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Public Function Move2Record(frm As Form, strWhere As String, Optional
bGotoNewRecord As Boolean, Optional strMsg As String) As Boolean
On Error GoTo Err_Move2Record
'Purpose: Move the bound form to the record matching the Where
string.
'Return: True if successful.
'Arguments: frm = reference to the form.
' strWhere = the WHERE clause to match.
Dim rs As DAO.Recordset
If HasProperty(frm, "Dirty") Then
If frm.Dirty Then
frm.Dirty = False
End If
End If
If bGotoNewRecord Then
'Go to a new record.
If Not frm.Visible Then
frm.Visible = True
End If
frm.SetFocus
If frm.AllowAdditions Then
If Not frm.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If
Move2Record = True
Else
strMsg = strMsg & "Form does not allow additions." & vbCrLf
End If
Else
'Find an existing record.
Set rs = frm.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
If frm.FilterOn Then 'Not found: try again without the
filter.
Set rs = Nothing
frm.FilterOn = False
Set rs = frm.RecordsetClone
rs.FindFirst strWhere
End If
End If
If rs.NoMatch Then
strMsg = strMsg & "Unable to locate the record." & vbCrLf
Else
'Display the record.
frm.Bookmark = rs.Bookmark
If Not frm.Visible Then
frm.Visible = True
End If
frm.SetFocus
Move2Record = True
End If
End If
Exit_Move2Record:
Set rs = Nothing
Exit Function
Err_Move2Record:
If Err.Number = 2449 Then 'Invalid expression: can't set focus to
form (perhaps subform?)
Resume Next
Else
Call LogError(Err.Number, Err.Description, conMod & ".Move2Record",
"Form = " & frm.Name & "; Where = " & strWhere)
Resume Exit_Move2Record
End If
End Function
-----------code ends-------------
