Select Case - "Invalid use of Null" Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I've got a button which is supposed to open differrent forms depending on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them together
- it gives me the message "Invalid use of Null". What could be wrong here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers (which
can have values with +/- or 0). Why it sais "Invalid use of Null"? Please,
can anybody help?

Thank you.
Lana
 
You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select
 
Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value. And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has "0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields] will
have a negative value and the [CashVND] will have equal positive value. And i
need to open 4 different forms depending on which of the [bank fields] is not
Null)

Can you show me my mistake please?
Thank you.
Lana


Douglas J. Steele said:
You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Lana said:
Hi all,
I've got a button which is supposed to open differrent forms depending on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers (which
can have values with +/- or 0). Why it sais "Invalid use of Null"? Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
 
That error makes no sense to me, as I don't see a reference to ID anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has "0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields] will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields] is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


Douglas J. Steele said:
You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Lana said:
Hi all,
I've got a button which is supposed to open differrent forms depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on each
record...
Lana


Douglas J. Steele said:
That error makes no sense to me, as I don't see a reference to ID anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has "0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields] will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields] is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


Douglas J. Steele said:
You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
The error message is telling you that the query that's being used for the
recordsource for the form you're trying to open contains 2 (or more) fields
named "ID" (whether or not they show up in the list of fields in the SELECT
part of the query). You need to be a little more explicit. Either

stLinkCriteria = "[Table1].[ID]=" & Me!ID1

or else alias the particular ID field of interest (SELECT Table1.ID AS
ThisID, Table1.Field2, Table2.FIeld3,...) and use that alias in the
criteria.

stLinkCriteria = "[ThisID]=" & Me!ID1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be
more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on each
record...
Lana


Douglas J. Steele said:
That error makes no sense to me, as I don't see a reference to ID
anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has
"0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields]
will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields]
is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


:

You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms
depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much Douglas!
When i changed to
stLinkCriteria = "[T_MAIN].[ID]=" & Me!ID1
all worked fine!!!!!!!!
Next time i should be careful and will not give same name to "ID" fields of
different tables.
Lana


Douglas J. Steele said:
The error message is telling you that the query that's being used for the
recordsource for the form you're trying to open contains 2 (or more) fields
named "ID" (whether or not they show up in the list of fields in the SELECT
part of the query). You need to be a little more explicit. Either

stLinkCriteria = "[Table1].[ID]=" & Me!ID1

or else alias the particular ID field of interest (SELECT Table1.ID AS
ThisID, Table1.Field2, Table2.FIeld3,...) and use that alias in the
criteria.

stLinkCriteria = "[ThisID]=" & Me!ID1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be
more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on each
record...
Lana


Douglas J. Steele said:
That error makes no sense to me, as I don't see a reference to ID
anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has
"0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields]
will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields]
is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


:

You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms
depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
One school of thought says there's nothing wrong with using ID as a field
name in every table. You just have to remember to disambiguate all
references to the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Thank you so much Douglas!
When i changed to
stLinkCriteria = "[T_MAIN].[ID]=" & Me!ID1
all worked fine!!!!!!!!
Next time i should be careful and will not give same name to "ID" fields
of
different tables.
Lana


Douglas J. Steele said:
The error message is telling you that the query that's being used for the
recordsource for the form you're trying to open contains 2 (or more)
fields
named "ID" (whether or not they show up in the list of fields in the
SELECT
part of the query). You need to be a little more explicit. Either

stLinkCriteria = "[Table1].[ID]=" & Me!ID1

or else alias the particular ID field of interest (SELECT Table1.ID AS
ThisID, Table1.Field2, Table2.FIeld3,...) and use that alias in the
criteria.

stLinkCriteria = "[ThisID]=" & Me!ID1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be
more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on
each
record...
Lana


:

That error makes no sense to me, as I don't see a reference to ID
anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

My fields (values, but not controls on form) sometimes have Null
value.
And
I wanted to treat them separately form "0" values at first. Anyway,
I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them
has
"0"
value my button will open the form "blanks" - which is also ok with
me.

But one thing still doesnt work - the cases with "Withdrawals" such
as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer
to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields]
will
have a negative value and the [CashVND] will have equal positive
value.
And i
need to open 4 different forms depending on which of the [bank
fields]
is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


:

You're looking at values for a number of different fields (or are
they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are
you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms
depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put
them
together
- it gives me the message "Invalid use of Null". What could be
wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are
numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of
Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
Back
Top