DLookup or Recordset

[jadams0173]

Hey all. I'm struggling with a simple task. I've used DLookup before but am very new to recordsets. I am looking at a table that has an ID(Autonumber), StoreageLoc(txt) and StoreageFlag(txt). All I need to do is find the first location where the flag is set to 0. I've tried this

Loc = DLookup("[LocationFlag]", "tblStencilLoc", "[Storageflag]=0")

This returns a type mismatch in criteria. There are multiple records that meet the criteria, but i'm just looking for the first. Would a recordset be better? In the big picture I will be setting this flag from 0 to 1 to try to use the open locations efficently. Any ideas or help would be greatly welcomed.

 

[jadams0173]

Ok. After I made the post it hit me like a brick. StoreageFlag should be datatype number not txt. I'm still interested in knowing if this is they best approach.

 

[Remou]

Hi
You said that Storageflag is text, so:
Loc = DLookup("[LocationFlag]", "tblStencilLoc", "[Storageflag]=[highlight]'0'[/highlight]")

If you want to use the information returned, LocationFlag should be a unique ID.
If you want to manipulate the record, it might be best to use a recordset.

 

[jadams0173]

Thanks for the response Remou. I would like to use a recordset to be able to easily change the storage flag from 0 to 1 as needed. I've been reading the forum trying to better understand recordsets. I've made DLookup work for now. If you can offer any advice or a place that I could read to understand how a recordset works and the advantage/disadvantages, if any, as oppose to using DLookup and other functions, it would be great welcomed.

 

[Remou]

Hi
I do not recall reading of the advantages / disadvantages of either method, so I will give you my (not very well thought out) tuppence worth. Perhaps someone else will add to it or correct me.
Dlookup is great where you just want to find out mainly static information. For example, I used to have bunches of 'lookup' tables that Dlookup worked very well with: Country, County, Tax Rate etc.
Recordsets are best where you are going to change data after retreiving it; where you have lots of criteria for your selection; where you are creating your own functions.
A few words on functions: they keep your code nice and easy to read and you can carry them with you where ever you go!

Finally, a recordset for the above would be quite simple:

Dim dbs as Database
Dim rst as Recordset
Set db = CurrentDB
Set rst = dbs.OpenRecordset("Select * from tblStencilLoc Where Storageflag='0'") 
'or Storageflag=0 for numeric.

It would be best to think out which fields you need, rather than using * - it makes it easier later on.

 

[jadams0173]

Hey thanks again Remou. So basically in the code above you are "opening" the table with an SQL query with criteria for storageflag='0'. Now could I use the FindFirst command to find the first 0 and then some how return the value in StorageLocation to a txt box and at the same time set StorageFlag to 1? I currently use the code below to find the first storeageflag=0 and return the StorageLocation. I don't change the flag until the user makes a selection and then I run an update query to change the value from 0 to 1. It works fine. I'm just trying to learn a different approach. Thanks for helping me understand.

Criteria = "[StorageFlag] = 0"
Loc = DLookup("[StorageLocation]", "tblStencilLoc", Criteria)

 

[Remou]

Hi
Yes. The example I gave only selects records that are set to 0 already, but you could use all records and search, which would be slower.
You can edit recordsets (and add new records):
rs.Edit
rs![StorageLocation] = 1
rs.Update

 

[jadams0173]

I'm really greatful for youe help! I will play around with it and see what happens! Thanks again Remou.