Form/Subform help pleasee!

[Lakhvir]

Howzit

I could just give you the answer, but this way you may get it.

This is from your post 15, where you unknowingly provided the solution



Your combobox returned S so in the criteria part of the dlookup you need to use the field in tblAppointmentTypes that holds the value S.

In your case the dlookup contains 3 parts

1. The field you want the dlookup to return
2. the table to perform the dlookup on
3. the field specifying which record to return

Lol, :s is an emotion lol - means confused

Nothing is returning S. Maybe I should have been more clear

 

[Kiwiman]

Howzit

Sorry I thought that meant that the msgbox returned S.

When you ran this code some value showed in a messagebox

Private Sub Combo10_AfterUpdate()
msgbox me.combo10
Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[AppointmentType]='" & Me.Combo10 & "'")
End Sub

That value I assume is stored in the table tblAppointmentTypes - but in what in what field?

It is in either AppointmentType or AppointmentTypeId. Whichever one it is in, then that is the field you want to use in the dlookup criteria section - there is only one logical place to make the change

Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[B][AppointmentType]=[/B]'" & Me.Combo10 & "'")

 

[Lakhvir]

Howzit

Sorry I thought that meant that the msgbox returned S.

When you ran this code some value showed in a messagebox

Private Sub Combo10_AfterUpdate()
msgbox me.combo10
Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[AppointmentType]='" & Me.Combo10 & "'")
End Sub

That value I assume is stored in the table tblAppointmentTypes - but in what in what field?

It is in either AppointmentType or AppointmentTypeId. Whichever one it is in, then that is the field you want to use in the dlookup criteria section - there is only one logical place to make the change

Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[B][AppointmentType]=[/B]'" & Me.Combo10 & "'")

AppointmentTypeID is an autonumber, that just goes 1, 2,3 etc/
AppointmentType is the name of the types of appointments. E.g. Property law, litigation
CPH - Is the cost per hour for the appointments.

 

[Kiwiman]

Howzit

Can you post the recordsource of the como10. Also what is the bound column of the combobox.

What actually showed in the messagebox?

 

[Lakhvir]

Howzit

Can you post the recordsource of the como10. Also what is the bound column of the combobox.

What actually showed in the messagebox?

This is the record source for the combo box:
SELECT [tblAppointmentTypes].[AppointmentTypeID], [tblAppointmentTypes].[AppointmentType] FROM tblAppointmentTypes ORDER BY [AppointmentType];

The bound column is 1.

The message box is showing the AppointmentTypeID when I select an appointment type from the drop down.

 

[Kiwiman]

Howzit

When you got the data mismatch earlier we changed the code, we changed the code on the basis that the criteria field AppointmentType was correct. In fact it is the wrong one, it should be AppointmentTypeID
Your solution should be

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

 

[Lakhvir]

Howzit

When you got the data mismatch earlier we changed the code, we changed the code on the basis that the criteria field AppointmentType was correct. In fact it is the wrong one, it should be AppointmentTypeID
Your solution should be

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

It works!!!!!!!!!!!!!!!!!!! Thank you sooo much!!!! I don't know how I can ever repay you!


I have another problem though and I don't know what's causing it - I'm sure it's unrelated to what we've just done. When I select the appointment type a number goes into the AppointmentType field in the tblAppointmentTypes. It's like some of the appointment types are being replaced, or just new records are being inserted with a number in the AppointmentTypeID.

I don't think I've constructed the form right, maybe?

Thanks again! It would be amazing if you could help me fix this thing aswell