Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Annual deletion / creation of tables... (2003)

    Good Mornin'

    Alright I have some tables here, let's call 'em normal Address tables of the format

    tblAdr
    Name
    Address
    YankeeTankee
    WobbleDobble

    Those tables represent participants of a huge conference I am organizing and those participants of course change every year - now first solution:
    1. I could do is to flag all of the data with a date-field
    - But: I don't want an exact date, just the year (let's say 2004,2005 etc.) and a date field in Access always wants full dates (at least what I got)

    2. my preferred solution (includes some coding I bet) rename old tables and create new tables of the same origin, meaning
    - old talbe tblAdr gets renamed (or is named) tblAdr2003 and the new table gets created (with the click of a button and entering of a year) tblAdr2004

    2nd SOlution is definately my preferred one. I'd love to have a listbox on startup of the database, where you have to create a new year and can choose the old years to look at that data...

    Please help me outta here guys, I know it's a rather complex one, but I hope you can help me somehow.

    'nuff said
    Dennis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Annual deletion / creation of tables... (2003)

    Hello Dennis,

    Welcome to Woody's Lounge.

    Unless the total number of participants runs in the hundreds of thousands, your first solution would actually be easier. Instead of a date field, you could have a number (integer or long integer) field, with default value =Year(Date()).

    Renaming tables would mean that you would also have to do extra work to handle the renaming in queries, forms and reports. If you have one adress table with a year field, you can easily filter it to display records for a specific year or range of years. You can populate a list box or combo box on a startup form with a list of years that are present in the table, so that the user can select a year to view.

    If you have more questions, you're welcome to post them.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual deletion / creation of tables... (2003

    Thanks for your response Hans!

    That Year(Date()) thingy did the trick - in, let's say, general., but there's some problems with that.

    First off: Thanks for your advice, renaming really sucks and it's just about 400 participants each year so that shouldn't be so hard to control... but now let's talk about the problem:

    I was talking about annual deletion because the conference is once a year, BUT data gets entered even the year before, meaning that for 2005's conference there'd be a 2004 in that date-limiter field - which leads to problems.
    My preferred solution (again I'm using that preferred, i'M really picky as you might notice) would be to have a Dropdown list in a popup window on startup of the database, where you can select the year and it sets that year as a default value for that date-limiter field.

    Again would you be so kind and help me out of my misery [img]/forums/images/smilies/wink.gif[/img]
    thx in advance

    Dennis

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Annual deletion / creation of tables... (2003

    Hi Dennis,

    I would put the combo box (dropdown list) on the address form itself; that way the user can easily switch. In the After Update event of the combo box, I would filter the form to display only records for the selected year, and set the Default Value for the year field to the selected year.

    I have attached a small demo database (zipped) to illustrate this. The After Update event for the combo box looks like this:

    <code>Private Sub cboSelectYear_AfterUpdate()</code>
    <code> If IsNull(Me.cboSelectYear) Then</code>
    <code> Me.Filter = ""</code>
    <code> Me.FilterOn = False</code>
    <code> Me.RegistrationYear.DefaultValue = ""</code>
    <code> Else</code>
    <code> Me.Filter = "RegistrationYear = " & Me.cboSelectYear</code>
    <code> Me.FilterOn = True</code>
    <code> Me.RegistrationYear.DefaultValue = Me.cboSelectYear</code>
    <code> End If</code>
    <code>End Sub</code>

    Here, cboSelectYear is the name of the combo box, and RegistrationYear the name of the year field.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual deletion / creation of tables... (2003

    Hans,

    man, you saved me some working hours, thank you very much indeed! This seriously looks pretty good! Fallin' in love with the forum already [img]/forums/images/smilies/wink.gif[/img]

    Alright I have made some minor modifications to the example provided by you, like I would like to have it. I think there must be some kind of standard-value for the year, which you HAVE to set in the beginning (this is important for those 'stupid' users always forgetting to enter the right year.

    What I did now it to have a small form pop up in the beginning and it works like a charm - BUT the form has to stay open all the time to provide a default value! I did it the dirty way now and deleted the close button, but that's not what I really want. Is there some way I can store that default value somewhere? Like in some cache or something...

    Actually I got the idea from setting the year in the beginning of database startup from a 10,000 $ program in which I have to do this all the time. I'm afraid is has to be like it, because I'm sure later users will keep forgetting to select the right year.

    I hope you can help me on this some more, but I can see the end of the tunnel - we're getting somewhere here!
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Annual deletion / creation of tables... (2003

    You can hide the startup form after the user has selected a year. That way it will remain in memory, without taking up real estate on the screen. See attached.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual deletion / creation of tables... (2003

    Hans your great!

    The only problem which I just experienced is that the filter criteria doesn't get "activated". Try it once yourself and you will see, that the filter is set to "off" and all records are shown...
    A workaround would be to refer in everything to our frmyear, but I'm eager to know how to make this work like you did now.
    Thanks for the "hide" tip, I'm definately gonna use that.

    Dennis

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Annual deletion / creation of tables... (2003

    To set the filter when frmAddress is opened from frmYear, change the line that opens it:

    DoCmd.OpenForm "frmAddress"

    in the On Click event procedure for the cmdHide button to

    DoCmd.OpenForm "frmAddress", , , "RegistrationYear = " & Me.cboreg

    This passes the filter in the WhereCondition argument of DoCmd.OpenForm.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual deletion / creation of tables... (2003

    Hope I'm not gettin on your nerves Hans...

    Now that works perfect in the beginning. Now here comes the last BUT in this thread (I hope):

    Let's play through this: frmyear > select year > OK > frmAddress = everything filtered by the selected year > close
    now the user plays around with some other forms (which certainly exist, just not in our example) which need the date too...

    BUT when reopening the frmAddress the previously set year is forgotten.... I need the program to have that year throughout the whole process...

    Dennis

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Annual deletion / creation of tables... (2003

    Change the instruction to open frmAddress back to what it was originally, and instead create an On Load event procedure for the frmAddress form:

    Private Sub Form_Load()
    cboSelectYear_AfterUpdate
    End Sub

    This will apply the filter each time the form is opened, not just the first time from frmYear.
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annual deletion / creation of tables... (2003

    Woah, works like a charm! Problem solved!

    Thank you very much Mr. Hans - always good to have such helpful people on boards!

    I'll be back
    Dennis

Posting Permissions

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