• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

Message box Yes/No on Access 2010 subform

I have a main form & subform. The subform displays item data info with different prices ( ItemID, ItemCode, Price1,Price2, Price3) . When the user clicks on a field (say Price1) all relevant fields are assigned to variables for later processing. I have a yes/no message box pop up confirming the selected fields of the record.  All variables shown in the message have the correct values. What I want to do is, when the answer is yes, to insert the variables into a table. However, nothing happens when the answer is yes. The DoCmd.RunSQL  assigned does not fire off . Any help is appreciated.
0
thao-nhi
Asked:
thao-nhi
  • 3
  • 2
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
if the record is being edited then it is generally better for the code to write to controls provided the values are in them.  
me.controlname = value

Open in new window


Is this a bound form?
If yes and you do want to change values in the record being edited using SQL, be sure to first save the record
if me.dirty = true then me.dirty = false

Open in new window

Is there a reason you are using DoCmd.TunSql instead of CurrentDb.Execute? What is an SQL statement that is not executing?

why do you have so many prices in one record? Perhaps the data structure could use some help too?
0
 
thao-nhiAuthor Commented:
The subform is bound to a table1  to show the data. When clicked, the record data will be inserted into table2. 1 record is for 1 item & 1 Item has different prices like wholesale, retail &  bulk.

So far I got:

Click_Event

Dim LResponse As Integer
Dim CustID as String
Dim sqls As String

LResponse = MsgBox("You selected" & vbCrLf & ItemCode & vbCrLf & "$" & ItemPrice & vbCrLf _
& "for W/O:" & " " & TempVars!CurrentWO_ID.Value & vbCrLf & "Please confirm!", vbYesNo, "Adding Item To Quotation")


If LResponse = vbYes Then
CustID = left(TempVars!CurrentWO_ID.Value,8)
sqls = "insert into Quotation (CustomerID, Item_Code, Item_Price) values('" & CustID & "', '" & ItemCode & "', '" & ItemPrice & "');"
DoCmd.RunSQL sqls

End If
0
 
thao-nhiAuthor Commented:
thanks
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

 ... did you get it working?
1
 
Dale FyeCommented:
thao-nhi,

When you write your SQL statement, you must wrap text strings with single or double quotes, but numeric values should not have those.  It appears that your SQL string contains several of these characters which it should not.  Assuming that CustomerID and Item_Price are numeric values and that Item_Code is a string, the SQL would look like:

sqls = "insert into Quotation (CustomerID, Item_Code, Item_Price) " _
        & "values(" & CustID & ", '" & ItemCode & "', " & ItemPrice & ");"

HTH
Dale
0
 
thao-nhiAuthor Commented:
Thanks for the note. I'll correct that
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now