Getting error on iif 1

[projecttoday]

The following form code works:

If IsNull(Me.txtqryCol2gndrvarid) Then
Forms.frmlisting.txtGndr = ""
Else
Forms.frmlisting.txtGndr = DLookup ("[abbr]", "tblGndrvars", "[gndrvarid] = " & Me.txtqryCol2gndrvarid)
End If

but if I change it to

Forms.frmlisting.txtGndr = IIf(Nz(Me.txtqryCol3gndrvarid), "", DLookup("[abbr]", "tblGndrvars", "[gndrvarid] = " & Me.txtqryCol2gndrvarid))

I get Syntax Error 3075 missing operator. Any ideas? Hardly important, but it would be nice to know.

Robert

 

[HughLerwill]

What's with;

iif(Nz(Me.txtqryCol3gndrvarid,...

may be;

iif(IsNull(Me.txtqryCol2gndrvarid),...

 

[MajP]

iif(expression, true part, false part)

expression: Nz(Me.txtqryCol3gndrvarid)

Does not evaluate to a true or false

maybe: NZ(Me.txtqryCol3gndrvarid) = "", true part, false part

 

[projecttoday]

I tried isnulll(... and = "" and neither worked.

When Me.txtqryCol2gndrvarid is not null, it works. That is, the dLookup part works. The problem is that I need to trap a null value as input.

 

[MajP]

The usual check is

Trim(controlvalue & " ") = ""

This will catch a null value, empty string, or spaces. Do yourself a favor and avoid iif statements. I can go on why they are not good. If this is form code then it should be

if trim(Me.txtqryCol3gndrvarid & " ") = "" then
Forms.frmlisting.txtGndr = ""
else
Forms.frmListing.txtGndr = DLookup("[abbr]", "tblGndrvars", "[gndrvarid] = " & Me.txtqryCol2gndrvarid)
end if

 

[projecttoday]

I changed it and I'll forget about iif. And it works when I open the main form. However when I run the subform by itself, I get "object doesn't support property or method" on this statement:

Forms.frmlisting.txtGndr = ""

I tried changing it to

Forms.Parent.frmlisting.txtGndr

but got the same error. txtGndr is on the main form.

 

[MajP]

If the main form is frmListing then simply

Me.parent.txtGndr

 

[projecttoday]

Why is it that

Forms.frmListing.txtGndr = DLookup("[abbr]", "tblGndrvars", "[gndrvarid] = " & Me.txtqryCol2gndrvarid)

never causes any problem? Is there any way to make it so the subform will run by itslef without an error?

 

[MajP]

One thing I did not catch
"Forms" is a collection of all open forms (except forms within subforms). When you reference an item from a collection its syntax is either

Forms!itemName
or
Forms("itemName)
such as

Forms("frmListing")
or
Forms!frmListing

So pick one or the other Such as
forms("frmListing").txtGender

Now if you run the subform by itself and "frmListing" is not open (therefore not in the forms collection") you will get an error (item not found).

So fix the syntax and ensure that "frmListing" is open when you run the code.

Additionally:
A subform does not get added to the Forms collection. So you can not reference it from the forms collection. You can only reference it from the subform's module using
Me
Or from the Main form with
SubformControlName.form

So if you want to run code only when it is a subform you can do something like

Public function isSubform(frm as access.form) as boolean
on error goto errLable
dim prnt as access.frm
set prnt = frm.parent
isSubform = true
set Prnt = nothing
exit function
errLable:
exit function
end function

Then you can call this from your form

if issubForm(Me) then
do sub form code here
else
do code if it is not a subform
end if

(code is the correct idea, not tested.)

 

[projecttoday]

At the moment it seems to be working on the main form. This thing has been somewhat frustrating. I need to be able to run the subform to hide and unhide columns. I seem to be able to do that even with the error messages.

Thanks for your help.

 

[wintersoul]

I am trying to populate one field based on a seperate field in another table. I am thinking that by using the iIF statement would be the right start. Table 1 has the filed "unitgeology" that holds the text values that i want to use for table 2. The common field for both tables is "holeID". Table 1 and table 2 both have a "from" and a "to" field that has numeric value representing feet. I want to fill a blank field "RockType" in table 2 with the values from "unitgeology" in table 1, but in order to place the right values in the proper order, it has to be based on same "holeID" and between or less than the "from" and "to" fields. As you can see it is a bit confusing, but i would greatly appreciate any help on doing this task. I will discuss this in further detail. thank you

 

[projecttoday]

You should not store the same information more than once. Instead, store the key to the other table. Then, look up the field on the form using a combobox.