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>

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

Thanks for the help Mike.

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

bph

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

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.

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'"

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

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

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

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