Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: Update table based on form combo boxes

    I have a table named TempEasyCategories it has 10 records and two fields "Question Number" and "Subcategory"
    The question number field is populated with numbers 1-10.

    I have a form with 10 combo boxes that pull a predefined list of subcategories from another table.

    I want the table "tempeasycategories" to update the subcategory field when the user selects a value in each combo box

    I used the following code but it is not working. can anyone help?

    Code:
     Private Sub cboQuestion1_Change()
    CurrentDb.Execute "UPDATE TempEasyQuestions" _
    & "Set Subcategory = [Forms]![Category Select]![cboQuestion1]" _
    & "WHERE [Question Number] = 1;"
     End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Make certain you use the right syntax

    My guess is the lack of whitespace
    Code:
    CurrentDb.Execute "UPDATE TempEasyQuestions" _
    & " Set Subcategory = [Forms]![Category Select]![cboQuestion1]" _
    & " WHERE [Question Number] = 1;"
    Or better yet assign the sql to a variable and then use the variable in the dbexecute. The reason being its easier to debug a variable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2016
    Posts
    82
    Provided Answers: 3
    Hi,

    Try

    Code:
    Dim sSql as string
    
    sSql = "UPDATE TempEasyQuestion Set Subcategory = " &  [Forms]![Category Select]![cboQuestion1] & " WHERE [Question Number] = 1"
    
    Msgbox sSql
    
    CurrentDb.Execute sSql
    Last edited by informer; 09-11-16 at 02:52.

  4. #4
    Join Date
    May 2016
    Posts
    82
    Provided Answers: 3
    Hi

    Just a brief comment, if Subcategory is a string field you need to enclose the value with single quote (no space with double quote) as follows


    Code:
    Dim sSql as string
    
    sSql = "UPDATE TempEasyQuestion Set  Subcategory = '" &  [Forms]![Category Select]![cboQuestion1] & "'  WHERE [Question Number] = 1"
    
    Msgbox sSql
    
    CurrentDb.Execute sSql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •