DLookup Problem

[gargara]

Hello to all the members ot this forum

First of all I want to apologize for my bad english.

I am new to access 2007, and totally noob , but i realy need some major help on one question , that i have no one to ask.

So , the deal is like that: I have two tables - tblStudents and tblPayments
In tblStudents lets say I have two columns - "IDNumber" (auto number) and "FirstName" (text field)
In tblPayments i have "ID"(which is the auto number of the payment and is not related to the problrem) , the next column is "IDNumber" (Long Integer) , after that i have "FirstName" (Text field) and finally one field for the money that they pay , but that is not the point.
I need to make a form of tblPayments in which when i fill the "IDNumber" manually (not from a combo box , because the numbers are like 1000) , then the second controller - "FirstName" automatically to return the name of the student. So i spend two days reading for DLookUp functions , but i am not getting any progress at all. I have tried many things and nothing worked, i get the same mistake -
"The expression you entered contains invalid syntax"

So i started from the beigining and tried only a A Function with No Criteria:

= DLookUp("[FirstName]", "tblStudents")
(which i wrote in the field Control Source of the controller "FirstName") and I get "The expression you entered contains invalid syntax"
so I have treied :

FirstName = DLookUp("[FirstName]", "tblStudents") , and

= DLookUp("FirstName", "tblStudents")

and so on and on .....

Not to mention that in the beginning i have tried the full function :

=DLookup("[FirstName]", "tblStudents", "[IDNumber] =" & Forms![Payments]!IDNumber)

FirstName=DLookup("[FirstName]", "tblStudents", "[IDNumber] =" & Forms![Payments]!IDNumber)

=DLookup("[FirstName]", "tblStudents", "IDNumber=" "IDNumber"


and so on and on.... everyting was wrong

Please help me just with the Function with No Criteria ,and i think i can handle it from there.

 

[KenHigg]

First off is IDNumber a text data type or a numeric data type?

 

[gargara]

In "tblStudents" IDNumber is AutoNumber
In "tblPayments" IDnumber is Numeric data - Long Integer

Thanks for the quick reply!

 

[JANR]

I hate Dlookup when used in controlsource on a from, usually get the syntax wrong. I use the AfterUpdate event to set values to other fields.

Me.FirstName = Dlookup("Firstname", "tblStudents", "[IDNumber]=" & Me.IDNumber)

JR

 

[gargara]

Thanks a lot JR , but I have allready tried this in the beginning. There is something wrong with my expressions or fields , and I am beginnig to wonder to take your advise for the AfterUpdate event, but i have spend the last two days non-stop trying everything , reading all kinds of stuff on-line and in books , and just want to know what am i doing wrong
If i understand how to get this function to work my all DB will be completed!

 

[JANR]

=DLookup("[FirstName]", "tblStudents", "[IDNumber] =" & Forms![Payments]!IDNumber)

You almost got it with this expression, BUT change the commas to ;

=DLookup("[FirstName]"; "tblStudents"; "[IDNumber] =" & Forms![Payments]!IDNumber)

JR

 

[KenHigg]

hummm... I think the commas are the correct way. Something else is wrong...?

 

[gargara]

JR you are send from haven to help people before they commite a suicide!!!

Those MF , so called "Experts" , could wrote this 10 000 times allready , but NO!!

Thank you so much for your help , i belive in justice again !!!

LONG LIVE JR!

 

[KenHigg]

Those MF , so called "Experts" , could wrote this 10 000 times allready , but NO!!

What does MF mean? And what experts are you referring to?

 

[JANR]

Glad to help.

The expressionbuilder show you the correct syntax but as I am now used to VBA I often forget the minor diffrences. aarggh!! I seldom use Dlookup in controlsource on a form.

JR

 

[gargara]

JR , in the Access 2007 Hepl it is written:

"=DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)"

What can I say? All the examples and tutorials in internet are with comma, i can't understant.
Thank's to you it works! Now I can use it too , and i can sleep again!

 

[lala]

What does MF mean? And what experts are you referring to?

i'm almost sure Experts are the Experts-Exchange website

and MF??? motherf**rs

you really didn't know?

 

[KenHigg]

I was just wondering if he/she was calling us MF'rs?

 

[lala]

lololol, no, he was praising you, guys, and saying that the Experts, which is a not a free website, couldn't do in hours what you did in a few minutes


something like that if i understood him correctly

 

[KenHigg]

Oh - My bad

 

[LPurvis]

Well, if it even is Experts-Exchange that is being referred to then, to be fair, there are some fantastic experts who frequent there (not saying that everyone or even most are top notch - but there are unquestionably some great folk).
Not that I post there myself much anymore (hence I'd try not to take offence even if that were the intended target :-s) and no, I never wear my "Genius" EE TShirt lol. :-s

However this question was all about the locale of the OP.
Using semi-colons is only required (and indeed even possible) if your local settings require that commas are used differently (for a decimal point etc I believe - internationalisation isn't a forte of mine) - but that kind of localisation information is important to know in answering a question like this, otherwise it's harder to get the answer from those of a different localised setting, and really wouldn't be fair to judge all "experts" accordingly - given that their syntax does use the more common comma as a parameter separator.

FWIW I thought the OP's English was excellent (better than many native speakers when asking questions ;-) but information as to the region might be useful in future in such cases.

Cheers.

 

[Xx_TownDawg_xX]

I hate Dlookup when used in controlsource on a from, usually get the syntax wrong. I use the AfterUpdate event to set values to other fields.

Me.FirstName = Dlookup("Firstname", "tblStudents", "[IDNumber]=" & Me.IDNumber)

JR

Ok, I've tried this:

Me.Fiscal_ID = Dlookup("Fiscal_ID", "qryFiscal", "[AuditDate]=" & Me.AuditDate)

Anyone see my error?

 

[boblarson]

Me.Fiscal_ID = Dlookup("Fiscal_ID", "qryFiscal", "[AuditDate]=#" & Me.AuditDate & "#")

 

[Xx_TownDawg_xX]

OK.. made that change.. pasted it into the AfterUpdate event.

Now on the control itself.. do I put Fiscal_ID for the source? I bound the control to the field in the table (Fiscal_ID)

 

[Xx_TownDawg_xX]

Let me upload a sample db, Bob.