Results 1 to 5 of 5
  1. #1
    Muz is offline Novice
    Windows 11 Office 365
    Join Date
    Nov 2023
    Posts
    1

    How to use a value from an Uncontrolled Text Box on a Form in a SQL Update Query

    I have a table called tblFoodOrder which has 2 fields, FoodName and Number, so Eggs, 23; Bacon 47 and so on. Using the form frmFoodOrder I want the user to be able to enter a number in the uncontrolled text box called Normal, that will update the Number field, dependant on the FoodName. The code I have so far is..

    Private Sub Command18_Click()
    Dim dbs As Database



    Set dbs = OpenDatabase("C:\Users\Mum\Documents\dbTestMenu.ac cdb")



    dbs.Execute " UPDATE tblFoodOrder " _
    & " Set FoodAmount = 36 " _
    & " WHERE FoodName = 'Eggs'; "

    dbs.Execute " UPDATE tblFoodOrder " _
    & " Set FoodAmount = Forms![frmFoodOrder]![Normal] " _
    & " WHERE FoodName = 'Bacon';"



    dbs.Close
    End Sub


    This works updating the number of Eggs to 36 ,in the table tblFoodOrder, but wont work using a number entered in an uncontrolled text box named Normal on the form frmFoodOrder
    to update the number of bacon rashers.

    Any help greatly appreciated
    Muz

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,425
    Add a a strSQL variable and populate that with each sql string.
    Then you can debug.print it and check it is OK.

    However you need to also parameterise the food name, so may as do it all in one go.
    Personally I would be using an autonumber for the food, and just showing the foodname in a combo.

    Failing that, upload the DB.

    Edit: Probably should have

    Code:
    dbs.Execute " UPDATE tblFoodOrder " _
    & " Set FoodAmount = " & Me.[Normal]  _
    & " WHERE FoodName = '" &  Me.Foodname & "'"
    

    If the code is in the Foodname form, no meed for the longwinded reference.

    I would still debug.print.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,410
    Why not just bind the Number field also?



  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,552
    What Gasman has suggested is putting your Update statement into a variable and debug printing it to see what it resolves to.
    Doing that, as well as always using Option Explicit, will save you hours of frustration.

    Code:
    Dim strSql as string
    
    strSql = "UPDATE tblFoodOrder   Set FoodAmount = " & Me.[Normal] & " WHERE FoodName = """ &  Me.Foodname & """"
    
    Debug.Print strSql
    
    'dbs.execute strsql, dbfailonerror
    
    
    
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,552
    I've been wondering why you are using an update query rather than an insert query. It makes me wonder if your tables are normalized.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 8
    Last Post: 05-29-2022, 01:01 PM
  2. Replies: 4
    Last Post: 11-04-2019, 06:53 PM
  3. Replies: 3
    Last Post: 11-14-2018, 01:20 PM
  4. Replies: 2
    Last Post: 08-03-2017, 04:58 AM
  5. UPDATE QUERY to Separate Numbers from Text in a Text Field
    By pjordan@drcog.org in forum Queries
    Replies: 2
    Last Post: 05-29-2015, 02:44 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
  •  
Other Forums: Microsoft Office Forums