Results 1 to 8 of 8
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Pop Up message. Record already created.


    When any payment is made a PAYMENT is processed in the PAYMENT TABLE, for every PAYMENT a PURCHASE INVOICE must follow. A control(command button) was created that starts an APPEND QUERY. The append query adds the fields needed from PAYMENT to the INVOICE PURCHASE table. In the INVOICE PURCHASE table there is a field keeping the PAYMENT number, so reference can be held, the append query put it there.

    The issue: Not to duplicate the PURCHASE INVOICE. When the form of PAYMENT is open and click on CREATE INVOICE button, access should see if the existing PAYMENT number on the open form is already in the PURCHASE INVOICE table in field PmtID. If it finds it, it should stop the action and not allow to process the same PURCHASE INVOICE again.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Assuming your payment number is on the open form containing the payment button, your button click code could check for the existence of the payment number via DLookup. Not sure what action you'd take other than just a message box without knowing more about the process. You might find this page useful for this and many other functions
    https://www.techonthenet.com/access/functions/
    Last edited by Micron; 01-28-2018 at 08:07 AM. Reason: added info

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I wanted to admit that I am lazy. I am not. I read and saved the page you referred me to. As a novice only using video tutorials, I am proud of my development. I can do a lot with design, and you guys on the forum really help, especially with the VBA code. I know what I read but but haven't learnt the VBA yet. Yes the Payment No will be in the PURCHASE INVOICE table. I need code or expression(btn01) that if on Form f02Payment field T006(open at the time), if the same Payment number is found in query q02InvPurchase field PmtID it should refuse execution of Append query and popup that INVOICE ALREADY CREATED.

    I believe you that Dlookup should do it, but I fall short in VBA.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm afraid not all is clear to me. On one hand, you're saying the number to look up is in PURCHASE INVOICE table; on the other, in query q02InvPurchase. Is the check to see if it's in the query records, and if not, it goes into the table? If so, maybe this is the start of your button click event code (you know how to create the event?):

    Code:
    If IsNull(DLookup("PmtID","q02InvPurchase","PmtID = " & Me.T006)) Then
      msgbox "Payment Number already exists."
      Exit Sub
    End If
    'otherwise, code to execute the query comes next
    I'm assuming that whatever field that Me.T006 is bound to (query? table?) is of the number data type and not text, otherwise that won't work.
    You should read up on naming conventions and adopt one. Spaces in object names are generally avoided, plus default wizard names or names like Text01 will prove to be pretty vague later on. Here's a couple of approaches, followed by advice on words to avoid in names.

    https://access-programmers.co.uk/for...d.php?t=225837
    http://access.mvps.org/access/general/gen0012.htm
    http://allenbrowne.com/AppIssueBadWord.html
    Last edited by Micron; 01-28-2018 at 01:17 PM. Reason: code correction

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't understand the process. A payment is made before a purchase? Is this a 1-to-1 relationship? If so, why have two tables?

    If you don't want to allow duplicate payment numbers in PurchaseInvoice table, set the field as Indexed (Duplicates No).

    Code like:

    If IsNull(DLookup("PaymentNum", "PaymentInvoice", "PaymentNum='" & Me.tbxPayNum & "'")) Then
    MsgBox "Invoice already created."
    Else
    ...
    End If

    or alternative:

    If DCount("*", "PaymentInvoice", "PaymentNum='" & Me.tbxPayNum & "'") = 0 Then
    ...

    Both assume PaymentNum is a text field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you for your effort. I will try this and come back. I am an accountant and businessman for more than 30 years. I do have to do other work even though I am passionate to spend every hour getting better at Access. My choice is to process a PURCHASE INVOICE for every credit purchase as well as every cash purchase, without duplicating it in the final figures. I proudly managed that. I will make sure the data type is number. Cash Purchases or Sales in the old times with GAAP always goes with an invoice and a receipt at the same time. I will apply this to receipt and Sales Invoices as well, but not to confuse let us just focus on the Purchase side.

    To answer you. As soon as one finished processing the Payment for which my supplier should give me a receipt, and the Payment Advice in my system bring that into book. The Supplier should also give me an invoice which he calls a sales invoice, and I call it a Purchase Invoice. As soon as I processed the Payment and the form for payment is open the Payment Number is created lets say PMT000243. The user the click on the button saying CREATE PURCHASE INVOICE. The append query appends the created info to the PURCHASE INVOICE table. Including in the append is to carry forward the Payment no to a field PmtID in the Purchase. Sorry for my repeat. So if it was done earlier this VBA code should then stop it.

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    June 7. Thanks for the effort.I will test and come back. Hope you can read my reply to Micron.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    I will make sure the data type is number
    If this "PMT000243" is what you're referring to, you cannot. Number field types cannot contain alpha or most other special characters. June's example was for text (sometimes aka alphanumeric) and will work with text values. The difference is mainly in the way the parts of the expression are delimited for text
    If you're using alpha prefixes to distinguish between Payment and Receipts, (for example REC000123 is for receipts) you might be better off separating the alpha from the numeric and stringing them together in a form control.

    Right now, I don't know where this is at or where it's going.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-24-2017, 09:03 PM
  2. Replies: 3
    Last Post: 07-24-2015, 11:24 AM
  3. Action on record before PK is created (record saved)
    By chris.williams in forum Forms
    Replies: 4
    Last Post: 09-14-2012, 10:41 PM
  4. Replies: 2
    Last Post: 03-28-2012, 02:41 PM
  5. Who created new record
    By ducecoop in forum Access
    Replies: 10
    Last Post: 10-28-2010, 02:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums