DLookup in Form

Romper

Romper

Registered User.
Local time
Today, 19:59
Joined
Oct 28, 2012
Messages
83
Hi all,
Okay, here's the scenario. I have a database for a stamp collection. I have a table named "CWused" with records data for Commonwealth used stamps. I have a form for this table called "CW Used Stamps". One of the combo boxes on the form looks up another table called "CW Countries". Within this table, there is only three columns "ID" "Country" & "CountryCode". Okay, so on the form, the combo box looks up the "Country". I need to create a field that will return the value of the "CountryCode" for the "Country" I have selected on the form. I only need the "CountryCode to appear visually, then I will use that code added to a catalogue number to create a unique name for the individual stamp. This will be typed into a totally different field.

I have exhausted tutorials, books and help screens but cannot get it to work. What should my ControlSource for the text box look like? Does it matter that the form name has spaces in it's name?

Should the following be correct?

=DLookup("[CountryCode]", "CW Countries", "[Country] = " & Forms![CW Used Stamps]![Country])

How critical are spaces in names as well as the syntax? I have tried this so many ways?
Please show me the correct ControlSource.

In desperation.......
 
Last edited:
Welcome to the forum.

I'm going to Guess that your field Country holds a string value if so your syntax should be;

Code: 
=[URL="http://www.techonthenet.com/access/functions/domain/dlookup.php"]DLookup[/URL]("[CountryCode]", "CW Countries", "[Country] = [B][COLOR="Red"]'[/COLOR][/B]" & Forms![CW Used Stamps]![Country] [B][COLOR="Red"]& "'"[/COLOR][/B])

It is generally advised to avoid using spaces and other special characters in Object and control names and restrict yourself to alpha and numeric characters and the Underscore (_)
 
Thanks for the reply,

Okay, string value? Now I'm lost. When you say the Country field should have a string value which country field would this be applied to? The form field or one of the tables?

Thanks for the welcome also.
 
Both, as they are both referring to the same thing (I hope :eek: ). Note the red highlighted adjustments I made to your code.
 
Hi John,
Just tried what you suggested and got the following message :-

The expression you entered contains invalid syntax
You may have entered an operand without an operator.

Let's go back to what you mentioned about a string value. What exactly did you mean?
Treat me as stupid, because this is how Access makes me feel.
 
What is the Data Type in the Country Field? Is it Text? DLookUp is a very Powerful statement.. If you want it to look up a String value it should be included inside single quotes as Pete has given in the above example.. If you are comparing Double/Integer/Long values you can use the statement as you have if you are using Dates as criteria you have to enclose them inside ##.. So.. Considering that the Country being a string it should be..
Code: 
'[COLOR=SeaGreen] If country is a String[/COLOR]
DLookup("[CountryCode]", "CW Countries", "[Country] =[B][COLOR=Red] '[/COLOR][/B]" & Forms![CW Used Stamps]![Country] & "[B][COLOR=Red]'[/COLOR][/B]")
'[COLOR=SeaGreen] If country is a Integer/Long/Double[/COLOR]
DLookup("[CountryCode]", "CW Countries", "[Country] = " & Forms![CW Used Stamps]![Country])
' [COLOR=SeaGreen]If country is a date, I know this is quiet funny.. but just an information. [/COLOR]
DLookup("[CountryCode]", "CW Countries", "[Country] = [B][COLOR=Red]#[/COLOR][/B]" & Forms![CW Used Stamps]![Country] & "[COLOR=Red][B]#[/B][/COLOR]")
 
...and if non of Paul's suggestions work, you might want to upload a copy of your DB.
 
Thanks so much both of you.
Yes, the Country field in the CW Countries table is text.

I will try the examples you have given me, and see what happens. I've tried so many different ways and nothing has worked. All I ever achieve is #Error. If I can't get it working, I might upload just the relevant tables & form for you to dissect. Would this be alright as the database is already 15mb and not yet fully developed?

Thanks once again.
 
You might not be able to upload anything over than 2mb here.. So you have two options here..

1. You might want to recreate the problem or scenario.. (or)
2. Make a copy of the current DB delete records (maybe leave only a few sample records), delete unnecessary forms and queries that are not in question, perform a Compact and repair.. bring the size down to 2MB and then save it in a 200-2003 version mdb file and then upload it..
 
