Dec 15 2005, 10:28 AM
when i go to my main booking form which is perhaps ID number '8'. If i click on the accomodation button to make a booking here, it jump to '4'. ie. the next space in the accomodation table.
Would the only way to solve this, be putting all the information into one big table?
Dec 15 2005, 10:31 AM
You are not providing enough information for anyone to help you.
Is the primary key of the record in your bookings form 8?
When you create a new record the ID becomes 4?
Is the ID an autonumber field?
Dec 15 2005, 10:42 AM
yes both tables are auto numbered. and both tables are linked with ID No. in the relationships.
When i fill in details on main form it is for eg. ID No. 11. When i click my command button to go to another form to enter more information, the ID No. then becomes maybe 4 or something as it is just jumping to the next record in its related table.
Dec 15 2005, 11:03 AM
From your description it sounds like Access is working correctly. Here is he correct setup in a One-To-Many relationship:
CompanyID (PK and auto)
ContactID (PK and auto)
If this is what you have then I assume Access is doing what it is supposed to do as the ID for Contacts will be nothing like the ID for Customers...
Dec 16 2005, 04:59 AM
cheers. had to change it slightly.
Dec 16 2005, 06:44 AM
What i have done is put everything into the one table. I have Booking ID on the main booking form, and the user has to click on a command button to get to accomodation form. On the main booking form, if the Booking ID was number eg. 29, if the user clicks on accomodation button to get there, the Booking ID displayed on the accomodation form isn't the same as the main booking form, it doesn't match it. It basically doesn't move.
Any suggestions please?
Dec 16 2005, 07:41 AM
whats best to do? keep my all my tables and do it that way, or keep just the one table?
Dec 16 2005, 11:26 AM
Without knowing what you are trying to accomplish it is hard to know how what to suggest. You database may only need one table, but only you can determine unless you explain what you want the db to do. The tables I suggested are a good example of a One-to-Many relationship. If your data is similar to that setup where you can have many records in a related table for each record in the primary table then you need two tables. If in a single table you end up with redundant information then there is a good chance you should not be using a single table....
Dec 18 2005, 03:25 PM
ive decided to use multiple tables. what the problem is creating the relatioships for the one-to-many. I have attached what i have to give u a better understanding.
Booking ID is primary key in 'booking details' table, and is a auto-number.
In the other tables Booking ID is the foreign key, and just number. Would this be correct? And how do i set up the realtionships properly?
Dec 18 2005, 04:16 PM
The Primary key and Foreign key are correct depending on the next paragraph....
You have an Events table and a Restaurant table and both of these tables have HighChairs and Covers as fields and I am not sure why. Also, what is the difference between a Restaurant and an Event? Can a Restaurant be an Event or related to an Event? Accomidation table looks like it is OK as it is but be sure and make the join between that and the Booking table and set referential integrity.
A hint - do NOT use spaces in object names...
Dec 19 2005, 06:04 PM
restaurant and event are two different things. event is perhaps a wedding or birthday, retuarant is every day occurance.
ok cheers, never used referential integrity before, thats what im prob doing wrong. will try that.
Dec 19 2005, 06:12 PM
You only need one table for Events and Restaurant since they have the same fields (covers, high-chairs, etc). Put all your data into a single table and add a field called TypeID. Create a new table:
TypeID (PK and auto)
In this table you will put Restaurant, Wedding, Birthday Party or whatever. Then this tables ID will go in the TypeID field in your Events table. Now you know if the Event is a Wedding or just the Restaurant... Now you only need referential integerity between tblTypeID and tblEvents.
Dec 20 2005, 04:12 PM
ok, thank you very much will give that a go!
Dec 20 2005, 04:27 PM
You are welcome and good luck with this!
Dec 21 2005, 06:58 AM
I can't get the basics to even work. I want the user to enter the customer's details on the main booking form, and this will autonumber to eg. 'BookingID' number 5. The user then clicks on the accomodation command button to take them to the accomodation form. The user would then enter the accomodation details, 'accomodationID' would autonumber to number 1, but the 'bookingID doesn't go to 5 to match the bookingID number from the main booking form.
I have set up "Booking Table" with BookingID (pk + autonumber) and "Accomodation table" with AccomodationID (pk + autonumber) and BookingID (foreign key + number). I have set up a one-to-many relationship for this.
How do i get the bookingID number to be the same as the main booking form when entering accomodation details?? Should relational integrity be selected??
Dec 21 2005, 12:17 PM
Each table should have an Autonumber as its Primary Key. This is a number that is used by Access and the users should have no need to see this number to know it event exits. There is, as far as I am concerned, no good reason to have any number available to the user as it will probably have no meaning for them anyway. Who knows who BookingID number 17 is anyway?
This is what your tables should look like and it sounds like you have them set up correctly:
BookingID (Autonumber and the Primary Key
...other necessary fields...
AccomodationsID (Autonumber and the Primary key)
BookingID (Number, Long Integer)
...other necessary fields...
Create a form based on tblBookings. Create a Datasheet view form based on tblAccomodations. Add the Datasheet view for of Accomodations to the Bookings form as as subform. Now you willl have your One-to-Many relationship and when you add a record to yoru Bookings form the BookingID will show up automatically in your Accomodation subform when you enter data there.
This should do what you want if it is still not working then can you compact/repair and zip your db and post it here (500k max size)?
Dec 21 2005, 01:11 PM
ok, will give that a go! thanks for ur help!
Dec 21 2005, 01:20 PM
I do not know if you had a form/subform setup before, but to use a table and a related table this is the setup you would normallly use. There are other ways to do this, but this is the easiest setup and Access will make the proper joins between the form/subform for you.
Dec 21 2005, 01:33 PM
got that working fine, thats what my problem was, didn't use a sub-form. ha. thanks.
Is it possible for the sub-form to be displayed when command button is selected? to keep it out the way!
Dec 21 2005, 03:08 PM
I just now saw your post. If you want me to know that you have made a reply then in my last message to you select Reply from the list to the right of my avatar. Then put your post in the Reply box at the bottom and I will be notified that you have responded.
I am not sure I understand your question. You can set the subforms Visible property to False to hide it and you can, using VBA, set the property to True to show it. Is that what you want to do?
Dec 22 2005, 03:06 PM
Yes that is what i want to do. I know how to set the sub-forms property to false, but what is the VBA code to show it, when i click on the accomodation button?
Dec 22 2005, 03:21 PM
This code will make a subform control visible if it is hidden:
Me.SubformControlName.Visible = True
I hope this is what you are after and be sure to change the name above to the actual name of your subform control...
Dec 22 2005, 06:46 PM
will give that a try! thank you. have a nice christmas!
Dec 22 2005, 06:50 PM
You are welcome and a Merry Christmas to you!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here