SELECT in Text Box

[jaanisf]

Cannot solve this. Imagine two tables [Table1] and [Table2]. [Table1] has three fields: Name, Birthdate and Occupation. [Table2] has two fields: Name and Birthdate. [Table2] is already filled up with meny records. Now, I have a form for editing [Table1] records with a ComboBox called "Name" and two TextBoxes: Birthdate and Occupation. When I choose a name from a list in ComboBox (list selected from [Table2] records) I want that TextBox to automaticaly show the Birthdate value (I want it to automaticaly take from [Table2] depending on what Name is chosen).

What should I write in TextBox's Control Source, assuming my forms name is [Form1]?

 

[Fizzio]

Use the DLookup function but also why are you storing the same data twice?

 

[jaanisf]

It is just a simple example, I have different tables and forms, but it just does not work :/ I tried the DLookUp function as well, but all I got is a #Name? error.

 

[jaanisf]

Is that What I have to write in TextBox's Control Source?
=DLookup("[Birthdate]", "Table2", "[Name] = " & Forms!Form1!Name);

I get an invalid syntax error. :/

 

[Rich]

Add the field/s to the combo's underlying query

 

[jaanisf]

it does not work in no way.. :/

 

[Rich]

What doesn't?

 

[jaanisf]

If I knew, I would not ask I mean, the whole thing does not work. I need just a string to write in Control Source. Or is there any other way?

I tried also SELECT [Birthdate] FROM [Table2] WHERE [Name] = [Form1]![Name] but it does not work as well. What I think is, that I have just a syntax problems. Can anyone just type in a right code?

 

[Rich]

Open the query that the combo is based on, add the fields that you want to the grid, add an unbound textbox to the form, set its control source to =[YourCombo].[Column](1) where column 1 is the second column

 

[jaanisf]

Argh, got you. And I thought whatta query. You see, I did not use any queries, I thought it can be done without them.
Ok, if you think I need queries though, let's build one.

 

[jaanisf]

Wait, wait.. then I must use variables. I mean how can I use the value [Form1]![Name] in my new query [Query1]?

--> SELECT [Birthdate] FROM [Table2] WHERE [Name] = / and what here? (must be a [Form1]![Name] value) /

It will be more difficult, don't you think? ;D

 

[jaanisf]

Well, found it a last. If someone ever needs it as well, I found it here

Thanks anyone for submiting

 

[Pat Hartman]

Let's go back to the DLookup() since you have syntax errors with it.

=DLookup("[Birthdate]", "Table2", "[Name] = '" & Forms!Form1!Name & "'")

I am assuming that [Name} is text and so surrounded the variable with single quotes. This will work as long as none of your names actually contains a single quote such as O'Neil. If that happens, you'll need to use double quotes.

Also, If your field is really named Name, change it. Name is a reserved word in Access.