Rabu, 15 Mei 2019

Re: [MS_AccessPros] Visible Property

 

Bill,
The debug window will require the qualification of the form object ([Forms]![f_EmployeeCommonInfo]!) while code running in the form can use simply Me.

I would try code like:

Dim lngEmpID as Long
lngEmpID = Nz(Me.EmpEmpID,0)

Me.sf_Dependents.Visible = DCount("*", "t_dependents", "DepEmpID = " & lngempID) > 0

Remember to set breakpoints and/or debug.print to help confirm your code and values.

Regards,
Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Bill.Singer@at-group.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, May 14, 2019 5:02 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Visible Property
 


Duane,

I went through your stuff again and did some more reading.

 

This works.

 

?Dcount("*","t_dependents","DepEmpID =" & [Forms]![f_EmployeeCommonInfo]![EmpempID])

 

 

It also works when I put it in the form and scroll between records.  I had to put in another record to test it out.  That was causing some of my other errors.

 

Except… when I go to a new record it tries to read "DepEmpID" and compare it to EmpempID before there is an EmpempID in existence.  Then I get error 3075.

Any thoughts on how to get it to not fire first thing on a new record.

 

Thanks for you help.  Sorry I am so slow.

 

 

Bill

 

Bill Singer

 

 

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, May 10, 2019 8:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Visible Property

 

 

Bill,

I still can't believe the expression you said works actually is correct. Did you copy and paste it into your email?

As written, it should never change.

 

I would open the debug window (press Ctrl+G) and enter:

?DCount("*", "t_dependents", "DepEmpID = " & "EmpempID") > 0

 

Then try again with a known employee ID in place of 999:

DCount("*", "t_dependents", "DepEmpID = " & 999) > 0

 

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Bill Singer Bill.Singer@at-group.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, May 9, 2019 10:06 PM
To: ms_access_professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Visible Property

 

 

Yes, your assumptions are correct.

Thank you,

Bill Singer

Sent from my phone



From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]

Sent: Thursday, May 9, 9:40 PM

Subject: Re: [MS_AccessPros] Visible Property

 

Really? I find that very difficult to believe since "EmpempID" is a string value and not any type of matching number. 

Can we assume t_dependents has a field named DepEmpID and it relates to EmpEmpID in the main form?

Duane

Sent: Thursday, May 9, 2019 8:50 PM

Subject: RE: [MS_AccessPros] Visible Property

 

 

Duane,

The below code works.   

 

Me.sf_Dependents.Visible = DCount("*", "t_dependents", "DepEmpID = " & "EmpempID") > 0

 

 

I tried the variation with the  Me.   And it went to error.

The error was

Run-Time error '3075':

Syntax error (missing operator) in query expression 'DepEmpID ='.

 

Have a good evening.

 

Bill

 

Bill Singer

 

 

Sent: Thursday, May 9, 2019 6:20 PM

Subject: Re: [MS_AccessPros] Visible Property

 

 

Bill,

The code you provided shouldn't work with the string  "EmpEmpID".

Me.sf_Dependents.Visible = DCount("*", "t_dependents", "DepEmpID = " & "EmpEmpID") > 0

I expect it is actually:

Me.sf_Dependents.Visible = DCount("*", "t_dependents", "DepEmpID = " & Me.EmpEmpID) > 0

 

Note I added the Me. to identify the source of EmpEmpID.

Duane

 

Sent: Thursday, May 9, 2019 4:53 PM

Subject: RE: [MS_AccessPros] Visible Property

 

 

Duane,

Got it. See Below,

Thanks.

Did a little reading on DCount, it has been a while.

 

 

Private Sub Form_Current()

 Me.sf_Dependents.Visible = DCount("*", "t_dependents", "DepEmpID = " & "EmpEmpID") > 0

 

 

End Sub

 

 

 

 

Bill

 

Bill Singer

 

 

Sent: Thursday, May 9, 2019 4:26 PM

Subject: Re: [MS_AccessPros] Visible Property

 

 

Bill,

I would probably not bind the control to a field since you can use DCount() to determine if there are any dependents. I would probably just use a small command button to display the subform to add the first dependent. After that, the code I provide should suffice to display the subform if needed.

 

