Searching a Query through a Form doesn't find all records and then deletes record

[MarksinMO]

I have a database for parts tracking ... it has the following Tables - tblPartsDatabase, tblReceive, tblUsed. I have forms to Create New Part, Receive (of parts), Usage (of parts). I have 2 query's that SUM the Receive and Usage Tables and a third query that results in the On Hand Quantity Using all the data in the tblPartsDatabase. Now the issue ... I have a form that searches the query based on a number of parameters the user Inputs (7 different unbound search boxes). I am in the testing mode ... I added a new part through the New Part form and everything works. Then I receive a quantity of the new part in ... this works. I run all the queries and the results are correct. Then I do a search for the new part ... the datasheet shows the item but when I run the search using the Part Number it doesn't find the part (it does find others since I used LIKE for the filter). Then if I clear the search the record for new part does not show up in the datasheet list. Basically when I open the search form I have 2226 records ... once I run ANY search record 2226 has been deleted. I'm sure this is some relationship issue that I am missing. The newest part "received" is record 2226 w/ PartNum 9007C52D ... Something I just noticed is that this does not show up in the record bar on the bottom left - 1st screen shot. The second screen shot is after I selected record 2225 and then re-selected record 2226 on the datasheet. The third screen shot is doing a search for 9007C and the results as shown. The fourth screen shot is after resetting the search field and clicking on search again - Record 2226 is gone. If I close the Part Search and reopen it ... the results again are screen shot 1.



I am quite new (a rookie or novice would be superior to me !!) to access and would appreciate any assistance and guidance ... what am I missing !!!

[June7]

Need to review data structure, form design, code. If you want to provide db for analysis, follow instructions at bottom of my post.

[MarksinMO]

Let me know if you get this ... Thanks

[June7]

Ok, got it.

Problem starts with table relationships. ID in tblPartsDatabase is autonumber and defined as primary key yet you are saving both ID and ManuPartNum into tblReceive and tblUsed. The defined PK should be the value saved as FK. There is no need to save ManuPartNo into the related tables, save only the ID and link tables on the PK/FK pair.

Then in QryQtyOH you have tblPartsDatabase join to QryUsed and QryRecv on both ID and ManuPartNum fields (compound link). Should be joining only on one pair (PK to FK). And don't use INNER JOIN, use outer (LEFT or RIGHT) - "Include all records from tblPartsDatabase and only those from QryUsed that match".

Why are you basing this search form on a query that has aggregate data - just to get the OnHand value? You are aware won't be able to edit the tblPartsDatabase data from this form because of including the aggregate queries?

Why include tblPartsDatabase in the aggregate queries?

Suggest you give the ID fields more meaningful names so will be less confusing which field is referenced.

[MarksinMO]

Thanks for the reply ... I'll muddle through (again a hack) your analysis and figure out how to correct ... The search/query is to get the OnHand Quantity but also to pick up all other data since the mechanics will this available to them in the future ... I have a different form to edit the database and I'm going to modify the tblPartsDatabase to eliminate QtyOH, QtyRecv & QtyUsed ... since that info is coming from the queries. I have do not want any editing of the data in the query just basically "read only". Not sure what you mean by Meaningful names? ... rename ID to ControlNumber?

[June7]

If every table has a field ID then how to distinguish them if several show in same query?

So you could do something like: PartsID_PK, PartsID_UsedFK, PartsID_RecdFK

Or don't do anything and deal with the multiple ID field names. Your choice.

[charlesgardner51]

one thing to keep in mind is that if you have used a wildcard operator "LIKE" and used "*" (thinking that will bring back all records) it wont test true for NULL fields contained in the query

[MarksinMO]

June7 ... thanks for the direction ... I modified the queries / forms and searches and accomplished everything I needed. Now to CG51's issue ... he's correct in the NULL fields ... is there a work around for this?

Mark

[June7]

Options:

1. calculate a field in query that handles possible nulls and apply filter criteria to the constructed field
IIf([fieldname] Is Null, "", [fieldname])

2. add Or Is Null to the criteria