Auto fill in table?

[Tommy B]

Hi Guys,

I am just moving into the realm of vba and could do with some help!

What I am trying to achieve is this:

I have a form called onenumberformtest, this form displays a table called "Rates Table". The field [Client Name] is a combo box and feeds from a table called "client standard financials". The "client standard financials" table contains generic data for my clients. What I would really like to do is have an afterupdate event or onexit event that looks at [Client Name] on the form and updates generic fields in "Rates Table" with data from "Client standard financials". I have been trawling through previous posts looking for clues...here is what I have tried...and failed. I understand what it is doing with its Dlookup....I just think I am being let down on my lack of vba knowledge...so if anyone can spare the time to put me on the right path I would be very grateful

Here is what I tried...probably totally wrong

Private Sub combo_client_name_Exit(Cancel As Integer)

Dim varRate, As Variant
varRate = DLookup("Rate", "Client standard financials table", "Client Name ='" & Forms!Onenumberformtest!Name & "'")

If (Not IsNull(varRate)) Then [Rates Table]![Rate] = varRate

End Sub

 

[Jack Cowley]

When you create your combo box based on Client data include the field(s) from that table that you want to add to the Rates table. They may be ClientID, ClientName, Address, for example.

In the After Update event of the combo box put code similar to this:

Me.RatesField1 = Me.ComboBoxName.Column(0)
Me.RatesField2 = Me.ComboBoxName.Column(1)
Me.RatesField3 = Me.ComboBoxName.Column(2)

In my above example RatesField1 would get the ClientID, RatesField2 the ClientName and RateField3 the Address.

 

[Tommy B]

This works great...thanks! I have one question though, I have based my combo box on a query. When I use the wizard to create it I want to bring in quite a few fields. Initially the combo box showed all the fields when I just wanted the user to see client name. I got around this by minimising the other fields to hide them in the wizard....but I get the feeling this may be a stupid way of doing it???

Tom.

 

[raskew]

Using Jack's example, say you only wanted to see client's name, but would actually search by ClientID:

Set the combo box properties as:
Bound Column: 1
Column Count: 3
Column Widths: 0"; 1"; 0"

 

[Tommy B]

Nice one...thank-you for taking time out to help me. This forum is without doubt the best place to learn new skills