Simple Search Form

  • Thread starter Thread starter maparchitects
  • Start date Start date
M

maparchitects

Hey peeps,

Need some help. I am creating a register for all of our archived drawings and
want to create a simple search form. How would I go about linking the form to
the tables with the data. I want the form to act as a lookup. e.g. if i want
to look up details on job number 044 i would type in 044 and the info on 044
would appear?

Any ideas?
 
The most usual way to do this is with a combo box. It makes it really easy
for the user if you set the Auto Expand property to yes. That way the user
can start typing and it will follow their typing until the one they want
appears in the text box portion of the combo and they can hit enter or, if
they prefer, they can use the drop down portion to scroll to it and click on
it. Then you use the After Update event to make the selected drawing the
current record.
Here is an example of how that is done:

Dim rst As Recordset

If Not IsNull(Me.cboDrawing) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Drawing] = '" & Me.cboDrawing & "'"
If rst.NoMatch Then
If MsgBox("Add Drawing Number " & Me.cboDrawing & " To the
Drawing Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Drawing Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtDrawing = Me.cboDrawing
Else
Me.cboDrawing = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.cboDrawing = Null
Ebd If

This will also give the user the ability to add a new drawing. One this to
add to this is that if you do add a new drawing, it will not show up
immediately in the combo's drop down list. You will have to Requery the
combo to make that happen. Where you do it depents on how your form is
designed. I typically do that in the After Insert event of the form. The
code below will requery the entire form, which will include the Combo, and
position you on the new drawing you just added:

Application.Echo False
strCurrDrawing = Me.txtDrawing
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Drawing] = '" & strCurrDrawing & "'"
Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing
Application.Echo True

Note that the combo is not bound to a field in the table. I have an
invisible text box that does that. That is txtDrawing as referenced in the
code above. Note that I set the text box to the value in the combo in the
combo after update event
 
Back
Top