1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2010 Question

Discussion in 'Business Applications' started by Dschsvr, Apr 14, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. Dschsvr

    Dschsvr Thread Starter

    Joined:
    Apr 14, 2015
    Messages:
    9
    I have been fighting with access most of the day. I have a database that keeps tracks of purchases & invoices. I occasionally have to look up an invoice by invoice number. I have a query/form that does that, but the problem is that if the invoice # i type is not in the database I simply get a blank form. Is there anyway to only have the form show up when the invoice number is in the database and to have a message box pop up that says "Invalid Invoice #" if the invoice # is not in the file. I have tried, queries and macros and keep getting error code 2482. .When i convert the macro to visual basic this is the code that shows up and it doesn't work either. I am not great at VBA. ANY help appreciated. Thank you


    '------------------------------------------------------------
    ' Copy_Of_mcrInvLun
    '
    '------------------------------------------------------------
    Function Copy_Of_mcrInvLun()
    On Error GoTo Copy_Of_mcrInvLun_Err

    With CodeContextObject
    DoCmd.OpenQuery "qryInvLUN1", acViewNormal, acEdit ' Deletes data in table
    DoCmd.OpenQuery "qryInvLUN1a", acViewNormal, acEdit 'Deletes data in table
    DoCmd.OpenQuery "qryInvLUN2", acViewNormal, acAdd ' Adds data to table
    DoCmd.OpenQuery "qryInvLUN3", acViewNormal, acEdit 'Appends data to table
    If (.tblInvLUN3!ValInv = -1) Then 'Supposed to only show the form if there is valid #
    DoCmd.OpenForm "frmView Invoices & Contracts by Invoice Number2", acNormal, "", "", , acNormal
    Else
    Beep
    MsgBox "Incorrect Invoice #", vbOKOnly, ""
    End If
    End With


    Copy_Of_mcrInvLun_Exit:
    Exit Function

    Copy_Of_mcrInvLun_Err:
    MsgBox Error$
    Resume Copy_Of_mcrInvLun_Exit

    End Function
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Dschsvr, welcome to the Forum.
    I like your description, it does feel a bit like you have to fight Access. :)

    I can help you with the necessary VBA code, but before I do can I ask why you are not using a "Find Combo" to find your Invoice?
    The combo will list only those available for selection, so if it is not on the list you can't mis-select it.
    Combo's are also very fast for selection as they cut down the list for selection based on what you type in them, or you can use the side bar to quickly move around it's list.
     
  3. Dschsvr

    Dschsvr Thread Starter

    Joined:
    Apr 14, 2015
    Messages:
    9
    I am very "self taught" on Access and the only Visual Basic class i ever had was about 25 years ago (lol) - when you had to number the lines. How do i create/use a Find Combo? I am certainly ready to do this the easiest way i can.
    Thank you.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    My ID stands for Old BASIC Programmer, been there & done that.
    The find combo needs to be on the form that displays your data, you do not need any programming as the Combo Wizard (in form design view) creates it for you.
     
  5. Dschsvr

    Dschsvr Thread Starter

    Joined:
    Apr 14, 2015
    Messages:
    9
    Oh - ok. What i was trying to do was not have to open the form unless the invoice # was there. I created a "button" that ran that macro to only show the form if there was data that matched the invoice # typed in by the employee to see if we had processed that invoice already. What it does right now is bring up the form whether there is a invoice that matches or not. Then we have to click on the close form button. Just some extra steps i have been trying to remove.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, what query is the form that you open based on?
     
  7. Dschsvr

    Dschsvr Thread Starter

    Joined:
    Apr 14, 2015
    Messages:
    9
    I have a query that looks up data in a UnionQuery table. It asks the user to "Enter the invoice #". After you type in the invoice #, it opens the form that shows you the Invoice #, PO #, date, cost center, gl code, etc..... If the invoice # is not there it still brings up a blank form that you have to close. What i have been trying to figure out how to do is to have it bring up a simple message box that says "Invalid Invoice # - Do you want to try another number or quit this query." (Something along those lines.
    Before i started messing with it - this is what i had been using - the query it is based has the criteria that the Invoice Number in the table must be the same as what the user enters - "i.e. - Enter Invoice #". Is this what you wanted to know ?

    Private Sub cmdView_Invoice_or_Contract_by_Invoice_Number_Click()
    On Error GoTo cmdView_Invoice_or_Contract_by_Invoice_Number_Click_Err

    DoCmd.OpenForm "frmView Invoices & Contracts by Invoice Number", acNormal, "", "", , acNormal


    cmdView_Invoice_or_Contract_by_Invoice_Number_Click_Exit:
    Exit Sub

    cmdView_Invoice_or_Contract_by_Invoice_Number_Click_Err:
    MsgBox Err.Description
    Resume cmdView_Invoice_or_Contract_by_Invoice_Number_Click_Exit

    End Sub
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, I need a copy of the query, but without the Parameter, the trouble is you are using that paremter for the input.
    Ideally that input of the invoice number should be on an Unbound form, which the Query and the VBA can both use.
     
  9. Dschsvr

    Dschsvr Thread Starter

    Joined:
    Apr 14, 2015
    Messages:
    9
    Am willing to send you the whole database if you want (lol) :) How do i send you a copy of the query ?
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If it does not contain any personal data it can be Attached to this thread in a zipped file.
    You use the "Go Advanced" and "Manage Attachments" buttons to do so.
    Or you can email it to my, I can provide you my email address in a private mail.
     
  11. Dschsvr

    Dschsvr Thread Starter

    Joined:
    Apr 14, 2015
    Messages:
    9
    Hate to ask but can you send me your e-mail ? I believe the Security at work will not let me upload a zip file. I have pulled all the company information out of it and added some generic data into the fields.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I have private mailed you.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    No luck with that one.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I have a copy, what button are working with?

    ps OK I have found it.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The code that I have supplied is as follows
    On Error GoTo Err_Command37_Click

    Dim stDocName As String, stLinkCriteria As String
    Dim rs As Object, SQL As String, Response
    On Error GoTo Err_Command37_Click

    Response = InputBox("Enter invoice number")
    If IsNull(Response) Then Exit Sub ' User chose No.
    'MsgBox Response


    SQL = "SELECT qryUnion_By_Invoice_Number1.* " & _
    "FROM qryUnion_By_Invoice_Number1 " & _
    "WHERE strINVOICENUMBER = '" & Response & "'"
    Set rs = CurrentDb.OpenRecordset(SQL)
    If rs.RecordCount <> 0 Then
    rs.MoveLast
    rs.MoveFirst
    'MsgBox rs.RecordCount

    Else
    MsgBox "Sorry there is no Invoice with that number" & Chr$(13) & "Please choose another"
    Exit Sub
    End If
    stLinkCriteria = "strInvoiceNumber = " & Chr(34) & Response & Chr(34)


    stDocName = "frmModifyInvoicebyInvoice"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command37_Click:
    Exit Sub

    Err_Command37_Click:
    MsgBox Err.Description
    Resume Exit_Command37_Click
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1146597

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice