Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    setting value in new record on a form (2000-SR1)

    On a continuous form, I first select ItemID, then the color, then move to the next record. I would like to be able to fill in some data on the next record based on the choice in the current record. For example, if ItemID is "2", after the ColorCode is selected, on "AfterUpdate", I would like the next record to automatically fill in ItemID as "25", then set the focus to the ColorCode combo box on that record.
    Here's my feeble attempt at learning how to do this on my own...which, of course, didn't work...

    If Me.ItemID = 2 Then
    RunCommand acCmdRecordsGoToNew
    Me.ItemID = 25
    Me.ColorCode.SetFocus
    End If

  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: setting value in new record on a form (2000-SR

    When you say your code did not work, what actually happened?

    I pasted your code into a form, and it did exactly what you said you wanted.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: setting value in new record on a form (2000-SR

    It appears to go to the new record, but focus goes to the ItemID combo box and no value is entered (rather than entering the desired value into the ItemID combo box, then moving the focus to the ColorCode combo box.

  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: setting value in new record on a form (2000-SR

    Here is the form I tested it in. It works here.

    This is very simple, and just uses text boxes.

    Check the names of the controls on your form. The name can be ( and it is often a good idea that they are) different to the field the control is bound to.
    Attached Files Attached Files
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: setting value in new record on a form (2000-SR1)

    Thanks, John, for your efforts on my behalf. I guess I didn't tell the whole story. The control source for the combo box for Item ID has an SQL statement, SELECT [tblItems].[ItemID], [tblItems].[Item], [tblItems].[Fee] FROM tblItems. The column width for the ItemID is set to "0", and the "Item" (which is a description of the item, rather than the ID number) is what shows up in the drop-down list. That (the item description, "Item") is what I would like to have show up on my form, but have the ItemID stored in the table. So, my intent was to have the next record show up with the correct ItemID stored, the item description "Item" show on the form, and focus be already set to the ColorCode combo box. Is it my SQL that's making it not work, or seem to not be working?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: setting value in new record on a form (2000-SR

    I got a chance to test the sample you provided, and I get it to work...I think my problem must be with that SQL described above. Does the code need to specify which column the value goes to or something like that?

  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: setting value in new record on a form (2000-SR

    The problem is that the value of a combo box with a hidden column is whatever is in the hidden column, not the value you see in the first visible column.

    So when you test for If Me.ItemID = 2 , 2 needs to be value you can't see.

    Just to experiment, set the width of the first column to some small value , and see what happens when the value of the ID really is 2.
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: setting value in new record on a form (2000-SR

    I tried as you recommended--in the new record, the value for the ItemID combo box goes to "0" (and that is stored in the table).

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

    Re: setting value in new record on a form (2000-SR

    What is the value of the Bound Column property of the ItemID combo box?

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

    Re: setting value in new record on a form (2000-SR

    Can you post a stripped down version, so we can have a look.

    I have just changed my example to one that uses a combo box and it still works as I expect it to.
    Attached Files Attached Files
    Regards
    John



  11. #11
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: setting value in new record on a form (2000-SR

    Bound column property is "1"
    The Row Source is
    SELECT [tblItems].[ItemID], [tblItems].[Item], [tblItems].[Fee] FROM tblItems;

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

    Re: setting value in new record on a form (2000-SR

    Please see John's latest reply.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: setting value in new record on a form (2000-SR

    Here it is...thanks for the help
    Attached Files Attached Files

  14. #14
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: setting value in new record on a form (2000-SR

    'tis sent...thx

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

    Re: setting value in new record on a form (2000-SR

    the problem is this code:

    If Not Me.ColorCode Then
    RunCommand acCmdRecordsGoToNext
    Me.ItemID.SetFocus
    End If


    This executes before the code you have been talking about in this thread, and already takes you to a new record with the focus on itemID.

    Take it out , and the other code will work.

    ColorCode is not a yes/no field so what are are testing for with Not me.colorcode?
    Regards
    John



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
  •