Expected variable or procedure not project?

[Gasman]

Hi all,

I was trying to post a problem I was having with DLookup.
To try and show the problem better I was going to upload a DB with the two tables in question.
However after exporting them to a new DB I was trying the DLookup in the immediate window as I did in the actual DB, and got the error in the attached picture.

The problem I am having is that the DLookup is not picking up the first linked many record table for the ID from the one table. the table was not odered on Load and so I believe just opens on ID order ascending?

Looking at the table I can see the record I was expecting to find is way before the record it is actually finding:banghead:

I have even changed the properties of the table to Order on the ID field and Order on load, yet the Dlookup still finds (what is to me) the second record?

So if can overcome the subject problem I can upload the DB for you to see better, unless anyone knows exactly where I have gone wrong.

This has only happened for one set of records, all the others still appear to work fine.?

TIA

 

[Minty]

The table will generally sort itself according to the indexes set.
So if you have a primary key field but set a unique index on something else (like a composite index) it will probably sort on that.

It is one downside to any of the Domain functions - you can't effectively specify a sort order.

 

[Gasman]

Hi Minty,

Thanks for the quick reply.

I went into the BE and sure enough I had a few indices set, so I removed all but the PK and the TransferID which I was using as the lookup value.
Still produced second record, so I deleted that index as well so now only the PK index exists) and it still gives me the second record.?

Do I have to do anything with the BE after removing indices?

 

[Gasman]

OK, something has gone screwy somewhere.
I would always put in the many record with a certian record type first and then the other record type second.
For this set they were the other way around if no order is set on form load., so 449 comes before 371 and it is the 371 record I was trying and expecting to locate?

Initially I thought I had just slipped up, but now can see I did not, just by the ID numbers.

 

[Gasman]

I've replaced the table with a query for now, that also allows me to filter by the record type (perhaps a better way anyway?).

This would happen just as I am going on holiday. , been working fine until now.:banghead:

 

[Frothingslosh]

Never count on a table to give you records in a specific order.

If you need records in a specific order, your solution in post 5 is the way to go.

Also, you should check to see if those indexes were important. The unique index may have been being relied on to prevent duplication, and the others may have been needed to speed up searches. (Then again, they could also have been providing no noticeable benefit. They're fun that way!)

 

[jleach]

That error is VBA specific, shouldn't have anything to do with the tables. Typically means that the name of the VBA project is the same as whatever function you're trying to call.

Example: vba project named ThisProject, and you happen to have a function named ThisProject, so you call your function:

If ThisProject() = whatever

You get that error due to it picking that up as the actual project name.

This is due to multipart naming. You can fully qualify any call with the VBA project name, then the module name, then the procedure:

If MyProject.MyModule.MyFunction() = whatever

So, if MyFunction and MyProject happen to be the same, it tries to resolve on the left end first.

Keep in mind, most default VBA project names in Access are "Database"

Maybe that's the issue?

hth

 

[Gasman]

Yes, I would have created them to try and speed things up. I have a pic os what was there and will see how it performs without them.
There is not a lot of data (at least at present) so will just see how it goes.

This has been running since October last year and it is the first time it has produced a wrong value, due to find the wrong record.

Never count on a table to give you records in a specific order.

If you need records in a specific order, your solution in post 5 is the way to go.

Also, you should check to see if those indexes were important. The unique index may have been being relied on to prevent duplication, and the others may have been needed to speed up searches. (Then again, they could also have been providing no noticeable benefit. They're fun that way!)

 

[Gasman]

Ah that explains it.

I created a DB called Dlookup to be able to post here and was using the Dlookup function to see if it did the same before posting.

One to remember.

Thank you.

That error is VBA specific, shouldn't have anything to do with the tables. Typically means that the name of the VBA project is the same as whatever function you're trying to call.

Example: vba project named ThisProject, and you happen to have a function named ThisProject, so you call your function:

If ThisProject() = whatever

You get that error due to it picking that up as the actual project name.

This is due to multipart naming. You can fully qualify any call with the VBA project name, then the module name, then the procedure:

If MyProject.MyModule.MyFunction() = whatever

So, if MyFunction and MyProject happen to be the same, it tries to resolve on the left end first.

Keep in mind, most default VBA project names in Access are "Database"

Maybe that's the issue?

hth

 

[Pat Hartman]

The table will generally sort itself according to the indexes set.

By definition, tables and queries are unordered sets. If you don't specifically sort a query, the rows are returned at the convenience of the database engine. This is much more obvious if you are using SQL Server but Access tends to fool us because every time you compact the database, all the tables are sorted into primary key order and rewritten. The indexes are also rebuilt and everything is decompiled.

So, when you open a table in DS view it looks like the table is always in PK order but you can't count on it. Never create a process that requires some specific order in the records unless you base it on a query with an order by clause.

If you doublt this, you can prove it to yourself. You will need to create about 100 large records. You need to exceed the size of a "block" which differs with your storage media. Make a couple of the fields 255 characters in length but initially fill them only partially. Then update one of the records and increase the contents of the record to the maximum width. If you've hit the sweet spot, the next time you view the unsorted table in DS view, the record you updated will move from position 22 or whatever to the end of the recordset. This happens because when you update a record and increase its size, Access cannot write it back to where it was originally and has to move it to the end of the existing data. So the more you update your data between compacts and the more likely you are to increase the size of text fields (in Jet/ACE - all text fields are variable in length), the sooner you will begin to see records pop out of order.