Controlsource won't let Dlookup to enter

  • Thread starter Thread starter Mishanya
  • Start date Start date
M

Mishanya

I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
 
Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
I don't see why you are experiencing the error, but an alternative, and more
efficient, solution would be to base the form on a query which joins the
form's current table to tblClients on ClientID. Include the ClientName
column in the query and bind a text box to it on the form.

Ken Sheridan
Stafford, England
 
OK, I'll do it this way, but still, it means I'm Dlookup-less :)

Ken Sheridan said:
I don't see why you are experiencing the error, but an alternative, and more
efficient, solution would be to base the form on a query which joins the
form's current table to tblClients on ClientID. Include the ClientName
column in the query and bind a text box to it on the form.

Ken Sheridan
Stafford, England

Mishanya said:
I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
 
The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

John Spencer said:
Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
 
Well, it's mystery to me. Your expression looks good to me. Some
additional thoughts are posted below.

First thing I do is to try to narrow the problem. I realize that you
probably have done all this, but it never hurts to suggest.
Is this happening in all controls on all forms in this database?
Is this happening on just the one form?
Is this happening on just the one control?
Is this happening with just DLookup or are the other aggregate functions
also affected?

Just as a test try using one of the other functions and see if that
works. Try DFirst

=DFirst("ShemLakoakh","Pirtei_Lakoakh_Klali","LakoakhID=" & [LakoakhID])

If that fails then I would suspect your form is corrupted.

You might have to rebuild the form or you might need to decompile your
database.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and
includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

John Spencer said:
Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
Click to expand...
 
Well, it's mystery to me. Your expression looks good to me. Some
additional thoughts are posted below.

First thing I do is to try to narrow the problem. I realize that you
probably have done all this, but it never hurts to suggest.
Is this happening in all controls on all forms in this database?
Is this happening on just the one form?
Is this happening on just the one control?
Is this happening with just DLookup or are the other aggregate functions
also affected?

Just as a test try using one of the other functions and see if that
works. Try DFirst

=DFirst("ShemLakoakh","Pirtei_Lakoakh_Klali","LakoakhID=" & [LakoakhID])

If that fails then I would suspect your form is corrupted.

You might have to rebuild the form or you might need to decompile your
database.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and
includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

John Spencer said:
Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
Click to expand...
 
thank You for such a detailed answer.
Meantime I've even reinstalled Windows and Office and the tried to use
Dfirst - same effect (none - error again)
So, my only hope is that the DB is corrupted indeed.
I'll certainly run all the tests You've suggested.
Thanks again.

John Spencer said:
Well, it's mystery to me. Your expression looks good to me. Some
additional thoughts are posted below.

First thing I do is to try to narrow the problem. I realize that you
probably have done all this, but it never hurts to suggest.
Is this happening in all controls on all forms in this database?
Is this happening on just the one form?
Is this happening on just the one control?
Is this happening with just DLookup or are the other aggregate functions
also affected?

Just as a test try using one of the other functions and see if that
works. Try DFirst

=DFirst("ShemLakoakh","Pirtei_Lakoakh_Klali","LakoakhID=" & [LakoakhID])

If that fails then I would suspect your form is corrupted.

You might have to rebuild the form or you might need to decompile your
database.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and
includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

John Spencer said:
Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
Click to expand...
 
Well, I've just found the reason.
Instead of using comma in the expression, I just needed to use ";"!
=DLookup("ClientName"; "tblClients"; "ClientID = " & ClientID)

Frustrated from entering the expression manually, using the syntax suggested
in all the books and websources (with commas), I entered the builder of the
ControlSource and clicked the Dlookup function from the list of functions.
Suddenly I saw, that ";" was used:
DLookup («expr»; «domain»; «criteria»).

Took me 1 week of buttering against the wall, reinstalling Windows and
Office. What can you say about this? How is this possible???


John Spencer said:
Well, it's mystery to me. Your expression looks good to me. Some
additional thoughts are posted below.

First thing I do is to try to narrow the problem. I realize that you
probably have done all this, but it never hurts to suggest.
Is this happening in all controls on all forms in this database?
Is this happening on just the one form?
Is this happening on just the one control?
Is this happening with just DLookup or are the other aggregate functions
also affected?

Just as a test try using one of the other functions and see if that
works. Try DFirst

