David-
A Requery of the subform control should show the new records. You could follow that with a SetFocus to the subform control.
Me.NameOfSubformControl.Setfocus
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 14, 2016, at 7:19 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
HAH and LOL!
That "crap" keyword is the only VBA code I have clearly understood during this message string!
Fantastic, it all works and I thank you for ALL of the education the past few days!
I have another question though (surprised?). It works but it is kind of klunky for the user. The combo to select the template record is the last record on the main form. So the subform is not filled in until the user tabs out of that combo. Since there is no main form control after the combo, it will not be obvious to the user that he has to tab out of the combo to get the subform to fill.
Should I add a command button following the combo with text something like "Create New Report?" Or just add a statement to the AfterUpdate Event of the combo to move the focus to the subform?
And THANK YOU again.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Crap. We're testing for Me.NewRecord in AfterUpdate, but it won't be set anymore at that point.
Do this:
1. At the top of the form module, declare a module variable like this:
Dim intNew As Integer
2. In the BeforeUpdate event of the form, do this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me. NewRecord Then
intNew = True
Else
intNew = False
End If
End Sub
If there is already other code in Before Update, put that new code at the top.
Then in your After Update, instead of testing for Me.NewRecord, do this:
If Not intNew Then
' put any custom error here about not creating a from the template
' and follow that with:
Exit Sub
End If
' Turn off the new indicator
intNew = False
' Put your copy the template code here:
End Sub
Before Update will still see the NewRecord indicator and set the module variable to pass to the AfterUpdate event.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 14, 2016, at 6:16 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have already moved it back to the form AfterUpdate event, and now I am getting the custom error message that a new record was not created so no new template records were created.
So somehow it is not recognizing that a new service report record was created, even though for certain new records are being created, just with no details.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Remember you moved the code to the combo box After Update. If the record hasn't been saved yet (Me.Dirty = True), then you'll have to save it. Or move the code back to the form AfterUpdate if you've removed all those bogus Me.Requery statements.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 14, 2016, at 5:58 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Yes, both ID fields have values. ServiceReportID is an autonumber and ServiceTemplateID is selected in the combo box on the main form which is bound to ServiceTemplateID.
After using the debug.print I could see that I had omitted a space after the SELECT key word. Interesting that the compiler did not throw an error on that. After correcting that, I am now getting:
"Unexpected error 3201 cannot add a record because a related record is required in tblServiceReports"
However, when I look at the SQL from the immediate window, the correct (current, new) record number is present in the SELECT statement. Has this record not yet been written to the tblServiceReports yet?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Right after you assign the SQL to the string variable, add:
Debug.Print strSQL
That will dump the contents to the Immediate Window (CTRL+G). You can copy and paste the assembled SQL string into the SQL view of a new query, then try to switch to Design view to check the syntax. That should give you a better idea of what is wrong. Is it possible that either ServiceReportID or ServiceTemplateID do not have a value? That will cause an error in the SQL.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 14, 2016, at 5:10 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I had a "me.requery" in a couple of AfterUpdate events on combo boxes. I did not realize until this happened that ME refers to the form (not the control) and that me.requery would save a record and requery the table that the form is based upon. I corrected those and moved the code to the AfterUpdate for the Templates combo. That combo is bound to the TemplatesID field of the tblServiceReports.
When I attempt to add a new record, I get "unexpected error, 3134 Syntax error in INSERT INTO statement".
So the strSQL is compiling but still throwing an error code for some reason.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
After Update for the form should work - unless you have code somewhere that's forcing a save at some point. But yes, using the After Update of the Templates combo should also work. Is the combo bound (has a Control Source)? And where is the combo located?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 14, 2016, at 4:48 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I would never have noticed the different quote marks; didn't even know there was such a thing as smart quotes and have no idea how they got there. Initially I copied your VBA code into OneNote and then pasted into my class module. Modified multiple times after that trying to get it to work, so no telling.
Anyway, I changed all the quotes and added the space - no joy. I gave up, deleted the entire sub and started from scratch. No syntax error now. No idea what is different in the strSQL now. It looks exactly the same to me. I then corrected a couple of small errors (no end if; Done:) and now it compiles and runs. Hallelujah.
However, the process needs to be modified. Since this is on the form AfterUpdate procedure, the code runs as soon as a new record is created. And apparently a record is created as soon as the Keys/Indexes are satisfied? In this case, as soon as the CustomerID and EquipmentID fields are completed, a record is created and the sub fires. At this point the user has not yet selected a template. I use customer and equipment to filter the Templates combo box, so I can't put the Templates combo box ahead of the Customer and Equipment fields.
It appears to me that I can move this sub to the AfterUpdate event for the Templates combo and it would still do all the same things. Correct?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
You can do it in the Immediate Window (CTRL+G) by copying the string and putting a question mark in front of it, then press enter:
?"INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) " & _
"SELECT " & 5 & ", SamplePointID, ParameterID " & _
"FROM tblServiceTemplates " & _
"INNER JOIN tblServiceTemplateDetails " & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
"WHERE tblServiceTemplates.ServiceTemplateID = " & 5 & ";"
The window will then return it all as one string if the syntax is correct (note that I replaced the references to ServiceReportID and ServiceTemplateID with constants to make it work):
INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) SELECT 5, SamplePointID, ParameterID FROM tblServiceTemplates INNER JOIN tblServiceTemplateDetails ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID WHERE tblServiceTemplates.ServiceTemplateID = 5;
You can then copy and paste that into the SQL window of a new query to see if it parses OK.
But I had problems when I went to test using a direct copy and paste from email. Note that SOME of your double quotes are "smart quotes" - not the simple double-quote character. Notably the quote at the beginning of every line except the INNER JOIN line and at the end of the first, third, fifth, and sixth lines. You're also missing a space after the last ampersand.
Fix those quote problems and try again!
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 13, 2016, at 4:29 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John, I have been reading several sites about SQL looking for the source of the syntax error. I thought i had found the error when one place said the SQL has to end with a semi colon. I added that and still get the syntax error when I attempt to compile. Here is my SQL now:
strSQL = "INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) " & _
"SELECT " & Me.ServiceReportID & ", SamplePointID, ParameterID " & _
"FROM tblServiceTemplates " & _
"INNER JOIN tblServiceTemplateDetails " & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
"WHERE tblServiceTemplates.ServiceTemplateID = " & Me.ServiceTemplateID &";"
"SELECT " & Me.ServiceReportID & ", SamplePointID, ParameterID " & _
"FROM tblServiceTemplates " & _
"INNER JOIN tblServiceTemplateDetails " & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
"WHERE tblServiceTemplates.ServiceTemplateID = " & Me.ServiceTemplateID &";"
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
I copied your code into the module and I am still getting a "syntax error" code.
I see one thing that you did differently, is that the form controls are not within quotes. I also see that I had a couple of places where I failed to have a space between concatenated strings.
Someone needs to create a tool that will take strSQL compound strings and strip out the quotes so you can see if the SQL statement actually makes sense or not. Or does that already exist somewhere?
Anyway, still have the syntax error and the entire block of code with strSQL is highlighted.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
Try this:
strSQL = "INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) " & _
"SELECT " & Me.ServiceReportID & ", SamplePointID, ParameterID " & _
"FROM tblServiceTemplates " & _
"INNER JOIN tblServiceTemplateDetails " & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
"WHERE tblServiceTemplates.ServiceTemplateID = " & Me.ServiceTemplateID
"SELECT " & Me.ServiceReportID & ", SamplePointID, ParameterID " & _
"FROM tblServiceTemplates " & _
"INNER JOIN tblServiceTemplateDetails " & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
"WHERE tblServiceTemplates.ServiceTemplateID = " & Me.ServiceTemplateID
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 12, 2016, at 11:04 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John, I am trying to get the templates to work using the SQL code you provided and I am really struggling (hours). Since the combination key of CustomerID and EquipmentID is not unique I didn't see how the SQL could work when there was more than one template for Customer/Equipment. So, to make it easier and better, I placed another field in tblServiceReports - ServiceTemplateID. So now, each time that a new service report record is created, the user has to select the template which it is based upon.
Here is the strSQL that I am putting into the AfterUpdate procedure:
strSQL = "INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID)" & _
"SELECT Me.ServiceReportID,SamplePointID,ParameterID" & _
"FROM tblServiceTemplates" & _
"INNER JOIN tblServiceTemplateDetails" & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID" & _
"WHERE tblServiceTemplates.ServiceTemplateID = Me.ServiceTemplateID"
"SELECT Me.ServiceReportID,SamplePointID,ParameterID" & _
"FROM tblServiceTemplates" & _
"INNER JOIN tblServiceTemplateDetails" & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID" & _
"WHERE tblServiceTemplates.ServiceTemplateID = Me.ServiceTemplateID"
When I try to compile, the error "Syntax Error" appears and the entire block of code above is highlighted. Sometimes the INTO keyword is highlighted and the error "expected end of statement" shows up.
I have spent hours and don't know how to correct it.
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (37) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar