ACCESS FEED: RE: [MS_AccessPros] Re: Indexes

Bill-

What's the SQL of the three queries? Do they have parameters in them? If they
do, you can't use Execute. You must set up the SQL in code with the parameters
resoved, then execute that.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

--------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of agent1of6
Sent: Friday, May 18, 2012 10:50 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Indexes

John,
I do not quite understand all of this so not I am in the learning mode again. I
am glad the colors change when this is inserted into Access. I put the correct
query names in there.

When I hit the button on the form this is the message I get.

Unexpected Error: 3061, Too few parameters. Expected 1

Below is the exact code copied to this email.
I am not sure what the error code means but I am going to keep working on it but
I thought I would send it as well. I did not find much good informaion on the
Execute Method. Is there some in your book?

Thanks for all you work.
Bill

Private Sub postG1D1_Click()
Dim intTrans As Integer, db As DAO.Database

On Error GoTo MyErrorTrap

Set db = CurrentDb

' Tell error trap we're starting a transaction
intTrans = True
' Start the transaction
BeginTrans

' Run the queries
db.Execute "aq_Day1Game1", dbFailOnError
db.Execute "aq_EventPosting2Home", dbFailOnError
db.Execute "aq_EventPosting2Visitor", dbFailOnError

' Commit the transaction
CommitTrans
' Turn off the flag
intTrans = False

MyExit:
Exit Sub

MyErrorTrap:
' Display the error (You can also test the error # and display a custom msg)
MsgBox "Unexpected error: " & Err & ", " & Error
' If transaction in progress, roll it back
If intTrans Then Rollback
' Turn off the flag so we don't try to rollback again
intTrans = False
' Bail
Resume MyExit

End Sub

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Bill-
>
> If you're running three queries in succession, not only should you be using
> Execute, but you should also be using a Transaction around the three queries
so
> that if any one of them fails, you can roll back everything. The basic code
is:
>
> Dim intTrans As Integer, db As DAO.Database
>
> On Error GoTo MyErrorTrap
> ….
>
> Set db = CurrentDb
>
> ….
>
> ' Tell error trap we're starting a transaction
> intTrans = True
> ' Start the transaction
> BeginTrans
>
> ' Run the queries
> db.Execute "Query1", dbFailOnError
> db.Execute "Query2", dbFailOnError
> db.Execute "Query3", dbFailOnError
>
> ' Commit the transaction
> CommitTrans
> ' Turn off the flag
> intTrans = False
>
> ….
> MyExit:
> Exit Sub
>
> MyErrorTrap:
> ' Display the error (You can also test the error # and display a custom msg)
> MsgBox "Unexpected error: " & Err & ", " & Error
> ' If transaction in progress, roll it back
> If intTrans Then Rollback
> ' Turn off the flag so we don't try to rollback again
> intTrans = False
> ' Bail
> Resume MyExit
>
> End Sub
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> ---------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Singer
> Sent: Thursday, May 17, 2012 11:34 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Indexes
>
>
> John,
>
> Actually I will have to run at least three append queries for each game. I
> have two tables in a one to many relationship. One to schedule the
> FACILITY, DATE, TIME, LOCATION and the other two to add the EVENT
> PARTICIPANTS (the many side) The other two queries (one for each team) for
> the many side table grabs the PK from the first table and adds it to the
> record in the Many table. All of that works and the relationship is
> determined correctly. I just need to prevent duplicates.
>
> I do not know what the execute method is so I guess I will be reading up on
> that tonight.
>
> Bill Singer
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Thursday, May 17, 2012 4:17 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Indexes
>
> Bill-
>
> In each case, are you inserting one or multiple rows? Actually, if you use
> the
> Execute method of a Database object, you can trap the error in code by
> including
> the dbFailOnError parameter. Your On Error routine can examine the error
> code
> and display an appropriate message.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> ----------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Singer
> Sent: Thursday, May 17, 2012 10:10 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Indexes
>
> John,
>
> John, Hopefully this will answer your first question.
>
> This is going to be tricky to explain. I wish I could send an attachment.
> I am working on this sports database. As an helpful game scheduling tool to
> the administrator of the league, I have created a form that will show a 10
> game schedule for a league with 8 teams. (very common where I am) It is a
> standard game schedule for an 8 team league. (Team1 vs. Team2, Team3 vs.
> Team4, etc)
>
> Each day has 4 games with two teams per game. On that form you can also
> enter dates across the top and game times on the left side. It looks
> similar to an excel spreadsheet. However, the form is just a visual
> representation of how a schedule could look and it is only achieved by me
> rearranging the text boxes for each day. There are only 8 different text
> boxes, I just make copies and re-arrange them for each game date to visually
> show how the team match ups will look. The underlying table only had 8 TEAM
> fields and not 80, which is how many show on the form.
>
> However it would be great if the administrator could schedule the games from
> that form. For example, if the administrator liked how the schedule looked
> for GameDay1 he could click the button on the form and it would run an
> append query that would grab the DATE, TIME, LOCATION, & TEAMS for the
> GameDay1 schedule and post them to the SCHEDULING table. I have the append
> queries built (and they work) and I know how to do buttons. The issue I
> want to avoid is a person pushing the button multiple times and scheduling
> the same game multiple times. If they do push the button multiple times I
> want a nice message to show up.
>
> Hopefully that makes sense.
>
> I will get back with the other info when I get home.
>
> Thanks,
>
> Bill Singer
>
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Viescas
> Sent: Thursday, May 17, 2012 1:52 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Indexes
>
> Bill-
>
> The only way to trap the error is to test for a duplicate in Before Update,
> issue your own error message, and cancel the save. Why are you running an
> Append query? If the user is entering the new data in a form bound to the
> table, that shouldn't be necessary. I would need to know the structure of
> your
> table (at least the key fields) to help you with the Before Update code.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Singer
> Sent: Thursday, May 17, 2012 8:19 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Indexes
>
> I have created a unique index on a table to prevent duplicate records from
> being added,(you can't play two events at the same facility at the same
> time) however the error message that comes up is not very user friendly. I
> am wondering if there is a way to change that message?
>
> The other option is to put a test and message box in the before update
> property of the button that runs that append query that updates the table.
> However this programming is a bit more difficult for me.
>
> Thanks for steering me in the right direction.
>
> Bill Singer
>
> MN
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>