=DFirst("ShemLakoakh","Pirtei_Lakoakh_Klali","LakoakhID=" & [LakoakhID])

If that fails then I would suspect your form is corrupted.

You might have to rebuild the form or you might need to decompile your
database.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and
includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

John Spencer said:
Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
Click to expand...
 
The delimiter is dependent on your operating system setup. You can
specify a comma or a semi-colon. MOST US systems/computers default to a
comma and I believe many (if not most) other countries default to a
semi-colon. The reason for non-US systems to default to a semi-colon is
that many of them use the comma as a decimal separator in numbers.

Sorry, I never even thought of that being the problem. I hope I will be
more aware of the possibility in the future.

If it is any consolation, you've taught me something I did not know.
Not all that hard, but not all that easy either.

Thank you for posting back with the solution to your problem. It not
only educated me, it will probably help someone else in the future.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Well, I've just found the reason.
Instead of using comma in the expression, I just needed to use ";"!
=DLookup("ClientName"; "tblClients"; "ClientID = " & ClientID)

Frustrated from entering the expression manually, using the syntax suggested
in all the books and websources (with commas), I entered the builder of the
ControlSource and clicked the Dlookup function from the list of functions.
Suddenly I saw, that ";" was used:
DLookup («expr»; «domain»; «criteria»).

Took me 1 week of buttering against the wall, reinstalling Windows and
Office. What can you say about this? How is this possible???


John Spencer said:
Well, it's mystery to me. Your expression looks good to me. Some
additional thoughts are posted below.

First thing I do is to try to narrow the problem. I realize that you
probably have done all this, but it never hurts to suggest.
Is this happening in all controls on all forms in this database?
Is this happening on just the one form?
Is this happening on just the one control?
Is this happening with just DLookup or are the other aggregate functions
also affected?

Just as a test try using one of the other functions and see if that
works. Try DFirst

=DFirst("ShemLakoakh","Pirtei_Lakoakh_Klali","LakoakhID=" & [LakoakhID])

If that fails then I would suspect your form is corrupted.

You might have to rebuild the form or you might need to decompile your
database.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and
includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

:

Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
Click to expand...
 
For the sake of curiosity, I went to the Windows Regional Standards and
changed the default for List Elements Delimeter to comma. Indeed, when I've
opened the form again, the source for the unbound box changed the Dlookup
sintax automatically to "commas".
Misterious the Access ways :)
Now I wonder, if it's OK to have comma as default standard for List Elements
Delimeter and for Decimal Fraction Delimeter both, or it will make some mess.
Thank You again for your patient guidance!

John Spencer said:
The delimiter is dependent on your operating system setup. You can
specify a comma or a semi-colon. MOST US systems/computers default to a
comma and I believe many (if not most) other countries default to a
semi-colon. The reason for non-US systems to default to a semi-colon is
that many of them use the comma as a decimal separator in numbers.

Sorry, I never even thought of that being the problem. I hope I will be
more aware of the possibility in the future.

If it is any consolation, you've taught me something I did not know.
Not all that hard, but not all that easy either.

Thank you for posting back with the solution to your problem. It not
only educated me, it will probably help someone else in the future.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Well, I've just found the reason.
Instead of using comma in the expression, I just needed to use ";"!
=DLookup("ClientName"; "tblClients"; "ClientID = " & ClientID)

Frustrated from entering the expression manually, using the syntax suggested
in all the books and websources (with commas), I entered the builder of the
ControlSource and clicked the Dlookup function from the list of functions.
Suddenly I saw, that ";" was used:
DLookup («expr»; «domain»; «criteria»).

Took me 1 week of buttering against the wall, reinstalling Windows and
Office. What can you say about this? How is this possible???


John Spencer said:
Well, it's mystery to me. Your expression looks good to me. Some
additional thoughts are posted below.

First thing I do is to try to narrow the problem. I realize that you
probably have done all this, but it never hurts to suggest.
Is this happening in all controls on all forms in this database?
Is this happening on just the one form?
Is this happening on just the one control?
Is this happening with just DLookup or are the other aggregate functions
also affected?

Just as a test try using one of the other functions and see if that
works. Try DFirst

=DFirst("ShemLakoakh","Pirtei_Lakoakh_Klali","LakoakhID=" & [LakoakhID])

