Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 16th, 2004, 12:48 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default Recordsets

I have a form which has two text boxes, industry and sector that are bound to a table named industry_sector which has two columns industry and sector.
An example of row in the table would be:
Industry Sector
BPH TOWING Trucks
BPH TOWING Other

After the user enters a industry and sector in the respective text boxes, I want to check to see if the industry exists, and if not, insert a row with the new industry and the sector named other.

The following code is what I am using and it returns an error after the Set rs... line, stating "Too few parameters. Expected 1." Disregard wrapping. That's all right in my window.

Dim sql As String
    Dim rs As Recordset
    Dim db As Database
    Set db = CurrentDb
    sql = "Select count(*) from industry_sector WHERE forms! add_frm_industrysector!txtNewIndustry = industry_sector!industry"
    Set rs = db.OpenRecordset(sql, DB_OPEN_DYNASET)

    If rs.RecordCount = 0 Then
    DoCmd.RunSQL "INSERT INTO Industry_sector(industry,sector) Values(forms!add_frm_industrysector!txtNewIndustry , 'Other')"
    Else
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    End If

Toughts? BPH THX>

 
Old February 16th, 2004, 01:42 PM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi there,
   The problem is with your SQL statements you are mixing literal and non literal characters. You have to separate the form from the string so the value is placed in the string. You will have to fix both of your SQL statements, here is one fixed below.

strSQL = "INSERT INTO Industry_sector(industry,sector) Values(" & Forms!add_frm_industrysector!txtNewIndustry & ",'Other')"

Also, use the execute method of the currentdb object when running action queries...it will outperform the runSQL every time.

Currentdb.Execute strSQL, dbFailonError

HTH,
Mike
 
Old February 16th, 2004, 02:15 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thanks for the help Mike.

I still get the error. Not sure why. Will keep trying.

bph

 
Old February 16th, 2004, 02:18 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: Houston, Texas, USA.
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have you tried stepping through the code to see where it bombs? Does your table (or query) Industry_sector have a required field that you aren't populating when inserting your new record?

Beth M
 
Old February 16th, 2004, 02:40 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

I'm pretty certain the dao parts are good, because I got some help from a solid source on that.

When I step through, I go past the first sql statement, then the set statement and that's when the error pops up.
The two fields in the table are indexed as composite primary key, although not unique.


Quote:
quote:Originally posted by BethMoffitt
 Have you tried stepping through the code to see where it bombs? Does your table (or query) Industry_sector have a required field that you aren't populating when inserting your new record?

Beth M
 
Old February 16th, 2004, 04:55 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Mike,
I reworked the first SQL statement to include both columns of the table as they are composite key. I get an object required error, which is an improvement I suppose. Does the syntax look correct?

sql = "SELECT count(*) FROM industry_sector WHERE industry_sector![industry] = " & Forms!add_frm_industrysector!txtnewindustry And industry_sector!Sector = " & 'Other'"

Quote:
quote:Originally posted by Dataman
 Hi there,
   The problem is with your SQL statements you are mixing literal and non literal characters. You have to separate the form from the string so the value is placed in the string. You will have to fix both of your SQL statements, here is one fixed below.

strSQL = "INSERT INTO Industry_sector(industry,sector) Values(" & Forms!add_frm_industrysector!txtNewIndustry & ",'Other')"

Also, use the execute method of the currentdb object when running action queries...it will outperform the runSQL every time.

Currentdb.Execute strSQL, dbFailonError

HTH,
Mike

 
Old February 16th, 2004, 05:02 PM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this SQL Statement

"SELECT count(*) FROM industry_sector WHERE industry = " & Forms!add_frm_industrysector!txtnewindustry & " And Sector = " & 'Other'"

Since you only have 1 table you don't need to qualify the fields and you were missing the --- & " ---- before the And.

HTH,
Mike
 
Old February 16th, 2004, 05:09 PM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this one...

"SELECT Count(*) FROM industry_sector WHERE industry = '" & Forms!add_frm_industrysector!txtnewindustry & "' And Sector='Other'"

I forgot the single quotes around the industry field.

set a break point int the code and then dump the SQL to the immdiate window to see if it looks correct.
Mike
 
Old February 16th, 2004, 05:21 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Compiler doesn't like it, but it is more readable. This is so close.

Quote:
quote:Originally posted by Dataman
 Try this SQL Statement

"SELECT count(*) FROM industry_sector WHERE industry = " & Forms!add_frm_industrysector!txtnewindustry & " And Sector = " & 'Other'"

Since you only have 1 table you don't need to qualify the fields and you were missing the --- & " ---- before the And.

HTH,
Mike
 
Old February 16th, 2004, 05:27 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: Houston, Texas, USA.
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

After your dim statements,
dim NewInd as string

NewInd = forms!add_frm_industrysector!txtNewIndustry

sql = "Select count(*) from industry_sector WHERE industry ='" & NewInd & "'"

Regards,

Beth M




Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordsets JezLisle Access VBA 11 July 17th, 2007 03:47 AM
Help with Recordsets voskoue Access VBA 1 January 23rd, 2007 08:36 AM
Assigning Recordsets mrjeret BOOK: Access 2003 VBA Programmer's Reference 0 July 6th, 2006 09:39 AM
Cloned Recordsets taraj Access VBA 4 June 13th, 2006 08:28 AM
Need help with recordsets chacquard Access VBA 5 June 21st, 2004 11:58 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.