Sabtu, 31 Maret 2012

[AccessDevelopers] Re: Make directions disapear when typing.

 



Hi,

I use the following to automatically select and copy the contents of a text box when it is clicked.

It's not exactly what you are asking for but it might help or give an idea.....

Good luck!

Andy

Private Sub Lastname_Click()
SelectText Me!Lastname
End Sub

Sub SelectText(ctlName As Control)
ctlName.SetFocus
ctlName.SelStart = 0
ctlName.SelLength = Nz(Len(ctlName), 0)
RunCommand acCmdCopy
End Sub

--- In AccessDevelopers@yahoogroups.com, "dwightknotgunn" <backyardautopart@...> wrote:
>
> Hi all. In a textbox I have the words " First Name ? ", next textbox " Last Name ? ". In the click event the box is set to empty. I would like the SetFocus to be here also < Herein lies the problem. I would like the user to start typing without clicking or clearing out the directions.
>

__._,_.___
Recent Activity:

Please zip all files prior to uploading to Files section.
.

__,_._,___

[belajar-access] Re: Access Developer Extensions

 

Terima kasih Kang atas informasi & petunjuknya.
Alhamdulillah sekarang programnya sudah dapat Running dengan baik.

Semoga semua kebaikan Akang dan semua rekan2 Accesser dapat balasan yang berlimpah dari Allah SWT. Amiin...

Salam Hangat,

Toni Coyev

--- In belajar-access@yahoogroups.com, "Effendy" <fvandee03@...> wrote:
>
> Di trust center ada macro setting. Set yg paling low.
>
> Sent from my mobile®
>
> -----Original Message-----
> From: "Coyev" <coyev@...>
> Sender: belajar-access@yahoogroups.com
> Date: Sat, 31 Mar 2012 02:35:13
> To: <belajar-access@yahoogroups.com>
> Reply-To: belajar-access@yahoogroups.com
> Subject: [belajar-access] Re: Access Developer Extensions
>
> Terima kasih Bang Haer atas petunjuk & informasinya. Smoga kebaikan Abang dibalas Allah SWT.
>
> Kemarin saya coba, dengan langkah2 sbb: sebelum program dibuat ACCDE dan Developer Extensions, di Menu Access->Access Options->Trust Center->Klik Tombol <Trus Center Setting...> ->Trusted Locations : saya tambahkan lokasi folder dimana database akan ditempatkan (Mis. C:\Program Files\Aplikasi\).
>
> Apa langkah2 saya benar atau salah? masalahnya masih tetap Macro tidak bisa jalan, tapi kalau VBA bisa jalan.
>
> Sudi kiranya Bang Haer atau para acceser sekalian bisa menjelaskan lebih rinci, maklum saya orang newbie.
>
> Terima Kasih atas perhatiannya.
>
> Wassalaam,
>
> Toni Coyev
>
>
> --- In belajar-access@yahoogroups.com, Haer Talib <haertalib@> wrote:
> >
> > Kayaknya sih masalah security warning / security notice saja.
> >
> > Pada Access 2007 dan 2010, anda bisa menambahkan lokasi file database
> > berada ke Trusted Location, jadi tdk muncul warning, dan juga VBA atau
> > macro bisa dijalankan.
> >
> > HAER
> >
> >
> > 2012/3/30 Coyev <coyev@>
> >
> > > Terima kasih Kang Opi atas informasinya, semoga Allah SWT membalasNya.
> > > Amiin... Saya coba install ulang lagi programnya, masalah yang pertama
> > > sudah tidak ada. Tp masalah yang ke dua dan ke tiga masih tetap.
> > >
> > > Mungkin ada solusi untuk masalah ke dua dan ke tiga dari para Accesser
> > > semua. Terima kasih atas perhatiannya....
> > >
> > > Wassalaam,
> > > Toni Coyev
> > >
> > >
> > >
> > > --- In belajar-access@yahoogroups.com, Sofyan Efendi <sofyanefendi@>
> > > wrote:
> > > >
> > > > Dear Pak Toni, saya pernah mengalaminya. Error terjadi karena didalam VBA
> > > > menggunakan reference yang mana di Access 2007 ada, namun di PC yang
> > > > menggunakan Access 2003 tidak ada. Atau karena kesalahan install, di PC
> > > > tersebut ada dua versi Access yg berbeda, dimana yang diinstall pertama
> > > kali
> > > > Access versi diatasnya, kemudian yg kedua diinstall Access versi
> > > dibawahnya.
> > > > Secara default, Windows akan menjalankan versi Access yang terakhir kali
> > > > dibuka.
> > > >
> > > > Barangkali teman2 ada yang menambahkan, dipersilahkan yah...
> > > >
> > > > Wassalaamu'alaikum Warahmatullahi Wabarakatuh,
> > > > Sofyan Efendi
> > > > http://imopi.wordpress.com/
> > > > ----- Original Message -----
> > > > From: "Coyev" <coyev@>
> > > > To: <belajar-access@yahoogroups.com>
> > > > Sent: Friday, March 30, 2012 1:15 PM
> > > > Subject: [belajar-access] Access Developer Extensions
> > > >
> > > >
> > > > > Saya coba membuat Developer Extensions Access 2007, kemudian saya
> > > install
> > > > > di komputer yang ada Ms. Access 2003. Masalahnya :
> > > > > - Begitu program di Running suka keluar dulu kotak Warning
> > > > > - Form yang mengandung Macro tidak bisa dipanggil, keluar tulisan:
> > > > > can't run the macro or call back function 'OnActionButton'.
> > > > > Make sure the macro or function exist and take the correct parameter
> > > > > - Program dari Access 2003 (MDE) jadi tidak jalan.
> > > > >
> > > > > Mohon solusi & bantuannya dari para Accesser. Terima kasih atas
> > > > > perhatiannya.
> > > > >
> > > > > Regard,
> > > > >
> > > > > Toni Coyev
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > SPAM IS PROHIBITEDYahoo! Groups Links
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > SPAM IS PROHIBITEDYahoo! Groups Links
> > >
> > >
> > >
> > >
> >
> >
> > --
> >
> >
> > Haer Talib
> >
> > *RumahAccess Indonesia* <http://www.rumahaccess.com>
> > *Tempatnya Belajar Microsoft Access*
> > Artikel | Forum | Milis | Download | Training | Links | Blog | Event
> >
>

__._,_.___
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

[belajar-access] Para Suhu mohon bantuan dan pencerahannya

 

Saya sedang membuat suatu program untuk mempermudah pencarian data untuk saya, yang saya inginkan pada subform kalau saya double clik akan keluar atau memanggil form editing dari data yang saya pilih

mohon para suhu untuk pencerahannya kalau para suhu masih bingung saya akan kirim email dari program yang saya bikin

__._,_.___
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

Re: [belajar-access] Textbox terisi value yg sama dengan men-double klik?

 

Dear Saudara Asep,
Coba bantu yaa..
Pada textbox "alamat orang tua" pilih properties pada event double click pilih event procedure diseblah kanan ada tombol titik, tombol itu diclick kemudian diline script nya ketikan [alamat siswa].text = [alamat orang tua].text

Catatan : nama control baiknya tidak dipisah spasi

Semoga membantu yaa..

Sent from my BlackBerry® smartphone from Sinyal Bagus XL, Nyambung Teruuusss...!

From: Asep Nurdiansyah <asepnurdiansah@gmail.com>
Sender: belajar-access@yahoogroups.com
Date: Sat, 31 Mar 2012 16:27:30 +0700
To: <belajar-access@yahoogroups.com>
ReplyTo: belajar-access@yahoogroups.com
Subject: [belajar-access] Textbox terisi value yg sama dengan men-double klik?

 

Mohon pencerahannya para Accesser..

Misalkan saya punya Textbox namanya "Alamat Siswa", kemuadian ada lagi text box namanya "Alamat Orang Tua", nah yg saya inginkan ketika textbox "Alamat Orang Tua" di double klik secara otomatis terisi value yg sama yg ada di text box "Alamat Siswa"..
Terimakasih sebelumnya...

__._,_.___
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

Re: [belajar-access] Re: Access Developer Extensions

 

Di trust center ada macro setting. Set yg paling low.

Sent from my mobile®

From: "Coyev" <coyev@yahoo.com>
Sender: belajar-access@yahoogroups.com
Date: Sat, 31 Mar 2012 02:35:13 -0000
To: <belajar-access@yahoogroups.com>
ReplyTo: belajar-access@yahoogroups.com
Subject: [belajar-access] Re: Access Developer Extensions

 

Terima kasih Bang Haer atas petunjuk & informasinya. Smoga kebaikan Abang dibalas Allah SWT.

