Text Box Query off of Input 1

[tqeonline]

Good Morning to all, just trying to do the very last thing for this database before Christmas!

I have a report. With one label and two text boxes.

I would like the text boxes to autofill the information based off what the label has.

Ex. If Label says Jane Doe, I want it to look into the qryDRE for Jane Doe and put Jane Does email in Text box txtEmail and Jane Does Phone in Text box txtPhone.

I have a database with three fields Name, Email, Phone

the Query just runs from the Database giving me those three fields.

How do I link those text boxes to look at the LABEL then return it. Sort of like a Vlookup in excel.

 

[tqeonline]

I tried this and it didn't

=DLookUp("[qryDRE]![Email]","[qryDRE]"," [txtDREName] ")

would some variation of this work?

 

[dhookom]

Why would a label control be dynamic? Generally a label displays a static string. Where does "Jane Doe" come from?

Typically you can add qryDRE to the record source of your report and join the appropriate fields. Then just include the email and phone fields in the report's record source.

Duane
Hook'D on Access
MS Access MVP

 

[tqeonline]

I also tried :

=DLookUp("[Email]","DREs","Name = '" & [txtDREName] & "' ")

and it just returns "Error?"

DHookum,

The label gets its information from the main database. When someone enters an item. There is a drop down that says DRE, they would select the person in this case Jane Doe.

that drop down is populated by a DRE database. Which is where the phone and email are stored. When I run a report it saves the Name in a global variable that is recalled by the Report. So the Label is just that stored variable. Which in this case would be static.

Now All, and i mean ALL, i lack is getting the Email and Phone fields to autofill the Phone and Email based off the Name.

 

[tqeonline]

I added the qryDRE to the Record Source. Now how could I like the label, Email, and Name together?

 

[tqeonline]

In the Record Source I added:
qryDRE with the fields being Name, Email, Phone

In the criteria of the Name I put:

=[Reports]![rptPPQP]![txtDREName].[Caption]

I linked the Email and Phone to the Record Source.

When I run the report the Email and Phone are both blank.

Any Ideas?

 

[dhookom]

How/where do you pull the value from the global variable to set the caption of your label in the report? You must have code or something to set the caption since a caption of the label can't be a global variable or a function.

Can you describe what you are attempting to do and why you are using a label rather than a text box?

Duane
Hook'D on Access
MS Access MVP

 

[tqeonline]

I am attempting to pull the Global variable which is stored when you click the Reports button. The Global variable is pulled from the main Database. In the Details of the report it says

txtDREName.caption = DRE

I am attempting to make the Phone text box and the Email textbox look at the txtDREName label and pull their correspondign values from a DREs database.

 

[dhookom]

You had a memory variable so why not use it rather than the caption of a label? Just add more code like:

  Me.txtEmail = =DLookUp("[Email]","DREs","Name = '" & DRE & "' ")

Duane
Hook'D on Access
MS Access MVP

 

[tqeonline]

I tried that in the Source of the textbox and it kept wanting to make the stored variable DRE a field. So I put it in the DETAIL of the report and it worked GREAT!

Thanks a lot dhookom!

code ended up looking like this:

txtDREName.Caption = DRE
txtDREEmail.Value = DLookup("[Email]", "DREs", "Name = '" & DRE & "' ")
txtDREPhone.Value = DLookup("[Phone]", "DREs", "Name = '" & DRE & "' ")