[AccessD] Need Some help

William Benson (VBACreations.Com) vbacreations at gmail.com
Mon May 9 12:22:56 CDT 2011


Steve, have you resolved? It's been awhile.

I think your problem is two-fold. You must change the SQL assignment as I
showed you to be syntactically correct... and this line:

	  DoCmd.OpenQuery strSql

Should be 

	  DoCmd.OpenQuery cstrQryName

But ... I am not really sure what you are trying to accomplish, it seems
pretty convoluted even for my taste, and I have an advanced degree in
Convolution.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Turner
Sent: Friday, May 06, 2011 6:01 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Need Some help

Hey Guys,

 Need some help here. I've tried everything I can find but I don't
understand some of it. I have a table with project year, project Month and
the Month Start Date and Month End Date's for a number of years. I am trying
to read this table MonthlDates for the ProjYear And ProjMonth to get the
Month Start and End Days. I have a query built that pulls this data and a
query table appears in Access 2010 with the data in it as a one line record.
How do I read this data into a variable MSD and MED so I can Update another
table called Variables we use to run reports from.? Code works till I get to
the how to read query for MSD and MED.

 

Dim rst As Recordset

    Dim strSql As String, strParm As String

    Dim strMessage As String

    Dim intCommand As Integer

    Dim qdf As QueryDef

    Const cstrQryName As String = "Project Month And Year"

   

'sets the project year and month

   

    PY = ProjYear

    PM = projMonth

    

 'Update the variables table

 

    Set rstVariables = db.OpenRecordset("Variables")

    With rstVariables

        .Edit

        !PY = PY 'ProjYear

        !PM = PM 'projMonth

        .Update

    End With

    

'Query MonthlyDates table for Month Start and End

 

     

  Set qdf = db.QueryDefs(cstrQryName)

   With DoCmd

       .SetWarnings False

       .OpenQuery (cstrQryName), , acEdit

       .SetWarnings True

    End With

    

 'how to read query for MSD and MED???

 

  Set rstEnd = db.OpenRecordset(cstrQryName)

   strSql = ("select *,(cstrQryName)") '<--MS ERROR can't find
(cstrQryName)

  DoCmd.OpenQuery strSql

  

 ' want to fill in these variables to update table Variables

   

  MSD = MonthStartDate

  MED = MonthEndDate

  

 

Steve A. Turner
Controller
Mid-South Engineering Co. Inc
P.O. Box 1399
Hot Springs, AR 71902
E-Mail: sturner at mseco.com and saturner at mseco.com
Phone: (501)321-2276
Fax:     (501)321-4750
Cell:     (501)282-7751

 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list