Using DLookup to put Value in to a Textbox (1 Viewer)

[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 & "#")

 

[Pat Hartman]

Updating a subform record from the main form is backwards. There is ONE main form record and multiple subform records. Are you sure you ALWAYS know what subform record is current when you run the code?

In the BeforeInsert event of the subform, do this:

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

OR, if the field is currently displayed on the main form, then

Me.txtManagerDiary = Me.Parent!txtLocalSystemDate

Here's a live example from one of my applications that does something a little different. But it shows how to reference parent form data.

Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.Parent!lstTableNames & "" = "" Then
        MsgBox "Please select a list before entering items.", vbOKOnly + vbInformation
        Cancel = True
        Me.Undo
        Me.Parent!lstTableNames.SetFocus
    End If
End Sub

The ONLY time you would use the Forms!yourform!yourcontrol reference style is if you are not in a subform but in a separate form.

And finally, pull rather than push in mainform-subform situations.