Update all rows w/in column using value entered on form (1 Viewer)

rdowney79

Registered User.
Local time
Today, 07:07
Joined
Nov 7, 2013
Messages
12
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.


Code:
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

Wino Moderator
Staff member
Local time
Today, 05:07
Joined
Aug 30, 2003
Messages
36,118
You would need to loop the recordset, but an update query would be far more efficient.
 

rdowney79

Registered User.
Local time
Today, 07:07
Joined
Nov 7, 2013
Messages
12
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

Wino Moderator
Staff member
Local time
Today, 05:07
Joined
Aug 30, 2003
Messages
36,118
The update query can refer to the form for the value; in the "Update To" line in design view.
 

rdowney79

Registered User.
Local time
Today, 07:07
Joined
Nov 7, 2013
Messages
12
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

Wino Moderator
Staff member
Local time
Today, 05:07
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 07:07
Joined
Nov 7, 2013
Messages
12
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?

Code:
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

Wino Moderator
Staff member
Local time
Today, 05:07
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 07:07
Joined
Nov 7, 2013
Messages
12
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

Wino Moderator
Staff member
Local time
Today, 05:07
Joined
Aug 30, 2003
Messages
36,118
No problem Ray.
 

Users who are viewing this thread

Top Bottom