Holiday Booking

AdamHughes

New member
Local time
Today, 22:31
Joined
Feb 17, 2006
Messages
8
Hi,

I am working on a database for an assignment for university. I am seeking some help for one of the forms I am creating. I have a properties table listing all the details of a property and a unique ID, I have a client table and a booking table etc.

I want to create a booking form, but I want to make it so that the individual placing the booking can enter all the required information in this form. I have added all the tables required (three named above) and I have began to tidy everything up (removing the booking ID as that is auto generated, removing all of the property table fields and replacing it with a combo box) but the problem I am having is dealing with the client.

The problem is that if they already exist, I want to be able to select them from a combo box, and it'll update all the fields on the form relating to the client details, however, if the client isn't already set up in the database then they'll need to be added to the system and so all the fields will need to be filled in and the client ID will have to have its auto number created. But I just cannot work out how to get around this.

Can you shine any light on what I should attempt?

I tried doing the same as I did for property - creating a combo box, but because the client ID is an auto number in the client table, it won't let me select any values from the drop down. If I could do that, then I think it would work a treat.

I cannot change the data type from auto number to something else in the client table as the bare set up of the database I received has to remain the same as part of the assessment. I figured if I could change that to a plain text field then I wouldn't be having this problem. But I wonder if I can resolve it otherwise..?

Thanks to anyone who tries to help!
 
Using the ComboBox (or a List Box) is a good way to select existing clients and pre-fill Form details with the previous experience of that Client.

To utilize previously record Client information from the Clients Table you will need to ensure that your Combo Box is set up to accommodate the data you require for your current Form. You may not require all Data from the Clients Table since reference to that Client can always be made through the Clients Record Number (ID).

Assuming the Client Combo Box is bound to a Field within the Bookings Table. You want to fill the Combo with previously booked Clients. This is Fine but you will also want to definitely set the Limit To List property for this Combo to True so that only Clients within the list can be selected.

I know...you're saying, "What about New Clients I want to book and also have them added to the Clients Table"? This is where the NotInList event for the Client Combo comes into play. There are lots of examples here in the forum, on the internet, and in MS-Access help which demonstrates in various different ways of how to utilize the NotInList event.

To fill the the Clients Combo Box with Client Names you might have something like this within the RowSource property of the Client Combo:

SELECT * FROM Clients

This is a pretty basic query and places a lot of data into the Combo Box most of which could very well be unnecessary. It depends upon what Client data is really required within your Bookings Form. You may only want the Client ID, the Client name, and the Client Address. If this is the case then the query would look like this:

SELECT ClientID, ClientName, ClientAddress FROM Clients ORDER BY ClientName

Here you can see we've selected three fields from the Clients Table to be represented within the Clients Combo Box. This now means that each Client Row within the Combo Box contains three Columns of data. The first data column (Column(0))contains the ClientID, the second data column (Column(1)) contains the Client's Name, and finally the third data column (Column(2)) contains the Client's Address.

To reference these data columns of a Combo Box within code would be like this:

MsgBox "The selected Client's ID is: " & Me.ClientCombo.Column(0)
MsgBox "The Client's name is: " & Me.ClientCombo.Column(1)
MsgBox "The Client's Address is: " & Me.ClientCombo.Column(2)


See how that works?

You will of course need to set other properties for you Combo Box in order for it to function properly. For example, you will need to set the Column Count property. In our example above it should be set this to three since we are utilizing three Table fields for the Combo.

You will want to set the Column Widths property so as to only display the columns of data you want to really see within the drop-down list. In this case, you want to only see the Clients Name so, again with our example above, we would set the Column Widths property like this:

0";1";0"

By setting a Column to 0 (zero) the column becomes non-visible. It's still there, just not visible to the User. It is however accessible through code as shown above.

The Bound Column property is also of great importance. It determines which data column is placed into Table. You want to leave this to 1 (one) so that the ClientID is placed into Bookings Table.

Upon selection of a Client's name from within the Combo Box, you can utilize what has been shown here to automatically fill in specific fields of your Form through code placed into the OnClick event of your Client Combo Box.

If a new Client is entered into the Text area of the Combo Box and the Client does not exist within the Clients Table, then the Combo Box Not In List event is fired. Your code to handle adding the Client to the Clients Table would go into this event. I will not show you code for this event. That is research and development you can easily accomplish on your own to complete the task at hand.

Good luck with your project.

.
 

Users who are viewing this thread

Back
Top Bottom