Populate a field on a subform

[dr223]

Hi ,

I have a subform, which is build up of 3 fields Payer_code, Fee_Amount, and Payer_Name. The name of the subform is FeesDetails.

I have a drop down list for the Payer_code with a list of all the payer codes. What I want to do is when I select or populate the Payer_code, the Payer_Name is populated instantly. How can this be done.

Your help is highly appreciated

Regards

 

[Access_guy49]

I would make my drop down box 2 columns. The first column being the name and the second being the payer code. Then set your column width to 0 and 1 (or 2 or whatever is needed) ex. 0";1" This will still give the appearance of just being the code but behind it is the name as well. Then do an after update function that sets the text of the name textbox to equal the value of the combobox. it should take the bound column which will be the first column which is your name. That should pretty much do what your looking for.

 

[dr223]

Thnak for you reply. The only problem is that I will have hundreds of payer_codes and payer_names. Will it be applicable and do u now the afterevent code.

Thanks

 

[Access_guy49]

My understanding is that you will have a list of Payer codes with associated names correct? i.e Code 1 = Bob. and code 2 = Jill. and these codes and names are stored in a table which you are using to populate your code drop down box? If This is the case then there shouldn't be any problem in using this method. I'll reply again with code examples shortly.

 

[Access_guy49]

Ok let's see if i can cover everything here. So you have a combo box that will drop down the Payer Code.
In the FORMAT properties set
Column Count = 2
Column Width = 1";0"
In the DATA properties set
Control Source = [Your payer Code] or unbound if this data will not be saved in a record
Rowsource type = Table/query
Row Source = Select DISTINCT [PayerCode].

, [PayerName].

.....
Bound column should be 1. (keeping in mind the order in which you query your items)

Now Make a Text Box. (lets say it is named txt_PayName)

The Code for this form should look something like this: (But with the appropriate names for your database of course)

Option Compare Database
Dim myName As String

Private Sub Code_AfterUpdate()
myName = Me.Code.Column(1)
End Sub

Private Sub Code_LostFocus()
Me.txt_Name.SetFocus
Me.txt_Name.Text = myName
End Sub

(Although i'm sure one of the more experienced members will likely have more efficient code.)

 

[dr223]

Thanks access guy49, managed to solve it.

However, I have another problem as outlined below



Is there a way of adding a text box on the Form Footer of a subform,
find the sum of a field on the subform (fee_Amount) i.e., =Sum([fee_Amount]) and set the default view to Dataset and be able to view it when you are in the Form view. Or is there a way around it rather than setting the default view property to Continuos Forms.

This is mainly because I have used list boxes on my subform when I use continuos view, the Total Amount is displayed but the subform form view is so unpleasant.

Thanks

 

[Access_guy49]

I COULD be missunderstanding you, but would it be possible to create a query that does your sum of the amount field and then add the query as a subform? that way the subform would still be in dataview?

 

[dr223]

That is a logical way of doing it, however, how I want it to work is that when an administrator types in the Fee_amount the total_amount is reflected underneath instantly. With a query it is not that efficient.

Is there not a way you can insert a field in a subform on its own / subform footer and be able to view it in datasheet view, rather than continuos view.

Thanks

 

[Access_guy49]

If you add a text box into a subforms footer it will not be displayed when viewing the form in dataview. however if the textbox is in the details section, it will be displayed in the dataview. however that doesn't exactly help you in trying to get a running sum.

 

[dr223]

Thanks, will try to sort it out further.