Update all rows w/in column using value entered on form

[rdowney79]

I'm trying to update all the rows in a column (column A, PO Number) within a table (iSupplierTable). The value (txtPONbr) is entered by the user on a form (NewPO). Any assistance would be greatly appreciated.

Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
 
strSQL = "iSupplierTable"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
    .Edit
        If ![PO Number] = Null Then
            ![PO Number] = [Forms]![frmNewPO]![txtPONbr]
        End If
    .Update
 
End With
[Forms]![frmNewPO]![txtPONbr] = ""
[Forms]![frmNewPO]![txtPONbr].SetFocus
 
cmdSubmit_Click_Error:
    If Err.Number = 3022 Then
        MsgBox "You have entered a PO Number that already exists in the table!"
    Else
        If Err.Number = 3058 Then
            MsgBox "You must enter a PO number!"
        End If
    End If
End Sub

Thanks,
Ray

 

[pbaldy]

You would need to loop the recordset, but an update query would be far more efficient.

 

[rdowney79]

You would need to loop the recordset, but an update query would be far more efficient.

How would I store a user input (from the form) to be used in an update query? Or Is there a way to prompt for a user input, with an update query alone?

 

[pbaldy]

The update query can refer to the form for the value; in the "Update To" line in design view.

 

[rdowney79]

The update query can refer to the form for the value; in the "Update To" line in design view.

does this look right for the query?

Field: PO Number
Table: iSupplierTable
Update To: [Forms]![frmNewPO]![txtPONbr]

I dont get any results

Thanks,
Ray

 

[pbaldy]

Looks okay, but what is the SQL of the query? Second, make sure you've tabbed out of that textbox on the form, or the value won't be seen by the query. Third, in design view of the query you'd use the exclamation point to actually execute the query and change the data.

 

[rdowney79]

Looks okay, but what is the SQL of the query? Second, make sure you've tabbed out of that textbox on the form, or the value won't be seen by the query. Third, in design view of the query you'd use the exclamation point to actually execute the query and change the data.

There is a submit button that is pressed after user inputs the value in the form, does this have any impact?

UPDATE iSupplierTable SET iSupplierTable.[PO Number] = [Forms]![frmNewPO]![txtPONbr];

Damn I must be green, I'm not sure what you mean on your third point.

 

[pbaldy]

How are you running the query? Hitting a button would take update the textbox if that's what you're asking. By the way, that will update every record in the table. To replicate your code (the Null test wouldn't have worked by the way) you'd add a criteria:

UPDATE iSupplierTable SET iSupplierTable.[PO Number] = [Forms]![frmNewPO]![txtPONbr] WHERE iSupplierTable.[PO Number] Is Null

 

[rdowney79]

Issue Solved. The issue was with how I was running the query. Instead of DataSheet View, I Ran the query instead and it worked. Thank you for all your help pbaldy.

Thanks,
Ray

 

[pbaldy]

No problem Ray.