search related records

[Homegrownandy]

Hi,

Happy new year and all that good stuff. I'm back with another annoying theory question. I have a list of purchase orders we receive. Each order can potentially have many lines, some may only have one.

On the orders form where we view each order I want to implement a more advanced search function. I want to be able to search for line details. Now, obviously the line details are a related record and not on the main form at all. It would become cluttered and confusing.

I'm aware there are many simpler options to find the information I'm looking for. I'm curious if this is possible to do a keyword search on a child record or records and then the parent would be displayed.

So it would return all orders were the line description matched. Even though that order may contain other lines that did not match.



I hope this makes sense. Even if this is not possible I thought it was worth an ask.

Andy.

[ranman256]

make a query that joins the Order master and the OrderDetails, all records.
put this in query a continuous form, with a text box in the header to search.

sub txtFind_Afterupdate()
If IsNull(txtFind) Then
   Me.FilterOn = False
Else
   Me.Filter = "[ItemName] like '*" & me.txtFind & "*'"
   Me.FilterOn = True
End If
end sub

then the list will filter to those found.
put a button on the header to open this record to the ORDER master form.(to show the master and subform of details)

sub txtOpenOrder_click()
docmd.openform "frmOrderDtl",,,"[OrderID]=" & me.txtID
end sub

[Homegrownandy]

Sorry Ranman.

the main form is a continuous form just listing Orders. There is no sub form. In fact the line information isn't on this form or in the query at all. This is why I am asking.

[Homegrownandy]

currently the user can search all details that are about the order except from details on each line. I would like to be able to keyword search the lines and show the orders for them. if that makes sense.

[orange]

Andy,

Please show us your tables and relationship set up.

[Homegrownandy]

The orders form only lists order details. It does not have any line information on that form. I don't think what I'm asking is possible but its always worth hearing opinions.

Edit, everything is possible; I mean for my level of skill and patience haha.

[orange]

But Order_Tbl and Order_LInes are related (1 to M) based on Order_Tbl.Order_ID =Order_LInes.[Order ID].

So you can search Order_LInes.Description for your keyword, then use the distinct [Order ID] of the result set to identify the
Order_Tbl.Order_ID records.

You should be aware of Access reserved words {description}, and
the syntax and related errors you may encounter when using field names with embedded spaces.

Perhaps you could describe the business process in more detail where the user needs to do this search and process the result.

[Homegrownandy]

Thanks, I see what you're saying, also thanks for pointing out the reserved word.

I'm happy with the process of what you are saying I was just wondering if you can do this without that information being present on the form. Probably through sql in the vba.

I'm still learning this process to be honest. This is to help our accountant that is using a system older than me. I was asked to find an order in her book and it took nearly an hour. Obviously this needs to be on a database.

every order we have is entered to the book/database. often the order only has partial information. It could be missing customers order number for example. So, we apply our own number temporarily on each line. Once an order comes in with all the information we need to go in and amend.

On the main orders form I cant list every line its really impractical. But if the only info we have is our number for a line then that's what we need to search for. (I know I said description not number earlier but the theory is the same, and to be honest I would like to search description too then we have the option for whatever is easiest.

okay, I'm doing some testing and the information for lines CAN exist there causing no issue if I click to only show unique values and then I hide the fields from that table. Question is now. Can I search those fields. I'm thinking not. I may need to change my approach.

Unless you can filter a hidden field I cant see this working though.

[orange]

If you(or the accountant) need to find Order(s) that have some "identifiable text" in related Order_Lines, then you could make a query or form etc to do so. Where and/or how it could be done and "interfaced" with your current business procedure is dependent on exactly what you need and where you are in the process. Some solutions could be more confusing/awkward than others, but I'm sure it could be done.

If you have some more details or data, you could get more focused answers/options.

I think you are confusing all Order Lines with selecting only those Orders (even Order_IDs) that have related lines that contain the keyword. The important part is to separate What you need from How you could do it. If it's a real requirement, then it should be done/resolved. And it sounds like this is a more common need, than a one-of/rare thing.

[Homegrownandy]

Thanks a lot for all the input. I believe I have the solution, the forms query could reference another query that is used to apply this filter. I should be okay now ill mark this as solved. Just visualising what I needed to do was the problem.

[orange]

Test it and be sure. Good luck.

[Homegrownandy]

Even better.. It works with the hidden fields and unique values. Before the form wasn't updating when I input the number. I needed to click elsewhere. this works fine now.
again thanks a lot.