Combo box issues :s

[Lakhvir]

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.

Hi,

Is the dlookup to get the price from the tblAppointmentTypes?

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

I've tried the above in the afterupdate but it doesn't work I think it may be wrong

 

[Kiwiman]

Howzit

Yes the dlookup is to get the price from the tblAppointmentTypes table.

The syntax is correct. What error are you getting? What is the name of the combobox?

 

[Lakhvir]

Howzit

Yes the dlookup is to get the price from the tblAppointmentTypes table.

The syntax is correct. What error are you getting? What is the name of the combobox?

I'm getting this error: http://i43.tinypic.com/t6oyz5.png
It is called Combo14

Thanks!

 

[Kiwiman]

Howzit

Do you have anything populated in the Main form? Can you give me another screenshot of the message, but make it so that I can see the form fields

 

[Lakhvir]

Howzit

Do you have anything populated in the Main form? Can you give me another screenshot of the message, but make it so that I can see the form fields

Do you mean like this? http://i42.tinypic.com/34eqqt4.png

As far as I'm aware, nothing is populated.

Thanks.

 

[Kiwiman]

Howzit

Not sure about that. How did you create the combobox? Where is the AppointmentTypeID. TO create a combobox from an exiting control, in design view rt click - > change to

 

[Lakhvir]

Howzit

Not sure about that. How did you create the combobox? Where is the AppointmentTypeID. TO create a combobox from an exiting control, in design view rt click - > change to

AppointmentTypeID is Combo14 I think. I deleted it first and then remade it as a combobox using the recordsource you posted

What should I do?

Thanks!

 

[Kiwiman]

Howzit

Delete the combobox, then drag the AppointmentTypeID from your field list (You may find it called "Add Existing Field") onto the form. This will initially show as a text box and will be automatically bound to the correct field. Then Rt Click - > Change to - > Combobox. Do not rename the control. Use the below SQL as your Rowsource of the combobox not the Control Source as I may have mentioned earlier. Also make sure that the combobox properties are as I mentioned in an earlier post.

SELECT tblAppointmentTypes.AppointmentTypeID, tblAppointmentTypes.AppointmentType FROM tblAppointmentTypes;

Then go to the Event Tab and create an after update procedure using that dlookup. In theory this code should work

Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[AppointmentTypeID]=" & Me.AppointmentTypeID)

 

[Lakhvir]

Howzit

Delete the combobox, then drag the AppointmentTypeID from your field list (You may find it called "Add Existing Field") onto the form. This will initially show as a text box and will be automatically bound to the correct field. Then Rt Click - > Change to - > Combobox. Do not rename the control. Use the below SQL as your Rowsource of the combobox not the Control Source as I may have mentioned earlier. Also make sure that the combobox properties are as I mentioned in an earlier post.

SELECT tblAppointmentTypes.AppointmentTypeID, tblAppointmentTypes.AppointmentType FROM tblAppointmentTypes;

Then go to the Event Tab and create an after update procedure using that dlookup. In theory this code should work

Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[AppointmentTypeID]=" & Me.AppointmentTypeID)

Hi,

I've tried this and I'm still getting the error I have uploaded the database for you so you can check it out

Thanks

 

[Kiwiman]

Howzit

Here we go. Compare this to what you have

 

[Lakhvir]

Howzit

Here we go. Compare this to what you have

Omg! That's PERFECT!!

If it's not asking too much, may you explain what you did, so that I know for future reference?(In a pm would be good, so that I can save it for myself )

I don't know how I can ever repay you! Just thank you soo soo much! I really wish you lots of good luck and I hope that I haven't been a pain in the neck for you

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Kiwiman]

Howzit

No - you compare the one you sent to the one I returned and see if you can see the difference. Hint look at the control sources \ record sources and rowsources of your forms and in some aspects the controls on the form

Let me know what you find then I'll let you know if you have missed anything. This way it may become a little clearer.

 

[Lakhvir]

Howzit

No - you compare the one you sent to the one I returned and see if you can see the difference. Hint look at the control sources \ record sources and rowsources of your forms and in some aspects the controls on the form

Let me know what you find then I'll let you know if you have missed anything. This way it may become a little clearer.

Okk. Good idea! I'll get back to you

Didn't mean to sound rude/lazy

 

[Lakhvir]

Wow, I didn't know you could combine two fields in the way you did for the drop down for the name! If I wanted to change the order of the name, I'd just switch forename and surname around? Also, if I wanted the title of the client in the list aswell, would I include that in the query and change the column count to 3?

I noticed you changed the Dlookup so that it's Private Sub AppointmentTypeID. Can I ask why it was so problematic when it was called combo14 or whatever?

I like the hide feature that you've used. It's better than having the ID's as it looks really ugly haha

Can I ask how you got the totalcost greyed out and automated? I think the grey out is because of the enabled and locked functions? How is it automated though? :S

Thanks sooo much! I owe you BIG time! You've made my weekend 1billion times better

 

[Kiwiman]

Howzit

Wow, I didn't know you could combine two fields in the way you did for the drop down for the name! If I wanted to change the order of the name, I'd just switch forename and surname around?

That is correct

Also, if I wanted the title of the client in the list aswell, would I include that in the query and change the column count to 3?

No - just combine the title in the same field that combines the last and first names. Leave the column count as 2.

I also would not put the title first, as it will make it harder to "find" your client if Mr \ Mrs etc is first. I almost always put the surname first - but that is my preference.

I noticed you changed the Dlookup so that it's Private Sub AppointmentTypeID. Can I ask why it was so problematic when it was called combo14 or whatever?

It wasn't the dlookup that was causing the problem as the syntax was correct. It was how you had the record sources \ control sources set for the forms that was causing the problem.

Can I ask how you got the totalcost greyed out and automated? I think the grey out is because of the enabled and locked functions?

That is correct - in this case just set the enabled to No

How is it automated though? :S

There is a new datatype (or one I did not notice in earlier versions) in access 2010 (2007?) called calculated field that you can add an expression to calculate the total. I wouldn't normally include a calculated field in a table, I just wanted to see it in play.

Have a good night my friend, I am done for the night

 

[Lakhvir]

Howzit
That is correct
No - just combine the title in the same field that combines the last and first names. Leave the column count as 2.

I also would not put the title first, as it will make it harder to "find" your client if Mr \ Mrs etc is first. I almost always put the surname first - but that is my preference.

It wasn't the dlookup that was causing the problem as the syntax was correct. It was how you had the record sources \ control sources set for the forms that was causing the problem.

That is correct - in this case just set the enabled to No

There is a new datatype (or one I did not notice in earlier versions) in access 2010 (2007?) called calculated field that you can add an expression to calculate the total. I wouldn't normally include a calculated field in a table, I just wanted to see it in play.

Have a good night my friend, I am done for the night

Okk, thank you very much for the informative post!

How do I explain to my teacher why I have CPH twice? Because I have this feeling that she will say it's not normalized as it's repeating data


If she does say it's repeating/un-normalized, is it still possible to do the form/subform with CPH just in the tblAppointmentTypes?

Sorry about this again

Goodnight buddy!

 

[Insane_ai]

Try this:
Lets call you combo box cboAppType
Edit the Combo box rowsource to hold more than one field like
Select appointmenttype,cph from tblAppointmenttype
Bind the data to column 1 so when the user chooses the appointment type, they see what they expect. After the session length is entered use a calculation like this:

TotalCost = Forms!formname.cboAppType.column(1) * Forms!formname.[sessionlength]

Since the combo box uses the master list to populate the types of session and their costs, you do not need the dlookup function to maintain the most current pricing.