Dlookup issue with Access 2003! PLEASE HELP!!!


S

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
 
Ad

Advertisements

M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top