Access 2010 Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

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.
 

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.
 

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.
 

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.
 

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
 

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.
 

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 ?
 

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.
 

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.
 

OBP

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

ps OK I have found it.
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top