filling fields in a subform by a choice on a combobox

[LOUISBUHAGIAR54]

I have a main form linked to a subform set in datasheet mode. On the main form I have three cascading combo boxes. I would like to fill up some fields of a new record on the subform depending on choices made from the last cascading combobox. Can I get some help on how to reference the fields in the new record using vba coded in the afterupdate event of this combobox ?

Please note also that other fields in the record are 'required' and are filled up manually by the user.

Many thanks for your assistance.

Louis

 

[LOUISBUHAGIAR54]

On the subform I will have previous records linked to an idcard number on the main form.

What I want to do is that on a choice of the last cascading combobox a new record is populated. For course as soon as one starts to do this the field on the subform related to the idcard number on the main form fills in authomatically.

However a few fields will be filled in by the choice made on the combobox. The rest are filled in manually. As some of the latter have the 'required' property I suppose I will not be able to access the recordset and populate it with with AddNew and rst.update as I have done before.

I hope I am being clear enough.

Since the last time I posted I have found some clues like the following

Private Sub Combo17_AfterUpdate()
DoCmd.GoToRecord , , acNewRec
Forms!FrmServices!FrmServicesub.Form.ServiceRef = [Combo17].Column(1)
Forms!FrmServices!FrmServicesub.Form.itemprice = [Combo17].Column(2)
End Sub

However until now I have not been able to get the code to work.

many thanks.

LB

 

[LOUISBUHAGIAR54]

It appears that I have not explained myself of what I have been trying to do. In any case I have managed to find a solution to my problem as follows.
The last of three cascading comboboxes is Combo17. On the afterupdate event I have put the following code.

Private Sub Combo17_AfterUpdate()
Me.FrmServicesSub.SetFocus 'sets focus to the subform
DoCmd.GoToRecord , , acNewRec 'opens up a new record in the subform.
Me.FrmServicesSub.Form.ServiceRef = [Combo17].Column(1) 'fills up the field ServiceRef depending on column 1 of the choice on the combobox.
Me.FrmServicesSub.Form.itemprice.Enabled = True ' enables itemprice field so that additions and any edits can be made
Me.FrmServicesSub.Form.itemprice = [Combo17].Column(2) ' updates another field on the new record on the subform.
Me.FrmServicesSub.Form.invoiceno = 0
Me.FrmServicesSub.Form.item_amount.Enabled = True 'enables item_amount field so that the number can be added
If Me.Combo0 = "operation" Then
Me.FrmServicesSub.Form.item_amount = 1 ' conditionally fills in item_amount depending on the value in another combobox.
Me.FrmServicesSub.Form.item_amount.Enabled = False

Me.FrmServicesSub.Form.Requery ' requeries the subform so that entries are saved.
DoCmd.GoToRecord , , acLast 'goes back to the record just saved and sets focus to another field prompting the user to enter a date.
Me.FrmServicesSub.Form.servicedate.SetFocus
Else
Me.FrmServicesSub.Form.item_amount.SetFocus ' prompts the user to make an entry into another field of the original record.
End If
Me.Combo0 = "" ' empties the first combobox
Me.Combo6 = "" ' empties the second cascading combobox
Me.Combo17 = "" 'empties the last cascading combobox.
End Sub

This code is working fine for me and I am satisfied with the result. However if this could have been done more efficiently or more professionallY I would be pleased to know how.

Many thanks to all.

Louis B.

 

[LOUISBUHAGIAR54]

Hi Pat,

Many thanks for your interest. I appreciate the last part of your note, and do agree that meaningful names to objects will make everything more clear.

As to the first part of your statement; I have other objects on the main form like the name of a client and his idcard number. The entries on the subforms are billing details (fields) for various services (records) connected with the client identified on the main form. The comboboxes on the main form make it easy to identify a service amongst thousands depending on their classification, depicted by the cascading comboboxes.
Aesthetically this presentation makes sense to me.

Many thanks again.

LouisB