If that fails then I would suspect your form is corrupted.

You might have to rebuild the form or you might need to decompile your
database.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and
includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

:

Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
Click to expand...
 
by the way, although the List Elements Delimeter in US system is set to
comma, when it comes to define the Column Widths list for Combobox, the
values are still devided by semi-colon. How about that?


John Spencer said:
The delimiter is dependent on your operating system setup. You can
specify a comma or a semi-colon. MOST US systems/computers default to a
comma and I believe many (if not most) other countries default to a
semi-colon. The reason for non-US systems to default to a semi-colon is
that many of them use the comma as a decimal separator in numbers.

Sorry, I never even thought of that being the problem. I hope I will be
more aware of the possibility in the future.

If it is any consolation, you've taught me something I did not know.
Not all that hard, but not all that easy either.

Thank you for posting back with the solution to your problem. It not
only educated me, it will probably help someone else in the future.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Well, I've just found the reason.
Instead of using comma in the expression, I just needed to use ";"!
=DLookup("ClientName"; "tblClients"; "ClientID = " & ClientID)

Frustrated from entering the expression manually, using the syntax suggested
in all the books and websources (with commas), I entered the builder of the
ControlSource and clicked the Dlookup function from the list of functions.
Suddenly I saw, that ";" was used:
DLookup («expr»; «domain»; «criteria»).

Took me 1 week of buttering against the wall, reinstalling Windows and
Office. What can you say about this? How is this possible???


John Spencer said:
Well, it's mystery to me. Your expression looks good to me. Some
additional thoughts are posted below.

First thing I do is to try to narrow the problem. I realize that you
probably have done all this, but it never hurts to suggest.
Is this happening in all controls on all forms in this database?
Is this happening on just the one form?
Is this happening on just the one control?
Is this happening with just DLookup or are the other aggregate functions
also affected?

Just as a test try using one of the other functions and see if that
works. Try DFirst

=DFirst("ShemLakoakh","Pirtei_Lakoakh_Klali","LakoakhID=" & [LakoakhID])

If that fails then I would suspect your form is corrupted.

You might have to rebuild the form or you might need to decompile your
database.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and
includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect
errors are gone, the indexes are repaired, inconsistencies between the
text- and compiled-versions of the code are fixed, and reference
ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first symptom in
this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
The problem is the ControlSource won't accept any trial to enter any Dlookup
as such, complaining about syntax error. So, whatever I enter - it does not
matter. As I've noted, the same line works fine if used in On Load or in
other computer. If those were the points You've listed, it would at least let
me finish the entrance in the ControlSource and then show an error in the
form itself. Instead, it automatically adds blank space after the "=":
= DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])
instead of
=DLookup("ShemLakoakh", "Pirtei_Lakoakh_Klali", "LakoakhID = " & [LakoakhID])

and pops the error msg about syntax.

:

Is the text box named something other than ClientID?
Do you have the ClientID field bound to a control on the form? Or at a
minimum is it available in the underlying query?

Is there a value in ClientID other than Null.

You might try

=DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID,0))



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
I've asked this before, but still can't get any further.

I'm trying to populate txtBox ClentName in the form, which is based on the
table that have ClientID as foreign key (so it only can pull the Autonumber
and I don't want to use Combobox to present ClientName because it is
read-only form).
So I put this in the ControlSource:

=DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

well, I can not leave the ControlSource, cuz this msg pops up:

"The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks"

I put [] on ClientID:
=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])
same effect (none - same error msg).

I checked myself putting exactly the same syntax in On Load event of the form:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID) -
and it works! So the syntax is OK.

Moreover - I copied the DB to other laptop and tried to enter same Dlookup
to my txtBox - it worked too! With no error msg.

I even reinstalled MS Office using the one wich is installed in the laptop
where my Dlookup worked - but again, nothing changed - same message, as if it
is my computer that does not want to digest it.

So, the syntax is OK (worked in On Load and in other computer), the Office
is OK - what is not OK?
Click to expand...
Click to expand...
Click to expand...
 
by the way, although the List Elements Delimeter in US system is set to
comma, when it comes to define the Column Widths list for Combobox, the
values are still devided by semi-colon. How about that?
Click to expand...

That's a regional setting in Windows... Control Panel... Regional Settings.
 
Back
Top