DLookup in Form

[John Big Booty]

The countries are what I would currently consider partially normalised, you could probably do better once you have a bit more experience under your belt

 

[Romper]

Thanks John,
You didn't have to take time out to do that, but I really appreciate it. Thank you.
I'll have a look at it thoroughly and see if I can apply the theory I've been reading into it's breakdown.

You're a Champ!

 

[Romper]

Hi John,
I've been looking at normalisation. I think I'm getting the basics so I'm going to start refining the DB. Can I ask? I've looked at what you did to the sample I uploaded. Am I correct in understanding that when you set the RowSource for the "Country" field on the form, the query has a last column [Country] when "Country" is already set as an earlier field in the same query. Is the [Country] last column there to define what is actually displayed? If not, what is the principle behind the way you've created the RowSource?

Cheers for all your help.

 

[John Big Booty]

The last field in that Row Source is probably an artefact from the 2010 Combo Box wizard. You will notice that it is hidden, it's purpose is simply to sort the data by country. This is superfluous as this could just as easily be done on the Country Field earlier in the Row Source.

 

[Romper]

Hi John,
Okay, no problem, thanks for all of your help on this. Much appreciated.

 

[Romper]

Hi all,
Okay, so I've normalized my database to the best of my ability. I've dissected the sample upload that was edited to show me how it should be. I've just attempted a test form to see how my new layout works. For some reason, I can't get my form to display the value but store it's ID back to the FRM_Main. As a test, my TBL_Main now has columns "MainID", "CountryID" & "CatID". When the table was created, I just named the fields as such. On my form, my combo box will display the "CountryName" and that is what it is saving back to the "CountryID" field in "TBL_Main" and not the actual ID as is good practice. Is it because of the way I've named the table fields or a row source issue, maybe something totally different? I've looked at the edited upload and can't see any major differences apart from the fact my version doesn't work!

What have I done wrong this time?

 

[John Big Booty]

You'd better upload what you've done. There's probably something small but significant that you've missed.

 

[Romper]

Okay,

Will do so shortly. Cheers

 

[Romper]

here's the upload.
please keep in mind that i was testing what i've learnt before i apply it to my actual database. Good job too I think.

Thanks so much.

:banghead:

 

[John Big Booty]

OK, your Table TBL_Main should be storing numbers not text for starters.

Now go back to the DB I uploaded and compare the Row Source of combo's I've used and yours. You should see mine;

and yours;

Note that the first column in mine is the bound column as is your but in mine that column is hidden.

If you follow the Combo Box wizard you should see the following;

Then

Then

See next post

 

[John Big Booty]

Then

Then

Then

Then

The

Continues in next post

 

[John Big Booty]

...and culminating in;

 

[Romper]

Thanks John,

I'll have a play. Is Combo Wizard in Access 2007 exactly the same? As I've never use the wizard! I've always just added existing fields.

Thanks for laying it out step by step.

Dave.

 

[John Big Booty]

That was '10, but '07 would be pretty similar if not identical. You find this thing a whole lot easier using the wizard.

 

[Romper]

OMG!

I've just done it! Now I feel like a real idiot, such a simple process and yet I never knew it could be done like that. As I said, I've always just dropped existing fields into my forms and then manipulated the field to what I've wanted. I really must thank you for not treating me like a dumb-ass and for the lengths you have gone to in helping me. You're worth your weight in gold mate.

Now I shall proceed with the re-hash of my old database and implement my new found knowledge...... scary isn't it?

Thanks so very very much.
Dave.

 

[John Big Booty]

Have fun with it (your new knowledge that is)

 

[Romper]

Cheers John,
It should mean I leave you in peace for at least a couple of days!

 

[Romper]

So much for the peace huh?

I just placed a new thread on the Reports forum. Basically what I need to know is what do I have to do to convert the ID values back to full descriptive values in a report. If you get the chance please look at my post the Reports forum.

Thanks.

 

[John Big Booty]

Looks as if Alan Sidman has helped you out on that one