Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: VBA code for retreiving Month and Year.


    I have a table storing the user selected year and month. Table structure is as follows:

    TableName: TblGlobal
    Fields: FocusMonth, FocusYear

    TblGlobal has only one record for FocusMonth and FocusYear, which stores an ID each of two other tables, viz. TblMonths and TblYears, having the following strucutres:

    TableName: TblMonths
    Fields: MonthID, MonthName

    TableName: TblYears
    Fields: YearID, YearName

    The TblGlobal record is set by a combo box (cmbYear) in a form. For e.g. cmbYear has ControlSource as FocusYear and RowSource picking up from TblYears.

    In VBA, I would like to have the MonthName and YearName (not the IDs) for the chosen FocusMonth and FocusYear stored on a string.

    Could someone help with the VBA code for this?


    P.S. I am pretty weak on commands to open databases, setting recordset etc. in VBA. So the declarations before setting the string will be helpful.

  2. #2
    Join Date
    Nov 2003
    I'm not exactly sure of what you are realy trying to do but, I'm going to take a stab at it anyway. Try this:

    In the OnClick event of the cmbYear ComboBox
    Dim MonthYearStrg As String  'String to hold the FocusMonth and FocusYear.
    If Not IsNull(Me.cmbYear) Then
       If Not IsNull(DLookUp("[FocusMonth]", "TblGlobal", "[FocusYear] = '" & Me.cmbYear & "'")) Then
           MonthYearStrg = DLookUp("[FocusMonth]", "TblGlobal", "[FocusYear] = '" & Me.cmbYear & "'") & ",  " & Me.cmbYear
       End If
       ' Just to show you have it....(you don't need this)
       MsgBox "My Month/Year String =  " & MonthYearStrg
    End If
    Hope this is what you're looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts