Daniel- And what constitutes a "duplicate" booking? Same member at the same facility at the same date and time? Same member in the same session?
John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Daniel Sent: Wednesday, December 14, 2005 6:54 PM To: John Viescas Subject: Re: Form help request Bookings table - BookingId (indexed no duplicates), Memberid, FacilityId, SessionNo, SessionDate, BookingDate, Cost BookingId is primary key MemberId, FacilityId and SessionNo are foriegn keys Data Types BookingId - Autonumber MemberId - Text FacilityId - Text SessionNo - Number SessionDate - Date/Time BookingDate - Date/Time Cost - Currency Thanks Daniel. --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Daniel- > > Well, you would write code in the BeforeUpdate event of the form for > entering new bookings to do a lookup on other bookings and make sure there's > not a duplicate. But as I indicated earlier, you might be able to > accomplish the same thing with a Unique key. I would need to know the > structure of your "bookings" table to tell for sure. > > John Viescas, author > "Building Microsoft Access Applications" > "Microsoft Office Access 2003 Inside Out" > "Running Microsoft Access 2000" > "SQL Queries for Mere Mortals" > http://www.viescas.com/ > > P.S. Had to post back to the group - your email is being returned as invalid > from Yahoo > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of > Daniel > Sent: Wednesday, December 14, 2005 5:32 PM > To: John Viescas > Subject: Re: Form help request > > Hi John, everything is working now in my database except the double > bookings problem, i.e. i can book the same court for the same time > and date. I was told this has to be dealt with in the VB code. do > you have any ideas? > > Thanks > > Daniel. > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Well, temporarily remove the relationships, define your indexes, > then put > > the relationships back. But I'm having a hard time envisioning > how adding a > > multiple-field unique index has anything to do with existing > relationships. > > > > John Viescas, author > > "Building Microsoft Access Applications" > > "Microsoft Office Access 2003 Inside Out" > > "Running Microsoft Access 2000" > > "SQL Queries for Mere Mortals" > > http://www.viescas.com/ > > > > > > -----Original Message----- > > From: [email protected] [mailto:[EMAIL PROTECTED] > On Behalf > > Of Daniel > > Sent: Tuesday, December 13, 2005 7:03 PM > > To: [email protected] > > Subject: [ms_access] Re: Form help request > > > > Hi, i cannot change the indexing as this is not allowed due to the > > relationships of the tables. > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > wrote: > > > > > > Daniel- > > > > > > I'd need to know more about the structure of your table, but > > perhaps you can > > > define a Unique index on the relevant fields to prevent the > > duplicates. > > > > > > John Viescas, author > > > "Building Microsoft Access Applications" > > > "Microsoft Office Access 2003 Inside Out" > > > "Running Microsoft Access 2000" > > > "SQL Queries for Mere Mortals" > > > http://www.viescas.com/ > > > > > > > > > -----Original Message----- > > > From: [email protected] > [mailto:[EMAIL PROTECTED] > > On Behalf > > > Of Daniel > > > Sent: Tuesday, December 13, 2005 6:48 PM > > > To: [email protected] > > > Subject: [ms_access] Re: Form help request > > > > > > Thanks for the help last night John. I got everything working, > now > > > im having problems with the booking form making double bookings > > i.e. > > > i have a court which i can book for a session time and date ( > this > > > can be double booked). any ideas? > > > > > > Thanks > > > > > > Daniel. > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > wrote: > > > > > > > > Daniel- > > > > > > > > Why a new query? What's the SQL of your new query? It sounds > > > like what you > > > > constructed is no longer updatable - hence the beeps. Can you > > > open the > > > > query and update records? > > > > > > > > John Viescas, author > > > > "Building Microsoft Access Applications" > > > > "Microsoft Office Access 2003 Inside Out" > > > > "Running Microsoft Access 2000" > > > > "SQL Queries for Mere Mortals" > > > > http://www.viescas.com/ > > > > > > > > > > > > -----Original Message----- > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] > > > On Behalf > > > > Of Daniel > > > > Sent: Monday, December 12, 2005 8:39 PM > > > > To: [email protected] > > > > Subject: [ms_access] Re: Form help request > > > > > > > > Hey John, done what you said and its still not working. Made a > > new > > > > qry from scratch and tried a brand new frm using that qry. Now > i > > > get > > > > a list of names and i cant select any of them, just get the > > > windows > > > > error beep in my ear when i click the options. > > > > > > > > Daniel. > > > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > > wrote: > > > > > > > > > > Daniel- > > > > > > > > > > You need to include Booking.MemberID and Booking.FacilityID > in > > > > your query. > > > > > For members, set Booking.MemberID as the Control Source of > > your > > > > combo box > > > > > (that's the Foreign Key), and as soon as you pick a value in > > the > > > > combo, > > > > > Access should autolookup the member's address info. Ditto > > > > FacilityID. > > > > > > > > > > John Viescas, author > > > > > "Building Microsoft Access Applications" > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > "Running Microsoft Access 2000" > > > > > "SQL Queries for Mere Mortals" > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > -----Original Message----- > > > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] > > > > On Behalf > > > > > Of Daniel > > > > > Sent: Monday, December 12, 2005 7:29 PM > > > > > To: [email protected] > > > > > Subject: [ms_access] Re: Form help request > > > > > > > > > > Ok, Here is the layout > > > > > > > > > > > > > > > Member: MemberID, Name, Address1, Address2, PostCode, > > > > > DateofBirth, CategoryCode > > > > > > > > > > Facility: FacilityID, FacDescription, Price (per session) > > > > > > > > > > Session: SessionNo, TimeRange > > > > > > > > > > Category: CategoryCode, CatDescription, AnnualCost, Discount > > > > > > > > > > Booking: BookingId, MemberID, FacilityID, SessionNo, > > > > SessionDate, > > > > > BookingDate, Cost > > > > > > > > > > > > > > > **SQL** > > > > > > > > > > > > > > > SELECT Member.Name, Member.Address1, Member.Address2, > > > > > Member.PostCode, Member.DateOfBirth, > Category.CatDescription, > > > > > Facility.FacDescription, Session.SessionNo, > Session.TimeRange, > > > > > Booking.SessionDate, Booking.BookingDate, Facility.Price, > > > > > Category.Discount, Booking.Cost > > > > > FROM [Session] INNER JOIN ((Category INNER JOIN Member ON > > > > > Category.CategoryCode = Member.CategoryCode) INNER JOIN > > > (Facility > > > > > INNER JOIN Booking ON Facility.FacilityId = > > Booking.FacilityId) > > > ON > > > > > Member.MemberId = Booking.MemberId) ON Session.SessionNo = > > > > > Booking.SessionNo; > > > > > > > > > > > > > > > Thanks > > > > > > > > > > Daniel. > > > > > > > > > > > > > > > > > > > > --- In [email protected], "John Viescas" > <[EMAIL PROTECTED]> > > > > wrote: > > > > > > > > > > > > Daniel- > > > > > > > > > > > > What's the structure of your tables? And what is the > > current > > > > > Record Source > > > > > > of the form (the SQL)? And please include the previous > text > > > > from > > > > > the thread > > > > > > in your replies. If I hadn't just answered your question, > I > > > > > wouldn't have a > > > > > > clue what you're talking about. > > > > > > > > > > > > John Viescas, author > > > > > > "Building Microsoft Access Applications" > > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > > "Running Microsoft Access 2000" > > > > > > "SQL Queries for Mere Mortals" > > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > From: [email protected] > > > > [mailto:[EMAIL PROTECTED] > > > > > On Behalf > > > > > > Of Daniel > > > > > > Sent: Monday, December 12, 2005 7:10 PM > > > > > > To: [email protected] > > > > > > Subject: [ms_access] Re: Form help request > > > > > > > > > > > > Hi John, im using a multi table query for the frm and i > have > > > the > > > > > combo > > > > > > box set for Name from Members tbl for risk of sounding > > stupid > > > > > (where > > > > > > do i set the foreign key)? > > > > > > > > > > > > p.s. i just bought your book on Sunday "Inside Out 2003" > > > > > > > > > > > > Daniel. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> Most low income homes are not online. Make a difference this holiday season! http://us.click.yahoo.com/5UeCyC/BWHMAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
