lookup specific record number

[Derkins]

Im trying to find VBA code that will look up a specific record in a table, does this exist? Im actually going to use VBA to scan the table for something im just not sure what function to use.

What i mean by specific record is not the dlookup function. I mean literally the first record in a table or the second record in a table. Or if record # can be used as the dlookup criteria that would work but i dont know how to do this.

thanks guys

 

[pbaldy]

You can't count on what position a record will occupy in the table, but you can certainly use record number with DLookup. Here's a good reference on syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[MarkK]

A technicality perhaps, but VBA has nothing to do with table access. You want SQL, or Structured Query Language. Here's a link to the first of three articles that provide more info than you'll need.
Fundamental Microsoft Jet SQL for Access 2000

 

[Derkins]

pbaldy do you happen to know the exact syntax for that?

if dlookup("patientname","tblpatients","[B]Recordnumber[/B] = ' 5 '") = ....

something like that?

 

[boblarson]

pbaldy do you happen to know the exact syntax for that?

if dlookup("patientname","tblpatients","[B]Recordnumber[/B] = ' 5 '") = ....

something like that?

Derkins -

Recordnumber is not something you should be searching for. Access does NOT store data in any meaningful order and "record number 5" today may be record number 7 tomorrow (or even later today). You should identify records with their PRIMARY KEY as that is what uniquely identifies a particular record (row of data).

 

[Derkins]

-boblarson

I understand what you are saying, but for the way i will be using it it does not matter to me if it changes. Im simply scanning a table for needed information. My question was simply if there is a syntax for recordnumber in the dlookup criteria? Is there?
I have it working the way you have stated but it is inefficient the way i have it coded. My PK is Case Number which will always be changing because of patient turnover. So is there a recordnumber reference in VBA?

 

[pbaldy]

In case there was confusion, when I said you could use record number, I was assuming you meant a primary key value, not the position of the record in the table. As I said earlier and Bob repeated, you can not count on the physical position of a record in a table. If "Recordnumber" is that key field in the table, then you're on the right track, just need to adjust to the syntax in the link.

Perhaps if you clarified what you're trying to do we can give you more specific help.

 

[Derkins]

Sorry guys let me clarify,

I do not care if a record in a table goes from line 5 to 500 this will not affect my code at all. The only thing I am asking is if there is a way to reference a line in a table without referencing any fields in that record. Line 5 for example. Just the 5th record currently stored in tblpatients.
I don't care what record is stored on line 5 i just need to loop through and check on a certain field that all lines contain and output that information.

 

[Rabbie]

Why don't you just have a query that selects that specific field from each record in the table and use that.

Like

SELECT fieldname FROM TableName;

 

[gemma-the-husky]

if you are looking for some data, then instead of iterating a recordset, or using dlookup, design a query, and in the criteria for your search column put

like "yoursearchtext"

a query is much more efficient, and doesnt use code - use that and see whether that gets you towards your solution