Trying to lookup a description in a table from an order Debug will not work for me

[Johnain]

Hi all.



My first post. I am a decent programmer in other environments but Access VBA is brand new to me. Although I have played successfully with Excel VBA.

I have a clients table with a client reference (ClientRef)and a description (ClientDesc). I have an orders Header table and an orders line item table.

The orders header table (SlsOrders) holds a client reference in a field called BillingClient which is the Control Source for a text box on my form SlsOrders. Beside this control I have a text box which I want to populate with the Client Name from the Clients table.

The BillingClient successfully populates BillingClient as one would expect. I want to use that value to collect the client description from the Clients table.

I am using the After Update event in the BillingClient object to collect it (or trying to!)

Here is my code

Private Sub BillingClient_AfterUpdate()
Dim RetVal As String
RetVal = ""
RetVal = DLookup("Clients", "ClientName", ClientRef = This.Value)
Forms![Form_SlsOrders]![BillToName].Value = RetVal
End Sub


First question is, do I need to refresh the form, and if do what is the syntax please. I am still struggling with syntax. Because ...

... it simply doesn't work. And I cannot step through it because debug will not work for me.

When I try to step through the code (F8) it just "boings"

I tried the "immediate" window to give it focus (from a search I did on here) by pasting in the code ...

Dim RetVal As String
RetVal = ""
RetVal = DLookup("Clients", "ClientName", ClientRef = This.Value)
Forms![Form_SlsOrders]![BillToName].Value = RetVal


... so that I could hit enter on each line, but it told me the code was invalid here.


I hope this post isn't to naive. But I have hunted in a number of places to find a solution.

[Johnain]

Hi again

I am making some progress with this. I have got a solution working in the "Immediate" window after code changes,

Private Sub BillingClient_AfterUpdate()
Dim RetVal As String
RetVal = ""
RetVal = DLookup("[ClientName]", "Clients", "[ClientRef] = Forms![SlsOrders]![BillingClient].Value")
Forms![SlsOrders]![BillToName].Value = RetVal



End Sub

.. But it doesn't work yet in the form itself which tells me that I am using the wrong event. Can somebody help with this please.

The Debugger is till a mystery to me. It will will not work in the SUB when I want to chase code through, and the Immediate window seems to just be unaware of the debugger. No response at all.

[ItsMe]

It could be your code is not firing. The AfterUpdate event will not fire until after the control loses focus and the data is saved to the table. Also, the data needs to actually change.

For this, you can step away from the debugger and use a msgbox.

Private Sub BillingClient_AfterUpdate()
msgbox "After Update code is now firing."
Dim RetVal As String
RetVal = ""
......

[June7]

Maybe should not have the control reference within quotes, concatenate the variable (reference to control is a variable). If ClientRef is a text type field, the parameter needs apostrophe delimiters.

RetVal = DLookup("[ClientName]", "Clients", "[ClientRef] = '" & Forms![SlsOrders]![BillingClient] & "'")

[Johnain]

First of all, thanks for your time and help.

Using Msgbox showed me that my code is not executing (thanks for that too!!), so at least a part of my problem is that I do not understand the execution sequence properly.

So I looked for an alternative event assignment. There are not too many to choose from (thankfully!).

Briefly, when I move to a new record the correct value is inserted into the control BillingClient correctly (I can see it on the screen). but the target control is not (I cannot see the ClientName value in it.

An event that looked promising was On Change, since the MSDN Developer reference (at http://social.msdn.microsoft.com/Sea...mark=true&ac=4) Tells me ...

The OnChange event occurs when the data in a form field has changed and focus is lost. Data in the field is validated before and after the OnChange event.

So that got me all excited. I had read elsewhere that a VBA update or a form prompted update would trigger the event. So I changed my code to

Private Sub BillingClient_Change()
MsgBox "Here we go"
Dim RetVal As String
RetVal = ""
RetVal = DLookup("[ClientName]", "Clients", "[ClientRef] = '" & Forms![SlsOrders]![BillingClient] & "'")
MsgBox "This code is now firing : " & RetVal
Forms![Form_SlsOrders]![Bill-To-Name].Value = RetVal
End Sub

.. but the wording in my MsgBox was far too optimistic . No message and no required value in the target object.

Oddly, I am enjoying this because I am learning lots of stuff in my research. But I will still welcome help.

Additional Thought : The MSDN note speaks of lost focus as a requirement. Does the BillingClient object ever have focus if it is updated by a record movement? That would challenge my other info that told me a VBA initialted change would set off the On Change event.

[Johnain]

Ok, so the latest position can be summarised as ...

1. This code works in the Immediate window. It populates my form with the client name.

RetVal = DLookup("[ClientName]", "Clients", "[ClientRef] = '" & Forms![SlsOrders]![BillingClient] & "'")
Forms![SlsOrders]![BillClientName].Value = RetVal

(The issue was that the lookup field is a string. SO I had to insert single quotes.

2. The event still doesn't run ... so that's my issue. I have ok code in the wrong place.

[ItsMe]

What is the name of the control that the user is onteracting with, "BillingClient"?

The after update event for BillingClient should be good enough as long as the user is typing info into BillingClient and then moves focus to another control. The act of tabbing out, or clicking somewhere else will cause the After Update event to fire.

[Johnain]

Hi ItsMe

That is a big part of the issue. This is an order manage/edit/delete form. The user doesn't change the billing customer because that customer will never change. Instead existing orders are read in from the database and the user changes delivery dates, quantities, prices etc. The user may change the delivery address, but that's my next issue - not this one.

But you have just told me some thing really valuable which I have been wondering about. I take from your question that events in an object are centered on the object itself, so that an After Update event refers to the time just after after the object in the form is updated with a new incoming value and not when the field in the database relating to that object is updated later on during a save.

So because no change really occurs after the billing data comes in because the user never touches it, the only change is when I move to that record using a standatd "Next Record" button in my form, or when the form loads.

Should I be putting the code into the Form's After Update event, I wonder? I'll give it a go.

[Johnain]

Nop, tried that. No luck.

I re-checked within the Immediate window and does the trick. Frustrating, but interesting.

[ItsMe]

You are creating events at the Form level. It is typically the user that interacts with the Objects at this layer. After the user updates info in a Control, the Control's after update event fires. It is the interaction with the Control that triggers the event. Updating fields at the table layer will have no effect on the Form's Events or Control's Events.

Give the user a button to click and use the Click Event to fire your code. Another approach may be the Form's On Current event, if the user is navigating from one record to the next. However, why involve the user and a form at all if the user is not interacting with the data somehow, like validating?

[Johnain]

You are creating events at the Form level. It is typically the user that interacts with the Objects at this layer. After the user updates info in a Control, the Control's after update event fires. It is the interaction with the Control that triggers the event. Updating fields at the table layer will have no effect on the Form's Events or Control's Events.

Give the user a button to click and use the Click Event to fire your code. Another approach may be the Form's On Current event, if the user is navigating from one record to the next. However, why involve the user and a form at all if the user is not interacting with the data somehow, like validating?

You are being most helpful and patient. Thanks.

The user will be interacting with other fields on the form. Changes to quantities, prices, dates etc, but probably will not change the ordering customer reference. All I want is to put a customer name beside the customer code that is stored in the order header. The code clearly arrives when the record is initially loaded into the form. If I allow the user to change the Billing Customer reference then I will need to cater for that as well, but at present that is unlikely.

I clearly have not understood the event cycle properly and I'm going to go and take a look at it.

Thanks again.

[June7]

Several ways to pull in related info.

1. form RecordSource includes the 'lookup' table, join type "Include all records from {data table} and only those from {lookup table} that match", bind textboxes to the related fields, set the Locked Yes

2. combobox includes the related fields as columns (hidden or not) then textboxes reference the columns by index - index begins with 0
=[comboboxname].[Column](x)

3. last on my list is DLookup in textbox ControlSource expression - no idea why you are trying this with VBA

If you want to provide db for analysis, follow instructions at bottom of my post, indicate form of interest

[Johnain]

Hi June7

I have much to learn in this area. Thanks for those tips. I am looking at them now. This is all very new to me.

[Johnain]

... and Hi again June. That worked like a charm. I took the combo box option since it seemed the most focussed on the immediate issue. I can now identify my Bill-to-Party and my Ship-to-Party. Great stuff.

problem solved - and a really useful general principle established.