INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VB code not finding Table

VB code not finding Table

(OP)
I am trying to create a Recordset using a SQL statement. I've done it before but, this time, it seems the SQL statement isn't finding the Table in the db so, it has an empty value for the Recordset.

Help please? What am I missing?

CODE -->

Private Sub cmdAddToReq_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MPL As AccessObject
Dim strSQL As String
Dim strTower As String
Dim strProg As String
Dim strProj As String
Dim strProjV As String
Dim strOM As String
Dim strFund As String
Dim strRecNo As String
     
  strSQL = "SELECT MPL.Tower, MPL.Program_Name, MPL.Project_Name, MPL.Project_Version, MPL.Offering_Manager, MPL.Funding_Source, MPL.Record_No FROM MPL WHERE (((MPL.Record_No)=[Forms]![Req_Add_Project]![Record_No]));"

  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  rst.MoveFirst
  
  strTower = rst![Tower]
  strProg = rst![Program_Name]
  strProj = rst![Project_Name]
  strProjV = rst![Project_Version]
  strOM = rst![Offering_Manager]
  strFund = rst![Funding_Source]
  strRecNo = rst![Record_No]
  
  Forms![Requirements Management]![Tower] = strTower
  Forms![Requirements Management]![Program_Name] = strProg
  Forms![Requirements Management]![Project_Name] = strProj
  Forms![Requirements Management]![roject_Version] = strProjV
  Forms![Requirements Management]![Offering_Manager] = strOM
  Forms![Requirements Management]![Funding_Source] = strFund
  Forms![Requirements Management]![Record_No] = strRecNo
  
  
  rst.Close
   
End Sub 

RE: VB code not finding Table

What do you mean by: "the SQL statement isn't finding the Table in the db "?
Do you get an error? Something like "Table or view does not exsist"?

And how do you know that: "it has an empty value for the Recordset. "?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: VB code not finding Table

(OP)
Hi Andy,

I get a "Too few parameters. Expecting 1." error. Then, when I debug, I get:

CODE -->

Set rst = db.OpenRecordset(strSQL) 

and when I hover over: Set rst , I get: rst = Nothing

RE: VB code not finding Table

I'm with Andy regarding needing more clarification.

I would use this code if Record_No is numeric:

CODE --> vba

strSQL = "SELECT Tower, Program_Name, Project_Name, Project_Version, Offering_Manager, Funding_Source, Record_No " & _
    "FROM MPL " & _
    "WHERE Record_No= " & [Forms]![Req_Add_Project]![Record_No]
  Debug.Print strSQL 

I would use this code if Record_No is text:

CODE --> vba

strSQL = "SELECT Tower, Program_Name, Project_Name, Project_Version, Offering_Manager, Funding_Source, Record_No " & _
    "FROM MPL " & _
    "WHERE Record_No= """ & [Forms]![Req_Add_Project]![Record_No] & """"
  Debug.Print strSQL 

Duane
Hook'D on Access
MS Access MVP

RE: VB code not finding Table

I would do this:

CODE

Dim strRecNo As String
'Dim intRecNo As Integer or Long if it is a Number

strRecNo = [Forms]![Req_Add_Project]![Record_No]

strSQL = "SELECT MPL.* FROM MPL WHERE Record_No = '" & strRecNo & "'"
... 

A lot easier to Debug when you step thru your code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: VB code not finding Table

(OP)
Thanks Duane,

The number one worked.

I am trying to run a query off of one form to populate fields on another and I'm having trouble with how to do it.

The code works now but strangely the Tower , Project_Version, and Funding_Source fileds fill in right away but, after about 30 seconds or so of waiting the other ones finally fill in. I had this working before and it did the same thing. That's why I tried this approach thinking it would work better. IDK why there is a delay though.

Thanks though!

RE: VB code not finding Table

It sounds like Record_No is numeric so I wouldn't assign the value to a "String".

Also, is this correct "[roject_Version]"?

What are you using this for: Dim MPL As AccessObject?

Duane
Hook'D on Access
MS Access MVP

RE: VB code not finding Table

(OP)
Thanks Duane,

I caught the [Project_Version] mistake. But, I guess the whole problem was the Record_No As a String issue.

As far as the Dim MPL As AccessObject... I thought that I had to declare the MPL table. Since the SQL code wasn't returning anything. I kept changing things around and at one point I got an error that said something like cannot find MPL.

Anyway, thanks for your help.

I got around the slow population of the fields on the target form by just using a DoCmd.Close ,acForm thing and then reopening it and it works great. I'm sure there's probably a better way... I still have a lot to learn with VB.

RE: VB code not finding Table

I personally don’t like to juggle the data around, so I would do this:

CODE

Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)

If rst.BOF <> rst.EOF Then
  rst.MoveFirst
  
  With Forms![Requirements Management]
     ![Tower] = rst![Tower]
     ![Program_Name] = rst![Program_Name]
     ![Project_Name] = rst![Project_Name]
     ![roject_Version] = rst![Project_Version]
     ![Offering_Manager] = rst![Offering_Manager]
     ![Funding_Source] = rst![Funding_Source]
     ![Record_No] = rst![Record_No]
  End With
End If
  
  rst.Close 

and eliminate all declarations for those variables.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: VB code not finding Table

(OP)
Cool! See... I never would have known how to do that. Thanks for the tip!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close