Results 1 to 3 of 3

Thread: Help with VBA?

  1. #1
    Join Date
    Jun 2005

    Unanswered: Help with VBA?

    Hi guys,

    Haven't been in these forums in awhile. Well, I seem to can't get an answer anywhere else, so I resort to the experts.

    Okay here's my dilemna... I have a function that lookup value on a link table (just one field). It looks like this.

    Function GetPO() As String
        GetPO = Nz(DLookup("[PONo]", "tblShipPO"), 0)
    End Function
    What I want the Function to do is this

    If GetPO already has a matching value in another table, then cycle through the records in tblShipPO until it finds a PONo that is not in the database or another table.


    GetPO = Nz(DLookup("[PONo]", "tblShipPO"), 0)
    If GetPO = DLookup("[PONo]", "tblAnotherTable") Then
    Go to next record in tblShipPO
    And So On

    How can I do this? Can I use something like For Each or is that different? Please help me write the code and explain to me.

    I hope I made sense, thanks in advance guys.
    The only true wisdom is in knowing you know nothing.

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    DLookup() takes three arguments:

    DLookup(<field to return>, <table to look in>, <search criteria>).

    You forgot to add any search criteria.

    Also, if all you're doing is checking if the value in one table doesn't exist in another, you can do this as a single query. Have a look at the unmatched records wizard.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Jun 2005
    Hi Teddy... Thanks for the response, as always.

    Okay, this is how my database is structured.

    I got a table called tblSNHeader that has a 1 to many relationship with another table called tblSNDetails. PONo is the related field.

    Now, I have a link table (text file) named lnkSNDetails that contains records with their POs. There could be like 300 records and the records are associated with 3 POs.

    Well first, I created a query that groups the PO's, so if, there are 300 records associated with 3 POs, I will only see 3 POs and total count for each PO.

    Now, all these records in the link table (lnkSNDetails) are suppose to be appended in tblSNDetails if they don't exist. But since, the relationship between tblSNHeader and tblSNDetails has enforced referential integrity. If they don't exist then, a function should create the POs found in the link table to tblSNHeader and then add the records.

    What I want the function to do is cycle through the 3 records and add them if they don't exist. This is where my trouble begins. How do I make the function (as I mentioned above), check if this PO exist, if not, add a new record in tblSNHeader, then once added or if it exist then go to the next PO in the list and add this in there and so on.

    I hope I made sense.
    The only true wisdom is in knowing you know nothing.

Posting Permissions

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