Senin, 01 April 2013

RE: [MS_AccessPros] Query not working properly

 

Glad to hear you figured it out!

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)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lon Lasher
Sent: Monday, April 01, 2013 8:56 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Query not working properly

This is what worked. Private Sub Form_BeforeUpdate(Cancel As Integer) If
Me.NewRecord Then Me.Frm_CADatasheet!Comments = " "
Me.Frm_DSIdaho!IdahoComments = " "
Me.Frm_DSOROther!OORComments = " "
Me.Frm_DSWashington!WAComments = " "
Me.Frm_DSOtherStates!OSComments = " "
End If One of the things I did wrong was NOT have a space between the two
quote marks. It all seems so simple now.
To: MS_Access_Professionals@yahoogroups.com
From: JohnV@msn.com
Date: Mon, 1 Apr 2013 00:23:50 +0200
Subject: RE: [MS_AccessPros] Query not working properly

Lon-

That's strange syntax - you put the "Form" in the wrong place!

Me.Frm_DSCalif.Form!Comments = " "

Me refers to the current form (the outer form). Next, you name the subform

control, and you want the Form property inside that to get to the embedded

form. Finally, you reference the control you want to update.

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)

-----Original Message-----

From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lon Lasher

Sent: Sunday, March 31, 2013 11:39 PM

To: MS_Access_Professionals@yahoogroups.com

Subject: RE: [MS_AccessPros] Query not working properly

Here are two versions of the code I'm trying. I get an error that reads

"object can not be found." I chose the Comments field because data is

seldom placed there.

Private Sub

Form_BeforeUpdate(Cancel As Integer)

If

Me.NewRecord Then

Me.Form.Frm_DSCalif!Comments

= ""

Me.Form.Frm_DSWashington!WAComments

= ""

Me.Form.Frm_DSOROther!OORComments

= ""

Me.Form.Frm_DSIdaho!Comments

= ""

Me.Form.Frm_DSOtherStates!OSComments

= ""

End If

End Sub

Private Sub

Form_BeforeUpdate(Cancel As Integer)

If

Me.NewRecord Then

Me.SubForm.Frm_DSCalif!Comments

= ""

Me.SubForm.Frm_DSWashington!WAComments

= ""

Me.SubForm.Frm_DSOROther!OORComments

= ""

Me.SubForm.Frm_DSIdaho!Comments

= ""

Me.SubForm.Frm_DSOtherStates!OSComments

= ""

End If

To: MS_Access_Professionals@yahoogroups.com

From: JohnV@msn.com

Date: Sat, 30 Mar 2013 08:25:27 +0100

Subject: RE: [MS_AccessPros] Query not working properly

Lon-

When you're on the "new" row in any form (including a form embedded as a

subform), you may see all sorts of values filled in. These values get

inherited from Default Value settings or the link fields in subforms, but

unless you "dirty" the row in some way, Access won't save it when the form

closes. You can ensure that the new row gets saved by setting any of the

values on the new row in code. You could do this in the outer form's

BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' If on a new record

If Me.NewRecord Then

' Make sure the subform record is "dirty"

Me.MySubform.Form!SomeField = " "

End If

End Sub

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)

From: MS_Access_Professionals@yahoogroups.com

[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lon Lasher

Sent: Saturday, March 30, 2013 7:06 AM

To: MS_Access_Professionals@yahoogroups.com

Subject: RE: [MS_AccessPros] Query not working properly

I'm back seeking any additional input. What is happening is that data in my

subforms no longer saves when I close the master form. It worked fine until

several days ago. When I try to run a query on any new data I enter, it

can't find that data because obviously it's never being saved. The only way

it saves is to go into each subform and enter the TPID number which is the

common link among the tables. Here are the techniques I've tried to fix the

issue. Compact & repair. Gone into each subform and opened the properties

and re-entered the link I want to use.Removed each subform and put it on the

master form again. Made sure relationships were set up in Database

Tools.Changed the Source Object in the input subforms based on the other

tables I'm using from the table name to an SQL query. > To:

ms_access_professionals@yahoogroups.com

<mailto:ms_access_professionals%40yahoogroups.com>

> From: lonlasher@hotmail.com <mailto:lonlasher%40hotmail.com>

> Date: Fri, 29 Mar 2013 14:51:47 -0700

> Subject: RE: [MS_AccessPros] Query not working properly

>

> When I enter a new record, I put data of some kind in nearly all fields in

all tables. The subforms fill automatically based on the linked field or

default entries (like price). Based on your comments, I manually entered the

"TPID" (sample: 70153), which is the common field among tables, in all the

tables. Now the new records show up with all data intact. For some reason,

even though the TPID shows in the subforms of the Master data entry form

during data entry, that data has stopped being saved to the sub tables

themselves. If that makes any sense. So a link is broken or something.

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

> From: JohnV@msn.com <mailto:JohnV%40msn.com>

> Date: Fri, 29 Mar 2013 22:22:03 +0100

> Subject: RE: [MS_AccessPros] Query not working properly

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

> Lon-

>

>

>

> Let me restate my question:

>

>

>

> When you enter a "new" record for March 28 or later, which table is

>

> affected?

>

> My point is that if there are no matching records (because of the INNER

>

> joins) in any one of the other tables, the record for March 28 won't show

>

> up. If you run the query directly in the database, do you see records past

>

> March 28?

>

>

>

> 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)

