Re: Command button to open form and find record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In addition to IsLoaded error in my previous posting, another error is
conMod - not defined.
If I commented out these lines, everything works fine.

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message news:%231vHr$DKJHA.5232@xxxxxxxxxxxxxxxxxxxxxxx
"Song Su" <songsu@xxxxxxxx> wrote in message
news:1FC80D54-7F50-438F-94A6-583C6A22CC56@xxxxxxxxxxxxxxxx

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
Tips for Access users - http://allenbrowne.com/tips.html
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-------------


.

Relevant Pages

  • Re: Incorporating optional function argument
    ... If, for instance, you had MsgBox =Test or Debug.Print Test there would be something for the user to observe. ... Public Function test(strMsg As String, Optional blnVal As Boolean = False) ... A break point at the Public Function line does not result in the code breaking. ...
    (microsoft.public.access.formscoding)
  • Re: Command button to open form and find record
    ... The target form could be open in DataEntry mode. ... Opens the form if it is not already open. ... Public Function OpenFormTo(strForm As String, strWhere As String, Optional bGotoNewRecord As Boolean, _ ...
    (microsoft.public.access.forms)
  • Re: Command button to open form and find record
    ... I don't want to filter out. ... Opens the form if it is not already open. ... Public Function OpenFormTo(strForm As String, strWhere As String, Optional bGotoNewRecord As Boolean, _ ... Optional strOpenArgs As String) As Boolean ...
    (microsoft.public.access.forms)
  • Re: Incorporating optional function argument
    ... Public Function test(strMsg As String, Optional blnVal As Boolean = False) ... If I put this into the event property, ...
    (microsoft.public.access.formscoding)
  • Re: Incorporating optional function argument
    ... For now I have added the optional argument to the event property function calls. ... Public Function test(strMsg As String, Optional blnVal As Boolean = False) ...
    (microsoft.public.access.formscoding)