Kemarin saya coba, dengan langkah2 sbb: sebelum program dibuat ACCDE dan Developer Extensions, di Menu Access->Access Options->Trust Center->Klik Tombol <Trus Center Setting...> ->Trusted Locations : saya tambahkan lokasi folder dimana database akan ditempatkan (Mis. C:\Program Files\Aplikasi\).

Apa langkah2 saya benar atau salah? masalahnya masih tetap Macro tidak bisa jalan, tapi kalau VBA bisa jalan.

Sudi kiranya Bang Haer atau para acceser sekalian bisa menjelaskan lebih rinci, maklum saya orang newbie.

Terima Kasih atas perhatiannya.

Wassalaam,

Toni Coyev

--- In belajar-access@yahoogroups.com, Haer Talib <haertalib@...> wrote:
>
> Kayaknya sih masalah security warning / security notice saja.
>
> Pada Access 2007 dan 2010, anda bisa menambahkan lokasi file database
> berada ke Trusted Location, jadi tdk muncul warning, dan juga VBA atau
> macro bisa dijalankan.
>
> HAER
>
>
> 2012/3/30 Coyev <coyev@...>
>
> > Terima kasih Kang Opi atas informasinya, semoga Allah SWT membalasNya.
> > Amiin... Saya coba install ulang lagi programnya, masalah yang pertama
> > sudah tidak ada. Tp masalah yang ke dua dan ke tiga masih tetap.
> >
> > Mungkin ada solusi untuk masalah ke dua dan ke tiga dari para Accesser
> > semua. Terima kasih atas perhatiannya....
> >
> > Wassalaam,
> > Toni Coyev
> >
> >
> >
> > --- In belajar-access@yahoogroups.com, Sofyan Efendi <sofyanefendi@>
> > wrote:
> > >
> > > Dear Pak Toni, saya pernah mengalaminya. Error terjadi karena didalam VBA
> > > menggunakan reference yang mana di Access 2007 ada, namun di PC yang
> > > menggunakan Access 2003 tidak ada. Atau karena kesalahan install, di PC
> > > tersebut ada dua versi Access yg berbeda, dimana yang diinstall pertama
> > kali
> > > Access versi diatasnya, kemudian yg kedua diinstall Access versi
> > dibawahnya.
> > > Secara default, Windows akan menjalankan versi Access yang terakhir kali
> > > dibuka.
> > >
> > > Barangkali teman2 ada yang menambahkan, dipersilahkan yah...
> > >
> > > Wassalaamu'alaikum Warahmatullahi Wabarakatuh,
> > > Sofyan Efendi
> > > http://imopi.wordpress.com/
> > > ----- Original Message -----
> > > From: "Coyev" <coyev@>
> > > To: <belajar-access@yahoogroups.com>
> > > Sent: Friday, March 30, 2012 1:15 PM
> > > Subject: [belajar-access] Access Developer Extensions
> > >
> > >
> > > > Saya coba membuat Developer Extensions Access 2007, kemudian saya
> > install
> > > > di komputer yang ada Ms. Access 2003. Masalahnya :
> > > > - Begitu program di Running suka keluar dulu kotak Warning
> > > > - Form yang mengandung Macro tidak bisa dipanggil, keluar tulisan:
> > > > can't run the macro or call back function 'OnActionButton'.
> > > > Make sure the macro or function exist and take the correct parameter
> > > > - Program dari Access 2003 (MDE) jadi tidak jalan.
> > > >
> > > > Mohon solusi & bantuannya dari para Accesser. Terima kasih atas
> > > > perhatiannya.
> > > >
> > > > Regard,
> > > >
> > > > Toni Coyev
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > SPAM IS PROHIBITEDYahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > SPAM IS PROHIBITEDYahoo! Groups Links
> >
> >
> >
> >
>
>
> --
>
>
> Haer Talib
>
> *RumahAccess Indonesia* <http://www.rumahaccess.com>
> *Tempatnya Belajar Microsoft Access*
> Artikel | Forum | Milis | Download | Training | Links | Blog | Event
>

__._,_.___
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

[belajar-access] Textbox terisi value yg sama dengan men-double klik?

 

Mohon pencerahannya para Accesser..

Misalkan saya punya Textbox namanya "Alamat Siswa", kemuadian ada lagi text box namanya "Alamat Orang Tua", nah yg saya inginkan ketika textbox "Alamat Orang Tua" di double klik secara otomatis terisi value yg sama yg ada di text box "Alamat Siswa"..
Terimakasih sebelumnya...

__._,_.___
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

RE: [MS_AccessPros] Create a query omitting related records (IN ?)

 

Connie-

I don't get any errors when I open form CustomerListOld on my machine. I
started to investigate the underlying queries, but it made my head spin around!

Can you explain in English what it is you want to display on the final form or
report? There's gotta be an easier way to do this!

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
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 mrsgoudge
Sent: Friday, March 30, 2012 9:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)

 
John,
I'm not at work since I don't work Fridays so I'm working from memory.

Sorry--I must not have been clear. You are right, Customer List is working and
as far as I can see is ordering them aright. I do not always want the males to
be first. It's based upon each transaction so the Ordr in the relevant event
will determine who is ContactID and who is SpouseID. I had not realized this
when first creating it and that's why I'm redoing it.

The CustomerListOld form is the one that was giving me the "cannot open any more
databases" message. It happened when I had other forms open. Hopefully,
decreasing the number of queries behind the CustomerList form will keep this
from being a problem

1. Would you please check the queries behind the CustomerList?
--a. The qCustomerList...SpID queries the Contact form. Should I be getting all
relevant Contact info at that point so that the Contact table doesn't need to be
opened again later in another query? Everything in this query will be joined
with about 6 queries later so if I add more fields from Contact table there will
be more info to be joined. Makes a difference?

--b. I believe that there will be a union query of 6 or so queries (each with
the relevant status info). Any tips on creating efficient union query?
qCustomerListClosedSeller is the one I will use as a template for the others.
And all of them will be joined together and that union query will be used to
create qCustomerList.

I'm looking forward to getting to work on this once I have your feedback. Tis
the season for more houses to be put on the web thus giving me less time to work
on Access.

