By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Trying to evaluate a simple Dlookup without success

P: 78
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:

Expand|Select|Wrap|Line Numbers
  1. = 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command167_Click()
  2.  
  3. Dim varX As Variant
  4. varX = DLookup("[Open/Closed]", "[tblName]", "[cboMoveTo1] = " & [Forms]![Master]![cboMoveTo1])
  5.  
  6. If varX = "Locked" Then
  7. MsgBox "This reference # is currently being edited by another user.  Please choose another Reference #!"
  8. Else
  9.  
  10.  
  11. Dim rs As DAO.Recordset
  12.  
  13.     If Not IsNull(Me.cboMoveTo1) Then
  14.         'Save before move.
  15.         If Me.Dirty Then
  16.             Me.Dirty = False
  17.         End If
  18.         'Search in the clone set.
  19.         Set rs = Me.RecordsetClone
  20.         rs.FindFirst "[Reference #] = " & Me.cboMoveTo1
  21.         If rs.NoMatch Then
  22.             MsgBox "Reference # not found. Please re-enter."
  23.         Else
  24.                     'Display the found record in the form.
  25.             Me.Bookmark = rs.Bookmark
  26.         End If
  27.         End If
  28.         End If
  29.         Set rs = Nothing
  30.         Set varX = Nothing
  31.  
  32. 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.
Jan 9 '10 #1

✓ answered by 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.column(0), the Name column(1) and the OpenClose column(2).

Getting the idea ?

Nic;o)

Share this Question
Share on Google+
7 Replies


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

Thanks
Jan 9 '10 #2

nico5038
Expert 2.5K+
P: 3,072
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.column(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)
Jan 10 '10 #3

P: 78
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!!!
Jan 10 '10 #4

nico5038
Expert 2.5K+
P: 3,072
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.column(0), the Name column(1) and the OpenClose column(2).

Getting the idea ?

Nic;o)
Jan 10 '10 #5

P: 78
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.column(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!!
Jan 10 '10 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 10 '10 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
I have a motto "There are no stupid questions, it's only stupid not to ask" :-)
I second that motto :D
Jan 11 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.