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