Combo box issues :s

[Lakhvir]

Hi,

I am using a combo box to select an appointmenttype. The appointment types are coming from another table. I am using a dlookup to also get the cost per hour from the same table.

Trouble is that in my subform, whenever I actually make a booking and select an appointment type, new records keep on going into the appointmenttype field in the tblAppointmentTypes.


Here are two screen dumps:

http://i40.tinypic.com/akyjx0.png

http://i43.tinypic.com/rkpjsx.png

What is causing this, and how do I prevent it?

Many thanks.

 

[NickHa]

What code do you have for the click event on your combo box? It sounds like the problem lies there, but it's impossible to tell without further details.

 

[Lakhvir]

What code do you have for the click event on your combo box? It sounds like the problem lies there, but it's impossible to tell without further details.

Hi, thank you for replying.

I've used a dlookup in the afterupdate thing. Here is the code:

Private Sub Combo10_AfterUpdate()
Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[AppointmentTypeID]=" & Me.Combo10)
End Sub

 

[Kiwiman]

Howzit

What is the recordsource of your subform?

 

[Lakhvir]

Howzit

What is the recordsource of your subform?

Hi,

How do you find out the recordsource of the whole subform? Clueless here lol

Actually, I think it's this:

SELECT [tblAppointmentBookings].[AppointmentBookingID], [tblAppointmentBookings].[BookingID], [tblAppointmentBookings].[AppointmentTypeID], [tblAppointmentBookings].[SessionLength], [tblAppointmentTypes].[AppointmentType], [tblAppointmentTypes].[CPH] FROM tblAppointmentTypes INNER JOIN tblAppointmentBookings ON [tblAppointmentTypes].[AppointmentTypeID] =[tblAppointmentBookings].[AppointmentTypeID];

 

[Kiwiman]

Howzit

In design view of yor form click on the top left corner of your subform , rt clik properties -> Go to the Data tab

 

[Lakhvir]

Howzit

In design view of yor form click on the top left corner of your subform , rt clik properties -> Go to the Data tab

SELECT [tblAppointmentBookings].[AppointmentBookingID], [tblAppointmentTypes].[AppointmentType], [tblAppointmentTypes].[CPH], [tblAppointmentBookings].[BookingID], [tblAppointmentBookings].[AppointmentTypeID], [tblAppointmentBookings].[SessionLength] FROM tblAppointmentTypes INNER JOIN tblAppointmentBookings ON [tblAppointmentTypes].[AppointmentTypeID] =[tblAppointmentBookings].[AppointmentTypeID];

Thanks. I've been trying to do the same thing all night lol :L

 

[Kiwiman]

Howzit

The reason that you are finding your appointmenttype table being updated as you have bound your that table to your form. There is no need to join the appointmenttypes table to the appointmentbookings table in this recordsource. Is there any reason why you cannot just use the appointmentbookings table as your recordsource?

What fields do you have in the appointmentbookings table.

Alos what is the recordsource of your main form?

 

[Lakhvir]

Howzit

The reason that you are finding your appointmenttype table being updated as you have bound your that table to your form. There is no need to join the appointmenttypes table to the appointmentbookings table in this recordsource. Is there any reason why you cannot just use the appointmentbookings table as your recordsource?

What fields do you have in the appointmentbookings table.

Alos what is the recordsource of your main form?