Thanks again!
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Upon further reflection, I think you can get the "male" spouses to always
appear
> first by changing qCustomerListContactNameSPID to this:
>
> SELECT ContactsLink.ContactID1, ContactsLink.ContactID2 As SpID
> FROM ContactsLink
> WHERE (((ContactsLink.TypeID1) In (115,116)))
> UNION SELECT ContactsLink.ContactID2, ContactsLink.ContactID1 As SpID
> FROM ContactsLink
> WHERE (((ContactsLink.TypeID1) In (115,116)))
> UNION SELECT ContactsLink.ContactID1, ContactsLink.ContactID2 As SpID
> FROM ContactsLink
> WHERE (ContactsLink.TypeID1 = 114) And ((ContactsLink.TypeID2 <> 115) Or
> (ContactsLink.TypeID2 IS NULL))
> UNION SELECT ContactsLink.ContactID2, ContactsLink.ContactID1 As SpID
> FROM ContactsLink
> WHERE (ContactsLink.TypeID2 = 114) And ((ContactsLink.TypeID1 <> 115) Or
> (ContactsLink.TypeID1 IS NULL));
>
> Note that the first two are as in your original query, except I don't pick up
> TypeID 114 - spouse. Those should be gathered as a pair to the 115 (husband)
> records. The last two pick up any "stray" 114 records where the partner ID
> isn't 115 or is blank.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> 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 John Viescas
> Sent: Thursday, March 29, 2012 8:15 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Create a query omitting related records (IN ?)
>
>  
> Connie-
>
> The Customer List form opens for me just fine with no errors. But it does have
> entries that don't meet your criteria, such as:
>
> Contact 1 Spouse
> Bur Cari Matthew
>
> If I understand your requirement correctly, you want Matthew listed as the
> Contact 1 and Cari listed as the Spouse, correct? (How sexist is THAT?)
>
> If that's the case, I'll start playing with it after you confirm.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> 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 mrsgoudge
> Sent: Wednesday, March 28, 2012 10:01 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)
>
>  
> It's in Files> 2_AssistanceNeeded>MSAccessPro3.26.12.accdb . Sorry--should
have
> realized the name was the name of the file I uploaded.
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Connie-
> >
> > I don't see your file. Where did you put it?
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > 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 mrsgoudge
> > Sent: Wednesday, March 28, 2012 7:31 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)
> >
> >  
> > John,
> > I've uploaded the applicable info--Connie's Customer List. Would be
wonderful
> if
> > you'd see something that would help solve the "too many databases" message!
> >
> > I am using a form for the report since we want buttons for each line so a
> click
> > will bring up the related event, main contact or spouse.
> >
> > Contacts for an event that are not a spouse/partner have their own line on
the
> > form. Otherwise they are combined.
> >
> > Form.CustomerList is the new one. It is for one status. After your approval
> I'll
> > adapt it for the other statuses and use a union query to put them together.
Is
> > it best to try to open a table once and get all the info needed at that
point?
> > If so, I'll need to try to do that with qCustomerListContactNameSpID. A new
> > assumption for this form is that the order for who should be the first
contact
> > is determined by the event (Listing, looking, buying etc. so the Ordr that
is
> > selected in that event is used).
> >
> > Form.CustomerListOld is the original form minus the data that is in
> > CustomerList. It should give you a sense for what I'm trying to do even
though
> > it's obviously problematic. An assumption here is that one person will
always
> be
> > the first when their names are combined and this data is drawn from the
> > ContactsLink table. Wrong assumption.
> >
> > A key part of the Customer List Form (which is used as a report) is the
> person's
> > status for the line of info. Each line represents one of the statuses in the
> > list below. Could have 5 lines of "Closed Buyer"for a person if they bought
5
> > properties with us, etc. Most statuses are derived based on whether they are
> in
> > sales, pending, withdrawn etc. tables but some were entered in
Contact_Status
> > because we need to have info about customers in this form that predates
2011.
> >
> > -Buyer—currently looking or a potential looker
> > -Past Buyer—Looked with us but ended up buying elsewhere
> > -Closed Buyer—Looked and bought with us representing them
> > -Seller—currently listed with us
> > -Past Seller—Previously listed with us and then listed with another realty
> > -Closed Seller—Listed their house and sold with us or we represented the
buyer
> > as he bought another agency's listing
> >
> > Tables—misc info:
> > HomeInfo (basic info on a house—not changed over time)
> > Contact_Status—pre 2011 status, year, etc are entered here. "Ent" in queries
> > ContactLink (relationships between contacts) is a fairly new addition so
info
> re
> > spouses is also elsewhere.
> > LookingEvent (Opening of event for buyers, begin/end date etc.)
> > LookingContacts (I think the info re what a buyer is looking for will be
moved
> > from this table in the future to its own table)
> > SellerType_List (Seller, renter, non seller to contact). I recognize that
this
> > list needs to be changed because of now having the ContactLink table
> >
> > Misc info on queries:
> > -qCustomerListContactNameRelWSp – Contacts with a husband, wife, or partner
> > -qCustomerListContactNameRelNotSpouse—Contacts without husband,wife or
partner
> > but in the ContactsLink table
> > -qCustomerListContactNameRelWSpouseReversed—Reversing the order of husband
and
> > wife. (Ex: Usually Kerri Bar is the first listed, but her spouse Ed Bra is a
> > Buyer since he's looking for a house for his mother. Kerri is associated
with
> > him in the spouse/partner columns)
> > -qCustomerListOffNoStatusDerived (Those sellers who took their house off the
> > market and did not relist with anybody)
> > -qCustomerListSellersRelisted (Took off market and then relisted)
> >
> > Thank you VERY much!
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > Connie-
> > >
> > > Run a query:
> > >
> > > UPDATE MyTable
> > > SET LastName = Left([LastName], 3)
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > 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 mrsgoudge
> > > Sent: Tuesday, March 27, 2012 11:53 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] Create a query omitting related records (IN
?)
> > >
> > >  
> > > John,
> > >
> > > How would I create and run code to change data in tables. For example:
> replace
> > > the last name with the first 3 letters of the last name?
> > >
> > > Thanks!
> > > Connie
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@>
wrote:
> > > >
> > > > John,
> > > >
> > > > I have one element working--got rid of the distinct and simplified it a
> bit.
> > > Before changing the rest I am planning on running it by you. Other work,
> > family
> > > emergencies, etc. have gotten in the way of getting it ready to send.
> > Hopefully
> > > will work today or tomorrow.
> > > >
> > > > Thanks for checking and being willing to look it over!
> > > > Connie
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> > > > >
> > > > > Connie-
> > > > >
> > > > > Did you ever solve this?
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside Out
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > 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 John
> Viescas
> > > > > Sent: Wednesday, March 21, 2012 10:42 AM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: RE: [MS_AccessPros] Create a query omitting related records
(IN
> > ?)
> > > > >
> > > > >  
> > > > > Also provide a sample of what it is you're looking for in your report
-
> > all
> > > the
> > > > > fields.
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside Out
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > 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 John
> Viescas
> > > > > Sent: Wednesday, March 21, 2012 10:28 AM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: RE: [MS_AccessPros] Create a query omitting related records
(IN
> > ?)
> > > > >
> > > > >  
> > > > > Connie-
> > > > >
> > > > > That gives me a headache. Any particular reason you're using DISTINCT?
> > > > >
> > > > > I think we need to back up and look at the table design again. Can you
> lay
> > > it
> > > > > out in a message or upload your database structure to Assistance
Needed?
> > > (Don't
> > > > > need the data necessarily.)
> > > > >
> > > > > I'm off to Luxembourg until Saturday, so won't be able to respond
again
> > > until
> > > > > the weekend.
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside Out
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > 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
mrsgoudge
> > > > > Sent: Tuesday, March 20, 2012 10:24 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] Create a query omitting related records
(IN
> > ?)
> > > > >
> > > > >  
> > > > > John, I would love that! When I haven't been sick, on vacation, or
> working
> > > on
> > > > > other tasks I've been getting the stuff ready to give you.
> > > > >
> > > > > BUT today I'm realizing that the underlying queries need to be
changed.
> I
> > > made
> > > > > the assumption that the husband/wife/partner names would always be in
a
> > > certain
> > > > > order (ex: Paul would always be the main/first contact and Connie the
> > > secondary
> > > > > one). WRONG! *&$%#! (How do you say that? ;-))
> > > > >
> > > > > I had created a several queries which each derived a status for a
> > particular
> > > > > contact involved in an event (Listing, Buying, Selling, etc) (Status =
> > > Buyer,
> > > > > Past Buyer, Closed Buyer ,etc.). One or more query for each status.
Then
> I
> > > made
> > > > > a union query of these and then combined with another query which had
> the
> > > proper
> > > > > order of spouses/partners taken from the ContactsLink table: Ex. Paul
> then
> > > > > Connie. But now I find the proper order is determined in the event.
> > > > >
> > > > > What I'm doing is creating a Customer List Report as dictated to me by
> my
> > > boss.
> > > > > If Paul and Connie Barnett (married) have sold 3 different houses that
> > would
> > > be
> > > > > 3 lines. --(LastName, FirstName, SpousesName, Current Address, ID of
the
> > > related
> > > > > event, etc.)
> > > > > If they had sold 3 different houses and their last names were
different
> > (ex:
> > > > > Paul Hanson and Connie Denler, then there would be 6 lines--3 with
> Connie
> > > first
> > > > > and 3 with Paul. If Connie is looking for a house for her father, that
> > would
> > > be
> > > > > another line where Paul's name would be in the SpousesName column but
> not
> > in
> > > a
> > > > > separate line even if his last name is different. If Paul and Connie
> were
> > > > > brother and sister then they would each have their own line and if
they
> > were
> > > > > each married, their spouses name would be in the spouse column. In
each
> > > event
> > > > > there is an Ordr. The one with the lowest number should be the first
> > listed.
> > >
> > > > >
> > > > > So the initial queries that figure out the statuses also need to
include
> > the
> > > > > ContactID's that should be associated with that status/event and
exclude
> > the
> > > > > ContactsID's for the spouse if they have the same last name.
> > > > >
> > > > > Here's the logic as I see it:
> > > > > If 2 of the contacts in the EventContact table (ex: ListingContacts)
are
> > > > > associated in the ContactsLink table with a TypeID1 of 114,115,116
> (wife,
> > > > > husband, partner) AND they have the same last name then the one with
the
> > > smaller
> > > > > Event.Ordr is the ContactID that should be in the queries results.
> > Otherwise
> > > > > both ContactID's are included.
> > > > >
> > > > > The ContactsLink table has the following related fields:
> > > > > ContactID1, ContactID2, TypeID1, TypeID2. One record could have the
> > > following
> > > > > info with the ID portion fleshed out:
> > > > > Paul Hanson, Connie Hanson, Husband, Wife.
> > > > > (ContactID =102,ContactID =113,TypeID1 =115,TypeID2 =114)
> > > > >
> > > > > Here's an example of one of the initial queries that is deriving a
> status
> > > that
> > > > > I'd like to adapt to account for the above scenario. It derives the
> > Contacts
> > > > > with a "seller" (lister) or "contact" status.
> > > > >
> > > > > SELECT DISTINCT Contacts.ContactID,
> > > > >
> > >
> >
>
IIf([SellerType_List].[Category]="s",2,IIf([SellerType_List].[Category]="c",11,"
> > > > > ")) AS StatusID, Listings.ListID AS ID, DatePart("yyyy",Date()) AS Yr,
> > > > > Listings.AgentID, Listings.ListingAgency AS Agency, Listings.Notes AS
> > > Notes2,
> > > > > ListingContacts.Priority AS Ordr
> > > > > FROM (Listings LEFT JOIN Withdrawn ON Listings.ListID =
> Withdrawn.ListID)
> > > INNER
> > > > > JOIN (Contacts INNER JOIN (ListingContacts INNER JOIN SellerType_List
ON
> > > > > ListingContacts.SellerTypeID = SellerType_List.SellerTypeID) ON
> > > > > Contacts.ContactID = ListingContacts.ContactID) ON Listings.ListID =
> > > > > ListingContacts.ListID
> > > > > WHERE (((Listings.ListingAgency)=1) AND ((Listings.CurrentListing)=-1)
> AND
> > > > > ((SellerType_List.Category)="s" Or (SellerType_List.Category)="c")) OR
> > > > > (((Listings.ListingAgency)=1) AND ((SellerType_List.Category)="s" Or
> > > > > (SellerType_List.Category)="c") AND ((Listings.ExpDate)>Date()) AND
> > > > > ((Withdrawn.ListID) Is Not Null));
> > > > >
> > > > > Thanks a zillion!
> > > > > Connie
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> > wrote:
> > > > > >
> > > > > > Connie-
> > > > > >
> > > > > >
> > > > > >
> > > > > > You're going to have to give us the underlying queries to solve
this.
> > And
> > > we
> > > > > > might solve your "too many databases" problem in the bargain!
> > > > > >
> > > > > >
> > > > > >
> > > > > > John Viescas, author
> > > > > >
> > > > > > Microsoft Office Access 2010 Inside Out
> > > > > >
> > > > > > Microsoft Office Access 2007 Inside Out
> > > > > >
> > > > > > Building Microsoft Access Applications
> > > > > >
> > > > > > Microsoft Office Access 2003 Inside Out
> > > > > >
> > > > > > SQL Queries for Mere Mortals
> > > > > >
> > > > > > <http://www.viescas.com/> http://www.viescas.com/
> > > > > >
> > > > > > (Paris, France)
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> mrsgoudge
> > > > > > Sent: Monday, March 12, 2012 5:46 PM
> > > > > > To: MS_Access_Professionals@yahoogroups.com;
> > > > > > MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: [MS_AccessPros] Create a query omitting related records (IN
> ?)
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > Good morning! Or whatever it is where you are :-)
> > > > > >
> > > > > > I have a series of many queries that is used to create a Customer
list
> > > which
> > > > > has
> > > > > > one line per Customer/transaction. This is about the final query.
> > > > > >
> > > > > > Husbands/wives or partners haves two lines per transaction--one for
> each
> > > > > > ContactID. A transaction (Pending, Off, Sold, Renting ...) can have
an
> > > > > infinite
> > > > > > number of contacts linked with it.
> > > > > >
> > > > > > I would like to only have one record per above mentioned
relationship
> > and
> > > have
> > > > > > the person with the lowest Ordr in that relationship be the one in
> > > > > > Contacts.ContactID column. The table that links them in relationship
> is
> > > > > > ContactsLink. The fields are:
> > > > > >
> > > > > > ContactsLinkID
> > > > > > TypeID1
> > > > > > TypeID2
> > > > > > ContactID1
> > > > > > ContactID2
> > > > > >
> > > > > > Types 114, 115, 116 refer to husband,wife, partner. I have included
> > > TypeID1 in
> > > > > > the query when it is 114,or 115 in case that helps.
> > > > > >
> > > > > > The Ordr comes from the related table PendingBuyers,
ListingContacts,
> > > > > > SalesBuyers. Sometimes there is no Ordr if the transaction was
entered
> > for
> > > a
> > > > > > year before 2011.
> > > > > >
> > > > > > I am a bit concerned because this is the recordsource for one of the
> > forms
> > > > > > (which acts as a report) that gives me the "cannot open any more
> > > databases"
> > > > > > messages when there are a couple of other forms open.
> > > > > >
> > > > > > Here's the query for the recordsource. I'd give you the underlying
> > queries
> > > but
> > > > > > there are MANY of them.
> > > > > >
> > > > > > SELECT qCustomerList_Pre1.ContactID, qCustomerList_Pre1.Last,
> > > > > > qCustomerList_Pre1.First, qCustomerList_Pre1.Spouse,
> > > > > qCustomerList_Pre1.Mailing,
> > > > > > qCustomerList_Pre1.City, qCustomerList_Pre1.ST,
> qCustomerList_Pre1.Zip,
> > > > > > IIf([ContactFormNotes] Is Not Null,[ContactFormNotes] & "; " &
> > > > > > [Notes2],[Notes2]) AS Notes, qCustomerListAllStatusYrListID.Yr,
> > > > > > Status_List.Status, qCustomerListAllStatusYrListID.ID,
> > > > > > IIf([SpID]="",Null,CLng([SpID])) AS SpouseID, Agent_List_1.AgentFN,
> > > > > > qCustomerListAllStatusYrListID.Ordr, qCustomerList_Pre1.TypeID1
> > > > > > FROM qCustomerList_Pre1 LEFT JOIN ((Status_List RIGHT JOIN
> > > > > > qCustomerListAllStatusYrListID ON Status_List.StatusID =
> > > > > > qCustomerListAllStatusYrListID.StatusID) LEFT JOIN Agent_List AS
> > > Agent_List_1
> > > > > ON
> > > > > > qCustomerListAllStatusYrListID.AgentID = Agent_List_1.AgentID) ON
> > > > > > qCustomerList_Pre1.ContactID =
> qCustomerListAllStatusYrListID.ContactID
> > > > > > WHERE (((Status_List.Status) Is Not Null)) OR
> > > > > > (((qCustomerListAllStatusYrListID.ContactID) Is Not Null))
> > > > > > ORDER BY qCustomerList_Pre1.Last, qCustomerList_Pre1.First;
> > > > > >
> > > > > > Thanks!
> > > > > > Connie
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > [Non-text portions of this message have been removed]
> > > > > >
> > > > >
> > > >
> > >
> >
>

__._,_.___
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] Help Hiding Document Tabs using VBA

 

Hi Pablo,

use Overlapping Windows instead of Overlapping Documents as your interface -- then you have a lot more control :)

