Minggu, 31 Maret 2013

Re: [MS_AccessPros] Updating Access Files

 

Russ

- I think you are right about the terminology, but it would be best if we got clarification from the Beave.

We developers commonly call Access databases with forms and reports "Applications".

Bill Mosca.

--- In MS_Access_Professionals@yahoogroups.com, "ghsclass65" <dyspoz2@...> wrote:
>
> I don't mean to put words in the Beaver's mouth, but he's probably calling Access applications "programs". Obviously in Access you can have a bare bones db with no forms, buttons, reports, etc. But in my mind, when you start adding things that make it simple for the non Access developer end user, it becomes an application or I guess you could call it a 'program" if you wanted to.
>
> Russ
>
> --- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@> wrote:
> >
> > Hi Jerry,
> >
> > It is not clear to me what you mean by "programs".
> >
> > Are they Macros, VBA Subs and Functions in Modules,
> > complete .mdb files, or something completely different?
> >
> > If you like you can upload a sample to the Group Files
> > 2_AssistanceNeeded folder, nothing confidential and
> > suitably zipped.
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > >
> > > Every Access application should be split into front-end and back-end files. All of your changes are typically made to the designs in the front-end queries, reports, forms, code, etc. The back-end contains only tables. 
> > >
> > > There should be code in the front-end that checks for the location of the back-end and possibly allows users to link to a back-end in a different location.
> > >
> > > Sending updates should be a no-brainer. They simply open the new front-end and are connected or prompted to their back-end.
> > >
> > > If you need to make changes in the back-end table structures, this can be handled with some DAO or other code.
> > >
> > > Duane Hookom MVP
> > > MS Access
> > >
> > > ----------------------------------------
> > > > From: jerrymathersasthebeav@
> > > >
> > > > I've created a few Access "programs" that others have found useful so I've given them a copy of the most recent version. Of course, it seems like a program is never done and I continue to make minor adjustments and tweaks to the program (okay, occasionally there may be a need for a major change due to an error I found/created). These mostly consist of some relatively minor changes to a Query or two and/or a Report. I might even need to add a new Query and Report, as well. The problem is I need to get any changes I've made in my copy into the copy I gave them.
> > > >
> > > > These people aren't working in my building. They are all working at other schools and are less familiar with Access than even I am so explaining how to make the changes isn't a viable solution. What I have been doing is asking them to let me have temporary remote access to the location where they are storing their copy of the file. Then I make the changes or add the new Query and Report by hand. This is okay, but what I'd really like to do is find a way to send them some kind of "package" containing all of the changes that will automatically update their copy of the program (without touching the data they've already input). I cannot think of any way to do this, but the thought occurred to me when I was doing some basic updating of various programs on my computer that there are so many programs that have automatic update capability it gave me the idea of doing something similar with their copy of the Access file.
> > > >
> > > > I was wondering if anyone can offer some suggestions on how I might manage this problem through an "automatic" update process for Access files. How to do you get any updates into your client's files? Have you figured out some way other than by hand?
> > > >
> > > > Any ideas would be greatly appreciated!
> > >
> >
>

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

__,_._,___

[belajar-access] butuh sukarelawan - d/h usulan lebih lanjut

 

Ok demi kemajuan komunitas kita ini :D
 
Saat ini Pak Andreas dan Saya sudah bersedia jadi "jaga gawang" jika kita wujudkan eb untuk belajar MS Access.
 
Nah, saya butuh setidaknya 1 orang lagi, idealnya 2. Jadi yang jaga gawang ada 4 orang.
 
 
Kita semua BELUM pernah pengalaman memelihara sebuah web untuk komunitas. Jadi semua sama-sama belajar.
 
Saya pikir ini juga merupakan pengalaman yang berharga bagi yang terlibat.
 
cgsatu (Elang)

From: "andreas.kodyat@gmail.com" <andreas.kodyat@gmail.com>
To: CGSATU <cgsatu@yahoo.com>
Sent: Saturday, March 30, 2013 2:05 PM
Subject: Re: [belajar-access] usulan - lebih lanjut
Ok, pak. Nanti kabari saja lagi utk proses selanjutnya.salam,Andreas.
From: CGSATU <cgsatu@yahoo.com>
Date: Fri, 29 Mar 2013 23:31:36 -0700 (PDT)
To: belajar-access@yahoogroups.com<belajar-access@yahoogroups.com>
ReplyTo: CGSATU <cgsatu@yahoo.com>
Subject: Re: [belajar-access] usulan - lebih lanjut

Ok Pak saya catat.
 
Saya sendiri BELUM pernah pakai Mojo Portal. Jadi kalau sudah bener jadi kita harus saling bantu dan back-up ini
 
setidkanya harus ada 3 orang deh. Bapak, saya dan satu lagi
 
cgsatu (Elang)

From: "andreas.kodyat@gmail.com" <andreas.kodyat@gmail.com>
To: belajar-access@yahoogroups.com
Sent: Friday, March 29, 2013 6:47 PM
Subject: Re: [belajar-access] usulan - lebih lanjut
 
Aku bersedia bantu, pak. Tp perlu diberi bimbingan lebih lanjut.Salam,Andreas.
From: aksan kurdin <aksan.kurdin@gmail.com>
Sender: belajar-access@yahoogroups.com
Date: Thu, 28 Mar 2013 20:39:21 +0700
To: <belajar-access@yahoogroups.com>
ReplyTo: belajar-access@yahoogroups.com
Subject: Re: [belajar-access] usulan - lebih lanjut

 
Ga ada yang berminat cak :)aksan kurdinOn 3/27/2013 10:59 AM, CGSATU wrote:> Berhubung Community Server yang versi gratisnya sudah tidak ada, maka > mungkin MojoPortal bisa digunakan sebgai alternatif.> Kebetulan saya lihat kok lumayan gampang. Nah sekarang bagaimana ?> http://demo.mojoportal.com/store-demo

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

[belajar-access] mengubah excel ke access [2 Attachments]

 
[Attachment(s) from AW fina included below]

dear all,\

mohon dibantu saya ingin mengubah rumus sumproduct yang saya gunakan
menjadi bentuk ms,access . adakah yang tahu caranya ?

terima kasih sebelumnya.

ket : data rekap kirim diambil dari file transaksi menggunakan rumus
sumproduct

Salam

__._,_.___

Attachment(s) from AW fina

2 of 2 File(s)

Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

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

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

__,_._,___

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/