Duane

 

Sent: Thursday, May 9, 2019 4:12 PM

Subject: RE: [MS_AccessPros] Visible Property

 

 

Duane,

Good point. Since the options box is unbound there will not be a saved field to change the visibility of the sub-form when scrolling through the records.

 

I think I am going to change this and make the options box bound to field that goes to the table so that the value is saved and the sub-form will show or not show based on the options box. 

 

I will have to mess around with it and see what works best.

 

Thanks, I am betting I will be back.

 

Bill

 

Bill Singer

 

Sent: Thursday, May 9, 2019 8:49 AM

Subject: Re: [MS_AccessPros] Visible Property

 

 

Hi Bill,

 

I would assume that you want to toggle the visibility of sf_Dependents based on if there are any dependents as you open or move through records on the main form. Try the following. Add breakpoints if it doesn't work as expected so you can step through the code.

 

The On Current event look something like:

  

  Me.sf_Dependents.Visible = DCount("*","Your Dependents Table Name","LinkingFieldName = " & LinkingFieldFromMain) >0

 

  'Assuming linking field is numeric

  If  DCount("*","Your Dependents Table Name","LinkingFieldName = " & LinkingFieldFromMain) >0

      Me.OptAnyDep = 1

     Else

       Me.OptAnyDep = 2   

  End If 

 

 

Your code in the Update of OptAnyDep would be:

 

Me.sf_Dependents.Visible = Me.OptAnyDep = 1

 

Regards.

Duane

 

Sent: Wednesday, May 8, 2019 8:57 PM

Subject: RE: [MS_AccessPros] Visible Property

 

Duane,

I should have provided that information.

 

The options box is unbound, but I can change that if you think it is a good

idea.

 

The name that comes up when I click on the frame is  OptAnyDep

The Yes button is called   OptionYes   ,   returns a 1

The No button is called  OptionNo     ,   returns a 2

 

I hope I covered what you needed and what you meant by the name of the

options group.   Do I have to keep the Frame around the buttons for it to be

a Group?

 

The subform that contains the information on the dependents is called

sf_Dependents

 

 

Bill

 

Bill Singer

Group Benefits Consultant

Phone: 763-754-8898

Fax: 763-754-8496

Toll Free: 877-902-8898

 

Sent: Wednesday, May 8, 2019 7:35 PM

Subject: Re: [MS_AccessPros] Visible Property

 

 

Hi Bill,

An options group returns a number. Is this bound to a field in the forms

record source? What is the name of the option group? What is the name of the

subform control?

 

Duane

 

Get Outlook for iOS <https://aka.ms/o0ukef>

  _____ 

<MS_Access_Professionals@yahoogroups.com

[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com

Sent: Wednesday, May 8, 2019 4:54:23 PM

Subject: [MS_AccessPros] Visible Property

 

I have Access 365 and am starting a new project so I think you will be

hearing more of me.

 

I am creating a health insurance enrollment database..

Here is the first issue.

 

If a person is single, with no dependents the form is shorter.

 

If the person has dependents I will need them to fill in the depends

information on a Sub Form.  I do not want the sub form to be visible unless

the new employee indicates they have dependent.   I am asking the question

on the form and the employee is to respond using an  Options box with the

"Yes" or "No" as their choices.  If they check no to the question then they

just continue on.  If they check Yes, I want the Sub Form to appear..   What

is the best way to change the "Visible" Property of the sub form after the

options box input. 

 

A macro?

Code?   A quick sample is always best for me.

 

Thanks,

 

Bill

 

Bill Singer

Minnesota

 




[Non-text portions of this message have been removed]


------------------------------------

Posted by: "Bill Singer" <bill.singer@at-group.net>

------------------------------------

------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:

<*> Your email settings:

    Individual Email | Traditional

<*> To change settings online go to:

    (Yahoo! ID required)

<*> To change settings via email:

<*> To unsubscribe from this group, send an email to:

<*> Your use of Yahoo Groups is subject to:

 

 



 



__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (18)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

SPONSORED LINKS
.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)