Events

[cybertyk]

Hello all its me again,

quick easy one this time i want to use this code its self explanatory at what it does

If Me.Company_Repeat.Value = "Yes" Then
Me.lblDelete.Visible = True
Else
Me.lblDelete.Visible = "false"
End If

"Company_Repeat" is a yes no box

i need to know what event to add this to so when the record is loaded it runs this code i have tryed it on Current, load and after update how ever the lbl does not show

please advise

i also want to do a similar thing for the flag company but im guessing same code for that XD

 

[vbaInet]

Code is:

Me.lblDelete.Visible = (nz(Me.Company_Repeat.Value, "") = "Yes")

Put that in the On Current event of the form and the After Update event of the Company_Repeat textbox.

 

[cybertyk]

Ah the old NZ im guessing im to use to programing in VB 6

 

[vbaInet]

Actually, you did mention checkbox, just so you know the values that they actually return are 0 for No and -1 for Yes. So change it to either one of the following:

Me.lblDelete.Visible = (Nz(Me.Company_Repeat.Value, No) = Yes)

Me.lblDelete.Visible = (Nz(Me.Company_Repeat.Value, 0) = -1)

Notice I didn't use quotes for the Yes.

 

[cybertyk]

ok that works but there is a slight probleam

the code is
Me.lblDND.Visible = (Nz(Me.Company_Repeat.Value, No) = Yes)
Me.imgFlag.Visible = (Nz(Me.Company_Flag.Value, No) = Yes)

its making them all flaged and do not disturb unless i tick the box

would i need to change it to the following
Me.lblDND.Visible = (Nz(Me.Company_Repeat.Value, yes) = no)
Me.imgFlag.Visible = (Nz(Me.Company_Flag.Value, yes) = no)

 

[vbaInet]

What I wrote was following what you wrote. If you want the negation of True, you put Not. Like this:

Me.lblDND.Visible = Not (Nz(Me.Company_Repeat.Value, No) = Yes)

Or this would make more sense:

Me.lblDND.Visible = (Nz(Me.Company_Repeat.Value, No) = No)

You could still try yours. See what the outcome is based on what you want.

 

[cybertyk]

thank you kind sir you have been a great help to me over the past few weeks i do have another question about a query i have written then query works a treat how ever is it possable to select one of the rows to load the record source?

 

[vbaInet]

No worries

I don't understand your question. Please elaborate.

You want to find a specific field's value based on some criteria setup in the query?

 

[cybertyk]

ok erm i tryed that code and the lbl and img are always visable when there propities are set to false :S even with the nrecent code verations you sent me.


and the other one is, can you select a record from a quary and open it in a form (basicly lol)

 

[cybertyk]

Ha its sorted this works perfecccct
Me.lblDND.Visible = (Nz(Me.Company_Repeat.Value, 0) = -1)
Me.imgFlag.Visible = (Nz(Me.Company_Flag.Value, 0) = -1)

 

[vbaInet]

Post your db and I'll have a quick look at what you're talking about.

Still isn't clear what you're asking. Set the Record Source property of your form to the query from the list.

 

[cybertyk]

right, ok im with you i could take that approce

lets say that i created anew record source

the following! SELECT tblCustomer.ID, tblCustomer.[Company Name], tblCustomer.[Company Address 1], tblCustomer.[Company Address 2], tblCustomer.[Company Address 3], tblCustomer.[Company City], tblCustomer.[Company Postcode], tblCustomer.[Company County], tblCustomer.[Company Catagory], tblCustomer.[Company Delete], tblCustomer.[Company Repeat], tblContact.ContactID, tblContact.[Contact Title], tblContact.[Contact FName], tblContact.[Contact LName], tblContact.[Contact Position], tblCustomer.[Company Flag], tblCustomer.[Company Flag]
FROM tblCustomer LEFT JOIN tblContact ON tblCustomer.ID = tblContact.ContactID
WHERE (((tblCustomer.[Company Delete])=False) AND ((tblCustomer.[Company Flag])=Yes))
ORDER BY tblCustomer.[Company Name];

then would i add a buttion with some VB code for open form and then change its record source?


i cant post the database im afried as parts of it are live and contian data protected by the data protection act


Sorry

 

[vbaInet]

Why do you want to change the record source when the form opens? Why don't you want to set the record source in the PROPERTY SHEET?

Well, with regards you not being able to post your db, it's your call. If there's confidential data you can always remove it on a copy of the db and input some dummy data. My only final suggestion would be one of this:

Me.lblDelete.Visible = (Nz(Me.Company_Repeat.Value, 0) = -1)

Me.lblDelete.Visible = (Nz(Me.Company_Repeat.Value, 0) = 0)

And if you don't understand what I'm doing then go back to your code:

If Nz(Me.Company_Repeat.Value, 0) = -1 Then
   Me.lblDelete.Visible = True
Else
   Me.lblDelete.Visible = False
End If

 

[cybertyk]

no that code you gave me work perfect thank you for that

i supose i can create a copy of the form and then set the record that way i was just tying to keep the number of forms to a min

i will just create a copy of the main form and change the record source

thank you for your help and what ill do is create a dummy database in a little while so you can take a look at the db

 

[vbaInet]

No need creating a dummy db now if it's working.

So you basically want to dynamically change the record source in some cases?

Have a look at this tutorial:

http://baldyweb.com/BuildSQL.htm

 

[cybertyk]

ok got all that working but i just had a brainstorm or its a migrain im not sure,

in the critira of a quary can you do something like

is null then "Dear Sir / Madam"??

 

[vbaInet]

I think it could be a migraine

You sure can:

AliasName: IIF(IsNull([FieldName]), "Dear Sir/Madam", [FieldName])

 

[cybertyk]

Cheers thanks ever so much

 

[vbaInet]

You're welcome.