Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    102

    Unanswered: Lookup, if exist then don't add.

    Okay here's my problem. I created a form (frmPOEntry) that has unbound controls that are bound to controls which has data bound to a temp table (did that make sense). The table that those controls are bound to has a subdatasheet (tblTodaysOrder) that is link using today's date. Now, when I enter a PO Number on the form, and I click the "Create PO" button (command button), a message box will pop up with vbYES and vbNo buttons. If vbYes is chosen, then a PO is created on the table (tblPONumber) and that PO is created with today's date, which then gets attached to the subdatasheet. Now all the records on the subdatasheet has that PO. I have a query that combines both the tblPONumber and its subdatasheet. When the PO Entry form is closed, the query runs and appends data to a seperate table (tblOrderHistory). Now, what I want to do is when the user clicks the "Create PO" button on the form (POEntry), I want to insert a code in there that searches the table 'tblOrderHistory' and if the PO number is already in tblOrderHistory then prompt a msgBox saying "Duplicate PO, please change PO number".

    Here are the frmPOEntry objects:

    Here's the code I have in the form

    Private Sub cmdButton011_Click()
    On Error GoTo Err_cmdButton011_Click


    Date = txtBox001
    PONumber = txtBox002
    ProcessingStaff = CurrentUser()
    Comments = txtBox003

    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "Are you sure you want to create this PO?"
    Style = vbYesNo + vbQuestion + vbDefaultButton2
    Title = "Create PO?"
    Help = "PO.Help"
    Ctxt = 1000

    If IsNull(Me![PONumber]) Then
    MsgBox "Please Enter PO Number."
    Else
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    End If
    If Response = vbYes Then
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.GoToRecord , , acNewRec
    MsgBox ("You have created a new PO")
    Else
    DoCmd.CancelEvent
    End If


    Exit_cmdButton011_Click:
    Exit Sub

    Err_cmdButton011_Click:
    MsgBox Err.Description
    Resume Exit_cmdButton011_Click

    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Evaluate DCount("[PO Number]", "tblOrderHistory", "[PO Number] = " & YourPoVariable) for anything > 0. Pop that in an if statement and take action accordingly.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Jun 2005
    Posts
    102
    Quote Originally Posted by Teddy
    Evaluate DCount("[PO Number]", "tblOrderHistory", "[PO Number] = " & YourPoVariable) for anything > 0. Pop that in an if statement and take action accordingly.

    First of all, I'd like to thank you Teddy for your response and everyone else who will response.

    Teddy, I'm new to VBA programming most of the codes I posted above I just learned from Access documentations. I'm not sure how to read your post, but can you clarify what you mean? Thx!

Posting Permissions

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