Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Angry Unanswered: hOW TO FETCH THE LATEST Id from the table

    Hi Every one:

    I have one problem adn solution I have is not that great.
    i have A table [Inventory Work Order].
    and a field [Work Order ID] , its autonumber field.
    when inventoryis not in stock , it places work order by entering new Id in that table. How do i fetch the id which was just inserted in table?

    Here is my approach
    1st step= work order has been placed under y Sales Order Item out of stock form.

    itemNo = Me![Item Number]
    Desc = Me![Description]
    If Not IsNull(formname![Comments]) Then
    Instructions = formname![Comments]
    SQLStmt = "Insert into [Inventory WorkOrder] ([Item Number],[Work Order Date],[Sales Order Number],[Description],[Quantity In Stock],[Quantity Ordered],[Quantity On Back Order],[Instructions]) Values ('" & itemNo & "','" & Date & "','" & Forms![Sales Order]![Sales Order Number] & "','" & Desc & "','" & Me![Quantity in Stock] & "','" & Me![Qty Ordered] & "','" & Me![BO Qty] & "','" & Instructions & "')"
    curdb.Execute (SQLStmt)
    initiate = True
    Call fetchWOID
    End If
    If IsNull(formname![Comments]) Then
    SQLStmt = "Insert into [Inventory WorkOrder] ([Item Number],[Work Order Date],[Sales Order Number],[Description],[Quantity In Stock],[Quantity Ordered],[Quantity On Back Order]) Values ('" & itemNo & "','" & Date & "','" & Forms![Sales Order]![Sales Order Number] & "','" & Desc & "','" & Me![Quantity in Stock] & "','" & Me![Qty Ordered] & "','" & Me![BO Qty] & "')"
    curdb.Execute (SQLStmt)
    initiate = True
    Call fetchWOID
    End If
    DoCmd.Close A_FORM, "ySales Order Item Out of Stock"

    End If
    Exit Sub




    'Second Step=Funcion fetchWOID

    Global ID As Long
    Public Function fetchWOID()
    'I added this portion Faisal Shah
    If ErrorTrapping = True Then
    On Error GoTo fetchWOID_Err_Handler
    End If
    Dim SQLStmt As String
    Dim adrs As Recordset
    Dim curdb As Database
    Set curdb = CurrentDb()
    SQLStmt = "Select * from [Inventory WorkOrder]"
    Set adrs = curdb.OpenRecordset(SQLStmt, dbOpenDynaset)
    adrs.MoveLast
    If Not IsNull(adrs![Work Order ID]) Then
    ID = adrs![Work Order ID]
    End If
    adrs.Close
    Set adrs = Nothing
    curdb.Close
    Set curdb = Nothing
    Exit Function
    fetchWOID_Err_Handler:
    DispError "Fetching Work Order ID", "Faisal Functions2"
    Exit Function
    End Function


    third step =to Open ywork order form according to global vriable ID


    If initiate = True Then
    DoCmd.OpenForm "yworkOrder2", acNormal, , "[Work Order ID]=" & ID
    End If




    now its opened the form correctly according to ID , then when it reached to 24, it started skipping one number and would open the form with previous ID. ...
    any solution to this problem?
    thanx in advnace

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    You could alter your SQL statemetn to just return the last entry. This might return one entry below as your ID number may not in fact be saved at this point, so you add 1.

Posting Permissions

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