ACCESS FEED: Re: Re: [MS_AccessPros] Run an append query automatically

John-

By the way, this is code with append query which works, but with pop ups.

Private Sub DayServiceID_AfterUpdate()
On Error GoTo ErrHandle

If Me.DayServiceID = 4 Then

Me.OperatorID = ELookup("CompanyEmployeeID", "tblDayServiceOperator", "DayServiceID=" & DayServiceID & " AND CountryID=" & ELookup("CountryID", "qryPartItineraryCityTourOperatorSelect", "PartItineraryCityTourID=" & Me.Parent.PartItineraryCityTourID))

If IsNull(ELookup("PartItineraryCityTourRoomID", "tblPartItineraryCityTourRoom", "PartItineraryCityTourID=" & PartItineraryCityTourID)) Then
DoCmd.OpenQuery "qryAppendRoom", acViewNormal, acEdit
Me.Requery
Forms!frmGroupBooking!subPartItineraryCityTour.Form.Refresh
Else
MsgBox ("Either the Mother Roominglist has no names, or you have names in your sub-Roominglist already.")

End If

Else

Me.OperatorID = ELookup("CompanyEmployeeID", "tblDayServiceOperator", "DayServiceID=" & DayServiceID)
End If

Me.Refresh

ErrExit:
    Exit Sub

ErrHandle:
    Resume ErrExit
End Sub

Regards,

Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-11-01 21:20
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Run an append query automatically

Kevin-

strSQL = "INSERT INTO tblPartItineraryCityTourRoom ( RoomTypeID, NumberOfRooms, PartItineraryCityTourID ) " & _
"SELECT tblGroupBookingRoom.RoomTypeID, tblGroupBookingRoom.NumberOfRooms, " & _
Me.Parent.PartItineraryCityTourID & " AS PartItineraryCityTourID " & _
"FROM tblGroupBookingRoom " & _
"WHERE (((tblGroupBookingRoom.GroupBookingID)= " & Me.Parent.GroupBookingID

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

On Nov 1, 2015, at 1:19 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
Here is the SQL of the Append query that works:
INSERT INTO tblPartItineraryCityTourRoom ( RoomTypeID, NumberOfRooms, PartItineraryCityTourID )
SELECT tblGroupBookingRoom.RoomTypeID, tblGroupBookingRoom.NumberOfRooms, [forms]![frmGroupBooking]![PartItineraryCityTourID] AS PartItineraryCityTourID
FROM tblGroupBookingRoom
WHERE (((tblGroupBookingRoom.GroupBookingID)=[forms]![frmGroupBooking]![GroupBookingID]));
Best Regards,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-11-01 19:55
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Run an append query automatically

Kevin-

What is the SQL of the Append query that works?

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

On Nov 1, 2015, at 12:06 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
You are right. This code is running in a subform. the main form is frmGroupBooking, and there're many subforms attached.
One of the subform "subPartItineraryCityTour" has two parellel subforms, subPartItineraryCityTourBookingRoom and subPartItineraryCityTourServiceBooking. Both are linked to subPartItineraryCityTour by an outer unbound textbox "PartItineraryCityTourID", which gets data when record got focus. What I want to achieve is that when a hotel booking service is chosen from subPartItinerararyCityTourServiceBooking, then append data from tblGroupBookingRoom to tblPartItineraryCItyTourBookingRoom automatically. By the way in tblParItineraryCityTour, I will use the Last CityID as overnight city to book hotel.

I tried many ways using strSQL to append data, it all failed, then I use a append query to get this done. But what annoys is that, the append query always prompt pop ups twice. Anyway it works. Yesterday I tried to use CurrentDb.Execute "qryAppendRoom" , it fails again.

I think it just that the strSQL thing always shuts me down...

Thanks a lot.
Best Regards,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-11-01 18:36
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Run an append query automatically

Kevin-

Is this code running in the subform or the form? I would guess the subform from you reference to Me.Parent to get to something on the outer form.

What is the name of the field on tblGroupBookingRoom that you're trying to filter, and what's the name of the control (outer or subform?) that you want to use as the filter? Also, is that field text or a number?

I would think that without adding the extra filter, you would get too many rows returned by the SELECT clause to insert into the table. And if this data needs to be related, why aren't you inserting the key values?

What's the structure of tblPartItineraryCItyTOurBookingRoom?

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

On Nov 1, 2015, at 3:31 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I need this "PartItineraryCityTourID" passed from sub continuous form current record, so the tblPartItineraryCityTourBookingRoom would have a link field to "tblPartItineraryCityTour". I tried many ways, it just doesn't append anything.
Best Regards,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-10-31 18:34
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Run an append query automatically

Kevin-

Your SQL is syntactically incorrect and should have generated an error. You will not see the error if you included On Error Resume Next in your code. You have commas in the wrong places and are missing blanks between keywords. Here's my attempt to fix it:

strSQL = "INSERT INTO tblPartItineraryCityTourBookingRoom (RoomTypeID, NumberOfRooms) " & _
"SELECT tblGroupBookingRoom.RoomTypeID, tblGroupBookingRoom.NumberOfRooms " & _
"FROM tblGroupBookingRoom WHERE GroupBookingID= " & Me.Parent.GroupBookingID

I have no clue what this end snippet is trying to do:

" & "," & PartItineraryCityTourID

Are you trying to add a second comparison on the field PartItinerayrCityTourID??

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

On Oct 31, 2015, at 2:19 AM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Duane-

After runing the code, no error appears, and there's no record appended either.

Best Regards,

Kevin

在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年10月31日上午9:02写道：

Two suggestions:
Don't state "it didn't work" without some statement of what your results were. Did you get an error? Were there no records added? Did record get added but they weren't as expected?

Add a debug.print to your code so you can easily see what SQL is being run. I rarely if ever Execute until I have used:

Debug.Print strSQL
' uncomment the next line once you have reviewed the strSQL
' CurrentDb.Execute strSQL, dbFailOnError

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Sat, 31 Oct 2015 08:06:31 +0800
Subject: Re: [MS_AccessPros] Run an append query automatically

By the way, I did something like this, it didn't work out. What's wrong with my code? Thanks.

Dim strSQL As String

If IsNull(ELookup("PartItineraryCityTourBookingRoomID", "tblPartItineraryCityTourBookingRoom", "PartItineraryCityTourID=" & PartItineraryCityTourID)) Then

strSQL = "INSERT INTO tblPartItineraryCityTourBookingRoom (RoomTypeID, NumberOfRooms,)" & _
"SELECT tblGroupBookingRoom.RoomTypeID, tblGroupBookingRoom.NumberOfRooms" & _
"FROM tblGroupBookingRoom WHERE GroupBookingID=Me.Parent.GroupBookingID" & "," & PartItineraryCityTourID

CurrentDb.Execute strSQL, dbFailOnError

End If

Regards,
Kevin Zhao

From: 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals]
Date: 2015-10-31 06:57
To: MS_Access_Professionals
Subject: [MS_AccessPros] Run an append query automatically

Dear All,
Is that possible to click on a command button run an append query automatically without pop up windows. For example:

source table: tblBooking
BookingID Roomtype NumberOfRooms

Destination table: tblCityRoomBooking
CityRoomBookingID CityTourID RoomType NumberOfRooms

By cliecking on a command button on the frmCityTour, I want to append "RoomType, NumberOfRoom" into tblCityRoomBooking, and I hope the "CityTourID" will be filled according to "CityTourID" from frmCityTour.

Can this be done? Thanks.
Best Regards,
Kevin

Regards,
Kevin Zhao