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

Kamis, 05 November 2015

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

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