Subform Datasheet will not display all records in table (1 Viewer)

Local time
Today, 10:42
Joined
Mar 18, 2020
Messages
34
I've made a purchase order system and am having trouble with a subform. My main form (Single Form) is unbound and has comboboxes set up to filter the subform. The subform displays as a datasheet. This works great except for one problem. My PODetail table has 17,000 records in it and the subform will not display all the records, particularly the older records. I first thought that maybe there is a limit on the datasheet view, but the first record that shows is "2" and then the next is "4562", then it jumps to "4694". This is before i even touch the comboboxes on the main form so just on open. You would think the subform should display all the records on the table, no? I have exported the table to excel to see if I could figure out what might be happening, but there is no rhyme or reason for this as far as I can see.

The main form's Filter on Load property is set to "No". The Filter property is blank.
The subform's Filter on Load is set to "No". The Filter property is blank. I do have this form's Order By property set to the PO number in descending order.

When I try to filter the data say by SKU, i'll get 6 out of the 17 records that should show up.

Any thoughts? I'm sure I'm just missing something stupid.
 

June7

AWF VIP
Local time
Today, 08:42
Joined
Mar 9, 2014
Messages
5,423
Suggest you provide db for analysis. Follow instructions at bottom of my post.
 
Local time
Today, 10:42
Joined
Mar 18, 2020
Messages
34
Database is full of sensitive info, such as employee information and projects. Can you specify what information might be helpful in assisting and I can send whatever info is necessary.
 

bastanu

AWF VIP
Local time
Today, 09:42
Joined
Apr 13, 2010
Messages
1,401
How exactly do you filter the subform, can you show us the code? Is your subform's record source a query\SQL statement that uses Like combo1 & "*" type expressions to "filter"? If yes than probably you are missing the records that have Nulls in the fields used for "filtering".

Cheers,
 
Local time
Today, 10:42
Joined
Mar 18, 2020
Messages
34
How exactly do you filter the subform, can you show us the code? Is your subform's record source a query\SQL statement that uses Like combo1 & "*" type expressions to "filter"? If yes than probably you are missing the records that have Nulls in the fields used for "filtering".

Cheers,
Thanks for the reply. I have combo boxes at the top of the main form that filter the subform. This is where I use the "Like" statement. For example:
Code:
Private Sub Combo224_AfterUpdate()

Dim StrFilter As String

Me.Refresh

StrFilter = "SKU like '*" & Me.Combo224 & "*'"

    Forms![Copy of POSearch]!POSearchSubfrm.Form.Filter = StrFilter
    Forms![Copy of POSearch]!POSearchSubfrm.Form.FilterOn = True

Me.Combo224.SelStart = Nz(Len(Me.Combo224), 0)

Me.Combo224 = Null

End Sub

Then, the subform is bound to a query. the SQL for the Query is:
SELECT [Purchase Orders].*, POSearchSubQry.*, Vendors.[Vendor ID], Vendors.[Vendor Name], [Employees Extended].EmployeeName
FROM [Employees Extended] INNER JOIN (Vendors RIGHT JOIN (POSearchSubQry INNER JOIN [Purchase Orders] ON POSearchSubQry.[Purchase Order ID] = [Purchase Orders].[PO ID]) ON Vendors.[Vendor ID] = [Purchase Orders].[Vendor ID]) ON [Employees Extended].[EmployeeName] = [Purchase Orders].[Created By];

I thought about blanks, but if I search for SKU "560H3500B" I should get 17 records. All 17 records have this SKU (I checked the table). I only get six records to show on the subform.
 

bastanu

AWF VIP
Local time
Today, 09:42
Joined
Apr 13, 2010
Messages
1,401
Probably the joins in the subform's query are eliminating some of the records. Open the query in datasheet view and manually filter for that SKU and you'll probably get 6 records. You can try to change the Inner Joins to Left Joins and see what happens.
Hard to say without seeing your form but I suspect the reason for the query including all those tables\queries is to display "related" information (such as the [Vendor Name] corresponding to the VendorID foreign key stored in the table). You should redesign the subform to eliminate those extra tables from its recordsource and replace the controls in the subform with bound combo boxes with 2 columns (in the previous example VendorID - bound column, width=0- and Vendor Name - column width 2"). This way any missing data will simply show up as an empty field rather than a missing record.

Cheers,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 19, 2002
Messages
42,971
Are you ever going to have to convert the BE to a different RDBMS? If the answer is even- maybe - then you need to start out with better techniques. Filtering an Access form is the worst possible solution for a BE that is ODBC. It totally prevents offloading any of the work to the server and makes it all fall on the shoulders of Access after clogging your LAN with unnecessary traffic.

Try obfuscating the data. Copy the database and then use queries to change the sensitive data. Make all the SSN' FirstNum & LastNum &7zeros and all the LastNames FirstLetter & Last letter, etc. Only takes a couple of minutes and doesn't break RI.
 
Local time
Today, 10:42
Joined
Mar 18, 2020
Messages
34
Are you ever going to have to convert the BE to a different RDBMS? If the answer is even- maybe - then you need to start out with better techniques. Filtering an Access form is the worst possible solution for a BE that is ODBC. It totally prevents offloading any of the work to the server and makes it all fall on the shoulders of Access after clogging your LAN with unnecessary traffic.

Try obfuscating the data. Copy the database and then use queries to change the sensitive data. Make all the SSN' FirstNum & LastNum &7zeros and all the LastNames FirstLetter & Last letter, etc. Only takes a couple of minutes and doesn't break RI.
Hi Pat, So, I'm a self taught newbie. Can you explain this in layman terms? What is RDBMS?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 19, 2002
Messages
42,971
I have a pronounced dent right in the middle of my forehead due to the OMG motion:)
My PODetail table has 17,000 records in it and the subform will not display all the records, particularly the older records. I first thought that maybe there is a limit on the datasheet view, but the first record that shows is "2" and then the next is "4562", then it jumps to "4694".
I forgot to address this. If you haven't compacted the BE in a while, you may be seeing the effects of what happens when you update a record and end up increasing its length. The database engine cannot put it back where it belongs and so ends up moving it somewhere else. Usually to the end of the table. Compact the database. Does the "issue" go away? If it does, what you need to do is to add order by clauses to all your queries.

When Access does a compact, one of the processes is to sort all tables into PK sequence and rewrite them so most people think that tables are always sorted by PK and that is not the case at all. Tables and queries are unordered sets. Think of a bag of marbles. The database engine retrieves rows at its convenience. The only way to ensure a specific order is to include an order by clause in a query.
 

Users who are viewing this thread

Top Bottom