Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    37

    Unanswered: sql lookup problem

    Hi wondering if someone can suggest the best way to do the following...

    I have a combox with customer names, when 1 is selected the name is inserted into a text box on a sub form. As well as that a sql query is run and the customerID is inserted into a table (CustomerOrder).

    [CustomerOrder]
    OrderID Autonumber
    CustomerID Number


    What I need to do is get the OrderID from the table.

    Any suggestions appreciated.

    JD

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't get what you need to do here, but check into the DLookup function. It's handy for returning a single value based one criteria.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2003
    Posts
    37
    Bascilly I need to get the OrderID that is created when the customerID is inserted into the table.

    The customerID is not unique so cant use it to find the orderID. I was thinking of getting the last value inserted into the database.

    JD

  4. #4
    Join Date
    Dec 2003
    Posts
    37
    still no luck with this one

    anyone got a suggestion ? anything to push me in the correct direction

    JD

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi JD

    Teddy was, I think, pointing you in the right direction. However, maybe the DMax function will be best suited to the particular problem. I am presuming that the autonumber is incremental and this function will be run immediatley after the insertion, otherwise the following fails. It also assumes you have the CustonerID value in a text box (called txtCustomerID) on a form:

    try:
    Dim lngOrderID as long

    lngOrderID = dmax("OrderID", "CustomerOrder", "customerID = '" & me.txtCustomerID & "'")

    Hope that does it!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Given the additional criteria, I think the DMax() function would indeed be a more efficient way around the situation. I should add that depending on the timing of the function, you may need to increment the value returned by DMax() by 1. The reason I suggest this, is until you move to the next record in your subform, the record does not exist for the purposes of a dblookup. Therefore you may have to "artificially" determine what that next number is going to be. The statement then becomes:

    lngOrderID = dmax("OrderID", "CustomerOrder", "customerID = '" & me.txtCustomerID & "'") + 1
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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