Recordsets

Dataman · February 16th, 2004, 05:28 PM

I posted another follow up just a few minutes ago...I forgot the quotes around the first variable.

Remember that if you are passing in text to a SQL statement, you must use quotes and single quotes are the easiest way to go. If you need to embed quotes in quotes thats another story, but the VBA help file in Access does a pretty good job explaining the process.

Mike

bph · February 17th, 2004, 10:09 AM

Thank you. I was reading some VB-Access stuff last night and I came across a phrase which said for Access2000 users should preface any recordset references with DAO. I'm using the DAO 3.6 Object Library.
Getting type mismatch error.

Quote:

quote:Originally posted by BethMoffitt
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

BethMoffitt · February 17th, 2004, 11:59 AM

That is correct. And this would also be applicable for XP as the default language for 2000 and XP is ADO, not DAO. So you would have

Dim db as DAO.Database
Dim rs as DAO.Database

and so on...

bph · February 17th, 2004, 12:05 PM

Thanks. I'll make the changes and see what that brings.

Quote:

quote:Originally posted by BethMoffitt
That is correct. And this would also be applicable for XP as the default language for 2000 and XP is ADO, not DAO. So you would have

Dim db as DAO.Database
Dim rs as DAO.Database

and so on...

bph · February 17th, 2004, 01:43 PM

Beth,
THANKs!
That DAO change worked. I'm so close. I step through further than I did before. I get prompted for a parameter value. It has my industry name and whatever I enter as parameter becomes a new industry and the sector is correct as other. So I fix my sql statement and I'm golden.

DATAMAN, Thanks to you also. I can't wait for the day that I'm able to help someone out.

bph · February 17th, 2004, 02:42 PM

It works! I need to keep my DAO's away from my ADO's and learn the use of variables behind forms.

  Dim Other As String
    Dim NewInd As String
    Dim sql As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Other = "Other"
    NewInd = Forms!add_frm_industrysector!txtnewindustry
    sql = "Select * from industry_sector WHERE industry ='" & NewInd & "'"
    Set rs = db.OpenRecordset(sql, dbopendynaset)
    If rs.RecordCount = 0 Then
    DoCmd.RunSQL "INSERT INTO Industry_sector(industry,sector) Values('" & NewInd & "', '" & Other & "' );"

I know I'm like that little sliver you can't get out of your finger and it just bugs the heck out of ya. But that sliver has a smile a mile wide right now. Ya'll are the best!

BethMoffitt · February 17th, 2004, 03:02 PM

That's cool :) Not meaning to rain on your parade, I would change your variable from Other to NewSector just for clarification purposes and to avoid confusion from:

Code:

Other = "Other"

So, change to:

Code:

NewSector = "Other"

Then your statement would be:

Code:

DoCmd.RunSQL "INSERT INTO Industry_sector(industry,sector) Values('" & NewInd & "', '" & NewSector & "' );"

Beth M
PS A sliver isn't necessarily a bad thing. It means you've been crafting something by hand :D

bph · February 17th, 2004, 03:19 PM

Don't mind the rain. It helps things grow.:)

That change is a good idea. THX!