Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting defaults

    I have a access database 2003 that the text boxs are on a form. The user has to enter the year and Unit Number. I know how to open up the property and set the default values. I'm trying to make it user friendly where when the database is opened a message box would appear and have the user to enter the year and Unit Number, if a year and Unit Number has already been selected the message would not appear. Once selected this selected year and Unit Number would be set as the default value for the text box "year" and "Unit Number". So each record would have the year and Unit number already selected and not have to be re-entered on each record. Anyway to do this without having to set the default value through property of the text field.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Once a Year and Unit name are entered as defaults, do they remain as the default for ever?
    You could have a table with just one record and 3 fields. An autonumber key, and two value fields to store these.
    Then define two custome functions:

    Code:
    Public function fnGetYear() as long
    fnGetYear = nz(Dlookup("DefaultYear","tblDefaults","[key]=1"),0)
    end function
    Code:
    Public function fnGetUnit() as long
    fnGetUnit = nz(Dlookup("DefaultUnitNumber","tblDefaults","[key]=1"),0)
    end function
    Then set the default values of the fields to the two functions.

    To do things at startup you need a macro called autoexec. Set the action of the macro to RunCode. Run these two functions, and if either returns 0 then open a form to enter values for these defaults.
    Last edited by johnhutchison; 2011-03-13 at 19:18.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes the year and unit number remain as default forever. I have a table already with the fields, year and unit number. do I make another table with those fields and a autonumber field? You can probably tell I'm a newbee by my response. Can someone make the suggestion and add to my database and upload again to see if this is the solution I'm looking for.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Look at the attached.

    A few changes I made:

    Year should not be used as a field name, as it is a Reserved Word, I changed it to GrievanceYear. Now that we have a Default, the 20 should be part of the year, so I got rid of the 20 you had in front of it.

    I did not use an autoexec macro. Instead I put the code in the OnLoad event of the Switchboard.

    I set the default value on the Grievance form to the functions I added to the module you had already.
    Attached Files Attached Files
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well thanks John, this is exactly what I was looking for. Again thanks

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Good.

    Would the year of the Grievance normally be the Current Year? In that case you could set it do =Year(Date()) . Then you would not need to have DefaultYear in tblDefaults.
    Regards
    John



  7. #7
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It would normally be the current year. The only thing I could think of is the user being off at the end of the year and not be able to enter the data until the year passed. Therefore I don't think the current year would work. Thanks for the input though.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I meant to say that you could set the Default to Year(Date()).
    A default is just a Default. You can still change it.
    Regards
    John



  9. #9
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This same database has a text box that the user will input a 4 digit number that corresponds with the Sent to Units Name. Example Wilkes County = 3124, there are approximately 100 different units so there are 100 different numbers. Also they are separated by 5 regions. Example Eastern, Central, etc. I'm trying to make it easier for the user to input the different numbers. I thought about a dialog box, or would a combo box work? Is the combo box limited to the number that can be stored. Any ideas
    Last edited by RonNCmale; 2011-03-17 at 02:03.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    A combo box sounds like a good idea to me. A combo can hold thousands of choices, so 100 would be no problem.
    I would create an extra table to hold Unit Names. I think this would have 3 fields, UnitNumber, UnitName and Region.

    Has a Region already been chosen before you enter the Unit Number? If so, you could use a query to restrict the combo to units from that Region.
    Regards
    John



  11. #11
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes on the region, and thanks again John. I'll google for examples of how to get combo boxes to restrict choices. This will get me going again.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is one Microsoft Example.

    You could have a combo for Region as well.
    Regards
    John



  13. #13
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've created a button on my database form field that will open another form that has three combo boxes. The first one picks the region, and the second one list the units in that region and the third shows the unit number. I now wish for the third combo box to place the unit number into the send to unit field. I've been working with it for weeks and can't seem to find a solution to populate the send to field from the combo box.
    Attached Files Attached Files

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Should I be able to see that button in the db you attached? It is not obvious to me. OK Found it. Tiny little button with a picture on it.

    I can see that frmProducts is the form you refer to.

    I put a Close Button on frmProducts with this code behind it.

    Code:
    If Not IsNull(Me.[Unit Number]) Then
       Forms("frmGrievance").[Sent_to_Unit1] = Me.[Unit Number]
    End If
        DoCmd.Close acForm, Me.Name, acSaveNo
    Regards
    John



  15. #15
    New Lounger
    Join Date
    Mar 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John it worked but when I closed I get the following error:
    Run-time error '2501' The close action was canceled. When I hit debug it went to
    DoCmd.Close.acForm,me.Name,acSaveNo.

Page 1 of 2 12 LastLast

Posting Permissions

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