Turning an Order Form into a Search Form

D

DS

Has anyone ever turned a regular Order Entry Form into a search or find
records form. I want to hit a button to empty out the fields in the
form, then be able to type the searc criteria into the field or fields
of my choice and have the results returned back o the same form.
Any suggestions would be helpful.
Thanks
DS
 
O

Ofer

All the fields that you want to use in your form hava to be unbound, when you
hit the button look for that record filtering by the data you entered.
But because this field are unbound changing them after you retrieve the data
wont change the data in the table unless you tell it to.

Just make the all form unbound, retrieve the data from the table when you
click a button, and with a click of the button update the data using sql.
 
D

DS

Ofer said:
All the fields that you want to use in your form hava to be unbound, when you
hit the button look for that record filtering by the data you entered.
But because this field are unbound changing them after you retrieve the data
wont change the data in the table unless you tell it to.

Just make the all form unbound, retrieve the data from the table when you
click a button, and with a click of the button update the data using sql.

:
So, you saying I should have unbound fields on my form, not visible,
Then when I click my button, the other (Bound) fields will become not
visible, and the unbounf fields will become visible. Then I enter my
criteria into the unbound fields, hit a search button and the data
should appear? But here is where I am confused, how does the unbound
fields find the data, through a filter? Just thinking out loud.
Thanks
DS
 
D

DS

DS said:
So, you saying I should have unbound fields on my form, not visible,
Then when I click my button, the other (Bound) fields will become not
visible, and the unbounf fields will become visible. Then I enter my
criteria into the unbound fields, hit a search button and the data
should appear? But here is where I am confused, how does the unbound
fields find the data, through a filter? Just thinking out loud.
Thanks
DS
I found what I need. If you go into Form view and then click on records,
filter, filter by form. Thats what I need, except in my Database I won't
have any menu bars, so I need to put a command button on my form to
imitate the filter by form....I tried the button wizard but that did
nothing, any help appreciated.
Thanks
DS
 
O

Ofer

I think that the best way will be when you press a button in your form, it
will open another form with all the fields you want to filter with.
in the second form all the fields will unbound. when you click on OK to
return to the main form. on the onclick event add that code

dim SQLStr as string, MyDB as databse, MyRec as recordset
SQLStr="Select * From MyTable Where Field1 = " & me.Field1 & " Field2 = " &
Me.Field2 & " etc etc etc how many fields that you have.

set MyDB=codedb
Set MyRec = MyDB.openrecordset(SQLStr)
if MyRec.eof then
msgbox "No match"
else
Forms![MainFormName].recordsource = SQLStr
Forms.[MainFormName].requery
endif

All the fields on the main form will be bounded
 
O

Ofer

If that what you want to do then on teh event of click on the button write

me.Filter = " Field1Name = " & me.Field1Name & " Field2Name = " &
me.Field2Name

me.FilterOn =true
me.requery
 
D

DS

Ofer said:
If that what you want to do then on teh event of click on the button write

me.Filter = " Field1Name = " & me.Field1Name & " Field2Name = " &
me.Field2Name

me.FilterOn =true
me.requery

:
This is what I'm putting and I keep getting Error 2448...
Also ItemMajorCat is a Combobox.....does that matter?

Me.Filter = " ItemID = " & Me.ItemID & " ItemMajorCat = " & Me.ItemMajorCat
Me.FilterOn = True
Me.Requery

Thanks
DS
 
O

Ofer

Are Both field Numeric?
if not then you should add ' before and after
eg
Me.Filter = " ItemID = '" & Me.ItemID & "' ItemMajorCat = '" &
Me.ItemMajorCat & "'"

you should also check in your code not to add a filter if the filter fields
are null.
 
D

DS

DS said:
This is what I'm putting and I keep getting Error 2448...
Also ItemMajorCat is a Combobox.....does that matter?

Me.Filter = " ItemID = " & Me.ItemID & " ItemMajorCat = " & Me.ItemMajorCat
Me.FilterOn = True
Me.Requery

Thanks



DS
This Works....

DoCmd.RunCommand acCmdFilterByForm

Only one problem, I can't get the apply filter button to come up on the
form, it's grayed out.
Thanks
DS
 
D

DS

Ofer said:
I think that the best way will be when you press a button in your form, it
will open another form with all the fields you want to filter with.
in the second form all the fields will unbound. when you click on OK to
return to the main form. on the onclick event add that code

dim SQLStr as string, MyDB as databse, MyRec as recordset
SQLStr="Select * From MyTable Where Field1 = " & me.Field1 & " Field2 = " &
Me.Field2 & " etc etc etc how many fields that you have.

set MyDB=codedb
Set MyRec = MyDB.openrecordset(SQLStr)
if MyRec.eof then
msgbox "No match"
else
Forms![MainFormName].recordsource = SQLStr
Forms.[MainFormName].requery
endif

All the fields on the main form will be bounded
:

So, you saying I should have unbound fields on my form, not visible,
Then when I click my button, the other (Bound) fields will become not
visible, and the unbounf fields will become visible. Then I enter my
criteria into the unbound fields, hit a search button and the data
should appear? But here is where I am confused, how does the unbound
fields find the data, through a filter? Just thinking out loud.
Thanks
DS
So I ended up copying the first form, changing all of the fields to
unbound and adding this code....but the code isn't working....

Private Sub FindIt_Click()
Dim SQLStr As String, MyDB As databse, MyRec As Recordset
SQLStr = "Select * From Items Where ItemID = " & Me.SearchID & "
ItemName = " & _
Me.SearchName & "ItemPrice = " & Me.SearchPrice & "ItemType = " &
Me.SearchType & _
"ItemMenu = " & Me.SearchMenu & "ItemSalesCat = " & Me.SearchSales & _
"ItemMajorCat = " & Me.SearchMajor & "ItemMinorCat = " & Me.SearchMinor & _
"ItemPrepCat = " & Me.SearchPrep

Set MyDB = CodeDb
Set MyRec = MyDB.openrecordset(SQLStr)
If MyRec.EOF Then
MsgBox "No match"
Else
Forms![AddItems].RecordSource = SQLStr
Forms.[AddItems].Requery
End If
End Sub

The Search Fields are all unbound on the search form
The code stops on the first line.....
Any help appreciated.
Thanks
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top