Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    still need help (Access2000)

    I had asked about updating a field from a combo box but there seems to be some confusion in the answer. I want to select the plan code in the combo box and then the PlanName field will automatically fill in. Apparently, I'm doing something wrong and not communicating what I need well either.

    I have the following:
    combo box
    Name:PlanCode
    Control source:PlanCode
    RowSource:SELECT [L_Plans].[PlanCode],[L_Plans].[PlanName] FROM L_Plans;
    Column Count: 2
    Bound Column: 1

    After Update Event is coded:
    Option Compare Database

    Private Sub PlanCode_AfterUpdate()
    Me![PlanName]=Me![PlanCode].Column(1)
    End Sub


    I don't get an error message when I select a PlanCode but the PlanName field remains blank.

    Thanks for the help in advance,
    Laura

  2. #2
    New Lounger
    Join Date
    May 2002
    Location
    Gen, Gen, Switzerland
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: still need help (Access2000)

    What you have written seems correct. Perhaps have you one of these problems:

    First thing to check is to be sure that the aferupdate event is really fired. So put a break point in the event handler. Access from time to time loose the connection between the code and the event and you have perfect code never executed.

    Second thing is to check that you have a control name PlanName or a control with another name but with Control source set to PlanName.
    By the way, I think it's a good thing to always rename the control : put a 'c' (for control) or a txt (for TextBox) to make a difference between the controls of the form and the fields of the form.
    And add an Option Explicit at the beginning of the form to be sure that all your variables are checked by the compiler.

    Hope this help.

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Gen, Gen, Switzerland
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: still need help (Access2000)

    By the way, if you just want to display the value of PlanName you don't need to write code, you can just change the query of your form to join the table L_Plans and add the field PlanName to your query. When the user inputs a value in PlanCode Access automatically update PlanName !

  4. #4
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: still need help (Access2000)

    Ok, apparently I just have a total mental block when it comes to this. I'm attaching the actual database (what there is of it). Would someone please take a look and tell what the heck I'm doing wrong with the Plan Code and Plan Name on the form "F_PatientRecord". I'd be indebted to you forever because I'm going to print out the correction and save it so I don't go through this again. My brain hurts!

    Thanks,
    Laura
    Attached Files Attached Files

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: still need help (Access2000)

    In your AfterUpdate event you have:
    Me![PlanName] = Me![PlanCode].Column(2)

    You need to change it to 1 not 2
    Me![PlanName] = Me![PlanCode].Column(1)

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: still need help (Access2000)

    Changed it, Pat. When I close the database and reopen it, the plan name is there but there is some hangup in that it doesn't update immediately.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: still need help (Access2000)

    The command
    Me![PlanName] = Me![PlanCode].Column(1)
    needs to be
    Me![Plan Name] = Me![PlanCode].Column(1)

    Notice the space between Plan and Name
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: still need help (Access2000)

    There is one other item to look at. The PlanName textbox should be unbound. Go to the Data Tab in design view, and delete the value in the Control Source. After making the box unbound, check the name in the other tab to ensure that PlanName does not contain a space between Plan and Name.

    One other item. Input the following in the after update event. Insert the .Value.

    Me![PlanName].Value = Me![PlanCode].Column(1)

    HTH
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: still need help (Access2000)

    Thank you so much. You guys are awesome!

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: still need help (Access2000)

    Would you like to tell us what you did to solve this.
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  11. #11
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: still need help (Access2000)

    It was that space in Plan Name, I had PlanName. I thought the name had to be the same as the control source, not the field name. And that fixed it so I didn't use Gary's suggestion because I'm too afraid of messing it up again. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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