Switch from tabs to overlapping windows (2007)
http://office.microsoft.com/en-us/access/HA012292811033.aspx

~~~ 2007
(1) Click the Microsoft Office Button Button in the upper left corner
(2) choose --> Access Options command button in lower right

~~~ 2010
(1) Click the File menu option in the upper left
(2) choose --> Options from the bottom of the left sidebar menu

The Access Options dialog box appears.

(3) choose --> Current Database

(4)  In the Application Options section, under Document Window Options:

check 'Overlapping Windows' or 'Display Document Tabs'

Overlapping Windows is what Access 2003 and previous versions use

Warm Regards,
Crystal

Microsoft MVP, Access
remote training and programming

www.AccessMVP.com/strive4peace

www.YouTube.com/LearnAccessByCrystal
www.YouTube.com/LearnByCrystal

* (:  have an awesome day :) *

________________________________
From: Pablo <psanchez95@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Saturday, March 31, 2012 1:25 AM
Subject: [MS_AccessPros] Help Hiding Document Tabs using VBA

Hi folks, can anyone tell me if they know how to hide the document tabs of a form using VBA? I have uploaded a picture of the problem in the "2-Assisted Needed" folder. FILE NAME: PS-03.31.2012

Thank you.
Pablo

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

__._,_.___
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] Indexing Fields

 

