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:

[CODE=vb]
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!t_sales_temp late.Product & "," & rs!t_sales_temp late.Category & "," & rs!t_sales_temp late.Customer & "," & rs!t_sales_temp late.Origin_of_ sale & "," & rs!t_sales_temp late.Load_point & "," & rs!t_sales_temp late.Vessel & "," & rs!t_sales_temp late.Credit_per iod & "," & rs!t_sales_temp late.Sales_term s & "," & rs!t_sales_temp late.Shipping_t erms & "," & rs!t_sales_temp late.Contract_N umber & "," & rs!t_sales_temp late.VAT & "," & rs!t_sales_temp late.Invoice_cu rrency & "," & rs!t_sales_temp late.Autolift & "," & rs!t_sales_temp late.VALUE_ONLY & "," & rs!t_sales_temp late.Export & "," & rs!t_sales_temp late.invoice_un it & "," & rs!t_sales_temp late.Tax_unit & "," & rs!t_sales_temp late.Ledger_uni t & "," & rs!t_sales_temp late.Contract_d ate & "," & intCounter & ")" & _
" where( t_sales_templat e.Template_Name = ' Methane - SGD Portfolio - Enterprise Bittern ')"

[/CODE]

[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.

[CODE=vb]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(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[/CODE]

[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.

[QVeen72]

Hi,

So template_name is the variable name in form? Then
you cannot use it in SQL statement, you use an external If condition:

If template_name = "whatever you want to check for" Then
' execute your sql statement here Without where condition
End If

Regards
Veena

[Killer42]

Can you please interrupt at the RunSQL and paste here a copy of strSQL ?

Also, you didn't tell us which of the two RunSQL methods is returning the error.

[fplesco]

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

[CODE=vb]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(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[/CODE]

Hi All -

I think the PROBLEM here is the WHERE clause of this query. INSERT statement DOES NOT NEED a WHERE clause....

If you intend to put WHERE clause to avoid duplicate record, then this way is not PERMITTED...

Search first for the possible duplicate of the record, if yields none, issue an INSERT command...

Hope this will make sense to you...

[fplesco]

Hi,

So template_name is the variable name in form? Then
you cannot use it in SQL statement, you use an external If condition:

If template_name = "whatever you want to check for" Then
' execute your sql statement here Without where condition
End If

Regards
Veena

Hi QVeen72 -

I haven't read up on this message when I posted my reply.

You are right, same thought in here.

[Killer42]

I think the PROBLEM here is the WHERE clause of this query. INSERT statement DOES NOT NEED a WHERE clause.

Ah! Good point.