Dlookup issue with Access 2003! PLEASE HELP!!!

[Steve Earley]

I'm working in Access 2003. What I am trying to do is on the click event of "Command167", run a Dlookup on the number that was just typed into "cboMoveTo1" and find the value located in the table "tblName" in the "Open/Closed" field.

The Dlookup seems to work fine on its own. I tested it with a text box that was set to:

= DLookup("[Open/Closed]", "[tblName]", "[cboMoveTo1] = " & [Forms]![Master]![cboMoveTo1])

and it would bring back the correct data.

Then what I need it to do is check to see if the Dlookup return equals "Locked" (from the "Open/Closed" field and if it does, display the message box only (without going to that record).

If the Dlookup returns anything other than "Locked", it will go onto the Recorset and bring up the old record for editing.

Here is the code I have that doesn't seem to work:




Private Sub Command167_Click()

Dim varX As Variant
varX = DLookup("[Open/Closed]", "[tblName]", "[cboMoveTo1] = " & [Forms]![Master]![cboMoveTo1])

If varX = "Locked" Then
MsgBox "This reference # is currently being edited by another user. Please choose another Reference #!"
Else


Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo1) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Reference #] = " & Me.cboMoveTo1
If rs.NoMatch Then
MsgBox "Reference # not found. Please re-enter."
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
End If
End If
Set rs = Nothing
Set varX = Nothing

End Sub



Seems simple enough, but no matter what I do, it won't work!!!! It always goes directly to the old record, whether it's locked or not.
I am brand new at this so I really shouldn't say it looks simple because it's all new, but I'm learning!!!

Any help would be greatly appreciated!!!!

Thanks!



Submitted via EggHeadCafe - Software Developer Portal of Choice
Custom Self-Expanding Multiselect Winforms ListBox
http://www.eggheadcafe.com/tutorial...58-8c7b8a74319a/custom-selfexpanding-mul.aspx

 

[Marco Pagliero]

The Dlookup seems to work fine on its own. I tested it with a text box that was set to:
= DLookup("[Open/Closed]", "[tblName]", "[cboMoveTo1] = " & [Forms]![Master]![cboMoveTo1])
and it would bring back the correct data.

This is the question. Your IF structure seems allright so if

If varX = "Locked" Then

doesn't work, something must be wrong with "Locked". Maybe varX has
trailing blanks or it is lowercase.
Next try with

If trim(ucase(varX)) = "LOCKED" Then

and let us know.

Greetings
Marco