auto suggest value (text)

[richardparker]

Hello,

I have a table where I keep track of [vendors] and an other table where i register [vendor invoices].

On the [vendor] table for every vendor there is field indicating the payment terms agreed with the particular vendor (eg. payment 30 days, payment 45 days, cash etc.).
The payment terms table - besides the ID field - only consists of the "Name" of the payment term and the "Number of days". The latter will be used for auto calculate invoice due dates.

So as I am prepairing the Form for the [vendor invoices] I set the field for the vendor as a combo box which works like a charm. Also besides the fact that I already have the payment terms set for the vendor I would like to be able to set a payment term for the individual invoices as well. Therefore I made a field on the [Vendor invoices] table dubbed "Payment term". This is because we have suppliers who issue invoices with 3 different payment terms depending on the product/service ...or what kind of underwear they're wearing.
So after having chosen the vendor I would like the [payment terms] field to automatically suggest a payment term looking at the previous [vendor name] field on the form and based on this look up the set Payment term for this particular vendor. So if the payment term really applies the user only has to hit TAB and jump to the next field.

Now I tried to use the DLookUp funciton but even if this is The Way I can't get the thing working.

Do you have any suggestions where I might go off the rails?

 

[Minty]

In your combo box add the payment term as another column (set the width to 0 Doesn't need to be visible.) Then in your after update event set your line payment term = me.yourcombo.column(2) assuming the payment term is in the 3 column of the combo box.
(Column numbers start at 0 just to confuse the locals...)

 

[richardparker]

gee, even if I knew there was a column property of the combo box I have doubts I could come up with such a solution. thanks!

so I created the combo box with the desired three (0, 1, 2) columns.
now this combo box with the [vendor name] and the related [payment term] on my form is "Combo36".
the field where I would like the value of column(2) to appear as a suggestion is also a combo box AKA "Combo29" - in case the payment term differs from the one originally set for the vendor I wan't to make the data entry easy. I am not sure if this is a problem.

Anywho I inserted "combo29 = me.combo36.column(2)" the in the "After update event" line of Combo36 on the property sheet - minus the quotation marks.

I must be on the wrong track becasue my reward for doing the above was an error message informing me that MS Access could not find object 'combo29 = me.'
I guess the syntax I use is where I am at fault.

 

[Minty]

You are pretty close

So you are trying to set combo29 to the value selected in combo36 ? (I know it's early days, but please rename your controls to something more meaningful, then you and I will have a better idea which control is doing what... cmbSugValue cmbDefault etc etc )

I think it should read Me.combo29 = Me.combo36.column(2)

The Me. refers to a current form control

 

[richardparker]

you have a good point indeed, I have renamed my controls
so my line looks like as follows:
me.Paymentterms=me.Vendor_name_and_pmt.column(2)

still "Something is rotten in Denmark, my friends" is what Shakespeare would say if he was trying to get to the bottom of this because I get almost the same error message: MS Access could not find object 'me.'

So the line (me.Payment....) should go to the field on the Vendor_name_and_pmt control Property Sheet, on the Event tab, in the line "After Update"? I'm asking because that's what where I'm putting it
I might be trying to get on a plane to NY at the Waterloo station...

 

[Minty]

That sounds about right, so I'm not sure why you would be having an issue.

When you type me. in the VBA editor it will auto populate with control names that are available. if PaymentTerms (Note the use of what's known as Camel Case to highlight the words - makes it much easier to read...) doesn't appear make sure you really have called your control that.

You sound as if you have the code in the correct place. Can you post a copy of the code for the after update in all it's glory ?

 

[richardparker]

holy cucumbers it is working! sweeet!
the code is the following:

Private Sub Vendor_name_and_pmt_AfterUpdate()
Me.Paymentterms = Me.Vendor_name_and_pmt.Column(2)
End Sub

I guess the problem was that I wasn't using the VBA editor, I merely copied the Me.Paymentterms = Me.Vendor_name_and_pmt.Column(2) part in the field after the "After Update"
this puts my Access capabilites into perspective nicely

Thank you for the help!!!