Help with 1 Form, 1 Report and 3 Tables interaction.

[Procyan]

Hello All,
BackGround: I recently started to use Access. I have been around MS Office products for quite a while but never used Access until I got my current position. I have made automated forms with MSWord and macros but Access is a different machine.

I have 2 simple tables the first one is as follows. It has 3 information columns and the autonumber.(Primary Key) The table contains hotelname, hotelph, hotel fax.

The second table I have. It has 6 information columns and the autonumber. (Also Primary Key) This table contains. guestname, rank, ssn, requestedhoteldate, durationofstay, remarks.

Both of these tables correspond with a form. To make things easier I only put the hotel name in the form as a combobox, so you don't have to type in the hotel information every time. Now my question.

I need to take the chosen hotel and the information entered and put it into the completed Report to print?

I suspect that I will need a third table that combines the information from the form, and link that to the report but I can't figure out how to get just one hotel to show or the relationship I need between the form and the tables.

Thank you in advance,

Michael

 

[bobburg]

Help with 1 form, 1 report and 3 tables.

You need to show a releationship between tables. Go to the design of the first table and make the autonumber an indexed field, no duplicates. Go to the second table and make a field called Hotelnum in the design mode. Select indexed, duplicates allowed. Do not make a primary field. Make this field a number but have the hotel name show up. You can do this by going to the data type drop down box and select lookup wizard. Select, that you want to look up values in a table. Select, next and select the table where the holel info. is kept. Select next and select the autonumber field and the hotel name field. Select next, and make sure the check box 'Hide key column' is checked. Select next and finish. Tie the relationship in the relationship window which is a button on your tool bar. Drag across from the autonumber field in the hotel table to the hotelnum field in the other table. This will create a one to many relationship. One record in the hotel table to many records in the other table. When doing this select box labeled Enforce Referential Integrity and the box Cascade Update Related Fields. Do not select the other Cascade Delete Related Fields unless you want to delete all records in the second table that are related to a deleted hotel in the first table. Hope this works for you. The second part of making a report is easy. Create a Query that contains both table. Link the tables in the query by dragging from the Hotelnum field to the auto number field in the hotel table. Right ckick the line and edit the join type. Select all the records in the second table and only the records that match the first table. Select the field you are looking for. Make a report that uses the query. Good luck

 

[Procyan]

Thank you for your help Bob,
Everything seems to be working now. I put an OpenReport command button on the form that inputs the data into the report. I set it to open in PrintPreview mode. So from there I can just hit the Print and it will do that. But it doesn't allways display the active record that I just put data into. It also has 10 records down on the bottom. How can I get just the activereport to display and not have 10+ records to search through?

Michael