UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup - Find A Value Based On Control + Field, Access 2016    
 
   
Firepen
post Jun 3 2020, 06:42 AM
Post#1



Posts: 6
Joined: 31-March 20



Hi Guys,
I am having a lot of trouble with this. Want to display in a text box, corresponding field based cbo box value on same form. Record not saved so need to find the field that corresponds with the value in the cbo on the unsaved record.

I am trying to use Dlookup as the control source of the txt box (but have also tried to set the value in VBA) I am 100% sure the names are all correct and present in the tables/form. I made a new tbl/frm to test on too. The value in CboG value does have a corresponding entry in the table that is under FID. I use no lookup or multi value fields in any tables.

Table = tblTable
Field Name1 = Fname (Short Text)
Field Name2 = FID (Primary, Autonumber)
CboBox = CboG

My statement in plain English: Find the Name (Fname) in the table, WHERE the ID (FID) in the table is the same as the Value (Number) currently in the cbo box.

I have tried (among others)
=DLookUp("[Fname]","[tblTable]","[tblTable].[FID] = " & [Me].[CboG].[Value])
=DLookUp("[Fname]", "[tblTable]", "[FID] = " & Me.CboG.Value)
=DLookUp("[Fname]", "[tblTable]", "[FID] = " & "[Me].[CboG].[Value]")
=DLookUp("[Fname]", "tblTable", "[FID] =" & Me.CboG)
=DLookUp("[Fname]","tblTable","[FID] =" & [Me].[CboG].[Value])
=DLookUp("[Fname]","tblTable","FID = '" & Me.CboG & "'")
=DLookUp("[Fname]", "tblTable", "FID = " & Me.CboG)

It does not show, even on existing records. Clearly, I either shouldn’t be using Dlookup or am using it wrong (still a beginner), but I cannot seem to see the right way in my searching so far.

Can anyone put me out of my misery!?

Attached File(s)
Attached File  Dtest.zip ( 27.26K )Number of downloads: 4
 
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 07:01 AM
Post#2


UtterAccess Moderator
Posts: 13,056
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

First, your combo is called 'Combo20', not 'CboG' !!!

You need to re-name it - go the the Properties sheet, select the 'Other' tab and enter the correct name there.

Then, for the ControlSource, you can change it to:
=DLookUp("Fname","tblTable","FID = " & [CboG])

'Me' is only available within VBA and refers to the class in which you are writing the code (normally a form or report module). You could have used [Form] in this instance (ie within an expression), but it is not really required.

All that said, I don't really see how your tables relate to each other. I guess you are just in an experimental phase.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
moke123
post Jun 3 2020, 07:08 AM
Post#3



Posts: 1,390
Joined: 26-December 12
From: Berkshire Mtns.


David beat me to it. You named the Combox's label not the combobox control.
Go to the top of the page
 
Firepen
post Jun 3 2020, 07:17 AM
Post#4



Posts: 6
Joined: 31-March 20



Thanks Guys,

Darn, the CboG misname was on the test DB (trying on a blank slate) Names were not the issue on the main DB as they were correct.

Me is only VBA! cheekybuddha you literally did put "Me" out of my misery, thanks guys got the test working will go sort out the main DB. I learned it all together so I mix up things. Thank you for expanding my knowledge, this will really help me!

Edit: Added a VBA based example too under the expression based txt and Interestingly the VBA version loads in noticably faster

Dim SVG As String
AVG = DLookup("Fname", "tblTable", "FID = " & [CboG])
Me.txtV = AVG

This post has been edited by Firepen: Jun 3 2020, 07:42 AM
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 07:31 AM
Post#5


UtterAccess Moderator
Posts: 13,056
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Moke and I are happy to help - keep asking the questions and keep on learning! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 05:19 AM