Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Auto-Fill Fields in same table (Access 2003)

    Once again, something I should probably know or be able to reference --but once again, I still can not get to my books to find the answer. Thanks to everyone I am still able to get my work done, big thanks to everyone who has assisted. Here it is:

    I have a Table that holds Requests (tblRequests) and a Table that holds the Location Information (tblLocation) (the tblLocation pulls the contact information from tblContacts)

    When I enter data into the "Location Code" field of the tblRequest, I would like to have it auto-fill the default information from the tblLocation and the tblContacts that the specific Location Code corresponds with. I was thinking that I should be able to make the default value (but still have the option to change) of the field equal the corresponding field from the other table, but I have not been able to figure out how to make this work.

    (i.e. Location Code =1 in tblRequest, auto-fill Man=tblContacts: Man field for tbl_Location Code = 1)

    I am at a big stand still until I figure this out, so if someone could point me in the right direction I would appreciate it greatly.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Should the user be able to change the fields that have been "autofilled"?
    If not, you don't need those fields, since they contain derived information. You can create a query based on tblRequest and on tblLocation, joined on the Location Code field, and use this as basis for other queries, and as the record source of forms and reports.
    If yes, you can fill the fields in the form used to enter the data, not directly in the table itself. You'd write code for the After Update event of the control bound to Location Code to fill some other controls with the appropriate information.

  4. #3
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Hans, thank you for your quick response, I was hoping you would pop up in here today.

    I do need the information in my tblRequest, as it must be editable. I am merely trying to auto-fill with the default value from the other table to speed up the request process for the request manager. If he chooses the location code and it fills in the default information and someone is filling in or it changes later, the request has to contain the information for the specific request.

    To make sure I am getting this correct, If I do like you are saying in the form, I would choose the Location Code feild on my tblRequest FORM and in the After Update event, put what I want it to do? That is what I was thinking but I wasn't sure how to write the code for looking up the fields in the location record that corresponds with the location I selected in my table. If you could give me an example of what it would look like I would greatly appreciate it.

    Thanks again for your help, you have been a real life saver on this project.
    -Laura

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    You could use a combo box for the Location Code field on the form based on tblRequest.
    The combo box would have tblLocation or a query based on tblLocation as Row Source, with as many columns as you need later on. You specify the number of columns in the Column Count property, and you hide the extra columns by specifying a width of 0 for them in the Column Widths property (this is a semi-colon delimited list).
    In the After Update event of the combo box, you can refer to the columns; counting starts at 0 for the 1st column. Here is a fictitious example:

    Private Sub Location_Code_AfterUpdate()
    ' Set ThisField to the value of the 2nd column
    Me.ThisField = Me.Location_Code.Column(1)
    ' Set ThatField to the value of the 3rd column
    Me.ThatField = Me.Location_Code.Column(2)
    End Sub

  6. #5
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Thank you again Hans, I did like you said, although I have a slight error.
    Is there something special I needed to do if it is auto-filling more than two fields on the form?
    I followed the steps you gave me, inserting my actual field names of course, and the first two work and the last three do not work.
    The auto fill updates ThisField and ThatField if I change the Location Code on my form but the other three Fields I told it to update do nothing.
    They are in the combo box and I checked everything to make sure the are properly identified, and still I get blank fields (i.e. No Auto-fill happening)

    The only difference between the first two fields and the last three fields that I can think maybe causeing the problem is that the last three fields have a lookup in the table that actually queries based on another table. If the error is caused by this then it is the query part and not the lookup, because the first two fields are also looking up from the same table.

    If you have any idea's I have retyped it twice and tried adjusting the location in the columns and I still get blank fields and no error message to assist. Thanks again.

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Have you checked that the Row Source of the combo box has the correct number of fields, and that the Column count has been set accordingly?

    If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  8. #7
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Thank you Hans, isn't it always the most simple of things that can throw and wrench in your plans. I checked the column count on my table but had forgot to check it on the form. That was my problem. Thanks again for your help. Hate to say it but I will probably be back soon as I am still in the starting process of a major database build. Thanks for your help.

  9. #8
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Another twist on the Auto Fill code help you gave me before.

    I have a combo box on a form called [Status], there are several options in the combo box to choose from (New, Transfer, PCR, Assigned, Resolved, Closed)
    I want to have another field on my form Auto-Fill the Date "Now()" and CurrentUser() when the status is changed.

    When the status is changed to Resolved, I want the [Resolve Date] field to auto fill the date Now() and the [Resolved By] field to auto fill the CurrentUser()
    When the status is changed to Closed, I want the Closed Date field to auto enter the date Now() and the [Closed By] field to auto fill the CurrentUser()

    I know that if AfterUpdate of Status, I can choose Me.ResolveDate = Now(), but I am not sure on how to do IF Status is Resolved THEN, and IF Status is Closed THEN. If you could please assist me with the IF portion I would greatly appreciate the assistance.

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

    Re: Auto-Fill Fields in same table (Access 2003)

    It depends a bit on whether the Status field contains a text value such as "Resolved" or corresponding numeric ID.

    Let's say it is a text value. You'd use code like this:

    Private Sub Status_AfterUpdate()
    Select Case Me.Status
    Case "Resolved"
    Me.[Resolved Date] = Now
    Me.[Resolved By] = CurrentUser
    Case "Closed"
    Me.[Closed Date] = Now
    Me.[Closed By] = CurrentUser
    End Select
    End Sub

    If it is a numeric value, replace "Resolved" and "Closed" in the above code with their numeric equivalents (without quotes).

  11. #10
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Thank you Hans, as always worked perfectly. To answer your question I was using text values such as Resolved. For better understanding: Why do I have to use Now() when I use a text but when I use this code I use Now without the ()?

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

    Re: Auto-Fill Fields in same table (Access 2003)

    In VBA, you don't have to add parentheses ( ) when you are calling a function (or procedure) without arguments. You do still need them in the first line of the definition of the function or procedure.
    In expressions in a query, form or report, the parentheses are obligatory - they tell Access that you are calling a VBA function instead of referring to a field.

  13. #12
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Auto-Fill Fields in same table (Access 2003)

    Thank you.

Posting Permissions

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