Trouble setting default value in form using DLOOKUP (1 Viewer)

T

thardyjackson

Registered User.
Local time
Today, 01:27
Joined
May 27, 2013
Messages
45
I have a form for new customers. One of the fields is the product they have chosen ("new model", "old model", etc.) in a drop down Combo box. I want default pricing to appear in the form based on the what user selects for the product. But, I am getting nothing shown in the price box after choosing the product.

I've tried two approaches but with no luck. What am I doing wrong? This is in the default property for "price"

=DLookUp("[priceDefault]","tbl_products","[product]= '" & [productChosen] & "'")

=DLookUp("[priceDefault]","tbl_products","[product]= " & [Forms]![frm_CustomerRegistration]![productChosen])

I'm using Access 2007 and Windows 7.

Thanks.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:27
Joined
Jan 20, 2009
Messages
12,851
Default is set when the form loads so is not useful for your task.

Write the value to the price textbox with the AfterUpdate of the combo.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:27
Joined
Feb 19, 2002
Messages
43,213
To elaborate on what Galaxiom said,
Include the price column in the RowSource query for the combo.
In the AfterUpdate event of the combo, copy the price to the UnitPrice field.
Code: 
Me.UnitPrice = Me.cboProductID.Column(2)
The columns of a RowSource are a zero based array so .Column(2) refers to the third column.
 
T

thardyjackson

Registered User.
Local time
Today, 01:27
Joined
May 27, 2013
Messages
45
Thanks. I'm trying to implement what you recommend (i'm a bit of a newbie) but i'm getting an error once I choose a product from the combo box .... Microsoft Office Access can find the object 'me.'

Here's what I've done ...

The combo box field name is: productChosen

In the Row Source property for productChosen, I typed: SELECT [tbl_products].[product], [tbl_products].[priceDefault] FROM tbl_products ORDER BY [product];

In the After Update property for productChosen, I typed: me.price =me.productChosen.Column(2)

I've also tried "tbl_customers" instead of "me"

Thoughts?
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 09:27
Joined
Nov 30, 2011
Messages
8,494
I think you are using the property... The code Pat has given you should go in the VBA module of the Form.. Delete whatever you have in there.. Then follow the steps below..



Then type the code between the Private Sub and End Sub..
 
T

thardyjackson

Registered User.
Local time
Today, 01:27
Joined
May 27, 2013
Messages
45
So, I changed the after update property to run code. I'm no longer getting the "object not found" error but the default price is not populating when i create a new record, choose a product in the drop down and then finish creating the record. The code I added is:

Code: 
Private Sub productChosen_AfterUpdate()
    Me.price = Me.productChosen.Column(2)
End Sub

Any ideas?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:27
Joined
Feb 19, 2013
Messages
16,604
it needs to be column(1) in vba the columns start from 0, in control properties, columns start from 1. Strange but true:)
 
T

thardyjackson

Registered User.
Local time
Today, 01:27
Joined
May 27, 2013
Messages
45
I came up with something that works (based on your ideas). Notice anything crazy?

My combo box is productChosen. I have =ProductDefaults([productChosen]) in the After Update property for productChosen. This custom function is part of the form's modules.

