Access 2010: Dlookup Help

[pinkpanther6666]

Please could i have some help with the Dlookup command in Access 2010, My database is partly constructed by the below

Table ........................ Player
Fields
PlayerName ...............TextBox
Gender ......................TextBox

Form Name ................Main
Fields

Name ....................... This is a combobox called Combo2
Sex ...........................This is a Text Box


In the control source of the "Sex" text box in my form i have put : -

=DLookUp("Gender","Player","[PlayerName]=" & "[Combo2]")
also i have tried
=DLookUp("Gender","Player","[PlayerName]=" & "[Name]")

What i want to happen in my form Is to select a name and there gender autofills the Sex Text Box
At the moment what appears at the moment is #Error


Can anyone help me please


Many Many Thanks in advance


Steve

 

[Micron]

Create a query that returns all of the fields you will want and create a parameter for the query field FirstName (or LastName or FullName). Notice I did not use NAME- it is a reserved Access word. Check these out before you go any further.

Enter [Fname]=forms!frmMain.Combo2 in the criteria row for Fname (or whatever you're going to call it) then run it to test.
If memory serves, you can temporarily set the form's recordsource to the name of the query, then match each form control to the correct query field name, then remove the form recordsource reference. Then open the form to see if that causes any issue. If not, carry on. If it does, I apologize, my memory is not absolute so a slightly different approach will be needed.

On the AfterUpdate event for the form combo, set the form's recordsource to the name of the query and call the requery method (Forms!frmMain.Requery) and you should get what you want if you tested the query first. As always, make suggested changes in a copy of your db or at least a copy of the forms or queries to be affected. When you close this form from any view, don't save the recordsource you have just set. Better yet, set the form recordsource to "" in the open event.

 

[pinkpanther6666]

Thanks for your quick reply


I think I know what you mean and will have a go



Why won't my original dlookup work


Many thanks


Steve

 

[Micron]

Why won't my original dlookup work

Glad you asked. I overlooked your expression - involved in too many things, methinks.
The syntax is "field", "domain", "criteria", and all references and delimiters must be provided where needed.
=DLookUp("Gender","Player","[PlayerName]=" & "[Combo2]") should be

=DLookUp("[Gender]","[Player]","[PlayerName]= ' " & Forms!Main.[Combo2] & " ' ")
I added space around the red single quotes so you could spot them easier. You would removed them in your final version (e.g. ='" & and then "'")
This is constructed for text values, and I think it should work OK. Numbers and dates use different delimiters.

 

[pinkpanther6666]

Morning,

I have tried this and still doesnt work. im thinking theres something wrong with my database,

Will report back any findings

Many thanks for your help


Steve

 

[Micron]

You must be getting some sort of error message? That might help me. Sometimes the " around the ['s cause an issue.
Does this work as sql for a new query (when the form is open and a combo selection has been made)?
SELECT Gender FROM Player WHERE PlayerName = Forms!Main.Combo2;