Runtime error 424

[towsie]

Hi,

I am having a problem with code below, I am getting a runtime error 424: Object Required.

The section of code in bold is where the error occurs.

Any help would be greatly appreciated.



'Allows user to upload Enterprise SGD Sales from Excel Spreadsheet
Private Sub butEnterpriseSG DUpload_Click()

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strWorkbook As String
Dim rstCounter As Recordset
Dim rstCheck As Recordset
Dim intCounter As Long

DoCmd.SetWarnin gs (False)

Set db = CurrentDb

'Check to see if data already exists in t_sales table for current accounting month
'Set rstCheck = db.OpenRecordse t("SELECT [acct_month] FROM [t_sales] WHERE [acct_month] = " & Forms!frmUpload Sales.txtAccMon th & " and [upload_flag] ='U';")
'If rstCheck.Record Count > 0 Then
' If MsgBox("This summary has already been run for the current period ( acct_month " & Forms!frmUpload Sales.txtAccMon th & "). " & Chr(13) & _
' " Do you want to overwrite the data for this period?", vbOKCancel, "Warning") = vbOK Then
' strSQL = "delete from t_sales where acct_month = " & Forms!frmUpload Sales.txtAccMon th & " and upload_flag='U' ;"
' DoCmd.RunSQL (strSQL)
' Else
' Exit Sub
' End If
'End If

