0

I have a query that is removing the first record from the tables each time I close it.

Background on what I'm doing. I have created a query and a form that I'm using to input the criteria into that query. When the user hits run it opens the query and displays the criteria they inputted.

The issue I'm running into is whenever the user leaves a text field blank on the form the query is suppposed to treat that as NULL and return everything. I'm doing that using this (Like [Forms]![myForm].[myCriteria] & "*"). It works fine until the user closes Access and upon opening it again, it will have the criteria in the first row of the table blank.

On the form I have two text boxes (textbox1 and textbox2) both are from two different tables. If the user will leave textbox1 blank, run the query, closes Access and then reopens it. The record associated with textbox1 will be blank in it's perspective table but only in the first row. Not entirely sure why this is happening

I tried to be clear as possible, let me know if there was any confusion or if any other information is needed.

EDIT

I'll try to expand a bit more.

There are 3 tables that all have records. Some of these records are used for criteria when the query runs.

Table1 will have the Component, Table2 will have the Status, Table3 will have other records that isn't necessary for the criteria but still shows when the query runs

The Component and Status are what should be used as the criteria for the query and the User will type that into textbox on a form that will then open the query. If the user leaves a textbox blank for either the Component or Status it should show all Components or all Statuses from the tables when the query runs. It does that but after you close and then reopen Access the Component field or the Status field in their respective tables will be blank but only on the first row of that table

user3586080
  • 7
  • 1
  • 4
  • It sounds like the fields may be bound? And that blank record may be getting inserted into the table because it is bound. http://stackoverflow.com/questions/13009890/bound-and-unbound-controls-in-ms-access – Mark C. Aug 05 '14 at 15:18
  • I think I see what you're saying. So if I use an unbound form will I still be able to use the form with the query? – user3586080 Aug 05 '14 at 15:38
  • I don't know exactly 100% how your form is set up to work, but if a user is simply entering things into textboxes and using that to filter a query, I see no reason why the fields should be bound. – Mark C. Aug 05 '14 at 15:39

1 Answers1

0

You mention in your post that you are using the Query to feed criteria from the Form, however the controls (two text boxes) are related to two different tables in the DB. This sounds to me like the Form is not Unbound where the form is locked with a table, and every time the form is loaded, the form loads up the data from the table, making it to directly edit/delete the records. In order to achieve the search functionality you want to, do the following. More on Unbound & bound : http://www.baldyweb.com/BoundUnbound.htm

  1. Create a blank form, not based on any table. Just a blank form with two text boxes and one button.
  2. Name the First Text box txtCriOne and the other txtCriTwo. Name the button, runQryBtn. Save the Form by giving it a name like Frm_searchQry
  3. Create a Query, involve the two tables by using a JOIN, or however you wish to. For simplicity I will use an example of customer table and orders table.Name it something like, Qry_searchDB

Qry_searchDB

SELECT 
    tblCustomer.CustomerName, 
    tblCustomer.CustomerTelephone,
    tblOrders.OrderNumber,
    tblOrders.OrderDate
FROM
    tblCustomer INNER JOIN tblOrders
ON
    tblCustomer.CustomerID = tblOrders.CustomerID_FK
WHERE 
    tblCustomer.CustomerName Like "*" & Forms!Frm_searchQry!txtCriOne & "*"
Or
    tblOrders.OrderReference Like "*" & Forms!Frm_searchQry!txtCriTwo & "*"

Now save this query. Then code your button click to open this Query. Something like.

Private Sub runQryBtn_Click()
    DoCmd.OpenQuery "Qry_searchDB"
End Sub

This would open up the Query based on the Criteria or list all records if both are left blank. Hope this helps.

PaulFrancis
  • 5,748
  • 1
  • 19
  • 36