Results 1 to 13 of 13

Thread: Default Value?

  1. #1
    Join Date
    Feb 2005
    Posts
    23

    Unanswered: Default Value?

    I am a complete rookie here, and creating a simple MS Access DB.

    I would like to set up a field in my form to have a defualt value based on the entry into the field from a previous record.

    In other words if in record 2 my "date" field was 12/12/2004, I want it so that the default value for the "date" field in record 3 is 12/12/2004.

    Is this possible, and if so would anyone mind giving me a tip.

    To me it seems I would have to use the expresion builder, however the books I have help me very little.

    Cheers.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There's been a question very similar to this recently, not sure of the reference, but it was on this forum.

    Yes, what you want can be done, but it isn't going to be easy and straightforward, and I'm not sure it would be soemthing that an Access newbie might want to do - depends on your experience and comfort at writing some code to work behid the scenes of 'standard' Access

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In the after update event procedure of the control in question (code, not expression, I'm afraid), enter something like this

    me!txtNameOfControl.DefaultValue = "#" & me!txtNameOfControl.Value & "#"

    - replace txtNameOfControl with the actual name of your control (it better not be Date, which is a reserved word)
    Roy-Vidar

  4. #4
    Join Date
    Feb 2005
    Posts
    23
    Quote Originally Posted by RoyVidar
    In the after update event procedure of the control in question (code, not expression, I'm afraid), enter something like this

    me!txtNameOfControl.DefaultValue = "#" & me!txtNameOfControl.Value & "#"

    - replace txtNameOfControl with the actual name of your control (it better not be Date, which is a reserved word)
    I am most likely doing something stupidly wrong but this is the code I ve been inputing, but alas without getting the desired result


    Option Compare Database
    Option Explicit

    Private Sub RadarStationID_AfterUpdate()

    Me!RadarStationID.DefaultValue = "#" & Me!RadarStationID.Value & "#"

    End Sub

    Suggestions?

  5. #5
    Join Date
    Feb 2005
    Posts
    23
    Quote Originally Posted by healdem
    There's been a question very similar to this recently, not sure of the reference, but it was on this forum.

    Yes, what you want can be done, but it isn't going to be easy and straightforward, and I'm not sure it would be soemthing that an Access newbie might want to do - depends on your experience and comfort at writing some code to work behid the scenes of 'standard' Access

    Thanks I'll try browsing through the forums. I just need some examples and such will learn eventually, what better way to learn than by hands on experience and trouble shoot

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    It is bound to a field with datatype date/time? Would your date settings by any chance differ from US? If so, try:

    Me!RadarStationID.DefaultValue = "#" & format$(Me!RadarStationID.Value, "yyyy-mm-dd") & "#"

    Could you define "but alas without getting the desired result" - do you get any results, are they wrong, is it #Name/#Error...
    Roy-Vidar

  7. #7
    Join Date
    Feb 2005
    Posts
    23
    Quote Originally Posted by RoyVidar
    It is bound to a field with datatype date/time? Would your date settings by any chance differ from US? If so, try:

    Me!RadarStationID.DefaultValue = "#" & format$(Me!RadarStationID.Value, "yyyy-mm-dd") & "#"

    Could you define "but alas without getting the desired result" - do you get any results, are they wrong, is it #Name/#Error...

    Sorry I was using date as an example. The three fields I want to stay in the following record are two ID fields and a date field. The ID fields are numbers.

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    "Could you define "but alas without getting the desired result" - do you get any results, are they wrong, is it #Name/#Error..."

    Numerics shouldn't need any delimiters.
    Roy-Vidar

  9. #9
    Join Date
    Feb 2005
    Posts
    23
    Quote Originally Posted by RoyVidar
    "Could you define "but alas without getting the desired result" - do you get any results, are they wrong, is it #Name/#Error..."

    Numerics shouldn't need any delimiters.
    I get "#Name?"

    Thank you Roy for taking the time.


    I think I am complicating this way too much for my own good. Maybe I'll go finish it and post the DB here and get someone to give me a few tips.

  10. #10
    Join Date
    Feb 2005
    Posts
    23
    What I am trying to do is basically set up the "RadarDataSubform" in such way that the biologists do not have to input the RadarStationID RadarSurveyID and SurveyDate everytime they input a record.

    My initial solution to this was the form named "RadarDataInfo" which has the RadarDataSubform linked to the three fields mentioned above in a seperate table. However I noticed that if they make a mistake and want to change any of the three fields not all the records change(ie the records are only visible through table view and not through the form) so I though I force them to input each one seperately(RadarDateSubform format) but save them the time by at least having default values in the fields.
    Attached Files Attached Files
    Last edited by Ervin; 02-27-05 at 18:55.

  11. #11
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In a subform, you'd probably need an action query (update), to update all the other records (dangerous - what if more records match the same criterion...)...

    For the default hingie, this is something I sometimes use, having it as a private sub that can be called for instance called from the after update event of the form. It works on the peculiar settings we have where I reside

    For this to work, one should hopefully only need to put some characters (no matter what) in the .Tag property of the controls in question (bottom property in the "Other" tab). Should work for the controltypes listed.
    Code:
    Private Sub rvsSetDefault()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ((ctl.ControlType = acTextBox) Or _
            (ctl.ControlType = acComboBox) Or _
            (ctl.ControlType = acListBox) Or _
            (ctl.ControlType = acCheckBox)) Then
    
            If (Len(ctl.Tag & vbNullString) > 0) Then        
                Select Case True
                    Case IsNull(ctl.Value)
                        ' If no value, don't change default value
                        ' this will need to be tweaked according to
                        ' requirements. Perhaps use "", so that the
                        ' “old” default value disappears…
                    Case IsDate(ctl.Value)
                        ' Dates - hmmmm - because of different
                        ' regional settings (differing
                        ' from US date format) it is sometimes
                        ' necessary to do some formatting
                        ctl.DefaultValue = "#" & _
                            Format(ctl.Value, "yyyy-mm-dd") & "#"
                    Case IsNumeric(ctl.Value)
                        ' No formatting for numbers
                        ctl.DefaultValue = ctl.Value
                    Case Else
                        ' Then it should be text, four double quotes...
                        ctl.DefaultValue = """" & ctl.Value & """"
                End Select
            End If
        End If
    Next ctl
    End Sub
    Roy-Vidar

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is another option
    if in effect you are using the form to do a batch input I'd suggest you create as many unbound controls as required (place them in the forms header or footer). Lets assume you called your unbound control(s) tbRadarSite, tbSurveyDate

    then create the controls as required in the forms details section, including the controls required for the data to be supplied from the unbound controls creataed earlier.

    View | Code
    select the form from the LHS combo box and the 'before insert' OR 'after insert' from the RHS combo box. You may prefer to place the code in the 'before insert' event.

    Code:
    if isdate(tbsurveydate)=true then SurveyDate=cdate(tbsurveydate)
    if isnull(tbRadarSite)=false then RadarStationID=tbRadarSite
    to automatically record the last used id & date, place some code in the forms 'after insert', or possible 'after update' events

    Code:
    if isdate(SurveyDate) then tbsurveydate=format(surveydate,"DD-MMM-YYYY")
    if isnull(radarstationId) then tbradarsite=radarstationId
    On the start of the batch get your users to enter the required values, perform any validation as required.

    incidently Roy-Vidar's
    ctl.DefaultValue = """" & ctl.Value & """"
    can be simplified using chr$(34)
    ie it could be expressed as
    ctl.DefaultValue = & chr$(34) & ctl.Value & chr$(34)
    Last edited by healdem; 02-28-05 at 02:37.

  13. #13
    Join Date
    Feb 2005
    Posts
    23

    Thumbs up

    Thank you guys for helping out, however I found a solution at another page, tweaked it a bit and it works perfectly.

    Instructions found here:

    How to fill record with data from previous record automatically in Access 2000



    I can go get some sleep now
    Last edited by Ervin; 02-28-05 at 04:09.

Posting Permissions

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