Open a form using search criteria

[misslee1888]

Hi

I have a form which a user uses to create a new call. I want a command button which will allow them to update a call. This form should prompt them to enter in the call Id, and show that record. If they do not know the call ID they should be able to put a (*) in the field and then be prompter to put in another search string i.e. Category and all the record would be available with that category.

I have a query which does it at the moment, however this means that I have to maintain two identical forms.

Could anyone help.


I want to be able to put code behnid a command button which will allow me to open the form at the correct record.

 

[Rob.Mills]

I think I'm understanding what you're trying to accomplish. What I've done in the past is create a small pop-up form that has textboxes for each of the areas I would like the user to be able to search on. Then a button that cycles through the textboxes and the first one it comes to that has data in it will open the form based on that criteria.

Does that help?

 

[misslee1888]

Thanks Rob

That does help me but I dont know how to do this and would this open my original form. Could you explain this to me in more detail of give me an example

 

[Rob.Mills]

Here's an example.

Let's say you have three textboxes. First is called ID, second is Date, and third is Name.

Here's the code you could put behind the button.

Dim ctl as Control
Dim txt as Textbox
dim varValue as Variant
Dim frm as Form

set frm=Forms!FormName

'Go through each control. If it's a textbox and it's not null then this will be what to search for
For Each ctl in frm.Controls

If TypeOf ctl is Textbox and Not IsNull(ctl) Then

'Now choose which way to open the form based on what textbox had criteria
Set txt=ctl

varValue=txt.Value

Select Case txt

Case frm!ID

DoCmd.OpenForm "FormName", , , "ID=" & varValue

Case frm!Date

Docmd.OpenForm "FormName", , , "Date=#" & varValue & "#"

Case frm!Name

Docmd.OpenForm "FormName", , , "Name='" & varValue & "'"

End Select

Exit For

End If

Next Ctl

This should get you started. We'll probably have to work out some bugs.

 

[misslee1888]

Rob

Having a problem, I used this code and repleced with my form and control names. I keep getting error 2450. Cannot find the form reffered to in macro expression.

I have checked that the spelling of the form is correct.

 

[Rob.Mills]

Post your code.

 

[jfgambit]

misslee:
Check out the following:

 

[misslee1888]

Thanks Rob and jfgambit

I managed to work around the setup of my for so I could get the query working, that wasnt originally. It does mean that I have two identical forms, but I think that I will manage with that.

I will keep the code and example that you both posted for me as I will probably use it in the future.


Thanks
Guys

Lisa