• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

MS Access Bound Objects.

T#1 - Employee Name
T#2 - Month and Month # (Jan and 1, Feb and 2, etc..... Dec & 12)
T#3 - Calendar - Year (2010-2020)
T#3 - Shortfall - Employee Name, Month, Month#, Year, & Amount

Form #1 - Employee Name, Month, Year & Amount

My issues is I want the Month# to fill in based upon the Month selected in the form.  So when someone is in the form and they select Month, the coinciding Month# will fill in T#2

I'm a Newbie will need extra guidance.
Michael Franz
Michael Franz
1 Solution
In the before update event of MonthName, execute code to set value of month
Select Case MonthName
Case "January"
    MonthNumber = 1
Case "February"
    MonthNumber = 2

Case Else
   msgbox("Invalid month entered. Click OK to enter a valid month.",vbyes)
   Cancel = true
End Select
your field =month(month name)
I'm a Newbie will need extra guidance
Upload a sample database of what you have done and explain what went wrong and what you expect.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

if your month field is a dropdown? (e.g. cboMonth), and your month# field is a textbox (e.g. txtMonthNum) then
on the change event of the dropdown add the following code

Private Sub cboMonth_Change()
End Sub
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So when someone is in the form and they select Month, the coinciding Month# will fill in T#2
You should not store data in this manner. You should instead simply store the Date, and then use the corresponding VBA and SQL functions to format that Date as you want.

For example, to show the Month as an Integer:


So if YourDateField contains the value "01-01-2016", that function would return a value of 1. You could do this on a Textbox in a Form or Report, in a Query, etc.
Michael FranzCFOAuthor Commented:
Thank you very much

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now