hi Hendra,

1. when a relationship has RI, Access automatically creates hidden index(es).  IMO, it is not advisable to create another one since it is redundant.  To see all Indexes, even Hidden ones, run my Analyzer and look at the reports

Crystal's Analyzer
http://www.accessmvp.com/strive4peace/Analyzer.htm

2. No

3. Allen Browne has great examples here:

http://allenbrowne.com/func-DAO.html

Warm Regards,
Crystal

Microsoft MVP, Access
remote training and programming

www.AccessMVP.com/strive4peace

www.YouTube.com/LearnAccessByCrystal
www.YouTube.com/LearnByCrystal

* (:  have an awesome day :) *

________________________________
From: "agesthahendra

Hi Everyone...
I have several questions about indexing...

* Is there any effects if we indexed manually the foreign key
  fields..?(the RI is implemented)... i just want to make sure the
  fields are indexed because i want to transfer the MDB to Mysql.

* Does Access create index to the field automaticly if we build join in
  query design (not in tables relationships window)..?
  (the tables are not joined in tables relationships window)

* To make the process faster, do we have to drop the index of an
  indexed field before we run Append / Delete query...?
  If so, how the syntax to drop and create index (create index with
  the type of index ... Duplicate OK or No Duplicate)

Any explanation would be appreciated..thank you

Regards
Hendra

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

__._,_.___
Recent Activity:
.

__,_._,___