Code: 
Private Function ProductDefaults(productChosen As Variant) As Currency
    Me.price = DLookup("[priceDefault]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.fee = DLookup("[feeDefault]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.depositMin = DLookup("[depositMinDefault]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.pmtMo1Expected = DLookup("[pmtMo1Default]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.pmtMo2Expected = DLookup("[pmtMo2Default]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.pmtMo3Expected = DLookup("[pmtMo3Default]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.pmtMo4Expected = DLookup("[pmtMo4Default]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.pmtMo5Expected = DLookup("[pmtMo5Default]", "tbl_products", "[product]= '" & [productChosen] & "'")
    Me.pmtMo6Expected = DLookup("[pmtMo6Default]", "tbl_products", "[product]= '" & [productChosen] & "'")
End Function
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 09:27
Joined
Nov 30, 2011
Messages
8,494
Okay I think we are missing the basic information here.. What is the RowSource of the ComboBox? It should be..
Code: 
SELECT [product], [priceDefault], [feeDefault], [depositMinDefault], [pmtMo1Default], [pmtMo2Default], [pmtMo3Default], [pmtMo4Default], [pmtMo5Default], [pmtMo6Default]
FROM tbl_products;
With Column Count 10, Column Width 2.5cm,0cm,0cm,0cm,0cm,0cm,0cm,0cm,0cm,0cm
And then your AfterUpdate should be..
Code: 
Private Sub productChosen_AfterUpdate()
    Me.price = Me.productChosen.Column(1)
    Me.fee = Me.productChosen.Column(2)
    Me.depositMin = Me.productChosen.Column(3)
    Me.pmtMo1Expected = Me.productChosen.Column(4)
    Me.pmtMo2Expected = Me.productChosen.Column(5)
    Me.pmtMo3Expected = Me.productChosen.Column(6)
    Me.pmtMo4Expected = Me.productChosen.Column(7)
    Me.pmtMo5Expected = Me.productChosen.Column(8)
    Me.pmtMo6Expected = Me.productChosen.Column(9)
End Sub

There is nothing wrong with your code.. but DLookUp is a simplified (inefficient) Query.. In this case you are Querying one table 9 times, to get one value.. So the reason your Run Time would be messy.. Which makes me think.. Why are you duplicating values from one table to another? Should they not be linked by Primary Key - Foreign Key?
 
T

thardyjackson

Registered User.
Local time
Today, 01:27
Joined
May 27, 2013
Messages
45
Got it working. Thanks all!

The reason for duplicate values is that I'm pulling default pricing values from tbl_products based on the productChosen. The user can override those values if desired. Overridden values get stored in tbl_customers.

Cheers.
 
R

ria4life

Registered User.
Local time
Today, 01:27
Joined
Feb 24, 2016
Messages
40
DLookUp return first value only

Can anyone please assist:
I have this dlookup code setup..but for some reason i only get the first record....I tried several things but cant seem to get over that hump...
Anyone please assist.

--All fields are setup as short text in the table. However Task_Code is a numeric value




Option Compare Database
Dim currentdb As String
Option Explicit



Private Sub Form_problem_Change()
Combo27 = DLookup("[Desc]", "[HPEM_Problem_Codes]", "[form_problem]= '" & [Task_code] & "'")
End Sub
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:27
Joined
Jan 20, 2009
Messages
12,851
Re: DLookUp return first value only

ria4life said:
Code: 
Private Sub Form_problem_Change()
Combo27 = DLookup("[Desc]", "[HPEM_Problem_Codes]", "[form_problem]= '" & [Task_code] & "'")
End Sub
Click to expand...

The Change Event fires every time a character is entered or deleted in the control. Probably not what you want.

The DLookup is looking for a match for the value held in a form control called Task_Code in a field called form_problem in a table called HPEM_Problem_Codes. Is that what you wanted?

BTW I strongly recommend you reconsider your naming system. I know many developers use them but I abhor the use of underscore in names. This character already has well defined meanings in the VBA.

FORM_ would have to be the worst start to any name because "Form_" is already used as the prefix in all Form level event procedure names.

Also note that you should start a new thread with your questions rather then tacking on to old threads.
 
R

ria4life

Registered User.
Local time
Today, 01:27
Joined
Feb 24, 2016
Messages
40
Galaxiom..thanks for the response...

I created a new thread but unfortunately cannot post links as i am a new user.



----The Change Event fires every time a character is entered or deleted in the control. Probably not what you want.
-Yes, this is how it is meant to function.

-----The DLookup is looking for a match for the value held in a form control called Task_Code in a field called form_problem in a table called HPEM_Problem_Codes. Is that what you wanted?
-Yes, this is what i would like to happen, but it only returns the very first result value no matter what option is selected.


Thanks for the tip. i will rename the forms/ DB's accordingly
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:27
Joined
Jan 20, 2009
Messages
12,851
ria4life said:
----The Change Event fires every time a character is entered or deleted in the control. Probably not what you want.
-Yes, this is how it is meant to function.
Click to expand...

The value in the TaskCode control would not be changing as you change the text in the FormProblem control so there would be no point using the change event.

Maybe you mean to look up the changing text in FormProblem. Something more like:

Code: 
Private Sub FormProblem_Change()
Me.Combo27 = DLookup("[Desc]", "[HPEM_Problem_Codes]", "[form_problem]= '" & Me.[FormProblem].Text & "'")

Note that the FormProblem control Value property does not change until the control loses focus so the Text property is used. Text is the current content of the control and is only available when the control has the focus.

I have also added the reference to the form object (Me) for the controls.
 
You must log in or register to reply here.

Similar threads

S
Default Values
Replies
5
Views
230
Pat Hartman
P
C
Solved Combine both IIf and DLookup as Form control Source
Replies
2
Views
396
captbluefin
C
ardy
Solved Expression Builder Latest Date in Forms
Replies
10
Views
451
ardy
ardy
B
Dlookup problem
2
Replies
21
Views
813
ebs17
E
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
410
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom