Solved DlookUp reference as textbox Control Source

[Manos39]

Please help,
I am trying to replace combo boxes in my forms, to text boxes and..

i have a particular combobox with rowsource

SELECT Tbl_Courses.courseID, Tbl_CourseNames.CourseName FROM Tbl_CourseNames INNER JOIN Tbl_Courses ON Tbl_CourseNames.coursenameID = Tbl_Courses.coursenameID;

then replacing with DlookUp when turn combo box to text box , all i get is !Typo or !Error using

=DLookUp("CourseName";"Tbl_CourseNames";"coursenameID =" & DLookUp("coursenameID";"Tbl_Courses";"courseID=" & [courseID]))

where do i miss?

 

[Gasman]

Your separators are ; ?

 

[plog]

Why do you have a seperate table for course names? How many courses share the same name?

For your issue, I suggest you turn your combo box drop down recordsource query into an actual query object. Once that is done you can do one simple Dlookup into it using the courseid to get the course name--you won't have to nest a dlookup inside a dlookup.

If you want to nest them, you need to take smaller bites. Get the inner Dlookup working by itself. Throw away the Dlookup into Tbl_CourseNames and make the other Dlookup return the correct value. Then once it is, you can bring back the main dlookup. right now, you don't know which dlookup is failing. Divide and conquer it to determine where this 2 part dlookup is failing.

 

[Manos39]

Your separators are ; ?

Yes

 

[Manos39]

Why do you have a seperate table for course names? How many courses share the same name?

For your issue, I suggest you turn your combo box drop down recordsource query into an actual query object. Once that is done you can do one simple Dlookup into it using the courseid to get the course name--you won't have to nest a dlookup inside a dlookup.

If you want to nest them, you need to take smaller bites. Get the inner Dlookup working by itself. Throw away the Dlookup into Tbl_CourseNames and make the other Dlookup return the correct value. Then once it is, you can bring back the main dlookup. right now, you don't know which dlookup is failing. Divide and conquer it to determine where this 2 part dlookup is failing.

All i am trying solve is how to make things easier to a user's eyes,
as for instance a user faces forms and he/she is obliged having to see combos instead of nice textboxes where there
in most of times lyies a fixed value which souldnt be in a selectable to choose option for a user..
The example was not so great picking that field..
I am trying to find an easy way to "concarnation" of a field and from what i found and the answers you gave me,
there is no way to "concarnate" easily..

 

[plog]

You quoted me then wrote a bunch of text unrelated to what I wrote. I do not know how to respond, other than-- reread my post, I gave you 2 methods to solve the issue you posted about..

 

[Manos39]

I undrestand and thank you for the complete answer. By your answer i am trying to say that by undrestanding what is going to take (my DB has many many forms) it is not easy to get in such trouble for achieving what i was seeking. That was very helpful to me

 

[Gasman]

All i am trying solve is how to make things easier to a user's eyes,
as for instance a user faces forms and he/she is obliged having to see combos instead of nice textboxes where there
in most of times lyies a fixed value which souldnt be in a selectable to choose option for a user..
The example was not so great picking that field..
I am trying to find an easy way to "concarnation" of a field and from what i found and the answers you gave me,
there is no way to "concarnate" easily..

Myself, I think combos look better than textboxes? They also prevent bad data.?
I have a DB that has a form for data entry.
You can see how many records in that table the form uses, you can see one textbox and that is the autonumber and not editable.
I entered all those records with a few keystrokes of the keyboard.

Eg, As soon as I type c in the Rank control, I get Captain.

 

[Pat Hartman]

The point of a combo is to constrain the data entry to a list of valid values. Making a user type the value is less user friendly than having him pick the name from the list.

Not sure what your objection is to how a combo looks or why you would choose looks over functionality.