Updating a value 1

[jbivin]

I am adding some things my boss requested to the database I am building to keep track of warehouse orders. I have a form that displays all of the current pending orders the warehouse needs to fill. I have a button that they can click to indicate that they have totally filled the order and it copies the entry from tblpending to tblfinished. I am now needing a way so that they can click a button and enter the amount delivered if they were not able to deliver the total amount and a button that will flag the order if they are not able to fill it at all.

Below is the code I have been trying to use but I cannot do anything with it:

Private Sub cmdOK_Click()

Dim strSQL As String

strSQL = "INSERTINTO tblPending.([Qty Delivered]) Values ('Text2.Value')" & _
"SELECT * FROM tblPending WHERE (((tblPending.[Part Number]) = '" & Text0.Value & "'"

DoCmd.RunSQL strSQL

DoCmd.OpenForm "frmOutput", , , , , , Text0.Value
DoCmd.Close acForm, "frmDeliveredShort"

End Sub

Text2.Value is a textbox on frmDeliveredShort where they can enter the amount delivered and frmOutput is the form the warehouse sees that displays all pending orders.

Any suggestions on how to perform this task would be greatly appreciated.

 

[dhookom]

I'm not sure what you are attempting to do but this statement is wrong in several ways:

strSQL = "INSERTINTO tblPending.([Qty Delivered]) Values ('Text2.Value')" & _
"SELECT * FROM tblPending WHERE (((tblPending.[Part Number]) = '" & Text0.Value & "'"

You are missing a space between INSERT and INTO. There should not be a . after tblPending. A single SQL statement can't INSERT using both "Values" and "SELECT". The 'Text2.Value' should be written like

   ... Values(" & Me.Text0 & ")"

I doubt you want to INSERT a record with only the Qty Delivered is populated.

Text1 and Text0 should be renamed so it makes some sense in the code.



Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Just a guess:

strSQL = "UPDATE tblPending SET [Qty Delivered]=" & Me!Text2 _
 & " WHERE [Part Number]='" & Me!Text0 & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jbivin]

Duane,

I don't want to insert a record with just the Quantity Delivered populated. I just want to modify a record and populate Quantity Delivered for a particular Part Number. This is for the warehouse operators to enter how much they delivered.

I will try your suggestions and see how they worl.

 

[jbivin]

I used your update statement PHV as follows,

Public Sub DeliveredShort()
DoCmd.SetWarnings False

Dim strSQL As String
Dim Qty As Integer
Dim ChangedQty As String

Qty = InputBox("Enter Amount Delivered", "Delivered Short or in Excess")

strSQL = "UPDATE tblPending" & _
"SET [Qty Delivered] =" & Qty & _
"WHERE [Part Number]='" & ChangedQty & "'"

DoCmd.RunSQL strSQL

End Sub

When I run it, it keeps telling me that there is a syntax error with the UPDATE and I can't tell what it is. Any help is appreciated.

 

[jbivin]

I got it. Thanks for all help. here is the code that worked.

DoCmd.SetWarnings False

Dim strSQL As String
Dim SelectedControl As Control
Dim Qty As Integer

Set SelectedControl = Screen.ActiveControl

Qty = InputBox("Enter Amount Delivered", "Delivered Short or in Excess")

strSQL = "UPDATE tblPending SET [Qty Delivered] ='" & Qty & "'" _
 & "WHERE [Part Number]='" & QtyTxtBox & "'"

DoCmd.RunSQL strSQL

 

[dhookom]

Is your [Qty Delivered] field actually text as your code indicates?

It also looks like you are missing a space before WHERE.

I'm not sure what you intend to do with the SelectedControl.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

The single quotes around Qty indicate that it's a text field.
Are you absolutely certain your code is working properly?
Here's a way to test it.

Add Debug.Print strSQL to your code.
Open the immediate window, copy the code you see there, and paste it into a new query.
Now, see if the query will run.

Randy

 

[jbivin]

Randy,

It is working good. It places the value that the person enters in the InputBox into the Qty Delivered field of the Part Number it is supposed to. I know I should have probably dimensioned Qty as a string, but it works for now.

Duane,

I am using SelectedControl to pull the name from a control and concatenate it with another value. I just didn't show that step in the posted code. It cuts out on about 100 if statements.

Thanks for all your help.