Prev: Avery label template 5596?
Next: Excel sheet (OLE object) sometimes open on the form sometimes in E
From: KM on 13 Jan 2010 15:57
It's something that's probably pretty simple for most... so I would
appreciate any help or guidance.

I have a form with a number of fields. I'm using a dlookup function to
bring in a value from another table based on what is entered in the fields
above. If I put the dlookup function in the control source within the text
box options, the right values come trough. My issue is that I want to edit
those values. If I put the same code in the default within the properties, I
either get an error, or nothing comes back.

How could I be able to have the text box on the form populate automatically
based on what's entered in previous fields, and then be able to change the
value if I needed to.

=DLookUp("[VendorName]","[MainTable]","[StoreNumber]=Forms![InvoiceTracking_frm]![StoreNumber]")
From: John W. Vinson on 13 Jan 2010 17:07
On Wed, 13 Jan 2010 12:57:10 -0800, KM <KM(a)discussions.microsoft.com> wrote:

>It's something that's probably pretty simple for most... so I would
>appreciate any help or guidance.
>
>I have a form with a number of fields. I'm using a dlookup function to
>bring in a value from another table based on what is entered in the fields
>above. If I put the dlookup function in the control source within the text
>box options, the right values come trough. My issue is that I want to edit
>those values. If I put the same code in the default within the properties, I
>either get an error, or nothing comes back.
>
>How could I be able to have the text box on the form populate automatically
>based on what's entered in previous fields, and then be able to change the
>value if I needed to.
>
>=DLookUp("[VendorName]","[MainTable]","[StoreNumber]=Forms![InvoiceTracking_frm]![StoreNumber]")

This will just display the looked-up value. Since you need to edit it (in this
table, leaving it unchanged in the MainTable I presume!) you'll need to "push"
it into the textbox.

To do this you'll need to use the AfterUpdate event of (in this case, I'm
guessing) the StoreNumber control. Something like

Private Sub StoreNumber_AfterUpdate()
If IsNull(Me!yourcontrolname) Then ' don't stomp on already entered value
Me!yourcontrolname = DLookUp(... <your expression above>...)
End If
End Sub

If you do want to overwrite the user entered value when a new store number is
selected, omit the If and End If lines.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Avery label template 5596?
Next: Excel sheet (OLE object) sometimes open on the form sometimes in E