>

>

>

> -----Original Message-----

>

> From: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

> [mailto:MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Lon Lasher

>

> Sent: Friday, March 29, 2013 9:53 PM

>

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

> Subject: RE: [MS_AccessPros] Query not working properly

>

>

>

> I don't query on date at all. Sorry if that was misunderstood. Date does

>

> not matter in this process. I query on Customer ID only. My issue is that

>

> any record started prior to March 28 is accessible. Any NEW record entered

>

> after March 28 does not respond to the query.

>

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

> From: JohnV@msn.com <mailto:JohnV%40msn.com>

>

> Date: Fri, 29 Mar 2013 21:29:37 +0100

>

> Subject: RE: [MS_AccessPros] Query not working properly

>

>

>

> Lon-

>

>

>

> Which of the tables have fields with dates after March 28? I suspect

>

>

>

> because of the INNER JOINs that there are no matching records for that

date

>

>

>

> in the other tables, so you get no result.

>

>

>

> 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)

>

>

>

> -----Original Message-----

>

>

>

> From: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> [mailto:MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Lon Lasher

>

>

>

> Sent: Friday, March 29, 2013 9:25 PM

>

>

>

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> Subject: RE: [MS_AccessPros] Query not working properly

>

>

>

> Copied using SQL view of the Query. No actual SQL code written.

>

>

>

> SELECT Tbl_rTPDirect.CustCode, Tbl_rTPDirect.TPID, Tbl_rTPDirect.CoName,

>

>

>

> Tbl_rTPDirect.CoAddress, Tbl_rTPDirect.CoCity, Tbl_rTPDirect.CoState,

>

>

>

> Tbl_rTPDirect.CoZip, Tbl_rTPDirect.[Sales Name],

>

>

>

> Tbl_rTPDirect.OtherNamesLoc, Tbl_rTPDirect.BillToName,

>

>

>

> Tbl_rTPDirect.Comments, Tbl_rTPDirect.BillToAddress,

>

>

>

> Tbl_rTPDirect.BillToCity, Tbl_rTPDirect.BillToState,

>

>

>

> Tbl_rTPDirect.BillToPostal, Tbl_rTPDirect.BillToPhone,

>

>

>

> Tbl_rTPDirect.CoPhone, Tbl_rTPDirect.CoFax, Tbl_rTPDirect.WATS,

>

>

>

> Tbl_rTPDirect.DirectTariff, Tbl_rTPDirect.DirectDiscount,

>

>

>

> Tbl_rTPDirect.DirectClass, Tbl_rTPDirect.DirectFloor,

>

>

>

> Tbl_rTPDirect.Comments, Tbl_rTPDirect.[Sales Name], Tbl_rTPDirect.[Sales

>

>

>

> Code], Tbl_rTPDirect.TPDate, Tbl_rTPDirect.OriginalDate,

>

>

>

> Tbl_rTPDirect.Hourly, Tbl_rTPDirect.MinB1, Tbl_rTPDirect.MinB1Rate,

>

>

>

> Tbl_rTPDirect.MinB2, Tbl_rTPDirect.MinB2Rate, Tbl_rTPDirect.PalletRate,

>

>

>

> Tbl_rTPDirect.PalletRateLevel, Tbl_rTPDirect.DirectPRPrice,

>

>

>

> Tbl_rTPDirect.PalletRateSpecial, Tbl_rTPDirect.PRWeight,

>

>

>

> Tbl_rTPDirect.PupRates, Tbl_rTPDirect.PupRateSpecial,

>

>

>

> Tbl_rTPDirect.OtherSpecial, Tbl_rTPDirect.FSLevel, Tbl_rTPDirect.CODLevel,

>

>

>

> Tbl_rTPDirect.CODlevel1, Tbl_rTPDirect.Hazmat, Tbl_rTPDirect.Residential,

>

>

>

> Tbl_rTPDirect.Inside, Tbl_rTPDirect.Liftgate, Tbl_rTPDirect.FAKtrailers,

>

>

>

> Tbl_rTPDirect.Contact, Tbl_rTPDirect.Other, Tbl_rTPDirect.OtherNamesLoc,

>

>

>

> Tbl_rTPDirect.extra, Tbl_rTPDirect.MinB2Ratea, Tbl_rTPDirect.MinB1Ratea,

>

>

>

> Tbl_rTPDirect.DirectPRPricea, Tbl_rCalif.CATariff, Tbl_rCalif.CADiscount,

>

>

>

> Tbl_rCalif.CAClass, Tbl_rCalif.CAFloor, Tbl_rCalif.Comments,

>

>

>

> Tbl_rCalif.PalletWgt, Tbl_rCalif.PalletNCA, Tbl_rCalif.PalletSCA,

>

>

>

> Tbl_rCalif.OtherCARates, Tbl_rIdaho.IdahoTariff, Tbl_rIdaho.IdahoDiscount,

>

>

>

> Tbl_rIdaho.IdahoClass, Tbl_rIdaho.IdahoFloor, Tbl_rIdaho.PalletWgt,

>

>

>

> Tbl_rIdaho.PalletNI, Tbl_rIdaho.PalletSI, Tbl_rIdaho.OtherIRates,

>

>

>

> Tbl_rIdaho.IdahoComments, Tbl_rOROther.OORTariff,

Tbl_rOROther.OORDiscount,

>

>

>

> Tbl_rOROther.OORclass, Tbl_rOROther.OORFloor, Tbl_rOROther.PalletWgt,

>

>

>

> Tbl_rOROther.PalletORPallets, Tbl_rOROther.OtherOROtherRates,

>

>

>

> Tbl_rOROther.OtherORComments, Tbl_rOtherStates.OSTariff,

>

>

>

> Tbl_rOtherStates.OSDiscount, Tbl_rOtherStates.OSClass,

>

>

>

> Tbl_rOtherStates.OSFloor, Tbl_rOtherStates.Comments,

>

>

>

> Tbl_rOtherStates.OtherOSRates, Tbl_rWash.WATariff, Tbl_rWash.WADiscount,

>

>

>

> Tbl_rWash.WAClass, Tbl_rWash.WAFloor, Tbl_rWash.PalletWgt,

>

>

>

> Tbl_rWash.PalletWWA, Tbl_rWash.PalletEWA, Tbl_rWash.OtherWARates,

>

>

>

> Tbl_rWash.WashComments FROM ((((Tbl_rTPDirect INNER JOIN Tbl_rCalif ON

>

>

>

> Tbl_rTPDirect.TPID = Tbl_rCalif.TPID) INNER JOIN Tbl_rIdaho ON

>

>

>

> Tbl_rTPDirect.TPID = Tbl_rIdaho.TPID) INNER JOIN Tbl_rOROther ON

>

>

>

> Tbl_rTPDirect.TPID = Tbl_rOROther.TPID) INNER JOIN Tbl_rOtherStates ON

>

>

>

> Tbl_rTPDirect.TPID = Tbl_rOtherStates.TPID) INNER JOIN Tbl_rWash ON

>

>

>

> Tbl_rTPDirect.TPID = Tbl_rWash.TPID;

>

>

>

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> From: JohnV@msn.com <mailto:JohnV%40msn.com>

>

>

>

> Date: Fri, 29 Mar 2013 19:01:23 +0100

>

>

>

> Subject: RE: [MS_AccessPros] Query not working properly

>

>

>

> Lon-

>

>

>

> I really need to see the entire SQL statement to try to spot something.

But

>

>

>

> one thing comes to mind - because you're doing INNER JOIN everywhere, you

>

>

>

> won't get any rows if there are rows missing in related tables for the

dates

>

>

>

> you want.

>

>

>

> 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)

