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

[Muz]

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

[Welshgasman]

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

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.

[Bulzie]

Why not just bind the Number field also?

[moke123]

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.

Dim strSql as string

strSql = "UPDATE tblFoodOrder   Set FoodAmount = " & Me.[Normal] & " WHERE FoodName = """ &  Me.Foodname & """"

Debug.Print strSql

'dbs.execute strsql, dbfailonerror

[moke123]

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.