Editing records in code- Now this is a problem worth solving ;)

[Benjamin Bolduc]

Hi Everyone,

I'm stuck again on an ordering system that I'm trying to develop. I'll expalin my situation and hopefully one of you brilliant, wonderful people can help me

I have a combobox, "TheList", that is connected to the query "DueOrders" on my "Order Check" form .

It basically shows any outstanding orders. When i double-click on an item in the list, it takes all the items on the list with the same Supplier and sends the info into a report that is generated and put into an email.

This all works just fine, (Which is a miracle in itself), but now I have one more problem to work out.

For every record that is sent to a report and successfully Emailed, i need it to automatically enter today's date into the "InquerySent?" field that is on the same record. This way, i know if I've already sent an inquery report for each product and when.

I've tried reading my books, but they only have ADO solutions that don't seem to work and I can't find anything specific enough on this forum.

Below is my code if that helps.

Thanks for the help on this, You guys are the best!

-Ben


Private Sub TheList_DblClick(Cancel As Integer)
Dim stWhere As String
Dim stWhere2 As String
Dim Switch As String
Dim Email As String
Dim PO As String
Dim Link As String
Dim cnn As Connection
Dim OnOrder As New ADODB.Recordset
Dim varPosition As Variant
Dim stSQL As String

stSQL = "UPDATE [Dueorders] SET [InquerySent?] = #" & Date & "# Where " & stWhere

PO = Me.TheList.Column(3)
Switch = ChoiceList.Value
Email = HyperlinkPart(Me.TheList.Column(2), acDisplayText)
Link = HyperlinkPart("http://www.microsoft.com/accessdev/articles/snapshot.htm")
Contact = Me.TheList.Column(12)
stWhere = "[Supplier]='" & Me.TheList.Column(1) & "'"
Set DB = Access.CurrentDb
Set rs = DB.OpenRecordset("Dueorders")
Select Case Switch

Case 1

If Not Me.TheList.Column(2) = "" Then
DoCmd.OpenReport "Dueordersreport", acViewPreview, , stWhere
DB.Execute stSQL
DoCmd.SendObject acSendReport, "Dueordersreport", acFormatRTF, Email, , , "PO# " & PO, "Hello, " + vbCr + vbCr & _
"Please review the following attachment as it contains important information from Old Dutch Mustard Co., INC / Pilgrim Foods." + vbCr & _
"If you have any problems opening this file, simply download and install Snapshot Viewer from the following link, directly" + vbCr & _
"from Microsoft's web site." + vbLf + vbLf & _
"http://www.microsoft.com/accessdev/articles/snapshot.htm" + vbCr + vbCr & _
"If you have any questions regarding this E-Mail or it's contents, please contact me via E-Mail, or by the following phone/fax number." + vbCr + vbCr & _
"Sincerely," + vbCr & _
"Ben Bolduc" + vbCr & _
"Pilgrim Foods" + vbCr & _
"Label Room" + vbCr + vbCr & _
"E-Mail: Benbolduc@earthlink.net" + vbCr & _
"Phone#: (603) 878-2100" + vbCr & _
"Fax#: (603) 878-2103"

'


ElseIf Me.TheList.Column(2) = "" And Not IsNull(Me.TheList.Column(13)) Then
Dim Answer As String
Dim Answer2 As String
Answer = MsgBox("An Email address is not currently available for this supplier. Would you like to print this document and fax it?", vbInformation + vbYesNo, "No Email Address")
Select Case Answer
Case vbYes
stWhere = "[Supplier]='" & Me.TheList.Column(1) & "'"
DoCmd.OpenReport "Dueordersreport", acViewPreview, , stWhere
DoCmd.PrintOut acSelection, , , acMedium, 1
Case vbNo
Answer2 = MsgBox("Would you like to print a Contact Info Inquery to fax to this supplier?", vbInformation + vbYesNo, "Send an Information Inquery?")
Select Case Answer2
Case vbYes
DoCmd.OpenReport "Email- Query2", acViewPreview, , "[Company]='" & Me.TheList.Column(1) & "'"
Case vbNo
DoCmd.CancelEvent
End Select
End Select


End If

Case 2
If Not Me.TheList.Column(2) = "" Then
stWhere2 = "[Transaction#]=" & Me.TheList.Column(0)
DoCmd.OpenReport "Dueordersreport", acViewPreview, , stWhere2
DoCmd.SendObject acSendReport, "Dueordersreport", acFormatSNP, Email, , , "PO# " & PO, "Hello, " + vbCr + vbCr & _
"Please review the following attachment as it contains important information from Old Dutch Mustard Co., INC / Pilgrim Foods." + vbCr & _
"If you have any problems opening this file, simply download and install Snapshot Viewer from the following link, directly" + vbCr & _
"from Microsoft's web site." + vbLf + vbLf & _
"http://www.microsoft.com/accessdev/articles/snapshot.htm" + vbCr + vbCr & _
"If you have any questions regarding this E-Mail or it's contents, please contact me via E-Mail, or by the following phone/fax number." + vbCr + vbCr & _
"Sincerely," + vbCr & _
"Ben Bolduc" + vbCr & _
"Pilgrim Foods" + vbCr & _
"Label Room" + vbCr + vbCr & _
"E-Mail: Benbolduc@earthlink.net" + vbCr & _
"Phone#: (603) 878-2100" + vbCr & _
"Fax#: (603) 878-2103"




