DoCmd.RunSQL and Viewing a Report

[Nuke1096]

So I have a form all done and working more or less how I want. However, one thing I want to do is make it so that the inputted values on the form are only actually inputted into the table when a person clicks on a command button labled as Submit. This is because I don't want new records to be created whenever somebody closes the form by accident. I have this working correctly, however, when the Submit Command Button is pressed, it is also supposed to send a .pdf file of a report based on the form. The problem is that the report that is being sent as a pdf is always the first record in the table. How do I get it to send the report with the correct record when using a DoCmd.RunSQ to append the information to the table?

I had it working correctly before I switched to the DoCmd.RunSQ method, because I had this code making sure it was the right record.

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acViewReport, , "[Charter_ID] = " & [Charter_ID]

This doesn't do anything anymore. Do I need to change this around? Or should I be using something totally different? Here is my code.

Private Sub SubmitButton_Click()
 
  Dim stDocName As String
  stDocName = "Report_Charter_Redesign"
    
  ' Saves the data inputted on the form and opens up a report with the data that was entered.
  
  DoCmd.RunCommand acCmdSaveRecord
  DoCmd.OpenReport stDocName, acViewReport, , "[Charter_ID] = " & [Charter_ID]
    
    
    
  Dim strSQL As String
    
  strSQL = "INSERT INTO Table_Charter_Form_Redesign (Customer_Name, Event_Type, People_Num, Pickup_Address, Destination_Address, Special_Notes, Order_Date, Event_Date, Special_Needs, Branch, Pickup_Time, Departure_Time, Quote, Customer_Phone, Payment_Method, Manager_Name, Manager_Contact, Driver, Branch_Details, Paid_Full, Paid_Date, Vehicles_Assigned) " & _
  "SELECT [Forms]![Form_Charter_Redesign]![CustomerNameBox] AS Expr1, " & _
  "[Forms]![Form_Charter_Redesign]![EventBox] AS Expr2, " & _
  "[Forms]![Form_Charter_Redesign]![NumberPeopleBox] AS Expr3, " & _
  "[Forms]![Form_Charter_Redesign]![PickupAddressBox] AS Expr4, " & _
  "[Forms]![Form_Charter_Redesign]![DestinationAddressBox] AS Expr5, " & _
  "[Forms]![Form_Charter_Redesign]![NotesBox] AS Expr6, " & _
  "[Forms]![Form_Charter_Redesign]![OrderDateBox] AS Expr7, " & _
  "[Forms]![Form_Charter_Redesign]![EventDateBox] AS Expr8, " & _
  "[Forms]![Form_Charter_Redesign]![SpecialNeedsBox] AS Expr9, " & _
  "[Forms]![Form_Charter_Redesign]![ComboBranch] AS Expr10, " & _
  "[Forms]![Form_Charter_Redesign]![PickupTimeBox] AS Expr11, " & _
  "[Forms]![Form_Charter_Redesign]![DepartureTimeBox] AS Expr12, " & _
  "[Forms]![Form_Charter_Redesign]![QuoteBox] AS Expr13, " & _
  "[Forms]![Form_Charter_Redesign]![CustomerPhoneNumberBox] AS Expr14, " & _
  "[Forms]![Form_Charter_Redesign]![PaymentMethodBox] AS Expr15, " & _
  "[Forms]![Form_Charter_Redesign]![BranchManagerBox] AS Expr16, " & _
  "[Forms]![Form_Charter_Redesign]![ManagerNumberBox] AS Expr17, " & _
  "[Forms]![Form_Charter_Redesign]![DriverBox] AS Expr18, " & _
  "[Forms]![Form_Charter_Redesign]![DetailsBox] AS Expr19, " & _
  "[Forms]![Form_Charter_Redesign]![EventPaidFullBox] AS Expr20, " & _
  "[Forms]![Form_Charter_Redesign]![DatePaidBox] AS Expr21, " & _
  "[Forms]![Form_Charter_Redesign]![VehicleBox] AS Expr22"
  
  DoCmd.RunSQL (strSQL)
      
      




       
  ' Base Sub Calls (Sends an email to the proper base based on Branch Combo Box)
  
  If Me.ComboBranch.Value = "Gowanda" Then
  Call send_Gowanda
  End If
  
  If Me.ComboBranch.Value = "Lancaster" Then
  Call send_Lancaster
  End If
    
  If Me.ComboBranch.Value = "Hamburg" Then
  Call send_Hamburg
  End If
  
  If Me.ComboBranch.Value = "Tonawanda" Then
  Call send_Tonawanda
  End If
  
  If Me.ComboBranch.Value = "Olean" Then
  Call send_Olean
  End If
  
  If Me.ComboBranch.Value = "Jamestown" Then
  Call send_Jamestown
  End If
  
  If IsNull(Me.ComboBranch.Value) Then
  Call send_BaseNull
  End If

