Remove Access Parameters Request For A Simple Access Search Form

[Dina]

i'm new to access and i need some help please ,I have a simple access search form , that's based on a query that fill parameters from the form textboxes , when the access form loads its keeps prompting for parameters value which looks ugly .. i want to open the form , displaying all the records in the table , and filter when i click search .. its a one minute job for an access expert , so i have no times to waste on newbies , also , i have a data entry entry form , that i want to generate success message after successful insert in database , i have done it in the button event if no error happens , still if i left all fields blank and clicked save , it displays the message .. i need to change that to display please fill the textboxes then click save , and display success message when the row is acually inserted .. please check the following attachment for sample.

[ItsMe]

In the first screenshot, the SearchQ form is looking for a field named "ID". To fix this problem you will need to edit the query or the form. Test that the query is functioning properly by opening the query object in Data Sheet view from the Navigation Pane. If you get the same error, you need to edit the query.

Open the query object, from the Navigation Pane, in edit mode. Delete the field named "ID" from the grid. Try opening the query in Data Sheet view and see if you get the same request for Parameter Value.

It may be that you have several tables, all containing a field named "ID". As you add the ID field to your query, you need to be sure that the table name is included. Access should do this automatically. After you are able to open the query in DataSheet view without any errors, add the field(s) you need back to your query. Then test the query again in datasheet view.

If the query opens and works good but the form is causing the request for Parameter Value, you will need to fix your form or add the field that your form is looking for. Your form has a control bound to the ID field in table or query named SearchQ. You need to adjust the control's control source to the correct field or add the SearchQ.ID field to the query.

[Dina]

In the first screenshot, the SearchQ form is looking for a field named "ID". To fix this problem you will need to edit the query or the form. Test that the query is functioning properly by opening the query object in Data Sheet view from the Navigation Pane. If you get the same error, you need to edit the query.

Open the query object, from the Navigation Pane, in edit mode. Delete the field named "ID" from the grid. Try opening the query in Data Sheet view and see if you get the same request for Parameter Value.

It may be that you have several tables, all containing a field named "ID". As you add the ID field to your query, you need to be sure that the table name is included. Access should do this automatically. After you are able to open the query in DataSheet view without any errors, add the field(s) you need back to your query. Then test the query again in datasheet view.

If the query opens and works good but the form is causing the request for Parameter Value, you will need to fix your form or add the field that your form is looking for. Your form has a control bound to the ID field in table or query named SearchQ. You need to adjust the control's control source to the correct field or add the SearchQ.ID field to the query.

i have only 2 tables , and this query is ment to be used for a search form that filters the result using some parameters using textboxes in the form ,i don't want to remove the parameters , yet i don't want access to prompt for the parameters , simply i want access to open the form first , and when the search button is clicked , i want to execute the query , filterning the results ..

[ItsMe]

When I use Dynamic Parameterized Queries I will create an Instant If statement to test the control on the form for Null, like this
IIf([Forms]![عرض بيانات المرضي]![txtID]<>"",[Forms]![عرض بيانات المرضي]![txtID],[Patients].[ID])

Your SQL was not good. Beyond the testing for Null issue, you were employing the AND operator where you should be using the OR operator. The following SQL statement will work if you use it to replace the SQL in your current query named searchQ.

SELECT Patients.ID, Patients.PatientName, Patients.Gender, Patients.Nationality, Patients.FileNumber, Patients.PatientNumber, Patients.YearOfBirth, Patients.NationalID, Patients.YearlyCheckDate, Patients.PatientType, Patients.Treatment, Patients.CellNumber
FROM Patients
WHERE (((Patients.ID)=IIf([Forms]![عرض بيانات المرضي]![txtID]<>"",[Forms]![عرض بيانات المرضي]![txtID],[Patients].[ID]))) OR (((Patients.PatientName)=IIf([Forms]![عرض بيانات المرضي]![txtName]<>"",[Forms]![عرض بيانات المرضي]![txtName],[Patients].[PatientName]))) OR (((Patients.FileNumber)=IIf([Forms]![عرض بيانات المرضي]![txtFileNo]<>"",[Forms]![عرض بيانات المرضي]![txtFileNo],[Patients].[FileNumber]))) OR (((Patients.PatientNumber)=IIf([Forms]![عرض بيانات المرضي]![txtPatID]<>"",[Forms]![عرض بيانات المرضي]![txtPatID],[Patients].[PatientNumber]))) OR (((Patients.NationalID)=IIf([Forms]![عرض بيانات المرضي]![TxtNationalID]<>"",[Forms]![عرض بيانات المرضي]![TxtNationalID],[Patients].[NationalID]))) OR (((Patients.CellNumber)=IIf([Forms]![عرض بيانات المرضي]![txtPhoneNo]<>"",[Forms]![عرض بيانات المرضي]![txtPhoneNo],[Patients].[CellNumber])));

