ListBox not showing correct stating point

[cuddles]

I have a database I built in test mode and now my main table has been populated with the live data. There are 1.3 millions rows. I built a form that has a search button and the ability to sort through records looking at searching by just about whatever they want and it will filter and show details in a listbox. The problem is, I used fake information for testing. I wrote over the first 46 lines so that the PK beginning with 1 has the REAL data. Or LIVE data I guess you could say. Then it goes to row 1,300,086. When I see my listbox view, the listbox is not beginning with line 1. It begins with line 46. I do not understand this because I wrote over lines 1-46. I have sort ascending by the primary key in the row source. The row source is a select query statement. Instead of a select statement here, would it be better to create a query and store it so it captures rows 1-46 as well or do I have to rebuild the table from scratch and append my data?

[June7]

SQL or query as RowSource shouldn't make a difference.

I don't understand, if only the 46 records are 'real' data, why is there 1.3 million rows?

Have you run Compact & Repair?

[cuddles]

No. 46 records were used for testing. Now they wanted me to load the REAL data that is the 1.3 million rows. So, I copied and pasted 1-46 from real data, then I appended the remaining of the 1.3 millions rows. I have compacted and repaired. I have even create a blank database, created the table from scratch and then appended my live data and it still keeps reverting to beginning at row 46. Row 1 has no null values, there are null values in 8 of the first 46 but only a few columns are null. My PK of course always appears. Is there a way to say no matter what make all rows appear based on the PK is not null?

[cuddles]

I only mention the null because I review the listbox and not 1 column contains a null value. I have 16 rows to display in this listbox.

[June7]

Why would PK be null? Is the PK an autonumber field?

I don't understand what is going on. If you want to delete most of the 1.3 million records (because the db is probably too large for forum post even in zip), leaving the problem records plus some good ones, and provide db for analysis, will take a look.

[cuddles]

The PK is not null. It is autonumber. I cannot post it. Work PHI violation. Healthcare.

[June7]

Could sanitize the db. Make copy and delete sensitive info (name, address, phone, ssn, email).

Data imported from what source?

[ItsMe]

??? Confused but I will throw this out there.

If you want your AutoNumber to start with 1, copy paste the table. Use the option for "Structure Only". Do a compact and repair for good measure. Then append the new table with production data.

[Missinglinq]

You really have no business displaying the Primary Keys. Autonumbers/Primary Keys are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. They should not be used as account numbers, serial numbers, etc. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how Autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, 1083225152 make such people get very nervous.

Linq ;0)>

[cuddles]

Ok, so I should change my primary key then to just numeric and say no duplicates? This way as they do data entry above the 1.3 million records it will assign the next number inline?

[cuddles]

I can make it so the number does not display on my listbox. My problem is the fact that rows 1-1.3 milllion all contain data of sometype. Some fields might be null. When I view my listbox of items on the form, it begins at 46 and then goes to like 1001 and then skips again until 5000 and so on through the entire listbox. Yet there is data in 1002, 1003, 1004 etc. It is as if only records that have 100% everything in every column are being displayed. I am not sure how to set it up to say give me it all. I am concerned that it does not pick up those items in the listbox. Yet the table I can see it all. I am posting my database not with 1.3 million records but just an example. What am I missing here?

[June7]

Table QualMain would be the one with 1.3 million records now reduced to 60?

This listbox of interest is on forms frmQualityProgramsUpdate and frmQualityProgramsDataEntry (I would make one form serve both purposes).

The RowSource of listbox is an SQL with 11 tables joined to QualMain, all with INNER JOIN. INNER JOIN requires related records in ALL tables for any records to show. The listbox has only 2 items listed (QID 46 and 60). Change each to RIGHT JOIN and all 60 records list.

[cuddles]

Ah ha. What a dummy I am................I never ever thought of that. I was thinking I could attempt some sort of show me null or not null but now I forgot totally about various joins....Thanks I will try that really quick here.

[cuddles]

Geeze oh Pete. June7, what would I do without you. You know I have programmed in SAS for like 10yrs and I do inner, outer, left, right, so many joins. But I am so used to just typing all my code myself in the proc sql statement and just got so into that habit. With Access over the last 2 months I have got into this habit of joining with design view. I need to start using the SQL view instead and maybe I would not have so many darn issues!!!!!!

[June7]

Access will automatically apply joins as established in Relationships window. Either set the relationships as you would normally want to see them or don't build relationships which will force you to always manually establish the links in the query builder.