Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set combo box default value to null (Access97)

    I have a form with an unbound combo box that correctly looks up records with this code:

    Sub comboFind_a_Trial_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "<trial_Number> = '" & Me![comboFind_a_Trial] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub

    When I add a new record, the Trial_Number of the previous record is still in the combo box. How can I get it to be blank? I have tried setting the Default Value property to "".
    Many thanks

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

    Re: Set combo box default value to null (Access97)

    Instead of settings its default value to "", set its value to Null in the code that executes after you add the new record.
    Put it in the after insert event.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set combo box default value to null (Access97)

    Apologies for being so thick, but, um, I don't have any explicit code to execute after adding a new record. We just click the 'arrow asterisk' button on the navigation bar at the bottom of the screen to add new records.

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

    Re: Set combo box default value to null (Access97)

    I am sure you are not thick! I didn't explain myself very well.
    The form in which you add the record will have an "after insert" event. Put code in there.

    Is this a subform of the form with the combo or another form altogether?
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set combo box default value to null (Access97)

    Same form; not a sub-form.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set combo box default value to null (Access97)

    I tried this code:

    Private Sub Form_AfterInsert()
    Me!comboFind_a_Trial = Null
    End Sub

    but it didn't work.

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

    Re: Set combo box default value to null (Access97)

    I attach a little db that tries to do the same thing and works OK for me.
    I have different field names I' sorry.

    Are you sure this line in your original post is correct?
    Me.RecordsetClone.FindFirst " = '" & Me![comboFind_a_Trial] & "'"
    Don't you have a field name between findFirst " and = " & etc
    Attached Files Attached Files
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set combo box default value to null (Access97)

    Hmmmmm... thanks for the sample db - but yours behaves the same as mine does. Maybe something to do with our version of Access97, which seems to have a few odd quirks. (maybe not updated with Service Patches or something? I have had trouble with bits not having been installed in the first place, e.g.: import data features)
    Anyway, I think we'll just learn to live with the combo box as it is.
    Thanks for your help. I always learn something new!

    By the way, the combo box code I originally posted is correct (cut and paste direct from db), and it works fine. (wonders never cease)

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

    Re: Set combo box default value to null (Access97)

    This is a puzzle to me!
    Your code does not work for me, until I put a field name in. Even then, it works but it doesn't do what I would want it do ie. only show records with a matching field value.
    In my example the combo box is supposed to clear after I add the new record. Tthis happens when the pencil goes away on the left. This only happens when I move the cursor to a different row. If you want the combo box cleared before then, yyou could put the code in the after update event for one of the fields.
    Regards
    John



  10. #10
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set combo box default value to null (Access97)

    Perhaps a clue to the puzzle: I am showing only the Single Form View (one record per page/form), and have set the Record Selectors to 'No'.
    The field name is 'entered' when you choose an item from the combo box. It really does work. Do you want a copy of the db to look at?

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

    Re: Set combo box default value to null (Access97)

    I believe you that it works, I just don't understand why!
    Do you want the combobox to become Null as soon as you start entering the new record, rather than when you have finished.
    Try this:
    In the oncurrent event put:
    if isnull(me!entered) then
    me!!comboFind_a_Trial = Null
    end if

    You might want to add me!entered.setfocus as well
    Regards
    John



Posting Permissions

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