Please Help

[Guest]

I have this code

DoCmd.Close
DoCmd.OpenForm "Mercedes Cert"
CurrentDb.Execute "UPDATE Cert SET [Date Cert by mercedes] = Date()",
dbFailOnError
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)",
, , "CCG's have been certified", EditMessage:=False

the problem I have is in the begining I have it filterd for specific
critera. I have a combo box that I pick what we call a batch number form.
Then it opens a form with those batch numbers. There is a list of amounts.
If there is a problem with an amount I have a hold button, so this one does
not get a date. However I just noticed when someone clicks the button to
place the date in all the records, it closes the form then reopens the for
the places the date. The problem with this is that it places the date in all
the records in the database. How can I get it to populate all the records
that are filtered ont the form except for the ones on hold.
Thanks for your time

Chey

 

[Guest]

Hi Chey,

Your update query, "UPDATE Cert SET [Date Cert by mercedes] = Date()", does
not include any criteria. Thus, it's doing exactly what you asked it to do,
which is update all records in the Cert table. You'll need to tack on the
appropriate WHERE clause to your SQL statement, so that it only acts upon a
subset of the records in your table.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

is there a way to where it would only update what was filtered minus
[Hold]=true?

Hi Chey,

Your update query, "UPDATE Cert SET [Date Cert by mercedes] = Date()", does
not include any criteria. Thus, it's doing exactly what you asked it to do,
which is update all records in the Cert table. You'll need to tack on the
appropriate WHERE clause to your SQL statement, so that it only acts upon a
subset of the records in your table.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have this code

DoCmd.Close
DoCmd.OpenForm "Mercedes Cert"
CurrentDb.Execute "UPDATE Cert SET [Date Cert by mercedes] = Date()",
dbFailOnError
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)",
, , "CCG's have been certified", EditMessage:=False

the problem I have is in the begining I have it filterd for specific
critera. I have a combo box that I pick what we call a batch number form.
Then it opens a form with those batch numbers. There is a list of amounts.
If there is a problem with an amount I have a hold button, so this one does
not get a date. However I just noticed when someone clicks the button to
place the date in all the records, it closes the form then reopens the for
the places the date. The problem with this is that it places the date in all
the records in the database. How can I get it to populate all the records
that are filtered ont the form except for the ones on hold.
Thanks for your time

Chey

 

[Guest]

Hi Chey,

Yes. You would do best to concentrate on creating a normal SELECT query that
selects just the records that you want to update. Use whatever criteria are
necessary. Then convert it to an UPDATE query. Using the Northwind sample
database for an example, suppose we wanted to update the UnitPrice for all
products, but only for those products that are not discontinued. We can start
with a normal SELECT query to filter the current products:

SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE (((Products.Discontinued)=0));

This query returns 69 current products. If I want to increase the UnitPrice
by 6% for the current products only, the SQL statement might look like this:

UPDATE Products
SET Products.UnitPrice = Round([UnitPrice]*1.06,2)
WHERE (((Products.Discontinued)=0));



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

I am now getting back to this question. Right now I have a drop down box
that lets me choose a batch number.
When i choose a batch number it filters when the form open. I then place
the people that I would like on hold. I have a command button with an event
procedure to Update...
I took out the close and open form. Do I still need to do a select query.
I am learning what they are and sometimes I don't understand. I would like
to somehow add it to the event procuedure.
Thanks for you time.
Chey

Hi Chey,

Yes. You would do best to concentrate on creating a normal SELECT query that
selects just the records that you want to update. Use whatever criteria are
necessary. Then convert it to an UPDATE query. Using the Northwind sample
database for an example, suppose we wanted to update the UnitPrice for all
products, but only for those products that are not discontinued. We can start
with a normal SELECT query to filter the current products:

SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE (((Products.Discontinued)=0));

This query returns 69 current products. If I want to increase the UnitPrice
by 6% for the current products only, the SQL statement might look like this:

UPDATE Products
SET Products.UnitPrice = Round([UnitPrice]*1.06,2)
WHERE (((Products.Discontinued)=0));



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

is there a way to where it would only update what was filtered minus
[Hold]=true?

 

[Guest]

Hi Chey,

I took out the close and open form.

You can use the value in a combo box, in an open form, to supply the
criteria for a query. The syntax on the Criteria line would look like this:

=Forms![NameOfForm]![NameOfControl]

where NameOfForm is the name of your form, and NameOfControl is the name of
the combo box on your form. The form must be open when the query is run. The
bound column of the combo box is what will be included in the criteria.

Do I still need to do a select query.

No. I only recommended a select query as a convenient method of verifying
that you are selecting only the desired records. When you convert the select
query to an action query, such as an update query, only those records that
were included in the recordset will be updated.

I would like to somehow add it to the event procuedure.

In fact, this is what I usually do. I have a personal preference for having
stuff self-contained within a form, so that the functionality is not
dependent on another object (ie. a saved update query in your case). You can
create the SQL statement for the update query in VBA code.

I have a nice little tutorial, written by another author that I can send to
you, which demonstrates the select query. I'd even be willing to spend a few
minutes modifying this sample so that it is completely self-contained, as I
described. If you are interested, send me a private e-mail message with a
valid reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

oh sorry. before in my event. I had it close the form the open it back up
then do the update. I did it for the reason that in my query I have a
critiera Hold-is false.
Then it does the update. I just want to be able to press the command button
and it udate only what was filtered, minus the hold. So my event procedure is
CurrentDb.Execute "UPDATE Cert SET [Date Cert by mercedes] = Date()",
dbFailOnError
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)",
"CCG's have been certified", EditMessage:=False

What should I as to the top line
CurrentDb.Execute "UPDATE Cert SET [Date Cert by mercedes] = Date()",
(Everything except [Hold]=true)

I hope this makes more sense.

Chey

Hi Chey,

I took out the close and open form.

You can use the value in a combo box, in an open form, to supply the
criteria for a query. The syntax on the Criteria line would look like this:

=Forms![NameOfForm]![NameOfControl]

where NameOfForm is the name of your form, and NameOfControl is the name of
the combo box on your form. The form must be open when the query is run. The
bound column of the combo box is what will be included in the criteria.

Do I still need to do a select query.

No. I only recommended a select query as a convenient method of verifying
that you are selecting only the desired records. When you convert the select
query to an action query, such as an update query, only those records that
were included in the recordset will be updated.

I would like to somehow add it to the event procuedure.

In fact, this is what I usually do. I have a personal preference for having
stuff self-contained within a form, so that the functionality is not
dependent on another object (ie. a saved update query in your case). You can
create the SQL statement for the update query in VBA code.

I have a nice little tutorial, written by another author that I can send to
you, which demonstrates the select query. I'd even be willing to spend a few
minutes modifying this sample so that it is completely self-contained, as I
described. If you are interested, send me a private e-mail message with a
valid reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I am now getting back to this question. Right now I have a drop down box
that lets me choose a batch number.
When i choose a batch number it filters when the form open. I then place
the people that I would like on hold. I have a command button with an event
procedure to Update...
I took out the close and open form. Do I still need to do a select query.
I am learning what they are and sometimes I don't understand. I would like
to somehow add it to the event procuedure.
Thanks for you time.
Chey

 

[Guest]

Hi Chey,

You might come to regret publishing your e-mail address in your last reply,
unless the State of Alaska has some good spam filters that automatically
protect your inbox...If not, then it's probably too late and the spammers
will have already harvested your e-mail address. <sad face>

If you are using Form Filters, this can get really messy. In some brief
testing that I've been doing this morning, it seems to work okay, as long as
you do not attempt to filter using a combo box control. For my example, I'd
like to stick with the sample Northwind database, so that anyone else who
might be interested, and is following this thread, can try the code. I'm also
going to stick with just a SELECT query for now.

Open the Products form in Northwind. Add a new command button and name it
"cmdUpdateFilteredPrices". Add the following event procedure:

Private Sub cmdUpdateFilteredPrices_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strFilter As String

Set db = CurrentDb()

strSQL = "SELECT Products.ProductName, Products.UnitPrice " _
& "From Products WHERE Products.Discontinued=0"

If Me.FilterOn = True Then
strFilter = Me.Filter
strSQL = strSQL & " AND " & strFilter
End If

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.RecordCount > 0 Then
rs.MoveLast
MsgBox "There were " & rs.RecordCount & _
" records selected.", vbInformation
End If

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
Select Case Err.Number
Case 3061 ' Too few parameters
MsgBox "Try removing filters. Do not use a combo or list box in
your filter."
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure
cmdUpdateFilteredPrices_Click..."
End Select
Resume ExitProc
End Sub


In my very brief testing, this seems to work as long as one does not attempt
to filter by either the Supplier or Category combo boxes.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

oh sorry. before in my event. I had it close the form the open it back up
then do the update. I did it for the reason that in my query I have a
critiera Hold-is false.
Then it does the update. I just want to be able to press the command button
and it udate only what was filtered, minus the hold. So my event procedure is
CurrentDb.Execute "UPDATE Cert SET [Date Cert by mercedes] = Date()",
dbFailOnError
DoCmd.SendObject acSendNoObject, , , "<Removed e-mail address>",
"CCG's have been certified", EditMessage:=False

What should I as to the top line
CurrentDb.Execute "UPDATE Cert SET [Date Cert by mercedes] = Date()",
(Everything except [Hold]=true)

I hope this makes more sense.

Chey