By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,959 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,959 IT Pros & Developers. It's quick & easy.

Runtime error 424

towsie
P: 28
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 butEnterpriseSGDUpload_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.SetWarnings (False)

Set db = CurrentDb

'Check to see if data already exists in t_sales table for current accounting month
'Set rstCheck = db.OpenRecordset("SELECT [acct_month] FROM [t_sales] WHERE [acct_month] = " & Forms!frmUploadSales.txtAccMonth & " and [upload_flag] ='U';")
'If rstCheck.RecordCount > 0 Then
' If MsgBox("This summary has already been run for the current period ( acct_month " & Forms!frmUploadSales.txtAccMonth & "). " & 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!frmUploadSales.txtAccMonth & " and upload_flag='U';"
' DoCmd.RunSQL (strSQL)
' Else
' Exit Sub
' End If
'End If

'Prompt for Enterprise SGD xls
strWorkbook = strGetOpenFileName("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.OpenRecordset("SELECT Max(t_sales.sale_number) AS Max FROM t_sales;")

If IsNull(rstCounter(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.CreateTableDef("EnterpriseSGD")
xl.Connect = "Excel 5.0;HDR=NO;IMEX=1;DATABASE=" & strWorkbook
xl.SourceTableName = "SGD$"
db.TableDefs.Append xl

Set rs = db.OpenRecordset("Select t_sales_template.product, t_sales_template.category, t_sales_template.customer, t_sales_template.origin_of_sale, t_sales_template.load_point, t_sales_template.vessel, t_sales_template.credit_period, t_sales_template.sales_terms, t_sales_template.shipping_terms, t_sales_template.contract_number, t_sales_template.vat, t_sales_template.invoice_currency, t_sales_template.autolift, t_sales_template.value_only, t_sales_template.export, t_sales_template.invoice_unit, t_sales_template.tax_unit, t_sales_template.ledger_unit, t_sales_template.contract_date " & _
"From t_sales_template;")


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(" & t_sales_template.Product & "," & t_sales_template.Category & "," & t_sales_template.Customer & "," & t_sales_template.Origin_of_sale & "," & t_sales_template.Load_point & "," & t_sales_template.Vessel & "," & t_sales_template.Credit_period & "," & t_sales_template.Sales_terms & "," & t_sales_template.Shipping_terms & "," & t_sales_template.Contract_Number & "," & t_sales_template.VAT & "," & t_sales_template.Invoice_currency & "," & t_sales_template.Autolift & "," & t_sales_template.VALUE_ONLY & "," & t_sales_template.Export & "," & t_sales_template.invoice_unit & "," & t_sales_template.Tax_unit & "," & t_sales_template.Ledger_unit & "," & t_sales_template.Contract_date & "," & intCounter & ")" & _
" where( t_sales_template.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
Sep 25 '07 #1
Share this Question
Share on Google+
15 Replies


P: 82
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 butEnterpriseSGDUpload_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:

Expand|Select|Wrap|Line Numbers
  1. 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)" & _
  2. " 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 & ")" & _
  3. " where( t_sales_template.Template_Name = ' Methane - SGD Portfolio - Enterprise Bittern ')" 
  4.  
  5.  
Sep 25 '07 #2

towsie
P: 28
Hi,

I have changed my code to



Set rs = db.OpenRecordset("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_currency as curr, autolift, value_only, export, invoice_unit, tax_unit, ledger_unit, contract_date " & _
"From t_sales_template;")


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( t_sales_template.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
Sep 26 '07 #3

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

Expand|Select|Wrap|Line Numbers
  1. If rs.RecordCount = 0 Then
  2. Else
  3.     rs.MoveLast
  4.     rs.MoveFirst
  5.     Do While Not rs.EOF
  6.             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)" & _
  7.             " 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 & ")" & _
  8.             " where(template_name  = ' Methane - SGD Portfolio - Enterprise Bittern ')"
  9.             DoCmd.RunSQL (strSQL)
  10.             DoCmd.RunSQL ("update t_sales set upload_flag = 'U'")
  11.             intCounter = intCounter + 1
  12.     rs.MoveNext
  13.     Loop
  14. End If
Sep 26 '07 #4

Expert 5K+
P: 8,434
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.
Sep 26 '07 #5

towsie
P: 28
I have already tried that but there was no success.
Sep 27 '07 #6

QVeen72
Expert 100+
P: 1,445
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
Sep 27 '07 #7

towsie
P: 28
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.
Sep 27 '07 #8

QVeen72
Expert 100+
P: 1,445
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
Sep 27 '07 #9

towsie
P: 28
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.
Sep 27 '07 #10

QVeen72
Expert 100+
P: 1,445
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
Sep 27 '07 #11

Expert 5K+
P: 8,434
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.
Sep 27 '07 #12

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

Expand|Select|Wrap|Line Numbers
  1. If rs.RecordCount = 0 Then
  2. Else
  3.     rs.MoveLast
  4.     rs.MoveFirst
  5.     Do While Not rs.EOF
  6.             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)" & _
  7.             " 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 & ")" & _
  8.             " where(template_name  = ' Methane - SGD Portfolio - Enterprise Bittern ')"
  9.             DoCmd.RunSQL (strSQL)
  10.             DoCmd.RunSQL ("update t_sales set upload_flag = 'U'")
  11.             intCounter = intCounter + 1
  12.     rs.MoveNext
  13.     Loop
  14. End If
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...
Sep 29 '07 #13

P: 82
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.
Sep 29 '07 #14

Expert 5K+
P: 8,434
I think the PROBLEM here is the WHERE clause of this query. INSERT statement DOES NOT NEED a WHERE clause.
Ah! Good point.
Sep 30 '07 #15

towsie
P: 28
Hi all,

The error has been resolved thanks to your help, it is greatly appreciated.

Cheers

Towsie
Oct 1 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.