Query if present on form

[stu_c]

Hi all.
I have two tables
TBLOrderDetails
TBLShelfnumbers

within the TBLOrderDetails I have used the look up wizard to see all the Shelf locations from TBLShelfnumbers

I have got the query to search for what ever is on the first entry in the form but wont do all records?

<>[Forms]![FRM_Stock_OnGoing]![ShelfNo]

 

[bob fitz]

Can you post a copy of the db with just a few fictitious records.

 

[stu_c]

Example

 

[stu_c]

I have even tried a Dlookup but again only searches for the record the curser is on not all the records

DLookUp("[ShelfNumber]","TBLShelfnumbers","[ShelfNumber]=[Forms]![FRM_Orders_OnGoing]![ShelfNo]")

 

[The_Doc_Man]

within the TBLOrderDetails I have used the look up wizard to see all the Shelf locations from TBLShelfnumbers

I have got the query to search for what ever is on the first entry in the form but wont do all records?

Never mind the fields and never mind the example DB. What, in common language, are you trying to do. We do better in advising you if you tell us your goal, not your method.

 

[stu_c]

Never mind the fields and never mind the example DB. What, in common language, are you trying to do. We do better in advising you if you tell us your goal, not your method.

In the example under TBLOrderDetails_All, the shelf numbers list I only want it to show the ones that haven't been allocated to an order

 

[stu_c]

Once you fix the tables to get the relationships correctly defined, (make sure the default for the Order Details FK is null), you would search for rows with null in the FK field.

All I wanted to do was run a query from an already open form but as mentioned its only searching the record that the curser is on rather than the whole form

 

[stu_c]

Hello
Yes, it is only one shelf per order on this form we need

That's the way forms work.

Are you sure that one shelf can only appear on one order? If so, create a query with a left join that selects all the rows in the shelf table where there is no match in the details table.

There is something wrong with the logic of why you are doing this query this way.

 

[arnelgp]

I don't know which form to work, so I worked on TblDetails_All form.
see if this resolved your issue.