>

>

>

> -----Original Message-----

>

>

>

> From: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> [mailto:MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Lon Lasher

>

>

>

> Sent: Friday, March 29, 2013 5:59 PM

>

>

>

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> Subject: RE: [MS_AccessPros] Query not working properly

>

>

>

> There is a master file with customer names & certain data related to

pricing

>

>

>

> for direct service points (we are a trucking company). There are six other

>

>

>

> tables with location specific pricing or sales representative data. Only

>

>

>

> the five location specific tables are included in the query. The query has

>

>

>

> inner joins between the main table and the other location specific tables.

>

>

>

> Very simple. Worked fine until yesterday. Nothing else I know has

>

>

>

> changed. I won't provide the list of all the table field names. This is

>

>

>

> the part where the joins are expressed.

>

>

>

> FROM ((((Tbl_rTPDirect INNER JOIN Tbl_rCalif ON Tbl_rTPDirect.TPID =

>

>

>

> Tbl_rCalif.TPID) INNER JOIN Tbl_rIdaho ON Tbl_rTPDirect.TPID =

>

>

>

> Tbl_rIdaho.TPID) INNER JOIN Tbl_rOROther ON Tbl_rTPDirect.TPID =

>

>

>

> Tbl_rOROther.TPID) INNER JOIN Tbl_rOtherStates ON Tbl_rTPDirect.TPID =

>

>

>

> Tbl_rOtherStates.TPID) INNER JOIN Tbl_rWash ON Tbl_rTPDirect.TPID =

>

>

>

> Tbl_rWash.TPID;

>

>

>

> Please remember, this still works fine for records entered prior to

>

>

>

> yesterday. It even picks up changes in those records. It just won't yield

>

>

>

> results from new records.

>

>

>

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> From: JohnV@msn.com <mailto:JohnV%40msn.com>

>

>

>

> Date: Fri, 29 Mar 2013 17:07:45 +0100

>

>

>

> Subject: RE: [MS_AccessPros] Query not working properly

>

>

>

> Lon-

>

>

>

> What is the structure of the table and the SQL of your query?

>

>

>

> 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)

