Full Version: indexing problem
UtterAccess Forums > Microsoft® Access > Access Forms
barrysmith83
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?
BrianS
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?
barrysmith83
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.
any suggestions?
Jack Cowley
From your description it sounds like Access is working correctly. Here is he correct setup in a One-To-Many relationship:
blCompany
CompanyID (PK and auto)
CompanyName
Address
...etc...
tblContacts
ContactID (PK and auto)
CompanyID (FK)
LastName
FirstName
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...
Jack
barrysmith83
cheers. had to change it slightly.
barrysmith83
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.
Help!
Any suggestions please?
Thanks,
barrysmith83
whats best to do? keep my all my tables and do it that way, or keep just the one table?
Jack Cowley
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....
th,
Jack
barrysmith83
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?
Thanks,
Jack Cowley
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...
hth,
Jack
barrysmith83
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.
thank you.
Jack Cowley
Barry -
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:
tblTypeID
TypeID (PK and auto)
Type
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.
hth,
Jack
barrysmith83
ok, thank you very much will give that a go!
Jack Cowley
Barry -
You are welcome and good luck with this!
Jack
barrysmith83
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??
Jack Cowley
Barry -
ach 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:
tblBookings
BookingID (Autonumber and the Primary Key
...other necessary fields...
tblAccomodations
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)?
Jack
barrysmith83
ok, will give that a go! thanks for ur help!
Jack Cowley
Barry -
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.
Jack
barrysmith83
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!
Jack Cowley
Barry -
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?
Jack
barrysmith83
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?
Thanks,
Jack Cowley
This code will make a subform control visible if it is hidden:
e.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...
Jack
barrysmith83
will give that a try! thank you. have a nice christmas!
Jack Cowley
You are welcome and a Merry Christmas to you!
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.