ElseIf Me.TheList.Column(2) = "" And Not IsNull(Me.TheList.Column(13)) Then

Answer = MsgBox("An Email address is not currently available for this supplier. Would you like to print this document and fax it?", vbInformation + vbYesNo, "No Email Address")
Select Case Answer
Case vbYes
stWhere = "[Supplier]='" & Me.TheList.Column(1) & "'"
DoCmd.OpenReport "Dueordersreport", acViewPreview, , stWhere
DoCmd.PrintOut acSelection, , , acMedium, 1
Case vbNo
Answer2 = MsgBox("Would you like to print a Contact Info Inquery to fax to this supplier?", vbInformation + vbYesNo, "Send an Information Inquery?")
Select Case Answer2
Case vbYes
DoCmd.OpenReport "Email- Query2", acViewPreview, , "[Company]='" & Me.TheList.Column(1) & "'"
Case vbNo
DoCmd.CancelEvent
End Select
End Select


End If

End Select

End Sub

 

[Travis]

I noticed that you create a "Where", for your criteria in limiting the report. You can use this (I noticed that Case 2 is the only one that uses stWhere2) to create an update query that can be run doing what you want. Try doing this after you run the report and after the Select Statements:

Dim stSQL as String
Dim db as DAO.Database

Set db=CurrentDb

'SQL String for Update Query
stSQL="UPDATE [TableName] SET [FieldName] = #" & Date() & "# " & stWhere

'Runs the Update Query
db.Execute stSQL

 

[Benjamin Bolduc]

That works fairly well Travis, thank you. There is still one problem though. I only want it to update those particular records that are sent to the report. Right now, its updating all the records that are on the form's query. Maybe I messed up on the syntax of something. Ive update my Code above.


Thanks again!
Ben

 

[Travis]

Dim stSQL as String
Dim db as DAO.Database

Set db=CurrentDb

'SQL String for Update Query
stSQL="UPDATE [TableName] SET [FieldName] = #" & Date() & "# Where " & stWhere

'Runs the Update Query
db.Execute stSQL


Add the word "Where" just befor the where statement.

To test to make sure the it will only update the records that you want, use Debug.Print on the stSQL variable and copy this to the SQL view of a new query. Then do a preview from there.

 

[Benjamin Bolduc]

So close!

I tried putting in just what you wrote, but it says there's a syntax error with the Where clause. Do you have any ideas?

Here's what was giving me the error

stSQL = "Update [dueOrders]set[Inquerysent?]= #" & Date & "# where" & stWhere

 

[Travis]

stSQL = "Update [dueOrders]set[Inquerysent?]= #" & Date & "# where" & stWhere

It looks like you are missing a Space right after the "# where"

There needs to be a space between "Where" and the stWhere variable.

When in doubt of what the problem is use Debug.Print to send what stSQL is equal to, to the immediate window. Then copy this into the SQL view of a new query. It should point to the error right away.

 

[Benjamin Bolduc]

Sorry, I've been out for a few days with bronchitis. I hope your not getting tired of me Travis but the code still doesnt work. It's the same Syntax error in where clause message. I'm hoping that this is a small mistake on my part and easily fixable.

Thanks for your help,
Ben

PS, I updated my code again, above

 

[Ian Mac]

stSQL="UPDATE [TableName] SET [FieldName] = #" & Date() & "# Where " & stWhere

This is Travis's code.
The code you provided has a couple of differences,

spaces either side of the word SET
and () after Date

Did you copy/paste you version?

 

[Ian Mac]

stSQL="UPDATE [TableName] SET [FieldName] = #" & Date() & "# Where " & stWhere

This is Travis's code.
The code you provided has a couple of differences,

spaces either side of the word SET
and () after Date

Did you copy/paste you version?

 

[Benjamin Bolduc]

Just to be sure, I went back and cut/pasted it. I updated my code once again but it still won't work. I know we'll get to the bottom of this but I wonder what could be wrong?

When the debugger comes on, it automatically highlights the

DB.Execute stSQL

"Dueorders" is the name of the query that the form is run on, and "Inquerysent?" is the name of the feild I want updated. But only when the specific records I choose are sent to the report.

Thanks again for your help!

Ben

 

[Drevlin]

Ok... I'm coming in at the Way tail end of this and the furthest I've gotten with your code is the:

stSQL = "UPDATE [Dueorders] SET [InquerySent?] = #" & Date & "# Where " & stWhere

part.

So I might be WAY off base here... but if your code in your database matches the code you have in your first post the problem I see is that stSQL equals:

UPDATE [Dueorders] SET [InquerySent?] = #8/29/02# Where

Basically you're trying to fill stSQL with your stWhere value before you've even put anything in it. At this point in the code stWhere = ""

So move your "stSQL =" line AFTER your "stWhere ="
I would suggest this:

...
Set rs = DB.OpenRecordset("Dueorders")
stSQL = "UPDATE [Dueorders] SET [InquerySent?] = #" & Date & "# Where " & stWhere
Select Case Switch
...


The best way to test for this error is to put a debug.Print stSQL on the line before your:
DB.Execute stSQL

 

[Benjamin Bolduc]

AH!

Hey you're right!

I just flipped the code and now it works fine. Lol, I guess I couldn't see the forest through the trees.

Thanks for your help everyone! You guys are the best