ACCESS FEED: Re: [MS_AccessPros] Fill up something instead of add new record

Sabtu, 07 November 2015

Re: [MS_AccessPros] Fill up something instead of add new record

OK, then try this:

   Dim strSQL As String
        strSQL = "UPDATE tblPartItineraryCityTour " & _
        "SET [HotelDistanceToCityCenter] = " & Me.Parent.HotelDistanceToCityCenter & ", " & _
        "[HotelCategoryID] = " & Me.Parent.HotelCategoryID & _
        " WHERE PartItineraryCityTourID = " & Me.Parent.PartItineraryCityTourID
Debug.Print strSQL
        CurrentDb.Execute strSQLOne, dbFailOnError

You are leaving spaces out between keywords - I've added them. If that still doesn't work, tell me what you get in the Immediate Window.

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 7, 2015, at 12:28 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-

Here is the real names and table structure: Please Help me with the update query. Thanks in advance.

====tblGroupBooking====
GroupBookingID
GroupCode
ClientContactID
HotelStarID
HotelDistanceToCityCenter
ClientCurrencyID
...

====tblPart==== 'This table is to divided the whole itinerary into parts.
PartID
GroupBookingID
PartNumber
PartRemark

====tblPartItineraryID====
PartItineraryDate

====tblPartItineraryCityTour====
(Since we may travel to differnent cities, we need firstly get the original booking condition to this table, and adjust it later on according to feedback from hotels. For example, if the price we get from city center is too high, we will change the distance to suburb area, or reduce to lower class (from 4star to 3star hotel), etc. This is part I need the update query. Please help.

PartItineraryCityTourID
PartItineraryID
CityID
PartItineraryCityTour
HotelStarID
HotelDistanceToCityCenter
HotelCategoryID

====tblPartItineryCityTourRoom====
PartItineraryCityTourRoomID
PartItineraryCityTourID
RoomTypeID
NumberOfRooms
Budget

Best Regards,

Kevin

zhaoliqingoffice@163.com

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-11-07 04:45
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Fill up something instead of add new record

Kevin-

NEVER give us anything other than the real names. We can't help you solve your problem if you try to "simplify" it.

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 5, 2015, at 10:51 AM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-

I just put it in context to make it understandable, but it was not the actual case. Sorry for confusing you.

Best Regards,

Kevin

在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年11月5日下午5:20写道：

Kevin-

In your original message you said you want to set a field in tblCity tour, but that's not the table you're referencing in the UPDATE statement.

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 5, 2015, at 9:57 AM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-

The first one will record client's original request, the second one will copy data from first one, and later it will be changed according to the number of rooms cancelled or added. You know the tour group size are always changing. We need to keep the record so that we can compare... In this case I need update sql. Please help. Thanks in advance.

Best Regards,

Kevin

在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年11月5日下午3:52写道：

Kevin-

Why are you duplicating the field in a second table? Except for key values, a value should appear in your database only once.

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 5, 2015, at 4:28 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I did somthing like this myself. But nothing happend after running. And I checked the debug.print strSQL, there's no error notes either.

Dim strSQL As String
        strSQL = "UPDATE tblPartItineraryCityTour" & _
        "SET [HotelDistanceToCityCenter] = " & Me.Parent.HotelDistanceToCityCenter & "," & _
        "[HotelCategoryID] = " & Me.Parent.HotelCategoryID & _
        "WHERE PartItineraryCityTourID = " & Me.Parent.PartItineraryCityTourID

        CurrentDb.Execute strSQLOne, dbFailOnError


zhaoliqingoffice@163.com

From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Date: 2015-11-05 08:21
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Fill up something instead of add new record

Kevin,

This is probably a simple update query. Can you provide some information about your primary keys? Is GroupBookingID a primary key in one of the tables?

Duane Hookom, MVP
MS Access

To: ms_access_professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 5 Nov 2015 07:41:55 +0800
Subject: [MS_AccessPros] Fill up something instead of add new record

Dear All-
Is that possible to fill up an existing record with some field from other table or form with strSQL, instead of insert a new record? For example:

An existing record from tblCityTour

tblCityTour
CityTourID: 11
GroupBookingID
CityTourDate: 11-Nov-2015
HotelStarID: (field I want to fill up)

The table from which I want to get HotelStartID.
tblGroupBooking
GroupBookingID: 2
ClientID: 5
HotelStarID: 3

Is that possible to fill up HotelStartID with the one from tblGroupBooking? Thanks in advance.

Best Regards,
Kevin

zhaoliqingoffice@163.com