On your form, you are using an embedded Macro with your control button's Click Event. If it works, fine, keep it. Otherwise, replace the control with a new Command Button. In the new command button use VBA in the click event.

Me.ResultView.Requery

I believe that is correct. If not, try
Me.ResultView.Form.Requery

[Dina]

yes the VBA is another problem : its fails to open code editor , i hate access .. C# will make it in 5 sec

[ItsMe]

I noticed the same error as well as your DB had some special things adjusted that does not allow for normal interface. Maybe you are working with a modified version of Windows/Office.

You could try Importing your table to a new, blank DB and creating a new query, form, and subform. I imagine there being an ability to create VBA modules/code.

[Dina]

this sql above .. doesn't work .. retrieves all data .. even when passing parameters for a specific record

[Dina]

i hope it does , i will give it a try . that is dummy data for testing purposes .. its not important .. the important is to have the search form working , about the success message ., why does it appear when i leave all textboxes empty and try to insert . its says .. added successful .. although nothing is really added !

[Dina]

didn't work .. couldn't create VBA even after i started another DB

[ItsMe]

I noticed that I was incorrect about the AND operator. The IIF will adjust for that. So go ahead and use AND if that is what you are after.

Also, you are correct. The SQL is not working. I downloaded your DB again and tried a couple of things. Could not get it to work. I believe there may be a corruption issue. Here is a working example of using a parameterized query. It is more complex than what you are trying to do but qryInvView is an example of a dynamic parameterized query.

The sample here is an incomplete effort to use DAO in conjunction with a Dynamic Parameterized Query. However, it is a good example of using the IIF to reference controls on a form.

.

[Dina]

I noticed that I was incorrect about the AND operator. The IIF will adjust for that. So go ahead and use AND if that is what you are after.

Also, you are correct. The SQL is not working. I downloaded your DB again and tried a couple of things. Could not get it to work. I believe there may be a corruption issue. Here is a working example of using a parameterized query. It is more complex than what you are trying to do but qryInvView is an example of a dynamic parameterized query.

The sample here is an incomplete effort to use DAO in conjunction with a Dynamic Parameterized Query. However, it is a good example of using the IIF to reference controls on a form.

.

i tried creating a new project .. and it let me create VBA , and when i added some elements in the form , i didn't open anymore generating the same old error .. i guess there's something wrong with arabic support

[ItsMe]

Perhaps it is associated to the language support. I do not know because the error is new to me. I suspected there may have been an issue with the translation of the form name. I was unable to get the query to work in the original DB using a new temp form named "Form1". I then started to suspect other things that are different about the DB. For example, not all of the field names are displayed correctly in the table or the query. At that point, I decided to offer a separate DB as a working example in hopes of illustrating what works and what does not.

I suspect additional trial and error will weed out the culprit.

[Dina]

Perhaps it is associated to the language support. I do not know because the error is new to me. I suspected there may have been an issue with the translation of the form name. I was unable to get the query to work in the original DB using a new temp form named "Form1". I then started to suspect other things that are different about the DB. For example, not all of the field names are displayed correctly in the table or the query. At that point, I decided to offer a separate DB as a working example in hopes of illustrating what works and what does not.

I suspect additional trial and error will weed out the culprit.

its now working for the part where it doesn't prompt for parameters but can't get the query to work correctly , also sometime the subform doesn't refresh although when i go and open the query itself , i find the query is executed correctly , is there anyway to force refresh if the subform in macro ?

[Dina]

its now working for the part where it doesn't prompt for parameters but can't get the query to work correctly , also sometime the subform doesn't refresh although when i go and open the query itself , i find the query is executed correctly , is there anyway to force refresh if the subform in macro ?

how do i use like in here :

(Patients.PatientName) = iif ( [Forms]![عرض بيانات المرضي]![txtName] <> "" , [Forms]![عرض بيانات المرضي]![txtName] , (Patients.PatientName) );

[Dina]

Thanks >> Its Now Working , And Fixed The Query , One Last Thing If You know How To Generate Success Message From Macro On Success Of Insertion Or Failure For Missing Data In TextBoxes.