Trying to evaluate a simple Dlookup without success

[WannabePrgmr]

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. I am brand new at this so I really shouldn't say it looks simple because it's all new, but I'm learning.

[WannabePrgmr]

Sorry, I forgot to mention I am working in Access 2003.

Thanks

[nico5038]

You don't need the [Open/Closed] lookup, when you add the field to the combo's rowsource. Just make sure the number of columns (under the format tab of the properties window) is incremented by 1 and set the column widths correctly, setting the [Open/Closed] column to a width 0 (=invisible).
Now after the combo has been updated you can use comboname.colum n(2) (when it's the third colmn) to get the value for the [Open/Closed] field.
You could even show the field to the user, so (s)he knows or (s)he can update the selected row.

Nic;o)

[WannabePrgmr]

Thank you for your reply! Not to sound like a complete idiot, but how do I make the number of columns increment? And in the rowsource of the combobox, it only gives me a list of all the tables I have (no individual fileds). Can I type in "Open/Closed" and have it work?

Could you give an example of the codes?

Thanks again and I apologize for my confusion!!!

[nico5038]

After you've selected a table (e.g. tblName) you can click the [...] button at the end of the property line.
Now Access will ask or you want to make a query and you accept that.
Now you can choose the fields needed (Like ID, Name and OpenClose) and close the query (accept the proposed "save").

When you do this for an existing combo box you'll need to switch from the Data tab to the Format tab to increment the number of rows into 3 and to set the width of the first and last column to 0, just leaving the name visible with a real width value.

Referring to the columns in VBA goes "zero-based", this the ID will be:
comboboxname.co lumn(0), the Name column(1) and the OpenClose column(2).

Getting the idea ?

Nic;o)

[WannabePrgmr]

Let me tell you something...... I've posted this question on 3 different websites and received a total of 149 "views"! You are the only one that posted anything, and it WORKS!!!! I apologize for asking stupid questions by the way! After you walked me through it, I remembered I had done that 100's of times before! I didn't know about the Comboboxname.co lumn(1) before though and that's what did it in the end!!!!

You have no idea how much I appreciate your help and I can't thank you enough!!

[nico5038]

Welcome to bytes.com !

I have a motto "There are no stupid questions, it's only stupid not to ask" :-)

Glad it's "fixed" and knowing that someone has gained some new knowledge is my greatest reward.

Nic;o)

[MMcCarthy]

I have a motto "There are no stupid questions, it's only stupid not to ask" :-)

I second that motto :D