Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 29th, 2003, 08:21 PM
Authorized User
 
Join Date: Aug 2003
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default Generating a report from 'after update'

I am trying to generate a duplicate report to warn the user that they have entered a duplicate record based on at least two parameters after they update a field in a form.

Any help would be greatly appreciated.

Mike
__________________
Mike
 
Old August 30th, 2003, 11:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Mike,

I'm making two assumptions here:

1. You're using a bound form.
2. By "report" you mean error message dialog box. You'd want your
   user's to have instantaneous notification I suspect.

What I'm thinking is that you can use the form's error event to disply a user-friendly error message dialog when a user tries to write duplicate values to fields defined in a multi-field, unique index (containing the fields that define your "at least two parameters.")

First, you'll need to create the index. Build a multi-field, unique index on the table that includes the fields (as many as you like) which define a duplicate record. This is done through the Indexes dialog in table design view. Just give the index a name, assign your "duplicate-record-defining" fields to it, and set its 'Unique' property to yes.

Second, add this code behind your form:


Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const errDuplicateIndexValue = 3022
    Dim strMsg As String

    If DataErr = errDuplicateIndexValue Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were uncuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Record."

        Me.txtText1.SetFocus

        Response = acDataErrContinue
    Else
        Response = acDataErrDisplay
    End If

End Sub

This will display a more user friendly error message than the Jet default error string which reads:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Hope this is in the vacinity of what you're looking for.

Bob

 
Old August 30th, 2003, 02:47 PM
Authorized User
 
Join Date: Aug 2003
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks for your suggestion. I inserted the coded but it returned the following compile error;

Me.txtText1.SetFocus
"Method or data member not found"

Mike
 
Old August 30th, 2003, 03:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Mike,

Me.txtText1.SetFocus is simply a reference to one of the textbox controls on the form I used to test the code. The names of the controls on your form will be different. 'Me' is an abbreviated means of referring to the current form, and the SetFocus method moves the focus back to (makes active) the txtText1 textbox control on that form.

You can delete or comment out that line of code if you like, or change txtText1 to the name of one of the controls on your form that you would like focus to return to when you close the error message dialog.

It can be helpful to the user, after they have entered invalid data in a control, to return focus to the control and clear the invalid data through code, as in:

Me.txtText1.SetFocus
Me.txtText1.Text = ""

Since you are using more than one field in your duplicate record definition, it's kind of a crap shoot I guess which control you return focus to.

Let me know if it gives you any other problems.

HTH,

Bob

 
Old August 30th, 2003, 06:00 PM
Authorized User
 
Join Date: Aug 2003
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks a lot! That works great. Can you recommend a good reference book for working with VB for Access?

Mike
 
Old August 30th, 2003, 06:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Mike,

You're welcome! Glad you got it going.

The two VBA references I've spent the most time in are:

"Access 2000 Developer's Handbook", by Getz, Litwin and Gilbert (2002 ed. available)
"VBA Developer's Handbook", by Getz and Gilbert

The first is Access specific and should be hit first. It covers Access topics not covered by the latter, like Access SQL and ADO/DAO. The latter is about the "universal" VBA language, wherever it shows up, and goes into greater detail on generic language features like string manipulation, class creation, searching and sorting, file I/O, etc. There's defintiely overlap.

For the sake of mentioning it, Access development is done using VBA (Visual Basic for Applications), not VB (strictly speaking) which is the stand-alone programming system that ships with Visual Studio. VBA has the same core components as VB, but lacks the forms engine and compiler (hence, no stand-alone components creation). The language engines of the two are equally powerful in most respects. Its just that VBA can only live as a hosted component in some other COM-based application (like Access).

Enjoy the tomes! :)

Bob






Similar Threads
Thread Thread Starter Forum Replies Last Post
Update text in the report after generating Rana AbuMari BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 0 February 24th, 2008 04:20 AM
Is database necessary for generating report. radhekrishna BOOK: Professional Crystal Reports for VS.NET 0 January 8th, 2007 06:10 AM
Generating and loading report method Access 1 June 28th, 2005 10:45 AM
generating report in pdf format. kavithaatpass J2EE 1 January 4th, 2005 09:59 AM
Generating a report from more than one query lic023 Access VBA 1 March 31st, 2004 06:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.