Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    8

    Unanswered: Auto Populate with Query 'After Update'

    I could use some help...

    I am creating a database for the equipment that my company uses. We area warehousing company with several buildings and hundreds of forklifts. I have a form that equipment operators are supposed to fill out at the end of the day. I created the below query to pull up the most recent information for that unit.

    SELECT [Tbl Status].[Equipment ID], [Tbl Status].[EmployeeID], [Tbl Status].[Building ID], [Tbl Status].[Status HourReading], [Tbl Status].[Maintenance Required], [Tbl Status].[Maintenance Comments], [Tbl Status].[Status Date]
    FROM [Tbl Status] INNER JOIN [Tbl Status] AS Self ON [Tbl Status].[Equipment ID]=Self.[Equipment ID]
    GROUP BY [Tbl Status].[Equipment ID], [Tbl Status].[EmployeeID], [Tbl Status].[Building ID], [Tbl Status].[Status HourReading], [Tbl Status].[Maintenance Required], [Tbl Status].[Maintenance Comments], [Tbl Status].[Status Date]
    HAVING ((([Tbl Status].[Status Date])=Max([Self].[Status Date])));

    I want to take the information from the following fields and auto populated the same fields in the Status Form after the [Equipment ID] is selected from a drop down menu: [EmployeeID], [Building ID], [Status HourReading], [Maintenance Required], [Maintenance Comments]

    If you can help me to solve this problem, I would be extremely thankful.
    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    after the [Equipment ID] is selected from a drop down menu: [EmployeeID], [Building ID], [Status HourReading], [Maintenance Required], [Maintenance Comments]
    Is a drop down menu a combo box?

  3. #3
    Join Date
    Mar 2003
    Posts
    8
    Yes, I used the lookup wizard to to get the following information from the equipment table.

    SELECT [Tbl Equipment].[Equipment ID], [Tbl Equipment].[EquipmentNumber], [Tbl Equipment].[EquipmentType], [Tbl Equipment].[FuelType ID] FROM [Tbl Equipment];

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    Ok. Usingf a combo box you can use hidden columns to populate the fileds on your form.

    First you need to create a select query to return [EmployeeID], [Building ID], [Status HourReading], [Maintenance Required], [Maintenance Comments], and any other fields you would like returned in the combo box.

    You then use the select query as the row source of the combo box, by copying the SQL or tying the combo box straight to your query.

    NExt tell the combo box how many columns it has in total. Using the above example it would be 5.

    Using the above example if we wanted to search the combo by employee ID, we would set the widths as follows:

    2.5;0;0;0;0

    This would make column 0 visible and all other columns hidden.

    Finally we need a little bit of code to help us out. In the after update event of the combo box we would write code like:

    Me.txtBuildingID=Me.cboEmpID.Column(1)

    We would repeat this for each column we wanted to pull data from.

    HTH

  5. #5
    Join Date
    Mar 2003
    Posts
    8

    Smile

    Do I have to have entries for each piece of equipment first? I tried this earlier and my equipment list went from several hundred down to one that had entered information for.

  6. #6
    Join Date
    Feb 2002
    Posts
    403
    Change your query joins until the query shows exactly what you want. In other words show all items of equipment and if there are matches show them. This is a right join(I Think I get them mixed up too)

  7. #7
    Join Date
    Mar 2003
    Posts
    8
    Thanks, I appreciate your help.

Posting Permissions

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