Full Version: Query Rows In Form To Start Blank
UtterAccess Forums > Microsoft® Access > Access Forms
Brendan Adelaide
Any help is very much appreciated. I have a form that returns data from a query. It has a search function which I have got working thanks to another post here. However, when the form loads rather than it showing the first 20 rows of data from the underlying table, I'd like it to show just one blank row. And when the user clicks Clear Search, for it to return to one blank row.
I have attached screenshots of the form in Form view and one in Design view in case that helps. Many thanks
Brendan Adelaide
Thank you Larry for the feedback. I added the code and it does the job <
However it now seems to be conflicting with my other code:
Private Sub Form_Load()
[Forms]![frmSearchWorkerIncident].[SearchWorker] = Null
[Forms]![frmSearchWorkerIncident].[SearchPlant] = Null
End Sub

and then also preventing the query from running when I click Find to execute it. Any ideas please?
To open a search form in blank mode;
For SQL server back end I used this code as form recordsource- SELECT * FROM TABLE/QUERY WHERE 1=2
For jet/mdb/accdb back end I used this code as form recordsource- SELECT * FROM TABLE/QUERY WHERE IDPrimkey=Forms!FormName.txtIDPrimkeyControl
Then on your search button you have to use SQL code in VBA to search a particular record.
Then to clear your form you have to reload the above form Recordsource except for the jet/mdb/accdb you can
use this code -SELECT * FROM TABLE/QUERY WHERE IDPrimkey=0, make sure you don't have 0 ID value
Try this and make a feedback
Brendan Adelaide
Thank you. The WHERE 1=2 code worked for me. The second line of code just resulted in parameters popping up.
Sorry for the delay in replying. It's a 6 months job as some weeks I don't get back to working on it. Now I just need to search for code examples to make my Search work.
You could try the following...
1. Modify the form's record source to something like:
2. Use the following code for the find button:
Me.RecordSource = "SELECT * FROM TableName WHERE Field1=" & Me.Textbox1 & " And Field2=" & Me.Textbox2
3. Use the following code for the clear button:
Me.RecordSource = "SELECT * FROM TableName WHERE 1=2"
Just my 2 cents...
Brendan Adelaide
No way! I've just tested it and it works beautifully. That is awesome and worth far more than 2 cents. Thank you
Glad to hear you got it to work. We were all happy to assist.
Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.