[midnightcamaro69]

Hi, I am trying to learn Microsoft Access. I have made a database about some movies as an example. The thing is that I want to make a Search Form where the User types in the information he knows about a movie and the results show up listing possible Matches. I have found a few examples but they were made in 2003 and previous versions so there are some hidden tables which I cant see and none of the examples explain what they are doing.

I know there is a way to do it, I have uploaded the Zip file so please guide me step by step on how to make a Search Form.

Thank You Very Much.

[OBP]

midnightcamaro69, welcome to the Forum.
It shouldn't really matter whether the database is in Access 2000-2003 or Access 2007 as to whether you can learn from it.
I can recommend a good post to learn from as Computerman was learning about Access and created the same kind of database, you can find it here http://forums.techguy.org/business-a...-database.html

First of all you need to decide what sort of Search form you want, do you want one that is based on the normal Form(s) that you use for data entry?
Or do you want a separate search form that will display the results of your search and then maybe open a selected movie record, which is what Computerman's database does?

PS, I can't open Access 2007 databases.

[midnightcamaro69]

Hi OBP, Basically I have set out a table which has all the fields and stores the actual Data. One of the forms I have created by using the Form Wizard will be used to add the information and another form created using the same method will be used to Search. Now I have added the desired Text Boxes that build up the criteria for search and a search button. I am now in the Visual Basic Viewer and on the event Button_Onclick. I have no idea what code I should put in, I do know some bits about visual basic but I have no idea what I need to put down in the code section. I dont know whether I should use this method or put the Table inside the search form as a subform.

I have used something like this in my code:

Dim varCrt as String

varCrt = Me.txtName.value (txtName is the textbox for Search Criteria)

Me.Filter = varCrt
Me.FilterOn = True

The above code does nothing, sometimes it makes all the fields that display the records blank or gives me an input box saying Add Parameters for txtName.

Thank you.

[OBP]

Is the Search form is "Bound" to the table, ie is the table the Record Source for the Form?
If it is you will need to set the Form's Filter as you are trying to do.
If it isn't, it best to use either a Query to filter the data or a VBA SQL generated query.

To set the form's Filter you use this kind of Format
Dim testdate As Date
testdate = Format(Date - 1, "mm/dd/yyyy")
Me.Filter = "[Customer Name] = 'Elie' AND [EntryDate] = #" & testdate & "#"
Me.FilterOn = True

Note the fact that you have to equate a Field with your Text String so if your record name field is called "FirstName" you would use
Me.Filter = "[First Name] =" & carCrt
or
Me.Filter = "[First Name] =" & Me.txtName

[midnightcamaro69]

Yes I believe it is bound to the table because when I used the form wizard I selected tbldvd(which is the actual data table) from the drop down box in the wizard and then I selected the fields I wanted on the form from the list it gave me where you click the arrow to send the Field onto the right column from the left one. What method would you suggest for me to use to achieve what I am trying to do ?

I replaced "FirstName" with "Movie Name" as that is the field but then it gives me an error with the "end" and "debug" button saying: Syntax error(missing operator) in query expression '[Movie Name]=Iron Man2

this is the code I used: Me.Filter = "[Movie Name] =" & Me.txtName where Movie Name is the Field on my form that displays the data from the table and it is a Field in the Table.

I am quite new to Access so I am confused, sorry about that.

Thank you.

[OBP]

That is OK, it is the torture of VBA Syntax.
Try this instead
Me.Filter = "[Movie Name] ='" & Me.txtName & "' "
That puts a pair of ' around the Text field which is the same as a pair of ".
You can also try
Me.Filter = "[Movie Name] =" chr$(32) & Me.txtName & chr$(32)
and
Me.Filter = "[Movie Name] = """ & Me.txtName & """ "

[midnightcamaro69]

It is working Now, I put in Iron Man 2 and it shows a record of Iron Man 2 but the thing is I have to type in the Exact value in the field to get that result, is there a possible way to make it search for matching terms ? For Example the database has only got data for Iron Man 2 so if I type in "Iron Man" it should find the matching Iron Man 2 record.

I used the code: Me.Filter = "[Movie Name] = """ & Me.txtName & """ "
Me.FilterOn = True

Thank you OBP

[OBP]

To do pattern matching you use
Me.Filter = "([Movie Name] Like ""*" & Me.txtName & "*"")

That will also find Man or 2 form the title.

[CarltonCalv]

First you will need to chande the join type between Progress and [Planned Cost] to be the typoe that included all record from Progress and only the maqtch from
Next change your expression to be:
Nz([Planned Cost.Total_Amount],0)-[Actual Cost.Total_Amount]

NovelAspect is trusted by small businesses and accountants as an industry leader for quickbooks hosting. Your small business or accounting firm will have simplified access to Quickbooks at the lowest cost offered by any national QuickBooks Hosting provider.

[midnightcamaro69]

thanks alot OBP, you are a genius thanks for your help it works