OnClick SQL Select -> Compare -> SQL Update (1 Viewer)

Slightly stuck on some VBA, and ive been looking at it for a bit too long for anything to make any sense


I’m trying to select a value in a record with the ID in a Textbox, compare the returned value with another again stored in a Textbox, and then update the record.



Below is a rough bad PSUDO code of some type and form :-S



MeButton_OnClick
{
Var_A = SQL = “SELECT turkey.weight FROM turkey WHERE turkey.id = (TextBox A);”

If Var_A < (TextBox B)
Then Var_B = “Yes”
Else Var_B = “NO”

SQL = “UPDATE turkey SET turkey.status = “It Worked”, turkey.discount = “Var_B” WHERE turkey.id = (TextBox A);”
}

Table = Turkey
-------------
ID (int)
Weight (int
Status(text(10))
Discount(Tick Box)




So if I had the record:
----------
ID = 1
Weight = 5
Status = “For Sale”
Discount = “No”
----------

And the textboxes:
Textbox A = “1”
Textbox B = "6"


When I clicked the button it would get the weight of the turkey with the ID of “1” (would return “5”). It would say that “5” is less than Textbox B (“6”), and would update the record to:
----------
ID = 1
Weight = 5
Status = “It Worked”
Discount = “Yes”
----------


Any help would be great

Cheers

Michael

Michael

Dont think you are going about this the right way.

I would suggest that the dlookup funtion would be one possible way to go. Best place to start for info on using this is Access help or do a search of the forum. If you do go down this route be aware that dlookup does not work well when there is more than record that meets the "where" criteria. So in your case if turkey.id is a primary key or other unique record than dlookup should work to identify then record you are looking for. If there is any possibility of multiple records matching the criteria then you would be better off creating a recordset.

Also note the syntax in SQL where statements with strings eg "......WHERE Turkey.id =""" & strText & """" or ".......WHERE Turkey.id = "' & strText & '"

Not quite sure what the squigly brackets are about

Cheers i ended up using dlookup