Show field in one form based on value on another form (1 Viewer)

[rhonda6373]

I am a beginner, so keep that in mind . I am actually a Business Analyst and not a developer.

I have an issue with a database that has two forms: frmBankruptcy and frmClaims. I think frmClaims is a subform. The forms are joined on CaseID.

There is one Bankruptcy case to many claims. There is a combobox on the claims form named cbo_CL_DEPT. The requirement is to show a label on frmBankruptcy only if there is a value of "THDA dba Voluntary Mortgage Loan Servicing" in cbo_CL_DEPT on frmClaims.

How can I achieve this? Thank you!!!!

 

[theDBguy]

Hi. You should be able to use something like:

 If Me.cbo_CL_DEPT="[FONT=Times New Roman]THDA dba Voluntary Mortgage Loan Servicing" Then[/FONT]
[FONT=Times New Roman]    Me.LabelName.Visible = True[/FONT]
[FONT=Times New Roman]Else[/FONT]
[FONT=Times New Roman]    Me.LabelName.Visible = False[/FONT]
[FONT=Times New Roman]End If

It's just a matter of finding the correct event or events to put it in. For starters, you could try using the Form's Current event.
[/FONT]

 

[Micron]

Maybe. You should first open said form in design view, select (click on) this combo and make sure the property sheet is visible. If not, look for its icon on the ribbon design tab. When sheet is visible and combo is selected, look at format tab and see if the column count is more than one. If it is, then you need to know which column is bound (to the table or query it's based on) versus which one is displayed after selection. For that, check the property sheet data tab. If they aren't the same, you probably don't want to use the displayed value, but rather the bound value. To figure that out, the format tab column widths will provide the clues. If you see 0; .5; .5 for example, the first column is hidden. If the bound column is 1 but the value you see is in column 2, then the suggestion probably won't work.

One step at a time if some of that doesn't make sense. You should at least be able to check the column count and bound column properties.


Edit: or am I off my rocker and the displayed data of a combo is the same as its value?? I should have checked first. Some things just don't stay in my head where I put them long ago.

 

[rhonda6373]

Thanks theDBguy! I was going to try the onCurrent and AfterUpdate.

My issue is they are two different forms. I am trying to evaluate the value(s) (Note: may be multiple) on one form to determine whether to make the label visible on the other form.

Form 1: frmBankruptcy. This is the form I need the label visible.
Form 2: frmClaims. This is the form with the combobox. There can be many departments to one bankruptcy case.

I think perhaps I need to query the table instead and compare to that since the claims form will not always be open? How would I do that and store the results of the query to compare with the value I am looking for? I could be totally off base though.

Let me know if I am missing something or you need more information.

Thanks, Micron. I will try both ways when I figure how to do this.

 

[theDBguy]

Thanks theDBguy! I was going to try the onCurrent and AfterUpdate.

My issue is they are two different forms. I am trying to evaluate the value(s) (Note: may be multiple) on one form to determine whether to make the label visible on the other form.

Form 1: frmBankruptcy. This is the form I need the label visible.
Form 2: frmClaims. This is the form with the combobox. There can be many departments to one bankruptcy case.

I think perhaps I need to query the table instead and compare to that since the claims form will not always be open? How would I do that and store the results of the query to compare with the value I am looking for? I could be totally off base though.

Let me know if I am missing something or you need more information.

Thanks, Micron. I will try both ways when I figure how to do this.

Hi. It might help if you post some images too. Okay, I think when you say different form, you're probably referring to a subform. So, one form with another form embedded within it, correct? Yes, you could query the table, but if it's a subform, then the subform will probably be filtered with records related to the main form. Still, everything is possible, so if we knew this filter, we can apply it to the table to check if any record matches the criteria.

 

[rhonda6373]

I am attaching screen shots.

 

[theDBguy]

I am attaching screen shots.

Okay, thanks. That helps a little bit. What is the record source for frmClaims?

 

[rhonda6373]

The record source for frmClaims is a table named Claims. Thanks.

 

[theDBguy]

The record source for frmClaims is a table named Claims. Thanks.

Okay, thanks. Could you please try this?

If DCount("*","Claims","CL_DEPT='THDA dba Voluntary Mortgage Loan Servicing' AND CaseID=" & Me.txtCaseID)>0 Then
    Me.LabelName.Visible = True
Else
    Me.LabelName.Visible = False
 End If

Edit: Forgot the DEPT part. Make sure to use the correct field names (CL_DEPT and CaseID) from your Claims table.

 

[Micron]

Maybe I'm still off-base, but you cannot refer to a main form from the child form using Me? Isn't the combo on the subform and the label on the main?
Why not just

If Me.cbo_CL_DEPT="THDA dba Voluntary Mortgage Loan Servicing" Then
  Me.PARENT.LabelName.Visible = True
Else
  Me.PARENT.LabelName.Visible = False
End If

or if you don't like "Parent" the full forms reference to the main form: Forms!frmMainFormName.ControlName.Visible =

 

[rhonda6373]

Woo hoo! This is awesome -- it does work. I put it in the OnCurrent event of frmBankruptcy.

I do have one more question.

I have frmBankruptcy open. I click the button to go to frmClaims. I change the value of CL_DEPT to THDA dba Voluntary Mortgage Loan Servicing on one of the claims. I exit the form to go back to the frmBankruptcy and the label is not visible unless I press F5 to refresh the screen. I think the users will be perfectly fine with this; I just wondered if there was a way to refresh frmBankruptcy in code without the user having to do this.

I put the following in the AfterUpdate event of frmClaims on the CL_DEPT field and tried Me.Requery in the OnCurrent event of frmBankruptcy, but that did not work.

If DCount("*", "Claims", "CL_DEPT='THDA dba Voluntary Mortgage Loan Servicing' AND CaseID=" & Me.txtCaseID) > 0 Then
    Forms![frmBankruptcy].lblTHDA.Visible = True
Else
    Forms![frmBankruptcy].lblTHDA.Visible = False
End If

Thank you SOOO much! You have made my day much easier.

 

[rhonda6373]

Thanks, Micron. I had seen DBguy's post first and tried his method which worked. I am going to try your method as well to see if that works. I will let you know.

 

[theDBguy]

Woo hoo! This is awesome -- it does work. I put it in the OnCurrent event of frmBankruptcy.

I do have one more question.

I have frmBankruptcy open. I click the button to go to frmClaims. I change the value of CL_DEPT to THDA dba Voluntary Mortgage Loan Servicing on one of the claims. I exit the form to go back to the frmBankruptcy and the label is not visible unless I press F5 to refresh the screen. I think the users will be perfectly fine with this; I just wondered if there was a way to refresh frmBankruptcy in code without the user having to do this.

I put the following in the AfterUpdate event of frmClaims on the CL_DEPT field and tried Me.Requery in the OnCurrent event of frmBankruptcy, but that did not work.

If DCount("*", "Claims", "CL_DEPT='THDA dba Voluntary Mortgage Loan Servicing' AND CaseID=" & Me.txtCaseID) > 0 Then
    Forms![frmBankruptcy].lblTHDA.Visible = True
Else
    Forms![frmBankruptcy].lblTHDA.Visible = False
End If

Thank you SOOO much! You have made my day much easier.

Try also adding the same code I gave you in the Activate event of the main form.

 

[rhonda6373]

DBGuy, that worked perfectly.

Micron, I do appreciate your suggestion! However, I was thinking that since the claims form is not always open, there would not be a way to trigger it. I think it is a subform, but is not embedded in the main form. You have to click a button to open it.

 

[theDBguy]

DBGuy, that worked perfectly.

Micron, I do appreciate your suggestion! However, I was thinking that since the claims form is not always open, there would not be a way to trigger it. I think it is a subform, but is not embedded in the main form. You have to click a button to open it.

Hi. Congratulations! Glad to hear you got it to work. Micron and I were happy to assist. Good luck with your project.