I have a form called BasicInfo.
On the form there is a field called text box called "County Name" and a ComboBox called "County Code"
There is a table called "CountyCode s" that has two fields; they are "County Code" and "County Name"
The combo box allows you to pick a county from a list tied to the "County Codes" table, and stores the "County Code" in the ComboBox field.
I want it to then automatically put the county name into the "County Name" text box.
I use the following syntax and I get no error message, but nothing happens. What is wrong with my syntax?
[County Name] = DLookup("[County Name]", "CountyCode s", "[County Code]=[Forms]![BasicInfo]![County Code]")
2 2915
I have a form called BasicInfo.
On the form there is a field called text box called "County Name" and a ComboBox called "County Code"
There is a table called "CountyCode s" that has two fields; they are "County Code" and "County Name"
The combo box allows you to pick a county from a list tied to the "County Codes" table, and stores the "County Code" in the ComboBox field.
I want it to then automatically put the county name into the "County Name" text box.
I use the following syntax and I get no error message, but nothing happens. What is wrong with my syntax?
[County Name] = DLookup("[County Name]", "CountyCode s", "[County Code]=[Forms]![BasicInfo]![County Code]")
Assuming your are placing what you wish to do in the afterupdate event of the combobox then the syntax is
Me![text box] = DLookup("[County Name]", "CountyCode s", "[County Code]='" & [Forms]![BasicInfo]![County Code] & "'")
Some advice... pay attentiion to field and control naming conventions. Spaces and the like become a pain to read where there is a space and not and vice versa
Incidentally why are you populating this way why not LEFT JOIN in a query between the table servicing your form and the county code table and then drag the County name to the query grid and use either an SQL statement as the recorsource for the form or a saved query. Access will AUTOLOOKUP the county name for you
As it is it seems to me you are breaking normalisation principles and creating redundancy potential there, because county name 'on its own' in your BasicInfo table will be 'standing alone' and totally reliant on any changes from a pick value from the drop down. Not 'efficient' should the County codes ever get changed on masse. County name will not be 'updated' as it is 'on its own'....if you understand me and if I am reading your situation correctly.
Regards
Jim
Assuming your are placing what you wish to do in the afterupdate event of the combobox then the syntax is
Me![text box] = DLookup("[County Name]", "CountyCode s", "[County Code]='" & [Forms]![BasicInfo]![County Code] & "'")
Some advice... pay attentiion to field and control naming conventions. Spaces and the like become a pain to read where there is a space and not and vice versa
Incidentally why are you populating this way why not LEFT JOIN in a query between the table servicing your form and the county code table and then drag the County name to the query grid and use either an SQL statement as the recorsource for the form or a saved query. Access will AUTOLOOKUP the county name for you
As it is it seems to me you are breaking normalisation principles and creating redundancy potential there, because county name 'on its own' in your BasicInfo table will be 'standing alone' and totally reliant on any changes from a pick value from the drop down. Not 'efficient' should the County codes ever get changed on masse. County name will not be 'updated' as it is 'on its own'....if you understand me and if I am reading your situation correctly.
Regards
Jim
Thanks for your answer on this. I will experiment with changing my approach the way you describe.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ronny Sigo |
last post by:
Hello all,
I want to perform a DLookup function with a variable (long) as critera.
I know if it was a string this would be the correct syntax :
Me!txtStad.Value = DLookup("", "tblPostnummers", " = '" &
sBoxvalue & "'")
Can anybody give me the correct syntax when boxvalue is a number (long) (In
that case it would be called nBoxvalue.
I don't seem to understand the delimiting :(:(:(
Any help appreciated ....
Thanks
|
by: jwa6 |
last post by:
syntax for a dlookup
ok I give up!
here is the error...
' Run-time error 3075 Syntax error in query expression '...'
this is the code before that a contractor wrote.
(I dont know if it ever worked....the table was wrong however)
|
by: Thelma Lubkin |
last post by:
I am still struggling with trying to match a user supplied string with
strings in a field of a table, where spaces are to be ignored.
Arno suggested that I use the Dlookup function, but I apparently can't
get its syntax right.
Access gives me "Run-time error '2001' You canceled the previous operation"
url: http://www.fontstuff.com/access/acctut15.htm,
a set of 'Access Tips' has this to say about this error:
|
by: basstwo |
last post by:
I have a field with a serial number in it. I want to use Mid to
extract the 4th and 5th characters, use them to lookup a value on a
small lookup table, and use the info from that table to fill in the
value for my lens field. I tried doing this:
=DLookUp(!,,Mid(!,4,2)=!)
Where LensLookup is my lookup table, LensName is the value I want to
have appear
|
by: MLH |
last post by:
DLookup("", "tblPreliminaryVINs",
"=Forms!frmVINODO!SerialNum")
is giving me a Type Mismatch error. That's confusing to me
and I don't know how to circumvent it. The field in
tblPreliminaryVINs is a 17-char text field. Forms!frmVINODO!SerialNum
is just an unbound textbox on a form (frmVINODO).
I run the DLookup during the textbox's BeforeUpdate event code. Some
VIN values I type in there do NOT give rise to the error. Some do. By
| |
by: bjaj |
last post by:
Hi
How do I use a boolean criterian with the funktion DLookup ?
I know the syntax for strings, numeric and date as follows
For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")
For strings: (note the apostrophe before and after the value)
|
by: Eric |
last post by:
Can any one please tell me the syntax is correct
Me.List17=DLookUp(,,="" And =")
Thanks,
|
by: sfrvn |
last post by:
I am embarrassed to say I cannot make this work. Recently upgraded to
Access 2003, but do not know if that part of problem (AKA 'syntax
change'). Would someone be kind enough to lead me by the hand?
Table name: LUtblProvider
Table field names: provPKID, name, company, type, phone
Table name: tblRef
Table field names: refPKID, prov, event, src
|
by: Birky |
last post by:
Can someone please help me with my syntax below? I am trying to enable an object on a form for specific users only and in doing so I am trying to use the DLookUp function to try and keep it sweet and simple. I have created a table named “Employees” where I plan to add the people that will have access to the particular object (named CmdProjReport). The table is very basic just housing the below info:
Employees Table:
KeyID EmployeeID ...
|
by: Breezwell |
last post by:
This is probably a simple question for someone out there.
I understand that the DLookup function takes has the following syntax:
DLookup(expression,domain,)
From what I have read, domain can be a table or query.
I have no problem getting the function to work when I use the actual name of a table for the domain. My question is, can I utilize a combobox that lists the names of my tables and pass the value of the selected table from...
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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...
| |