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.
7 2057
Sorry, I forgot to mention I am working in Access 2003.
Thanks
nico5038 3,080
Recognized Expert Specialist
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)
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 3,080
Recognized Expert Specialist
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)
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 3,080
Recognized Expert Specialist
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 14,534
Recognized Expert Moderator MVP
I have a motto "There are no stupid questions, it's only stupid not to ask" :-)
I second that motto :D
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Steve |
last post by:
Hello, I've been a PHP programmer for a number of years and have just
started to learn JS. My Employer (a water analysis lab) wants what
should be a very simple .js written that basically takes sample hold
time data from EPA regulations and spits out when a sample would
expire, so we can properly label the thing.
The problem is that the .js I have written appears to be doing
something unexpected.
The Analysis options are presented as...
|
by: John Hargrove |
last post by:
I am building a database to manage test samples in an environmental
laboratory. I am learning Access as I go and don't know much about
the programming aspects. I hope to make the application user-friendly
for the sample login people by populating form controls. Using the
DLookup function, I am able to populate a form control in one record
using table data. I want to be able to populate multiple records of a
subform control (test...
|
by: MUHAMAMD SALIM SHAHZAD |
last post by:
dear sir,
i would like to get your assitance about the above issues, can't find
any better slutions or codes
- table name: MOTOR2000
- field name1: POLICYNO
- field name2: ExPolicyNo
i made the form for above where is one button for renewal policy, so
|
by: chris.thompson13 |
last post by:
I am having a problem setting the criteria part of the DLookup method
correctly and am consequently getting an error message.
I have a database of staff duties, part of which is a query (qryDaily)
that returns all staffs duties for a selected day.e.g.
Fullname Duty
Name1 E1
Name2 Sick
Name3 Pwk N2
|
by: Tess2819 |
last post by:
Hi Everyone,
This is my first topic so I hope you can help. I have searched but
can't seem to find what I am looking for, so here it is.
I want to create a query using design view in MS-Access that will show
the award amount for each persons sales each month based on the range
shown between High and Low Points.
Any help would be greatly appreciated! Thanks
| |
by: Lee |
last post by:
I have an orders form which has orderID as the primary key, it also
has supplierID as a foreign key located on the form. I need a lookup
field (that looks up the account number which is located in the
suppliers table) on the orders form that syncs, so when the user moves
through the different orders the supplierID changes along with there
orders as well as the different account numbers related to the
supplierID. A previous post suggested...
|
by: |
last post by:
There are assorted "SQL Injection vulnerability assessment tools" out there.
They scan your site and send your report. They also take your money.
We don't have the money so I was wondering if I could replicate the tool's
behavior myself. I am guessing that they work by attempting a
non-destructive injection attack against your DB and evaluating the success
or failure of that test.
I am curious if a) I'm correct about this, and b) if...
|
by: John |
last post by:
To prevent the null-error from showing up when dlookup returns false I
created the code beneath which doesn't seem very elegant. How can I code
this without having to use the dlookup twice?
If Not IsNull(DLookup("", "tbSoortVerzoek", " =
""" & Forms!!Soort_verzoek & """")) Then
strForm = DLookup("", "tbSoortVerzoek",
" = """ & Forms!!Soort_verzoek & """")
Else
MsgBox ("There is no form available")
|
by: Denise |
last post by:
Front end is Access 2002, back end is linked Oracle tables.
My users need to describe things in feet and inches and want to use
the standard ' and " abbrevations.
On a testing form I go to a record with double or single quotes in the
location description and click a button to see if Access can tell me
the ID number and find its way back to the original record.
When the text contains double quotes the FindFirst works but the
Dloookup...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |