Wrox Programmer Forums
|
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, 05:28 PM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 17th, 2004, 10:09 AM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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
 
Old February 17th, 2004, 11:59 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...
 
Old February 17th, 2004, 12:05 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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...
 
Old February 17th, 2004, 01:43 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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.

 
Old February 17th, 2004, 02:42 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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!


 
Old February 17th, 2004, 03:02 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 17th, 2004, 03:19 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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

That change is a good idea. THX!






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.