DLookup not working (1 Viewer)

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
Hi all,

I have a very simple database which I have attached. I've created a form based on one table. I want one of the fields to automatically look up the value from the other table and update the original table with an AfterUpdate event.

This is the code:
Code:
Option Compare Database
Option Explicit

Public Sub Shipment_Terms_AfterUpdate()
Me.Shipment_Rate = DLookup("CM3_Net", "Freight", "Shipment_Terms=" & "Company")
End Sub
Code:

I know I'm probably doing something very basic wrong but I can't work out what it is!

I want the "Shipment Rate" field to be the one updated with the relevant "CM3 Net" from the "Freight" table.

Can anyone help?

Thanks,
Nick
 

Attachments

  • Line Plan.mdb
    864 KB · Views: 264

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
If Company is text:

DLookup("CM3_Net", "Freight", "Shipment_Terms= '" & Company & "'")

If company is numeric:

DLookup("CM3_Net", "Freight", "Shipment_Terms=" & Company)

JR
 

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
Hi JR,

Thank you for that. I now get the following error though:

"The expression After Update you entered as the event property setting produced the following error: Syntax error"

As "Company" is text, I changed the code to the following:

Code:
Public Sub Shipment_Terms_AfterUpdate()
me.Shipment_Rate = DLookup("CM3_Net", "Freight", "Shipment_Terms= *'"* & Company & *"'"*)
End Sub

Is it something underlying in the Table relationships?

Nick
 

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
The syntax for dlookup is:

Dlookup("field", "Domain", Criteria) so

So are you are trying to ask this:

I want to find CM3_Net in table Freight where the field "Shipment_Term" in table Freight is the same as my control "Company" on my form

JR
 

vbaInet

AWF VIP
Local time
Today, 15:59
Joined
Jan 22, 2010
Messages
26,374
And if you look at the syntax of JR's DLookup criteria part, you didn't follow what he advised.

By the way, you should be using the CompanyID field and not the ComanyName field in the criteria part.
 

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
Hi Guys,

Yup, apologies about that! I've amended the syntax as follows:

Code:
Public Sub Shipment_Terms_AfterUpdate()
Me.Shipment_Rate = DLookup("CM3_Net", "Freight", "Shipment_Terms= '" & Company & "'")
End Sub

JR, that is exactly the question I'm asking.

vbaInet, I do now have an error in the criteria part. "Variable not defined". However, I really only have basic vba knowledge so I'm not entirely sure how to use the CompanyID field rather than the Company Name field.

Nick
 

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
This is the correct one:

Code:
Public Sub Shipment_Terms_AfterUpdate()
Me.Shipment_Rate = DLookup("[CM3 Net]", "Freight", "[Shipment Terms]='" & Me.Shipment_Terms & "'")
End Sub

The fieldnames didn't have underscores but spaces.

JR
 

vbaInet

AWF VIP
Local time
Today, 15:59
Joined
Jan 22, 2010
Messages
26,374
I haven't looked at your db but I would imagine you have a ShipmentID field there. Maybe it's not called ShipmentID but it is a field that distinctly identifies each Shipment record.

Maybe Shipment_Terms is the field as JR included it in the DLookup()
 

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
Yep nothing on this form called Company ;)

As a tip for the future, you should add a ID field to your table Freight as joining and searching on text fields is a pain in the b***.


JR
 

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
JR/vbaInet, thank you so much - that last one was perfect! Works like a treat now.

And thanks JANR, I'll start again and make sure to include an ID field.

Thanks guys,
Nick
 

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
Sorry, I've got another question off the back of that one.

When I change the "Shipment Terms" in my form, with resulting Dlookup returned in "Shipment Rate", does that also update the "Shipment Terms" and "Shipment Rate" fields in the underlying "Item Details" table automatically?

Nick
 

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
yes it will since you have bound the form and its controls to Items details table.

JR
 

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
I think I spoke too soon!

It is now just bringing back the first "CM3 Net" value from the "Freight", whichever "Shipment Terms" I select.
 

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
I am an idiot :mad:

Code:
Public Sub Shipment_Terms_AfterUpdate()
Me.Shipment_Rate = DLookup("[CM3 Net]", "Freight", "[Company]='" & Me.Shipment_Terms & "'")
End Sub

JR
 

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
Well since I am already looking at your db, ther is an easier way to optain the CM3 Net than a dlookup.

If you include CM3 Net field in your combobox and make the field hidden, then you can refer it by using the Column(x) property and no need for the extra overhead that a dlookup uses.

To hide the column:

1. Set number of columns to 2
2. Set the column widths to 0,46;0

Code:
SELECT Freight.Company, Freight.[CM3 Net] FROM Freight ORDER BY Freight.[Company];

Then in code:

Code:
Public Sub Shipment_Terms_AfterUpdate()
Me.Shipment_Rate = Me.Shipment_Terms.Column(1)
End Sub

JR
 

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
Hi JR,

I'm afraid I don't know where to start with a ComboBox or where to put the first SQL code - I thought neither Tables nor Forms have SQL design views?

I'm probably showing how little I really know. I do have some training coming up!

Nick
 

JANR

Registered User.
Local time
Today, 16:59
Joined
Jan 21, 2009
Messages
1,623
I have done it for you. check out the property sheet of the combobox Shipment Terms

JR
 

Attachments

  • Line PlanRev.mdb
    328 KB · Views: 109

Nevsky78

Registered User.
Local time
Today, 15:59
Joined
Mar 15, 2010
Messages
110
Right, thanks JR!

I'l try and re-create to see if I fully understand it.

Thanks again for all your help.

Nick
 

Users who are viewing this thread

Top Bottom