End Sub


Sub send_Gowanda()


Dim EmailTo As String
Dim EmailCC As String


EmailTo = "Test@email.com"
EmailCC = "Test@email.com"



DoCmd.SendObject acReport, stDocName, "PDFFormat(*.pdf)", EmailTo, EmailCC, "", "New Gowanda Charter Submission", "A new charter has been submitted. Attached is a copy for your convenience.", True, ""


End Sub




Sub send_Lancaster()


Dim EmailTo As String
Dim EmailCC As String


EmailTo = "Test@email.com"
EmailCC = "Test@email.com"



DoCmd.SendObject acReport, stDocName, "PDFFormat(*.pdf)", EmailTo, EmailCC, "", "New Lancaster Charter Submission", "A new charter has been submitted. Attached is a copy for your convenience.", True, ""


End Sub




Sub send_Hamburg()


Dim EmailTo As String
Dim EmailCC As String


EmailTo = "Test@email.com"
EmailCC = "Test@email.com"



DoCmd.SendObject acReport, stDocName, "PDFFormat(*.pdf)", EmailTo, EmailCC, "", "New Hamburg Charter Submission", "A new charter has been submitted. Attached is a copy for your convenience.", True, ""


End Sub




Sub send_Tonawanda()


Dim EmailTo As String
Dim EmailCC As String


EmailTo = "Test@email.com"
EmailCC = "Test@email.com"



DoCmd.SendObject acReport, stDocName, "PDFFormat(*.pdf)", EmailTo, EmailCC, "", "New Tonawanda Charter Submission", "A new charter has been submitted. Attached is a copy for your convenience.", True, ""


End Sub




Sub send_Olean()


Dim EmailTo As String
Dim EmailCC As String


EmailTo = "Test@email.com"
EmailCC = "Test@email.com"



DoCmd.SendObject acReport, stDocName, "PDFFormat(*.pdf)", EmailTo, EmailCC, "", "New Olean Charter Submission", "A new charter has been submitted. Attached is a copy for your convenience.", True, ""


End Sub




Sub send_Jamestown()


Dim EmailTo As String
Dim EmailCC As String


EmailTo = "Test@email.com"
EmailCC = "Test@email.com"


DoCmd.SendObject acReport, stDocName, "PDFFormat(*.pdf)", EmailTo, EmailCC, "", "New Jamestown Charter Submission", "A new charter has been submitted. Attached is a copy for your convenience.", True, ""


End Sub




Sub send_BaseNull()


MsgBox "You must specify a Branch for this Charter!"


End Sub

[June7]

Is this an UNBOUND form?

Why do you have SaveRecord if you are using INSERT sql to create record?

And why would you open report before the INSERT?

[Nuke1096]

That was leftover from before I was using the Insert SQL code. Its currently just commented out.

Anyway, I actually found a fix to this problem by following these instructions.

http://office.microsoft.com/en-us/ac...001034563.aspx

But this way feels somewhat sloppy. Is there a way to do this right from code without having to create an additional report and two additional queries?