'Prompt for Enterprise SGD xls
strWorkbook = strGetOpenFileN ame("Excel Files (*.xls)" & Chr(0) & "*.xls" & Chr(0), "H:\", , "Select Enterprise SGD workbook")

If strWorkbook = "False" Then
Exit Sub
End If

DoCmd.Hourglass (True)
Set rstCounter = db.OpenRecordse t("SELECT Max(t_sales.sal e_number) AS Max FROM t_sales;")

If IsNull(rstCount er(0)) Then
intCounter = 1
Else
intCounter = rstCounter(0) + 1
End If

'Get Enterprise SGD sales from Excel spreadsheet and set upload flag = U

Set xl = db.CreateTableD ef("EnterpriseS GD")
xl.Connect = "Excel 5.0;HDR=NO;IMEX =1;DATABASE=" & strWorkbook
xl.SourceTableN ame = "SGD$"
db.TableDefs.Ap pend xl

Set rs = db.OpenRecordse t("Select t_sales_templat e.product, t_sales_templat e.category, t_sales_templat e.customer, t_sales_templat e.origin_of_sal e, t_sales_templat e.load_point, t_sales_templat e.vessel, t_sales_templat e.credit_period , t_sales_templat e.sales_terms, t_sales_templat e.shipping_term s, t_sales_templat e.contract_numb er, t_sales_templat e.vat, t_sales_templat e.invoice_curre ncy, t_sales_templat e.autolift, t_sales_templat e.value_only, t_sales_templat e.export, t_sales_templat e.invoice_unit, t_sales_templat e.tax_unit, t_sales_templat e.ledger_unit, t_sales_templat e.contract_date " & _
"From t_sales_templat e;")


If rs.RecordCount = 0 Then
Else
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strSQL = "Insert into t_sales (product, category, customer, origin_of_sale, load_point, vessel, credit_period, sales_terms, shipping_terms, contract_no, vat, invoice_currenc y, autolift, value_only, export_ind, invoice_unit, tax_unit, ledger_unit, contract_date, sale_number)" & _
" values(" & t_sales_templat e.Product & "," & t_sales_templat e.Category & "," & t_sales_templat e.Customer & "," & t_sales_templat e.Origin_of_sal e & "," & t_sales_templat e.Load_point & "," & t_sales_templat e.Vessel & "," & t_sales_templat e.Credit_period & "," & t_sales_templat e.Sales_terms & "," & t_sales_templat e.Shipping_term s & "," & t_sales_templat e.Contract_Numb er & "," & t_sales_templat e.VAT & "," & t_sales_templat e.Invoice_curre ncy & "," & t_sales_templat e.Autolift & "," & t_sales_templat e.VALUE_ONLY & "," & t_sales_templat e.Export & "," & t_sales_templat e.invoice_unit & "," & t_sales_templat e.Tax_unit & "," & t_sales_templat e.Ledger_unit & "," & t_sales_templat e.Contract_date & "," & intCounter & ")" & _
" where( t_sales_templat e.Template_Name = ' Methane - SGD Portfolio - Enterprise Bittern ')" DoCmd.RunSQL (strSQL)
DoCmd.RunSQL ("update t_sales set upload_flag= 'U'")
intCounter = intCounter + 1
rs.MoveNext
Loop
End If

End Sub

[fplesco]

Hi,

I am having a problem with code below, I am getting a runtime error 424: Object Required.

The section of code in bold is where the error occurs.

Any help would be greatly appreciated.



'Allows user to upload Enterprise SGD Sales from Excel Spreadsheet
Private Sub butEnterpriseSG DUpload_Click()

Hi Towsie -

As I see your code, it seemed to me that these values you want to insert in T_Sales table is fetched coming from another table and is available in RS recordset.

If that's really the case.

add rs! in the field, see below:

strSQL = "Insert into t_sales (product, category, customer, origin_of_sale, load_point, vessel, credit_period, sales_terms, shipping_terms, contract_no, vat, invoice_currency, autolift, value_only, export_ind, invoice_unit, tax_unit, ledger_unit, contract_date, sale_number)" & _
" values(" & rs!t_sales_template.Product & "," & rs!t_sales_template.Category & "," & rs!t_sales_template.Customer & "," & rs!t_sales_template.Origin_of_sale & "," & rs!t_sales_template.Load_point & "," & rs!t_sales_template.Vessel & "," & rs!t_sales_template.Credit_period & "," & rs!t_sales_template.Sales_terms & "," & rs!t_sales_template.Shipping_terms & "," & rs!t_sales_template.Contract_Number & "," & rs!t_sales_template.VAT & "," & rs!t_sales_template.Invoice_currency & "," & rs!t_sales_template.Autolift & "," & rs!t_sales_template.VALUE_ONLY & "," & rs!t_sales_template.Export & "," & rs!t_sales_template.invoice_unit & "," & rs!t_sales_template.Tax_unit & "," & rs!t_sales_template.Ledger_unit & "," & rs!t_sales_template.Contract_date & "," & intCounter & ")" & _
" where( t_sales_template.Template_Name = ' Methane - SGD Portfolio - Enterprise Bittern ')" 
 
 

[towsie]

Hi,

I have changed my code to



Set rs = db.OpenRecordse t("Select product, category, customer, origin_of_sale as origin, load_point as lp, vessel, credit_period as credit, sales_terms, shipping_terms, contract_number as contract_no, vat, invoice_currenc y as curr, autolift, value_only, export, invoice_unit, tax_unit, ledger_unit, contract_date " & _
"From t_sales_templat e;")


If rs.RecordCount = 0 Then
Else
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strSQL = "Insert into t_sales (product, category, customer, origin_of_sale, load_point, vessel, credit_period, sales_terms, shipping_terms, contract_no, vat, invoice_currenc y, autolift, value_only, export_ind, invoice_unit, tax_unit, ledger_unit, contract_date, sale_number)" & _
" values(" & rs("product") & "," & rs("category") & "," & rs("customer") & "," & rs("origin") & "," & rs("lp") & "," & rs("vessel") & "," & rs("credit") & "," & rs("sales_terms ") & "," & rs("shipping_te rms") & "," & rs("contract_no ") & "," & rs("vat") & "," & rs("curr") & "," & rs("autolift") & "," & rs("value_only" ) & "," & rs("export") & "," & rs("invoice_uni t") & "," & rs("tax_unit") & "," & rs("ledger_unit ") & "," & rs("contract_da te") & "," & intCounter & ")" & _
" where( t_sales_templat e.Template_Name = ' Methane - SGD Portfolio - Enterprise Bittern ')"
DoCmd.RunSQL (strSQL)
DoCmd.RunSQL ("update t_sales set upload_flag= 'U'")
intCounter = intCounter + 1
rs.MoveNext
Loop
End If

End Sub

I now error an error

runtime error 3075: Syntax error (missing operator) in query expression 'CRUDE OIL'

I have made the line that errors bold.

Thanks

Towsie

[towsie]

I have now solved this error, but now I am getting runtime error 3137: Missing semicolon (;) at end of the SQL statement.

If rs.RecordCount = 0 Then
Else
    rs.MoveLast
    rs.MoveFirst
    Do While Not rs.EOF
            strSQL = "Insert into t_sales (product, category, customer, origin_of_sale, load_point, vessel, credit_period, sales_terms, shipping_terms, contract_no, vat, invoice_currency, autolift, value_only, export_ind, invoice_unit, tax_unit, ledger_unit, contract_date, sale_number)" & _
            " values('" & rs("product") & "','" & rs("category") & "','" & rs("customer") & "','" & rs("origin") & "','" & rs("lp") & "','" & rs("vessel") & "','" & rs("credit") & "','" & rs("sales_terms") & "','" & rs("shipping_terms") & "','" & rs("contract_no") & "','" & rs("vat") & "','" & rs("curr") & "','" & rs("autolift") & "','" & rs("value_only") & "','" & rs("export") & "','" & rs("invoice_unit") & "','" & rs("tax_unit") & "','" & rs("ledger_unit") & "','" & rs("contract_date") & "'," & intCounter & ")" & _
            " where(template_name  = ' Methane - SGD Portfolio - Enterprise Bittern ')"
            DoCmd.RunSQL (strSQL)
            DoCmd.RunSQL ("update t_sales set upload_flag = 'U'")
            intCounter = intCounter + 1
    rs.MoveNext
    Loop
End If

[Killer42]

I have now solved this error, but now I am getting runtime error 3137: Missing semicolon (;) at end of the SQL statement.

So, put a semicolon on the end of your SQL statement - problem solved.

[towsie]

I have already tried that but there was no success.

[QVeen72]

I have now solved this error, but now I am getting runtime error 3137: Missing semicolon (;) ...

Hi,
OK, just a few points I noted:
* Why not Open 2 Recordsets and use "AddNew" method, that way you can trap errors better.
* contract_date.. . wrap with "#" not single quote.
* All the fields you have wrapped with single quote, Numeric/Currency values should be without quotes.
* What if any field value of the "rs" object is "Null"?

Regards
Veena

[towsie]

Hi,

None of fields are null, I have removed the quotes from the numeric fields and and added the # for contract_date but I receive the same error.

[QVeen72]

Hi,

Why do you need Where condition here?
where(template_ name = ' Methane - SGD Portfolio - Enterprise Bittern ')"

What is template_name ?
Is it a field name (from rs) or Control name in the form?


Regards
Veena

[towsie]

Hi,

The where condition is used to make sure that the correct data is input for each record in the recordset. I am just using that template at the moment to make sure that the correct data is being input into the table.