Using an sql string as the datasource for a textbox.

[rghollenbeck]

I have a grades database for an English teacher at a community college. I have an unbound form where I want to automatically insert values based on a value on another form.



I have prepared a little web page which displays all my relationships at http://www.rghollenbeck.com/grades_web/. I have also uploaded an image of the relationships to this post. I hope it works.

My basic problem is that I created an unbound textbox and inserted the source as:

="SELECT tblCourses.CourseDescription 
FROM tblCourses 
WHERE tblCourses.CourseCode='" & [Forms]![frmSelectCourse].[cboSelectCourse]"' & ";" 

But that generates an error:

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

I am attempting to display the course description in that text box.

[RuralGuy]

I'll start with your trailing ampersand was in the wrong location in the string:

="SELECT tblCourses.CourseDescription 
FROM tblCourses 
WHERE tblCourses.CourseCode='" & [Forms]![frmSelectCourse].[cboSelectCourse] & "' ;" 

...but I doubt you can use this as the ControlSource of a textbox anyway. Use a DLookup() instead.

[rghollenbeck]

Private Sub Form_Load()
' Get Course Description for form
Dim StrSQL As String
StrSQL = "SELECT tblCourses.CourseDescription FROM tblCourses WHERE tblCourses.CourseCode =" & [Forms]![frmSelectCourse].[cboSelectCourse] & ";" ' cboSelectcourse is a long integer
txtCourseDescription = DoCmd.RunSQL strsql
msgbox StrSQL
End Sub

produces a syntax error.

I had trouble getting Nz(dlookup(. . .),"Null Value") to work too. I'll post the code I used after this.

[RuralGuy]

Here's a link to the syntax for the Domain functions: http://access.mvps.org/access/general/gen0018.htm
All of the arguments are strings.

[rghollenbeck]

This worked:

Private Sub Form_Load()

' Get Course Description for form

txtCourseDescription = Nz(DLookup("CourseDescription", "tblcourses", "tblCourses.CourseCode=" & Forms!frmSelectCourse.cboSelectCourse), "Returned Null Value.")

' tblCourses.CourseCode is a long integer

End Sub

Thank you!

[RuralGuy]

Excellent! I take it the current form is not the frmSelectCourse form?