65536 Record Limit on Lookup

[calian]

I have a have a database where one .mdb file is used strictly to record information on people, kind of like a roster - one entry person. Another .mdb is used for transactions, is linked to the roster.mdb, and uses dlookup() to find information about the person to fill part of the transaction file based on the person's record id number. Pretty straight forward.

There are about 75,000 records in the roster.mdb, and that will only go up. However, if the person's record number is greater than 65536, the dlookup returns nulls in all fields.

I know the records are there, and records above the 65536 limit will print on reports, so the system is able to access them.

The record number field is an autonumber, and every field or variable involved in either .mdb is set to long integer.

I have a combo box on the transaction screen that links to the roster.mdb. Clicking the pull-down displays the person's ID number, last name, first name, middle name, and date of birth. It displays all records through 65536, and *part* of 65537.

Some of the information in the roster must be incorporated in the transaction file (for example, we track address history), so it's no good just using the ID number. Some repeatable information must be entered with each transaction record, and I'd like to reduce operator "creativity" by having it drawn from the roster file.

This is the first time I have dealt with a lookup on a file this size. Have I overlooked an inherent integer-only limit to the number of records that can be used in a dlookup? or combo box? I don't remember reading it, but that doesn't mean much. The roster.mdb file is only about 25megs, nowhere near the 1gb limit, and contains only the roster table with links to a reference table with valid city names and such things, so it's not the file size or number of objects in the file. Everything else about the forms, code and reports works, but for the records after 65536, I'm in a world of hurt.

Thanks for any help.

-Terry

 

[BNPMike]

Sorry I know this isn't what you you are asking but why are you using DLookup?

I ask this in all seriousness because I've long wondered what it's for. If you want to join in related information there's this thing called SQL which does it wonderfully. Access also fills the data automatically so you don't have to program anything.

Is it time to change? mike.stephens@bnpparibas.com

 

[calian]

I'm familiar with SQL, but am lousy at using it.

I use dlookup because it seemed a quick and simple way to grab data from one file, and use it in another. A left over from my days of writing DOS applications using dBase, I suppose. In this case the data entry people will always have the record number at hand. Seemed a simple solution. What would be a better one?

Not sure what you mean by Access filling in the data automatically, or for there being no programming needed. Would there not be a need for coding to trigger the query, import the data, and store it in the appropriate fields?

-Terry

 

[BNPMike]

You are right I was wrong.

What Access will do automatically is fill in the data on the screen as soon as you enter a foreign key value. If there is already a key value then the data obviously will be there already.
mike.stephens@bnpparibas.com

 

[BNPMike]

contd

What I am getting at here is you should not normally be copying data from one table to another. That's rather against the whole spirit of relational databases. There's some very sophisticated programming in there to stop you having to do this ie algorithms to create your connected view through joining separate tables.

In extreme cases there can be a argument for de-normalisation but I suspect that's not true for you. I've never need to do this so far.

If you need to create another 'copy' eg an output file, then do this as a query and not by moving data yourself. mike.stephens@bnpparibas.com

 

[Rhonin]

Sometimes DLookup is just a quick handy way especially if you need to assign it to a variable for calculations etc...

As for your problem, I routinely use it to pull data from a table with 250,000+ records and have no problem.
What version are you using and ...

 

[calian]

Rhonin,

I'm using Access 97, SR-2 on a machine running NT4.

As far as completely normalizing the tables, the reason for the lookup is to keep a history of addresses. These people move, and some of the users want that history, not just the current addresses.

Does the fact that the combo box displays *part* of record 65537 tell me anything?

Thanks again.

-Terry

 

[calian]

An odd follow up: when I drop the Row Source information from the combo box, and turn it into a simple text box - the dlookup code works!

Since the combo box would not display any numbers higher than 65537 when it was tied to the table, it follows, I suppose, that the dlookup had no number to use in the lookup procedure. However, since the ID box will display any number entered, it works. Means the problem was not in the code, but in the combo box.

Is there a record number or data limit to a combo box? The field to which it is bound is a long integer.

Mucho confused....

-Terry