VB code not finding Table 2

[cdgeer]

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?

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

 

[Andrzejek]

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.

 

[cdgeer]

Hi Andy,

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

[highlight #EDD400]Set rst = db.OpenRecordset(strSQL)[/highlight]

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

 

[dhookom]

I'm with Andy regarding needing more clarification.

I would use this code if Record_No is numeric:

  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:

  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

 

[Andrzejek]

I would do this:

Dim strRecNo As String[green]
'Dim intRecNo As Integer or Long if it is a Number[/green]

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.

 

[cdgeer]

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!

 

[dhookom]

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

 

[cdgeer]

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.

 

[Andrzejek]

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

  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.

 

[cdgeer]

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