Hi,
I was just looking at making a copy, deleting the unrelated objects and then upload. Where do I upload to?
Thanks.
 
Follow this method..
attachment.php



On the immediate window..
attachment.php
 

Attachments

  • img1.png
    img1.png
    37.3 KB · Views: 281
  • img2.png
    img2.png
    95.9 KB · Views: 295
Hi,
I have attached the database as requested. I hope the upload is successful. please read the label i have placed on the form for an explanation of what I would like to happen. Please feel free to comment on anything you notice that may be incorrect. Keep in mind I have deleted quite a few other tables in an effort to reduce file size.

Thanks for the help. Really!
 

Attachments

Having had a quick look at the DB, the very first thing I'd suggest is reading up on the concept of Data Normalisation, you might even want to work through a tutorial on the subject. You may also get some inspiration for an improved table structure form one of the data models available here.

Now given that country code forms part of the Row Source for your combo you could populate an unbound text box using the following as it's Control Source;

Code: 
=[country].[column](1)
 
Awesome!

That works a treat. Thank you so very much for solving that.

I was just about to start another thread about linking two forms, but I'll look at the normalization bit first as that could possibly change things. I've tried reading about normalization before but the things I read were confusing and complex and not really worded for a novice like me. However, I'll give it another crack!

Thanks a lot for this.
Dave.
 
Normalisation requires you to think in a completely different manner to the way you would if you were using Excel, probably the hardest thing you will need to get your head around :D
 
Just reading the link now, the Steve Litt additional reading is good. So in essence, would I be correct in saying each table should only have a ID (primary) and one other field? Most of my tables (not all) are like that. But I have one "main" table that my form uses as it's RecordSource for my stamps. This table has approx 68 fields in it (stamps aren't just a piece of paper with a picture on it). That's why I was going to ask about linking two forms, as my original database had a form with all 68 controls on it. It worked fine, but whenever I ran "Analyze Performance", it came up with a message for the form stating "The Setting For This Property Is Too Long".

I'll give the normalization tutorial a go and see if that makes any sense to me as I have trouble with this.

Thanks once again.
Dave.
 
As a general rule of thumb each table will have a Primary Key (ID, Autonumber) and at least one other field.

A table with 68 fields would, on the face of things, appear to be de-normalised.

If when you look at the records in your table, you find that a field has repeated occurrences, of a value, then you need to think; Will I benefit from hiving that information off into it's own table and only storing the ID in my main table.

For example if I look at your table CountriesCWealth2002 your Fields Country, CountryCategory, CountryCode and FormerlyKnownAs, could all be removed to a single table and in their place simply store the CountryID which references all those fields. You might then go on to normalise that table further by removing CountryCategory and even FormerlyKnownAs to their own table; and so on :D

This process then eliminates inconsistencies in data entry, where you might get keying errors or even CANADA - British Columbia & Vancouver Island entered as CANADA - BC & Vancouver Is. and further variation there of.
 
Thanks John,

I'm sorry, I'm really struggling with this. Maybe it is the Excel mindset. I understand what you have mentioned above, what I don't understand is how the information then comes together via it's relationships. Surely sooner or later you end up with one table that has all the info in it for one record, hence my 68 controls. I have one main table for the complete record of each stamp. That information is entered via a form that will have the look up controls for all other tables, e.g. perforations, gum types, paper types, colours, watermarks etc.

I don't comprehend how the fields then come together to give me a complete picture if the tables are broken down so far. How would CountryID bring each snippit together in practical terms?

Sorry, I'm not stupid I swear! But no matter how much I've read, I don't get it.

:banghead:
 
You must log in or register to reply here.

Similar threads

S
Unbound form and combobox for Country and Salution Selection
Replies
7
Views
432
silentwolf
S
Steve R.
No Cheer Here
Replies
3
Views
370
Isaac
Isaac
H
Solved Dlookup Or SetValue not working
Replies
17
Views
726
mike60smart
mike60smart
S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
358
SkeletorPlus
S
E
Solved DLookup value display with a minus sign
Replies
16
Views
785
Mark_
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom