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]
>
Tidak ada komentar:
Posting Komentar