having trouble with Lookup

[Ron Berns]

I am still pretty green at this coding. I am using Access2003.

I am trying to lookup a record in another table(stores) and save a field in the
current table (Table1). Here is what I have:

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Table1")

..
..
..
StoreNo = Mid(MyString, 3, 4)
error --> rst!ST_GLN = DLookup("[GLN_NO]", "stores" , "[STORE_NO] = [StoreNo]")
rst.Update



Thanks in advance.
Ron

 

[Jeff Boyce]

Ron

Are you saying that you want to take a value stored in one table and copy it
into another table? If so, why? Doing whatever you're trying to do this
way would violate basic relational database design unless your situation is
fairly unusual.

If you'll describe your situation a bit more, folks here may be able to
offer more specific assistance.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JimBurke via AccessMonster.com]

You need to put the StoreNo value outside the quotes:

rst!ST_GLN = DLookup("[GLN_NO]", "stores" , "[STORE_NO] = " & StoreNo)

This will work if StoreNo is a number. If it isn't numeric then you'd need to
enclose the value in quotes:

rst!ST_GLN = DLookup("[GLN_NO]", "stores" , "[STORE_NO] = """ & StoreNo &
"""")

That's 3 quotes after [STORE_NO = and 4 quotes at the end, if you need this
version.

You should always have error checking just in case the dlookup doesn't work
for some reason. I would never just directly assign the value of a DLookup to
a recordset field. I would do something like this:

dim GLN as Long ' or whatever type GLN-NO is defined as in the table

GLN = nz(DLookup("[GLN_NO]", "stores" , "[STORE_NO] = " & StoreNo),-1)
if GLN <> -1 then
rst!ST_GLN = GLN
Else
do whatever you would do here in case the dlookup didn't find anything
End If

The NZ fuction tells Access what value to assign in place of Null, so if the
DLookup doesn't find that value of StoreNo, it will be assigned -1. If the
field is text then make it vbNullString and change the If statement
accordingly.

You should also have an On Error statement in the sub as well in case of an
unexpected error, which you may already have.

I am still pretty green at this coding. I am using Access2003.

I am trying to lookup a record in another table(stores) and save a field in the
current table (Table1). Here is what I have:

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Table1")

.
.
.
StoreNo = Mid(MyString, 3, 4)
error --> rst!ST_GLN = DLookup("[GLN_NO]", "stores" , "[STORE_NO] = [StoreNo]")
rst.Update

Thanks in advance.
Ron