Search creates issues in my MS Access form

Question

In MS Access i have created a database which has a main form and a sub form and are in a one to many relationship, then i have created a search box on my main form which i should be able to search base on both, the main and sub forms' fields which i can do it. Now when ever i am searching the form shows the searched record but the problems is that non of the main forms' controls (save, new, delete, next, previous and etc) on my main form is working. It would be my pleasure to guide me on this issue...Thanks,

Need more info - what method is your search box using? it sounds like the search box is filtering the main forms underlying recordset to just one record. So, not being able to navigate forward or backward is normal. As far as new records go, that could be a couple things. Your main forms settings could be set to not allow additions or you could be changing the underlying recordset to read only, etc. — gangreen
In search box's after update method i have done the following three line queries: 1.dim qry = "select parenttbl.*, childtbl.* from parenttbl inner join cildtbl on parenttbl.id = childtbl.fid where parenttbl.id=" & Me.mysearchbox.text &";" 2.Me.RecordSource = qry 3.Me.FilterOn = True — Nabalad
Is there a reason you are changing the recordsource on the main form to include the child table? If the child/master properties in your mainform/subform are set up properly, you only need to filter the records on the main form. Remove childtbl from your filter and see if that affects your ability to save. — gangreen
Thanks, but i don't know how to do this at all, i just knew the above command. Please tell me what to do were to do. — Nabalad

gangreen gangreen · Accepted Answer · 2012-02-27T06:45:05

Re Navigation

Based on the method you are using to search, you will not be able to navigate backward and forward. If you do want to navigate backward and forward, you will need to specify a search range. For example, instead of searching for a SINGLE record with ID 123, you will need to search for a RANGE of records, i.e., between ID 123 and ID 234.

Re Saving

You do not need to explicitly save records on bound forms (forms that are tied directly to a recordsource). You just change the data, and then move on. Once you move on, your data will be saved - as long as table constraints are not violated.

Re Search

When you create a main form/sub form in Access, the 2 forms are linked through the Child/Master properties. So, if you are searching for a specific record in the main form, you generally don't need to worry about filtering the sub form as well. Using a method similar to what you have in the comments it would be:

dim qry As String
qry = "select parenttbl.* from parenttbl where parenttbl.id=" & Me.mysearchbox.text &";"
Me.RecordSource = qry

By joining the child table in your original code, you may have confused Access' ability to save, but I doubt it. I didn't test it, but I don't think you need to turn the filteron property to true after changing the recordsource. See how filteron is used in this post by Allen Browne http://allenbrowne.com/ser-28.html

Re FilterOn With that said, you can probably tweak his use of the filteron property to "search" for your desired record, then you may be able to turn the filteron property to False and then navigate backward and forward in your recordset. I didn't test it though.

EDIT: Here is an example when to set the FilterOn property to false:

Me.FilterOn = False
DoCmd.GoToRecord , , acNext

For this to work, you will need to specify the ORDER BY clause in the SQL query that your main form is bound to. Otherwise you may not navigate backward and forward the way you would expect.

Search creates issues in my MS Access form

1 Answers