Results 1 to 6 of 6
  1. #1
    rghollenbeck is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    12

    Using an sql string as the datasource for a textbox.

    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:

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


    But that generates an error:

    Code:
    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.
    Last edited by rghollenbeck; 10-08-2011 at 03:45 PM. Reason: I wanted the code to show on the page without needing to scroll.

  2. #2
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    I'll start with your trailing ampersand was in the wrong location in the string:
    Code:
    ="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.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    rghollenbeck is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    12
    Code:
    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.

  4. #4
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    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.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    rghollenbeck is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    12
    This worked:

    Code:
    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!


  6. #6
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    Excellent! I take it the current form is not the frmSelectCourse form?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-07-2011, 11:33 AM
  2. Load textbox from another textbox
    By siddel77 in forum Programming
    Replies: 12
    Last Post: 08-30-2011, 01:46 PM
  3. SQL + VBA String
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 08-22-2011, 08:44 AM
  4. Connection String
    By cradaa in forum Access
    Replies: 4
    Last Post: 05-24-2011, 07:28 AM
  5. Replies: 7
    Last Post: 02-23-2011, 06:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums