Sabtu, 31 Oktober 2015

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

 

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
 
Date: 2015-10-31 18:34
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 
(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
 
Date: 2015-10-31 06:57
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





__._,_.___

Posted by: "zhaoliqingoffice@163.com" <zhaoliqingoffice@163.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (13)

.

__,_._,___

Re:RE: [MS_AccessPros] Run an append query automatically

 

Thank you, Glenn.



在 "'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>,2015年11月1日 上午2:27写道:

 

When trying to debug my SQL, I find it helpful to debug.print the sql variable (strSQL for example), copy that from the immediate window and paste it into the sql view of a blank query. Viewing (rather than executing) the query then will indicate whether you have the SQL syntax correct.

 

Glenn

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, October 31, 2015 1:20 PM
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Run an append query automatically

 

 

It took me a while to learn the tools and I still don't know or use them all. You might want to review this page for some debugging tips www.tek-tips.com/faqs.cfm?fid=7148

Duane

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 22:24:16 +0800
> Subject: Re: RE: [MS_AccessPros] Run an append query automatically
>
>
>
> Duane,
> You are right. Actually I am ashamed of myself for not knowing how to
> use debug.print... I am going to learn it today. Thanks a lot for your
> suggestion.
> Best Regards,
> Kevin
>
> ________________________________
> Regards,
> Kevin Zhao
>
> From: Duane Hookom duanehookom@hotmail.com
> [MS_Access_Professionals]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 22:04
> To: Access Professionals Yahoo
> Group<mailto:ms_access_professionals@yahoogroups.com>
> Subject: RE: [MS_AccessPros] Run an append query automatically
>
>
> Kevin,
> What did you see in the debug window after altering the code?
>
> You should really do yourself a favor and learn how to debug your code
> using debug.print and breakpoints. It's surprising how you can find
> your mistakes when doing what many of us have been doing for decades. I
> probably make as many mistakes as you (or more) but I catch them early
> and move on.
>
> Duane Hookom, MVP
> MS Access
>
> ________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 09:19:37 +0800
> Subject: Re:RE: [MS_AccessPros] Run an append query automatically
>
> 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>20151031 上午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]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 06:57
> To: MS_Access_Professionals<mailto:ms_access_professionals@yahoogroups.com>
> 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
>
>
>
>
>
>
>
>
>

__._,_.___

Posted by: zhaoliqingoffice <zhaoliqingoffice@163.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (12)

.

__,_._,___

Re:RE: [MS_AccessPros] Run an append query automatically

 

Duane-
I am going to learn these tools. Thanks a lot.
Best Regards,
Kevin



在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>,2015年11月1日 上午1:23写道:

 

It took me a while to learn the tools and I still don't know or use them all. You might want to review this page for some debugging tips www.tek-tips.com/faqs.cfm?fid=7148

Duane

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 22:24:16 +0800
> Subject: Re: RE: [MS_AccessPros] Run an append query automatically
>
>
>
> Duane,
> You are right. Actually I am ashamed of myself for not knowing how to
> use debug.print... I am going to learn it today. Thanks a lot for your
> suggestion.
> Best Regards,
> Kevin
>
> ________________________________
> Regards,
> Kevin Zhao
>
> From: Duane Hookom duanehookom@hotmail.com
> [MS_Access_Professionals]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 22:04
> To: Access Professionals Yahoo
> Group<mailto:ms_access_professionals@yahoogroups.com>
> Subject: RE: [MS_AccessPros] Run an append query automatically
>
>
> Kevin,
> What did you see in the debug window after altering the code?
>
> You should really do yourself a favor and learn how to debug your code
> using debug.print and breakpoints. It's surprising how you can find
> your mistakes when doing what many of us have been doing for decades. I
> probably make as many mistakes as you (or more) but I catch them early
> and move on.
>
> Duane Hookom, MVP
> MS Access
>
> ________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 09:19:37 +0800
> Subject: Re:RE: [MS_AccessPros] Run an append query automatically
>
> 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]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 06:57
> To: MS_Access_Professionals<mailto:ms_access_professionals@yahoogroups.com>
> 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
>
>
>
>
>
>
>
>
>

__._,_.___

Posted by: zhaoliqingoffice <zhaoliqingoffice@163.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (11)

.

__,_._,___

RE: [MS_AccessPros] Run an append query automatically

 

When trying to debug my SQL, I find it helpful to debug.print the sql variable (strSQL for example), copy that from the immediate window and paste it into the sql view of a blank query. Viewing (rather than executing) the query then will indicate whether you have the SQL syntax correct.

 

Glenn

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, October 31, 2015 1:20 PM
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Run an append query automatically

 

 

It took me a while to learn the tools and I still don't know or use them all. You might want to review this page for some debugging tips www.tek-tips.com/faqs.cfm?fid=7148

Duane

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 22:24:16 +0800
> Subject: Re: RE: [MS_AccessPros] Run an append query automatically
>
>
>
> Duane,
> You are right. Actually I am ashamed of myself for not knowing how to
> use debug.print... I am going to learn it today. Thanks a lot for your
> suggestion.
> Best Regards,
> Kevin
>
> ________________________________
> Regards,
> Kevin Zhao
>
> From: Duane Hookom duanehookom@hotmail.com
> [MS_Access_Professionals]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 22:04
> To: Access Professionals Yahoo
> Group<mailto:ms_access_professionals@yahoogroups.com>
> Subject: RE: [MS_AccessPros] Run an append query automatically
>
>
> Kevin,
> What did you see in the debug window after altering the code?
>
> You should really do yourself a favor and learn how to debug your code
> using debug.print and breakpoints. It's surprising how you can find
> your mistakes when doing what many of us have been doing for decades. I
> probably make as many mistakes as you (or more) but I catch them early
> and move on.
>
> Duane Hookom, MVP
> MS Access
>
> ________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 09:19:37 +0800
> Subject: Re:RE: [MS_AccessPros] Run an append query automatically
>
> 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>20151031 上午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]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 06:57
> To: MS_Access_Professionals<mailto:ms_access_professionals@yahoogroups.com>
> 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
>
>
>
>
>
>
>
>
>

__._,_.___

Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)

.

__,_._,___

RE: [MS_AccessPros] Run an append query automatically

 

It took me a while to learn the tools and I still don't know or use them all. You might want to review this page for some debugging tips www.tek-tips.com/faqs.cfm?fid=7148

Duane

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 22:24:16 +0800
> Subject: Re: RE: [MS_AccessPros] Run an append query automatically
>
>
>
> Duane,
> You are right. Actually I am ashamed of myself for not knowing how to
> use debug.print... I am going to learn it today. Thanks a lot for your
> suggestion.
> Best Regards,
> Kevin
>
> ________________________________
> Regards,
> Kevin Zhao
>
> From: Duane Hookom duanehookom@hotmail.com
> [MS_Access_Professionals]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 22:04
> To: Access Professionals Yahoo
> Group<mailto:ms_access_professionals@yahoogroups.com>
> Subject: RE: [MS_AccessPros] Run an append query automatically
>
>
> Kevin,
> What did you see in the debug window after altering the code?
>
> You should really do yourself a favor and learn how to debug your code
> using debug.print and breakpoints. It's surprising how you can find
> your mistakes when doing what many of us have been doing for decades. I
> probably make as many mistakes as you (or more) but I catch them early
> and move on.
>
> Duane Hookom, MVP
> MS Access
>
> ________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 31 Oct 2015 09:19:37 +0800
> Subject: Re:RE: [MS_AccessPros] Run an append query automatically
>
> 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]<mailto:MS_Access_Professionals@yahoogroups.com>
> Date: 2015-10-31 06:57
> To: MS_Access_Professionals<mailto:ms_access_professionals@yahoogroups.com>
> 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
>
>
>
>
>
>
>
>
>

__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (9)

.

__,_._,___

Re: RE: [MS_AccessPros] Run an append query automatically

 

Duane,
You are right. Actually I am ashamed of myself for not knowing how to use debug.print... I am going to learn it today. Thanks a lot for your suggestion.
Best Regards,
Kevin


Regards,
Kevin Zhao
 
Date: 2015-10-31 22:04
Subject: RE: [MS_AccessPros] Run an append query automatically
 

Kevin,
What did you see in the debug window after altering the code?

You should really do yourself a favor and learn how to debug your code using debug.print and breakpoints. It's surprising how you can find your mistakes when doing what many of us have been doing for decades. I probably make as many mistakes as you (or more) but I catch them early and move on.

Duane Hookom, MVP
MS Access


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

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
 
Date: 2015-10-31 06:57
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






__._,_.___

Posted by: "zhaoliqingoffice@163.com" <zhaoliqingoffice@163.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (8)

.

__,_._,___

RE: [MS_AccessPros] Run an append query automatically

 

Kevin,
What did you see in the debug window after altering the code?

You should really do yourself a favor and learn how to debug your code using debug.print and breakpoints. It's surprising how you can find your mistakes when doing what many of us have been doing for decades. I probably make as many mistakes as you (or more) but I catch them early and move on.

Duane Hookom, MVP
MS Access


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

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
 
Date: 2015-10-31 06:57
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






__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (7)

.

__,_._,___

Re: [MS_AccessPros] Run an append query automatically

 

John-
The last one "PartItineraryCityTourID" is a link which links two tables.
I will try and let you know.Thanks a lot.
Regards,
Kevin



在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>,2015年10月31日 下午6:34写道:

 

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 
(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
 
Date: 2015-10-31 06:57
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





__._,_.___

Posted by: zhaoliqingoffice <zhaoliqingoffice@163.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

.

__,_._,___