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.

 

[Pat Hartman]

Your relationship is backwards. The Shelf Numbers table needs what we call a foreign key (FK). This field points to the "parent" record in the Order Details table. So, remove the ShelfNumber from the Order Details table and add OrderDetailsID to the Shelf Number table. If this is a many-to-many relationship, then you need a third table which we call a junction table. Look at this very simple schema of students and classes.

tblStudents
StudentID (PK)
StudentName

tblClasses
ClassID (PK)
ClassName

tblStudentClasses
StudentClassID (PK)
ClassID (FK to tblClasses)
StudentID (FK to tblStudents)

PS - I know this is a sample but it is poor practice to name all your ID's "ID". Use descriptive names so you have a shot at figuring out what table they belong to just by their name.

 

[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

 

[Pat Hartman]

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

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.

 

[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

 

[Pat Hartman]

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

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.

 

[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.