>

>

>

> From: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> [mailto:MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Lon

>

>

>

> Sent: Friday, March 29, 2013 4:54 PM

>

>

>

> To: MS_Access_Professionals@yahoogroups.com

<mailto:MS_Access_Professionals%40yahoogroups.com>

>

>

>

> Subject: [MS_AccessPros] Query not working properly

>

>

>

> I have a 3,600 record database split into front and back ends. Users have

>

>

>

> the front ends on their PCs. The database itself is in Access 2000 format

on

>

>

>

> a Windows 2003 server box.

>

>

>

> I also have a Microsoft Word 2010 document with multiple fields linked to

a

>

>

>

> query in my database. To find the correct data to use in the Word

document,

>

>

>

> I used a filter based on an indexed code that is assigned to each customer

>

>

>

> for which I want information.

>

>

>

> Starting yesterday (March 28), any new customer entered in the database

will

>

>

>

> not show up in the Word document. If I change information in an customer's

>

>

>

> record that existed prior to March 28, that information shows.

>

>

>

> I finally have determined the query itself is at fault. If I run the query

>

>

>

> itself with the new customer's ID code, the query won't find it.

>

>

>

> I've never run into a query that quit working like this. Anyone have any

>

>

>

> ideas? If you need more info to respond, ask away.

>

>

>

> [Non-text portions of this message have been removed]

>

>

>

>

>

>

>

> [Non-text portions of this message have been removed]

>

>

>

> ------------------------------------

>

>

>

> Yahoo! Groups Links

>

>

>

>

>

>

>

> [Non-text portions of this message have been removed]

>

>

>

> ------------------------------------

>

>

>

> Yahoo! Groups Links

>

>

>

>

>

>

>

> [Non-text portions of this message have been removed]

>

>

>

> ------------------------------------

>

>

>

> Yahoo! Groups Links

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

> [Non-text portions of this message have been removed]

>

>

>

> ------------------------------------

>

> Yahoo! Groups Links

>

>

>

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links



[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (15)
.

__,_._,___

Tidak ada komentar:

Posting Komentar