Form/Subform help pleasee!

Lakhvir

Registered User.
Local time
Today, 00:49
Joined
Mar 9, 2012
Messages
46
Hello guys, I'm new :p

I'm currently doing some coursework at school and have come across a problem. My system is basically an appointment booking system for a family law firm.
I have the main booking form with the following fields:
BookingID, ClientID, StartTime, EndTime, Date, TotalCost

In the subform, the fields should be:
AppointmentBookingID, BookingID, AppointmentType, CostPerHour, SessionLength

What I want is for them to fill in the main form, and then at the sub form they should be able to select the appointment type from a list and then the costperhour automatically gets filled in. How do I do this please? Normally, I'd make a combo box and then use the "find values lookup...", but it doesn't come up.

Can somebody please help me?

Thank you very much :)
 
Howzit

Yes create a combobox to select the appointmenttype then in the after update event procedure use dlookup to populate the cost

Something like

Code:
me.costperhour = dlookup("[yourcostfield]","yourappttypetable","[yourapptmenttypefield]=" & me.appointmenttype)
 
Howzit

Yes create a combobox to select the appointmenttype then in the after update event procedure use dlookup to populate the cost

Something like

Code:
me.costperhour = dlookup("[yourcostfield]","yourappttypetable","[yourapptmenttypefield]=" & me.appointmenttype)

Hi, thank you for replying!

Do I do this dlookup after update on the appointment type field or on the actual cost field?

Excuse my dumbness but what is me. ? Really confused :S

Thanks so much!
 
Howzit

After the selection of your appointmenttype field as it is only then that yoy have the information to perform the Dlookup to get the cost for that appointment type.
 
Howzit

After the selection of your appointmenttype field as it is only then that yoy have the information to perform the Dlookup to get the cost for that appointment type.

What is "me." ?

Please, and thank you. I really want to get this done so that I can rest lol :p
 
Howzit

Me. is a shortcut to reference controls on your current form \ report, instead of having to do the full referencing like Forms!Mainform!ControlName


me.costperhour means that there is a control on your current form called costperhour

Check this out for a better description of control referencing
 
Howzit

Me. is a shortcut to reference controls on your current form \ report, instead of having to do the full referencing like Forms!Mainform!ControlName


me.costperhour means that there is a control on your current form called costperhour

Check "" out for a better description of control referencing

Is there anyway I can attach my database, so you can help me as I'm really clueless lol :(
I keep getting an error. Microsoft Access cannot find the object 'me'.

Where do I put the code? Maybe I've put it in the wrong place? I've placed it in the After update box, for the AppointmentType combo box.

Or should I list all of my fields, tables so we can rewrite the code?

So sorry for being dumb :( :confused:
 
Howzit

As it is course work you should try and muddle through this as best you can - we will offer guidance where necessary.

Post the code that you have currently.

You should have an event procedure something like the below where YourcomboCtrl and me.costperhour and me.appointmenttype are the control names on your form - see the Other tab on your control properties

Code:
Private Sub YourcomboCtrl_AfterUpdate()
    me.costperhour = dlookup("[yourcostfield]","yourappttypetable","[yourapptmenttypefield]=" & me.appointmenttype)
End Sub
 
Howzit

As it is course work you should try and muddle through this as best you can - we will offer guidance where necessary.

Post the code that you have currently.

You should have an event procedure something like the below where YourcomboCtrl and me.costperhour and me.appointmenttype are the control names on your form - see the Other tab on your control properties

Code:
Private Sub YourcomboCtrl_AfterUpdate()
    me.costperhour = dlookup("[yourcostfield]","yourappttypetable","[yourapptmenttypefield]=" & me.appointmenttype)
End Sub


I didn't know we had to build an event procedure lol - such a noob I am :p I've never made an event in Access before :s

I'm assuming you mean the name of the boxes on the form for the control names?

I've done this, but I keep getting a runtime error :S

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


Thanks for the help! I think we've almost cracked it! *high five*
 
Howzit

Nice one. Take the spaces out either side of the table name.

You show

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

Where you want

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

Nice one. Take the spaces out either side of the table name.

You show

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

Where you want

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


Hi, I've done that but I'm getting another runtime error " Datatype mismatch"


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

Thanks!
 
Howzit

I assumed that the appointment type was a number - but it looks like it is text. Therefore you need the following

Code:
Private Sub Combo10_AfterUpdate()
Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[AppointmentType]='" & Me.Combo10 & "'")
End Sub
 
Howzit

I assumed that the appointment type was a number - but it looks like it is text. Therefore you need the following

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

I'm getting no errors now, but the actual cost isn't coming up :(

Help please!
 
Howzit

The syntax is correct. Do you have costs populated in the table tblAppointmentTypes?

Does the combobox return the expected value? When the code below runs what value shows in the message box - is it the expected value - i.e. the appointment type?

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

The syntax is correct. Do you have costs populated in the table tblAppointmentTypes?

Does the combobox return the expected value? When the code below runs what value shows in the message box - is it the expected value - i.e. the appointment type?

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


It comes up with the appointment type id :S

In the tblAppointmentTypes I have three fields:

AppointmentTypeID
AppointmentType
CPH

I'm guessing I've done something wrong lol :/
 
Howzit

I suspect that you need to use the field AppointmentTypeID in your dlookup not AppointmentType as this is the field that is storing "S"?
 
Howzit

I suspect that you need to use the field AppointmentTypeID in your dlookup not AppointmentType as this is the field that is storing "S"?


I've tried this:

Code:
Private Sub Combo10_AfterUpdate()
MsgBox Me.Combo10
Me.CPH = DLookup("[AppointmentTypeID]", "tblAppointmentTypes", "[AppointmentType]='" & Me.Combo10 & "'")
End Sub

It's still showing the AppointmentTypeID in the box and the price isn't coming up.

:(
 
Howzit

Go back to the way it was and take a closer look at which section you should change based on my comment in 16.
 
Howzit

Go back to the way it was and take a closer look at which section you should change based on my comment in 16.


Right, I've reverted back to this:
Code:
Private Sub Combo10_AfterUpdate()
Me.CPH = DLookup("[CPH]", "tblAppointmentTypes", "[AppointmentType]='" & Me.Combo10 & "'")
End Sub

In post 16, what do you mean by S?
 
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

It comes up with the appointment type id :S

In the tblAppointmentTypes I have three fields:

AppointmentTypeID
AppointmentType
CPH

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
 

Users who are viewing this thread

Back
Top Bottom