Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Unanswered: Passing a Max Value to Field on Form

    Hi,

    I have this table [Details] in Access that monitors a car's activity in and out of a company.

    Car No | KM out | Date out | Time out | KM in | Date in | Time In
    ------------------------------------------------------------------------
    11 | 22 | 12/12/2004 | 12:00 | 55 | 12/12/2004 | 13:00

    In a form [Details], I have a combobox of the availabe Car numbers where a user would select a car number. I want to:

    As soon as the user selects a car number from the combobox, the field of the KM out should automatically be filled by the value of KM in of the last time the car was used i.e. I have to get the maximum date and time for each car (because one car can be used in the same day several times).

    I know I have to fill the after_update function of the car No with this query

    Froms!Details.KMout= SELECT d1.KMin
    FROM details AS d1
    WHERE (((d1.TimeIn)=(SELECT Max (d2.TimeIn)
    FROM details AS d2
    WHERE d2.DateIn=(SELECT max(d3.datein)
    FROM Details AS d3
    where d3.carnumber=d2.carnumber)
    and d2.carnumber=d1.carnumber)));

    But i dont know how to do it in VB !

    Can someone help me please?
    Thank you,
    Mahi

  2. #2
    Join Date
    Nov 2004
    Posts
    108

    recordset

    use a Recordset, since you are using Access you can use DAO which is optimized for Access look for help in MSDN on Dao connection and recordset

    I always use ADO (with SOL Server) it should also work for access
    Code:
    Dim conn as new ADODB.Connection
    conn.Open connStr          'global variable containg a string to connect to the DB
    me.KM=conn.Excecute ( _
      "SELECT   d1.KMin " _
    & "FROM     details d1 " _
    & "WHERE    (((d1.TimeIn) = " _
         & "(SELECT Max (d2.TimeIn) " _
         & "FROM details d2 " _
         & "WHERE d2.DateIn = " _
              & "(SELECT max(d3.datein) " _
              & "FROM Details d3 " _
              & "WHERE d3.carnumber=" & COMBO.TEXT & " and d3.carnumber=d2.carnumber) " _
              & "and d2.carnumber=d1.carnumber)))").fields("KM")
    conn.close
    Set conn = Nothing
    to err is human ; to really mess things up requires a computer

Posting Permissions

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