Searching in a simple form (1 Viewer)

[fmccarl]

I am trying to set up a search window which is made up of 5 or 6 combo boxes. With the choice of any of these, in combination or along, I would like to make a control button which will forward the appropriate ID # to a form made for editing, viewing, etc.
Please be gentle, I don't know VB.
Thanks

 

[bob makepeace]

Hi,

There are several ways to do this.

Probably the easiest is to build in the
criteria in the query underlying the screen.

If you default the combo boxes to null and
pop the code below into a module all you'll
need to do is requery the form whenever
a combo box is chosen.

Public Function cvnull(varControl As Variant) As String

If IsNull(varControl) Or varControl = "" Then
cvnull = "*"
Else
cvnull = varControl
End If

End Function

In the query underlying the form put
-like cvnull(forms!formname!comboname)- in
the criteria section.

Repeat for each combo.

If a combo isn't selected it will return all
records. If one is selected, when you
docmd.requery the form the data will be
filtered.

There are more complex ways of doing it code
but I think this method will do it for you.

If you want a seperate screen for the search
you can have a second query the same as the main screen query but with the criteria in it. When a search is selected, set the recordsource of the main form to the second query ie. forms!formname.recordsource="queryname"

Put a 'Reset' button on your main screen
to return to the criteria free query
ie.
forms!formname.recordsource = "originalquery"

hope this helps

cheers

bob


[This message has been edited by bob makepeace (edited 02-06-2001).]

 

[fmccarl]

Thanks Bob!!!
I'll try it and get back to you.

 

[fmccarl]

Sorry Bob. I'm having problems understanding how to program things.
I will give you exactly what I want, and maybe you can help.

One of the fields (ie. Movie Names) is a selection in a form. This should be a combo box so when the first few letters are typed in, the name will appear. If the movie name doesn't exist (notinlist?) then a dialog box would appear asking if they want to add this movie name. Otherwise, a button will exist beside the field to open another form with the information pertaining to movie name. Also, how can I use multiple selections to narrow down searches?

I understand if this is too much, but I could really use the help.

Thanks,

Foster

 

[bob makepeace]

Hi,

Your movie names table should have an
index. Create the combo putting the
index and the movie name in a query behind it. Then select the bound column as column 1
and show 2 columns, hiding the first so
you just see the movie names.

To add a new one to the list use something like the following code:-


'This code adds a name to the security table

Public Function fnAddName(NewData As Variant, Response)
Dim db As Database, rec As Recordset, x As Integer, Added_Field As Boolean

On Error GoTo addname_err

If MsgBox("This Name is not in the list - do you wish to add it?", vbOKCancel) = vbCancel Then
Response = acDataErrContinue
Exit Function
End If

Set rec = db.OpenRecordse("tblsecurity", dbOpenTable)

rec.addnew


rec!SName = NewData
Added_Field = True
Response = acDataErrAdded

rec.Update


function_exit:

If Added_Field = False Then
Response = acDataErrContinue
End If

fnAddName = Response


Screen.ActiveControl.Dropdown


Exit Function

To show a record based on combo boxes chosen
put a query behind a form with the combo's
as criteria as shown above ie.

like cvnull(forms!form!combo)

Put a 'View' button on the main form
which opens the second form with the criteria
in the query behind it!

hope this makes sense.

cheers

bob