Update All Using a Command Button

[Enigo]

Hi,

I have a search form (code below) when I select my criteria and it displays
in the form I want to have a Command Button to update the Approval Status of
all records that have met my criteria to Approved in my Customer Transactions
table.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.OINumber) Then
strWhere = strWhere & "([Oracle Invoice Number] = """ & Me.OINumber
& """) AND "
End If

If Not IsNull(Me.cmbTierGroup) Then
strWhere = strWhere & "([Tier Group] = """ & Me.cmbTierGroup & """)
AND "
End If

If Not IsNull(Me.CName) Then
strWhere = strWhere & "([CustomerName2] Like ""*" & Me.CName & "*"")
AND "
End If

If Not IsNull(Me.txtCurrency) Then
strWhere = strWhere & "([Currency] Like ""*" & Me.txtCurrency &
"*"") AND "
End If

If Not IsNull(Me.txt90Plus) Then
strWhere = strWhere & "([(>90) Amount Due] >= " & Me.txt90Plus & ")
AND "
End If

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([OffLineDate] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([OffLineDate] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Search Form works fine. Can anyone help with the Approval process?

 

[Carl Rapson]

Hi,

I have a search form (code below) when I select my criteria and it
displays
in the form I want to have a Command Button to update the Approval Status
of
all records that have met my criteria to Approved in my Customer
Transactions
table.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.OINumber) Then
strWhere = strWhere & "([Oracle Invoice Number] = """ & Me.OINumber
& """) AND "
End If

If Not IsNull(Me.cmbTierGroup) Then
strWhere = strWhere & "([Tier Group] = """ & Me.cmbTierGroup & """)
AND "
End If

If Not IsNull(Me.CName) Then
strWhere = strWhere & "([CustomerName2] Like ""*" & Me.CName &
"*"")
AND "
End If

If Not IsNull(Me.txtCurrency) Then
strWhere = strWhere & "([Currency] Like ""*" & Me.txtCurrency &
"*"") AND "
End If

If Not IsNull(Me.txt90Plus) Then
strWhere = strWhere & "([(>90) Amount Due] >= " & Me.txt90Plus & ")
AND "
End If

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([OffLineDate] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([OffLineDate] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Search Form works fine. Can anyone help with the Approval process?

Is your form bound to the [Customer Transactions] table? If so, build your
Update statement using the same Where clause you built for the filter:

strSQL = "UPDATE [Customer Transactions] SET [Approval Status]='Approved' "
& strWhere
DoCmd.RunSQL strSQL


Carl Rapson

 

[Enigo]

Hi Carl,

THe form is bound to Customer Transactions table. I have put in the code you
recommended.

But when I click on my command button to approve all it does not recognise
the strWhere variable as this is part of the subform and in a different
function.

How do i get this to work on the click of the command button?

 

[Carl Rapson]

You didn't say anything about a subform in your original question. How is
this project structured? What forms do you have, and in which form is the
code you originally posted? In which form do you want the Update to occur?

Carl Rapson

Hi Carl,

THe form is bound to Customer Transactions table. I have put in the code
you
recommended.

But when I click on my command button to approve all it does not recognise
the strWhere variable as this is part of the subform and in a different
function.

How do i get this to work on the click of the command button?

Hi,

I have a search form (code below) when I select my criteria and it
displays
in the form I want to have a Command Button to update the Approval Status
of
all records that have met my criteria to Approved in my Customer
Transactions
table.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.OINumber) Then
strWhere = strWhere & "([Oracle Invoice Number] = """ &
Me.OINumber
& """) AND "
End If

If Not IsNull(Me.cmbTierGroup) Then
strWhere = strWhere & "([Tier Group] = """ & Me.cmbTierGroup &
""")
AND "
End If

If Not IsNull(Me.CName) Then
strWhere = strWhere & "([CustomerName2] Like ""*" & Me.CName &
"*"")
AND "
End If

If Not IsNull(Me.txtCurrency) Then
strWhere = strWhere & "([Currency] Like ""*" & Me.txtCurrency &
"*"") AND "
End If

If Not IsNull(Me.txt90Plus) Then
strWhere = strWhere & "([(>90) Amount Due] >= " & Me.txt90Plus &
")
AND "
End If

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([OffLineDate] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([OffLineDate] < " & Format(Me.txtEndDate
+
1, conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the
"
AND " at the end.
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Search Form works fine. Can anyone help with the Approval process?