Hello (Really sorry about this, but I'm a super noob )

I have attached a shell database for you. It contains the tables that I want to use. What I want to do is make a booking form with a subform on it.

I want the main booking form to have the following fields:
BookingID
ClientID
StaffID
StartTime
EndTime
Date
TotalCost

On this main booking form I want a subform. The subform should contain the following fields:
AppointmentBookingID
BookingID
AppointmentType
CPH
SessionLength

I want the appointment type to be a drop down list.

Can you see if this whole idea is even feasible for me? Or am I just doing something that is impossible? Maybe once you figure it out you can help me understand more

I'm very sorry and I hope you ain't getting angry with me I'm a really newbie hence why I'm soo confused.

Please, and thankyou.

 

[Kiwiman]

Howzit

You need to add a new field in the tblAppointmentBookings table to hold the CPH (what is this?). If this is Cost Per Hour then surely yu will need one other field to hold the number of hours? Then use the tblAppointmentBookings as the recordsource for your subform.

The recordsource of your main form should be the table tblBookings - linking on BookingId.

In your subform you have the combobox (bound to the Appointmenttypeid of your bound tblAppointmentBookings using the after_update event procedure to populte the CPH.

 

[Lakhvir]

Howzit

You need to add a new field in the tblAppointmentBookings table to hold the CPH (what is this?). If this is Cost Per Hour then surely yu will need one other field to hold the number of hours? Then use the tblAppointmentBookings as the recordsource for your subform.

The recordsource of your main form should be the table tblBookings - linking on BookingId.

In your subform you have the combobox (bound to the Appointmenttypeid of your bound tblAppointmentBookings using the after_update event procedure to populte the CPH.

CPH is cost per hour. SessionLength is the number of hours. If I hold these fields again, then doesn't the database become un-normalized?

 

[Kiwiman]

Howzit

No not at all.

In regards to CPH, you also need this in your tblAppointmentBookings table to accurate calculate the total cost for the line.

Your tblAppointmentTypes table is your master table that holds your CPH that is in effect currently. So when you make new bookings this is the value your dlookup uses.

So when you make changes to the CPH in your tblAppointmentTypes table, it will not affect any historical bookings as they already have the CPH stored in the tblAppointmentBookings when the bookings were made.

You will not however need another field to hold the number of hours as you already have it.

 

[Lakhvir]

Howzit

No not at all.

In regards to CPH, you also need this in your tblAppointmentBookings table to accurate calculate the total cost for the line.

Your tblAppointmentTypes table is your master table that holds your CPH that is in effect currently. So when you make new bookings this is the value your dlookup uses.

So when you make changes to the CPH in your tblAppointmentTypes table, it will not affect any historical bookings as they already have the CPH stored in the tblAppointmentBookings when the bookings were made.

You will not however need another field to hold the number of hours as you already have it.

Do you mean in the tblAppointmentBookings form? I quite don't understand :/

Everything that you've said makes sense, but urghh

 

[Kiwiman]

Howzit

No you need to add it to the tblAppointmentBookings TABLE like I said. You already have the control on the form, but it is bound to the wrong table. On the subform you need to change the recordsource to the tblAppointmentBookings TABLE

 

[Lakhvir]

Howzit

No you need to add it to the tblAppointmentBookings TABLE like I said. You already have the control on the form, but it is bound to the wrong table. On the subform you need to change the recordsource to the tblAppointmentBookings TABLE

Ooh, I'm sorry
Can I call the field the same thing? CPH?

I have reattached the database. Is this right now?

 

[Kiwiman]

Howzit

You can call the field CPH - in fact that's what I would do.

 

[Lakhvir]

Howzit

You can call the field CPH - in fact that's what I would do.

Ok. So what fields would I bring into the sub-form?

Currently, the main form has:
BookingID, ClientID, StaffID, StartTime, EndTime, Date

The subform has:
AppointmentBookingID, BookingID, AppointmentTypeID, SessionLength, CPH, AppointmentType

Now that I'm getting CPH from the tblAppointmentBookings, where are the prices coming from?

Really confused :/

 

[Kiwiman]

Howzit

Mainform fields - what you have

Subform fields - what you have except remove the AppointmentType. You already have the AppointmentTypeID using a combobox to select the appointment type from the table AppointmetnTypes - so you do not need both. The recordsource for this combobox should be:

SELECT tblAppointmentTypes.AppointmentTypeID, tblAppointmentTypes.AppointmentType FROM tblAppointmentTypes;

The combobox should have the following properties

• Bound Column = 1
• Column Count = 2
• Column Widths = 0;5

You are not getting the CPH from tblAppointmentBookings you are storing them in this table when the after_update event procedure of the combobox is triggered. You are getting them from the tblAppointmentTypes table as this is your master table that holds the CPH per appointmenttype.

 

[Lakhvir]

Howzit

Mainform fields - what you have

Subform fields - what you have except remove the AppointmentType. You already have the AppointmentTypeID using a combobox to select the appointment type from the table AppointmetnTypes - so you do not need both. The recordsource for this combobox should be:

SELECT tblAppointmentTypes.AppointmentTypeID, tblAppointmentTypes.AppointmentType FROM tblAppointmentTypes;

The combobox should have the following properties

• Bound Column = 1
• Column Count = 2
• Column Widths = 0;5

You are not getting the CPH from tblAppointmentBookings you are storing them in this table when the after_update event procedure of the combobox is triggered. You are getting them from the tblAppointmentTypes table as this is your master table that holds the CPH per appointmenttype.

Hi, thank you that makes much more sense now

So for the subform, I am using the following fields:

AppointmentBookingID, BookingID, AppointmentTypeID, SessionLength, CPH

So I need to make AppointmentTypeID a combo box? And then copy and paste the code you provided in the recordsource?

 

[Kiwiman]

Howzit

Yes that is correct. You will also need to create the after update event procedure for the combobox using the dlookup from the earlier post.