Multiple criteria DLookup() problem

[brownti via AccessMonster.com]

I am getting a run-time error 3075 from the following code:

Dim lkupDoorID As Variant
lkupDoorID = DLookup("[DoorID]", "tblDoorBuilds", _
"[DoorWidth] = '" & Forms![frmDoors]![DoorWidth] _
& "' And [DoorHeight] = " & Forms![frmDoors]![DoorHeight])
Forms![frmDoors]![DoorID] = lkupDoorID

Where DoorWidth = 1'6 and DoorHeight = 6'8 and are both text fields. Anybody
have suggestions?

 

[Douglas J. Steele]

The problem is that you're using single quotes as your text delimiters, but
your text includes single quotes.

Try

lkupDoorID = DLookup("[DoorID]", "tblDoorBuilds", _
"[DoorWidth] = """ & Forms![frmDoors]![DoorWidth] _
& """ And [DoorHeight] = """ & Forms![frmDoors]![DoorHeight] & """")

Exagerated for clarity, that's

"[DoorWidth] = " " " & Forms![frmDoors]![DoorWidth] _
& " " " And [DoorHeight] = " " " & Forms![frmDoors]![DoorHeight] & " " "
" )

 

[brownti via AccessMonster.com]

Thats kinda what i figured. I have that working, although right now i just
have it so that the looked up value shows in a message box. What i want to
accomplish is that when a user puts in the sizes (ie 1'6 & 6'8) the DoorID
will be looked up in the tblDoorBuilds and that value will be entered on the
tblDoors with the 1'6 and the 6'8. I hope that makes some sense...

The problem is that you're using single quotes as your text delimiters, but
your text includes single quotes.

Try

lkupDoorID = DLookup("[DoorID]", "tblDoorBuilds", _
"[DoorWidth] = """ & Forms![frmDoors]![DoorWidth] _
& """ And [DoorHeight] = """ & Forms![frmDoors]![DoorHeight] & """")

Exagerated for clarity, that's

"[DoorWidth] = " " " & Forms![frmDoors]![DoorWidth] _
& " " " And [DoorHeight] = " " " & Forms![frmDoors]![DoorHeight] & " " "
" )

I am getting a run-time error 3075 from the following code:

[quoted text clipped - 7 lines]

Anybody
have suggestions?

 

[Douglas J. Steele]

Sorry, I don't understand.

It sounds like you want an Update query that joins the two tables and
updates tblDoors with the appropriate value, something like:

UPDATE tblDoors INNER JOIN tblDoorBuilds
ON tblDoors.Field1 = tblDoorBuilds.Field2
SET DoorID = tblDoorBuilds.DoorID
WHERE tblDoorBuilds.[DoorWidth] = Forms![frmDoors]![DoorWidth]
And tblDoorBuilds.[DoorHeight] = Forms![frmDoors]![DoorHeight]

It's almost always considerably more efficient to use SQL rather than VBA.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thats kinda what i figured. I have that working, although right now i
just
have it so that the looked up value shows in a message box. What i want
to
accomplish is that when a user puts in the sizes (ie 1'6 & 6'8) the DoorID
will be looked up in the tblDoorBuilds and that value will be entered on
the
tblDoors with the 1'6 and the 6'8. I hope that makes some sense...

The problem is that you're using single quotes as your text delimiters,
but
your text includes single quotes.

Try

lkupDoorID = DLookup("[DoorID]", "tblDoorBuilds", _
"[DoorWidth] = """ & Forms![frmDoors]![DoorWidth] _
& """ And [DoorHeight] = """ & Forms![frmDoors]![DoorHeight] & """")

Exagerated for clarity, that's

"[DoorWidth] = " " " & Forms![frmDoors]![DoorWidth] _
& " " " And [DoorHeight] = " " " & Forms![frmDoors]![DoorHeight] & " " "
" )

I am getting a run-time error 3075 from the following code:

[quoted text clipped - 7 lines]

Anybody
have suggestions?

 

[brownti via AccessMonster.com]

i think i got it figured out. I have also found that SQL makes more sense
than VBA in most cases, however i know very little to no SQL, and i have
experience with excel VBA so i am trying to make that work

Sorry, I don't understand.

It sounds like you want an Update query that joins the two tables and
updates tblDoors with the appropriate value, something like:

UPDATE tblDoors INNER JOIN tblDoorBuilds
ON tblDoors.Field1 = tblDoorBuilds.Field2
SET DoorID = tblDoorBuilds.DoorID
WHERE tblDoorBuilds.[DoorWidth] = Forms![frmDoors]![DoorWidth]
And tblDoorBuilds.[DoorHeight] = Forms![frmDoors]![DoorHeight]

It's almost always considerably more efficient to use SQL rather than VBA.

Thats kinda what i figured. I have that working, although right now i
just

[quoted text clipped - 26 lines]