Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2005
    Posts
    6

    Unanswered: Automatically filling other fields in a form

    I believe this is in the forum somewhere but for the life of me I can't seem to locate it....

    I have three fields on a form, cboFiscalYear, txtStartDate, and txtStopDate. cboFiscalYear is a combo box and its Row Source is the FiscalYear field in a table called tblFiscalYears which consists of three fields, FiscalYear, StartDate, and StopDate.

    I want to select a fiscal year in the combo box and, on update, fill in txtStartDate with StartDate and txtStopDate with StopDate.

    If someone can point me to a thread that explains how to do this, I would greatly appreciate it. A web link elsewhere works for me also. Thanks.

  2. #2
    Join Date
    Jul 2006
    Posts
    108
    wow you just described the essential use of a form

    try this: right click on 1 of the textboxes, and change the control source to that field.

  3. #3
    Join Date
    Apr 2005
    Posts
    6

    More specific information

    I guess I should have added one more thing. The form's Record Source is a table called tblCurrentFiscalYear that contains one record made up of three fields, CurrentFiscalYear, CurrentStartDate and CurrentStopDate. I use the CurrentStartDate and CurrentStopDate fields from this table in the criteria section of a query that selects all the invoice records in the database that have a purchase date within that fiscal year.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The simplest way, I think, is going to be to scrap your current combobox and redo it with the combox wizard; you could write code behind your current one to do it, but it's easier letting the wizard do it.

    First you must have your form set up to display the record you want to retrieve, i.e. you must have fields set up with the appropriate Control Sources. Then simply:

    Add a combo box to your form. The Combobox Wizard will pop up
    Select "Find a record based on the value I selected in my combobox."
    Hit Next.
    Click on the field you're searching by (FiscalYear) to move it to the right side.
    Hit Next.
    Size the column appropriately.
    Hit Next.
    Name the combobox.
    Hit Finish.

    Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, and the combobox will "autofill" as you type.
    Hit <Enter> and the record will be retrieved.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2005
    Posts
    6

    Almost, but not

    I will first admit that I must really be old school and set in my ways. I have always done combo boxes by creating a text box or selecting a field from the field chooser and then converting it to a combo box and setting the Row Source for it. I don't think I have ever used the combo box wizard before.

    Nonetheless, it still doesn't do what I need to do. When I select a fiscal year using the cboFiscalYear combo box, I can see all three fields in the Row Source that it is selecting from, which is tblFiscalYears. And when I select one of the fiscal years, it populates the combo box and the Control Source CurrentFiscalYear field just fine. But it doesn't fill in the Control Source fields CurrentStartDate and CurrentStopDate.

    I'm quite sure this requires VB code triggered by an AfterUpdate event, and I'm positive I've used it before in some database I wrote somewhere, but I simply can not locate it. I also believe it was this forum that had a thread explaining how to do it. If this wasn't a rush job (what isn't?), I would have just experimented until I figured it out. I guess I should have just done it anyway. I would probably be done by now. :-)

    Thanks for turning me on to the combo box wizard. That looks like it may work better for many of the simpler forms I create that use combo boxes. Perhaps I should go check out all of the wizards someday. I may be doing a lot of things the hard way. I started out with an early version of Access. I believe it was circa Windows 95, and now I'm on Access 2003. There are probably all kinds of goodies I should learn about. All I need is the time to educate myself, like that'll happen. :-)

  6. #6
    Join Date
    Jun 2004
    Posts
    3
    Try this in your afterupdate event of cboFiscalYear.

    Me.txtStartDate = Me.cboFiscalYear.Column(1) 'startdate
    Me.txtStopDate = Me.cboFiscalYear.Column(2) 'stopdate

    If "cboFiscalYear" is not bound to the field, add this "Me.txtStopDate = Me.cboFiscalYear.Column(0)" where 0 is the first field "FiscalYear".

    Quote Originally Posted by mowbetta
    I believe this is in the forum somewhere but for the life of me I can't seem to locate it....

    I have three fields on a form, cboFiscalYear, txtStartDate, and txtStopDate. cboFiscalYear is a combo box and its Row Source is the FiscalYear field in a table called tblFiscalYears which consists of three fields, FiscalYear, StartDate, and StopDate.

    I want to select a fiscal year in the combo box and, on update, fill in txtStartDate with StartDate and txtStopDate with StopDate.

    If someone can point me to a thread that explains how to do this, I would greatly appreciate it. A web link elsewhere works for me also. Thanks.

  7. #7
    Join Date
    Apr 2005
    Posts
    6

    Woohoo!

    That's it! The column doomaflotchie. Many thanks for reminding me about that. It works fine now. I sure appreciate your time to help out this old man whose brain just ain't what it used to be anymore. I'm printing out the code and adding it to my notebook for the future. Knowing me though, I'll lose the damn notebook. :-) This forum rocks. Thanks to all of you who helped.

Posting Permissions

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