Goto record in Parent form by searching criteria in Subform

[csharrock69]

Hi

I need to search for criteria found in a subform which when found then goes to the record in the parent form. There may be multiple records which share the same criteria so this project needs to be able to go to all records that share that criteria (essentially a "go to next record button").

Advice from the article shown below has been used and tailored to this project however error messages which I don't understand have popped up. I have little experience in using VBA/SQL but can understand the general jist of code when presented.

Here's the previous advice I tried to use:
accessmonster.com/Uwe/Forum.aspx/access/113062/Goto-record-in-Parent-form-by-searching-criteria-in-Subform

On the parent form that displays details of a particular object there is a subform showing multiple documents relating to that particular object. In some cases there may be no documents for an object.

Each object is uniquely identified by its "Component_Reference_Number". Each document is uniquely identified by its "OC_Reference". Both of these are strings.

I would like to search for an object (in fact, all objects) on the main form by typing a document number into a combo box. Once that document number has been typed in, clicking the "next record button" will go to the next record (object) that shares that same document number. It would be nice to have a "reset" button too so that all records can be browsed in the usual manner.

What code needs to be put into the form and combo box in order to be able to do this?

Like the author of the aforementioned article, I'm no programmer but can implement code if told where to put it and what the code is.

Many thanks in advance

Chris.

 

[John Big Booty]

Welcome to the forum.

I'm not entirely sure I understand why you want to use criteria in the sub form to search for data in the main form

Perhaps the sample here might help you.

 

[csharrock69]

Hi John, thank you for your rapid and helpful reply.

Perhaps the sample here might help you.

Just tested the sample you refer to above and it's great! It sets out what I wanted to do however there has been an issue with it and I would like to call upon your wisdom once again please!

When in form view on the form FRM_SearchMulti and when typing in the visible text box, it seems that it will only find records for the first field of the query. In other words, in design view of the query, the text box only searches via the field in which the criteria is filled in with this code: Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
It will not search by any of the other fields (the ones where the above code has been put into the "Or" box)

Any ideas?

Thanks a lot for your help so far

Chris.

 

[gemma-the-husky]

without looking at anything - what you are asking for just cannot happen

if you have a form/subform this is saying that any item on the subform is dependent on the current item in the main form.

therefore there is nothing in the subform that can need whatever data is displayed in the main form to change (other than a total, based on the subform, say)

so, if you really have this situation then you don't have a true form/subform relationship, or your data structure is not normalised.

you need to investigate and clarify this first, in my opinion.

 

[csharrock69]

Hi Dave

To clarify, I can confirm that

any item on the subform is dependent on the current item in the main form

I dont understand what you mean by:

therefore there is nothing in the subform that can need whatever data is displayed in the main form to change (other than a total, based on the subform, say)

One object (main form) can have multiple documents associated with it. These documents are listed within the subform. Some objects have no documents while others have one or more documents. This one to many relationship is linked by a common object reference number.

The primary key of a record in the objects table is its object reference number. The primary key of a record in the documents table is a combination of the object reference number and the document number.

Does that clarify things?

 

[John Big Booty]

....

When in form view on the form FRM_SearchMulti and when typing in the visible text box, it seems that it will only find records for the first field of the query. In other words, in design view of the query, the text box only searches via the field in which the criteria is filled in with this code: Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
It will not search by any of the other fields (the ones where the above code has been put into the "Or" box)

Any ideas?

Thanks a lot for your help so far

Chris.

The query searches all fields that have the criteria Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*" in them.

What this sample will not do is search on multiple terms typed into the search field. It can only search on one string typed into the search field.

 

[csharrock69]

Oh right. So to save extra sweating over code (if access even has the capacity for searching on multiple strings), the issue could be overcome by having multiple search boxes- one for each string.

 

[csharrock69]

Hi John

After testing your example that you mentioned in the link, it seems that the search doesn't show all records based upon the criteria entered in the text box. It appears that if there is a blank field for that record in one of the related tables, the search does not show that record at all regardless of whether it matches the criteria entered in the search box.

It would be great to show all of the records in the search that had matching values in the "criteria" box but may or may not have matching values in the "or" box. Did that make any sense??

How could this issue be resolved?

 

[John Big Booty]

Hi John

After testing your example that you mentioned in the link, it seems that the search doesn't show all records based upon the criteria entered in the text box. It appears that if there is a blank field for that record in one of the related tables, the search does not show that record at all regardless of whether it matches the criteria entered in the search box.

It would be great to show all of the records in the search that had matching values in the "criteria" box but may or may not have matching values in the "or" box. Did that make any sense??

How could this issue be resolved?

Without seeing your table and query structure I can't really give any advise. All I can assume is that the blank fields (in your tables) are critical to either the relationship between tables or within the query it self. If you look at the table TBL_winery in the sample you will notice that there are plenty of records that contain blank fields.