Using DLookup to put Value in to a Textbox

[Malcolm17]

Hello,

I am looking to use DLookup to put values in to textboxes using VBA, can I do something like this please:

Me!subfrmDiary.txtManagerDiary.Value = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = Forms.frmBackground.txtLocalSystemDate")

I have also tried:

Forms.frmBackground.subfrmDiary.txtManagerDiary = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = Forms.frmBackground.txtLocalSystemDate")
Forms.frmBackground.subfrmDiary.txtManagerDiary.Value = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = Forms.frmBackground.txtLocalSystemDate")
Forms!frmBackground!subfrmDiary!txtManagerDiary.Value = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = Forms.frmBackground.txtLocalSystemDate")
Forms!frmBackground!subfrmDiary.txtManagerDiary.Value = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = Forms.frmBackground.txtLocalSystemDate")

I have also tried DLookup in another textbox and coping the data to an unbound textbox to then add or edit then save on lose focus.

I am looking to be able to lookup the data for the textbox, edit it and then save is using a RunSQL command After the textbox Loses Focus. The Data will depend upon the date selected. I'm trying to avoid the page being linked to a query because it is part of a multi user system and I have a couple of different scenarios to use this with different type of queries.

Thank you,

Malcolm

 

[plog]

 "tbl_Net_Diary", "TheDate = Forms.frmBackground.txtLocalSystemDate"

To insert a variable's value into a string, the variable must be outside of the quote marks, otherwise you are just literally inserting the name of the variable into that string. Additionally, you have to let the criteria know you are using a date value by surrounding the date value in hash marks (#):

 "tbl_Net_Diary", "TheDate = #" & Forms.frmBackground.txtLocalSystemDate & "#"

 

[Malcolm17]

Hi Plog,

Thank you for that, I have updated to the following as you said but it returns me the Run time error 438; Object doesn't support this object or method. Can you think of anything else I can try please?

Forms!frmBackground.subfrmDiary.txtManagerDiary.Value = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = #" & Forms.frmBackground.txtLocalSystemDate & "#")

 

[CJ_London]

Can you think of anything else I can try please?

perhaps take a look at the thread

Forms: Refer to Form and Subform properties and controls

if you do, you will realise there is a problem with this
Forms!frmBackground.subfrmDiary.txtManagerDiary.Value

and maybe this
Forms.frmBackground.txtLocalSystemDate

 

[Malcolm17]

Fabulous!! Thank you both for your help.

I got it working with this:

Forms!frmBackground!subfrmDiary!txtManagerDiary.Value = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = #" & Forms!frmBackground.txtLocalSystemDate & "#")