Cannot get VBA to put data in to a textbox after hiding (1 Viewer)

[Malcolm17]

Hello,

I have a form and a subform which has a textbox called tctManagerDiary, when I click on a button to hide the subform then click on a button to show the subform the data will not append to the textbox. The code I am trying is

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

I have also tried other variations like

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

I have also tried refreshing and requerying the form, but no joy.

Please can anyone suggest what I am doing wrong and how I can correct this.

Thank you,

Malcolm

 

[theDBguy]

Do you know (have you verified) if the DLookup() is returning a valid result (not Null)?

 

[Gasman]

Is the date control in the correct format?

 

[CJ_London]

what is your date format? dd/mm/yyyy or mm/dd/yyyy?

if the former then a date of 11th Oct will be treated as 10th Nov because sql expects either mm/dd/yyyy or the standard yyyy-mm-dd

so you would need to use the format function

format(Forms!frmBackground.txtLocalSystemDate,”yyyy-mm-dd”)

 

[Malcolm17]

I'm pretty sure that the DLookup is working but my date is the issue, I think I remember this problem before. My date format is dd/mm/yyyy.

I'm trying this but it doesn't work, please can you help

Forms!frmBackground!subfrmDiary.txtManagerDiary = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = #" & Format(Forms!frmBackground.txtLocalSystemDate, "yyyy-mm-dd" & "#"))

Thank you, Malcolm

 

[Gasman]

I'm pretty sure that the DLookup is working but my date is the issue, I think I remember this problem before. My date format is dd/mm/yyyy.

I'm trying this but it doesn't work, please can you help

Forms!frmBackground!subfrmDiary.txtManagerDiary = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = #" & Format(Forms!frmBackground.txtLocalSystemDate, "yyyy-mm-dd" & "#"))

Thank you, Malcolm

Well I would test it in the immediate window or debug.print the result?
Why the external forms reference?

 

[Malcolm17]

I've just checked, it is a date format issue as the DLookup works but it thinks that it is 10th of November.

The working code is

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

however I need to try and format it - please can you help me with this?

I also used the code below and it works, again I still have the date format issue. I just used the Forms!Main!Subform etc as it felt a bit more explainable to myself for the future.

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

Thank you,

Malcolm

 

[Malcolm17]

Yay, I have it working now with the code below.

Thanks again for all your help again tonight

Me!subfrmDiary!txtManagerDiary = DLookup("ManagerDiary", "tbl_Net_Diary", "TheDate = " & Format(Forms!frmBackground.txtLocalSystemDate, "\#mm/dd/yyyy\#"))