start a form in finding mode 2

[tatika]

Does anyone know how to start a form in finding mode?

My goal is to open a blank form, enter a piece of information, and receive back a complete information pertaining to that record.

Is it possible?

Thanks in advance

 

[AccessGuruCarl]

tatika

There was a FAQ posted here, seems the link has been deleted or moved.

Luckily I copied the code, can't give credit where due, I didn't keep the 'posters' name.

1st...
Paste this code into a new module.

Option Compare Database
Option Explicit

Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
    
    '  Create criteria for WHERE clause.
    If FieldValue <> "" Then
        '  Add "and" if other criterion exists.
        If ArgCount > 0 Then
            MyCriteria = MyCriteria & " and "
        End If

        '  Append criterion to existing criteria.
        '  Enclose FieldValue and asterisk in quotation marks.
        MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))
        
        '  Increase argument count.
        ArgCount = ArgCount + 1
    End If
End Sub

2nd...
Create a blank form with a couple of textboxes and a command button. Save it as frmSearch

Name the textboxes - Find1,Find2
Name the command button View
This form as 2 events, the form load, and the button click.

Paste this code on frmSearch.

Option Compare Database
Option Explicit

Private Sub cmdView_Click()
'On Error GoTo Err_VIEW_Click
'------------------------------ Starting here ----------------------
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim db As Database
Dim rs As Recordset
Dim intRecCount As Integer
  Set db = CurrentDb
    '  Initialize SELECT statement.
    MySQL = "SELECT * FROM [tblScouts] WHERE "
    
    '  Use values entered in text boxes in form header to create criteria for WHERE clause.
        'text box name on form     'Field name in Table  'blank info ' number of times run Addt
    AddToWhere [Find1], "[ScoutName]", MyCriteria, ArgCount
    AddToWhere [Find2], "[CarWeight]", MyCriteria, ArgCount
   
    '  If no criterion specifed, return all records.
    If MyCriteria = "" Then
        MyCriteria = "True"
    End If

    '  Create SELECT statement.
    MyRecordSource = MySQL & MyCriteria

    ' Optional Order By clause
    If Me![Find1] <> "" Then
        MyRecordSource = MySQL & MyCriteria & " ORDER BY [ScoutName]"
    ElseIf Me![Find2] <> "" Then
        MyRecordSource = MySQL & MyCriteria & " ORDER BY [CarWeight]"
    Else
        MyRecordSource = MySQL & MyCriteria & " ORDER BY [ScoutName]"
    End If
  Set rs = db.OpenRecordset(MyRecordSource)
  intRecCount = rs.RecordCount
  rs.Close
If intRecCount > 0 Then
     ' display the subform
    Me![SearchsubForm].Form.Visible = True
     ' set record source to Subform
    Me![SearchsubForm].Form.RecordSource = MyRecordSource
Else
MsgBox "Sorry, No Match Found!"
    If Me![SearchsubForm].Form.Visible = True Then
    ' hide the subform
    Me![SearchsubForm].Form.Visible = False
    End If
End If
Exit_VIEW_Click:
    Exit Sub

Err_VIEW_Click:
    MsgBox Error$
    Resume Exit_VIEW_Click

End Sub

Private Sub Form_Load()
' hide the subform
Me![SearchsubForm].Form.Visible = False
End Sub

3rd...
Create a form that will display your results.
Based on a table or query. Save it as frmSearchSubForm
After you have the form layout designed, open the forms properties, delete the recordsource. Now if you open this form, you'll see a bunch of #Name? in each field. Close the form, and add this form to the search form using the subform wizard.

You could add another click event on the subform to open other forms, or what ever else is needed.

Hope this helps


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[RoyVidar]

Wouldn't that be DougP, faq702-1177?

Roy-Vidar

 

[AccessGuruCarl]

That would be it Roy.

I kept the entire link...

http://www.tek-tips.com/faqs.cfm?spid=702&sfid=1177

This is the results when I try to follow the link.
Tek-Tips - Page Not Found
The page you tried to access was not found on the server. It may have been moved or deleted.

If you have any questions, please feel free to contact Tek-Tips Forums Management.

Carl




AccessGuruCarl
Programmers helping programmers
you can't find a better site.