Main form to show a banner or a checkbox if continuous form contains a specific word (1 Viewer)

[Happytobealive]

Hi,
I have a main form that has info of person and a few continuous subforms that contain info connected to that person, however i want a certain message to pop up or a checkbox should be ticked (whichever is more simple) evetime one of the continuous forms contain a specific word.
What boud be the best way to do it?

 

[theDBguy]

Hi. Welcome to AWF!

I might use DCount().

 

[Happytobealive]

Hi. Welcome to AWF!

I might use DCount().

Thank you,
However i tried to use DCount(). but i think i'm new and couldn't get it to tick by itself on the main form on when i click that data field in the continuous form. maybe you can explain it better to me?

 

[theDBguy]

Thank you,
However i tried to use DCount(). but i think i'm new and couldn't get it to tick by itself on the main form on when i click that data field in the continuous form. maybe you can explain it better to me?

Can you post a sample db with test data?

 

[Happytobealive]

Can you post a sample db with test data?

i don't know how to make a test db:
here is the info i have will this work?:
Main form: DRNMzipexpand
subform name: InsuranceListsbfrm
on the subform Column name: InsType
on the subform Specific word: Medicaid
on the main form checkbox: medicaidcheckbox

 

[theDBguy]

i don't know how to make a test db:
here is the info i have will this work?:
Main form: DRNMzipexpand
subform name: InsuranceListsbfrm
on the subform Column name: InsType
on the subform Specific word: Medicaid
on the main form checkbox: medicaidcheckbox

Hmm, I guess we could give it a try. What fields are used in the Linked Master and Child fields for each subform? Also, what are the names of the tables for each subform?

 

[Happytobealive]

Hmm, I guess we could give it a try. What fields are used in the Linked Master and Child fields for each subform? Also, what are the names of the tables for each subform?

The link is: DrID
name of table: InsuranceList

 

[theDBguy]

The link is: DrID
name of table: InsuranceList

Okay, let's try the following. In the Current event of your main form, try using the following:

If DCount("*", "InsuranceList", "InsType Like '*Medicaid*' AND DrID=" & Nz(Me.DrID,0)) > 0 Then
    MsgBox "Search word found!"
End If

The purpose of the above is to just check if the logic works. As you go through the records on the main form, if the word "Medicaid" is found in the table InsuranceList and that record is related to the current record, you should get a message box. Please confirm that it is coming up or not coming up at the correct time (based on your records data) and let us know, so we can continue.

Remember, this is just a test phase...

 

[Happytobealive]

Okay, let's try the following. In the Current event of your main form, try using the following:

If DCount("*", "InsuranceList", "InsType Like '*Medicaid*' AND DrID=" & Nz(Me.DrID,0)) > 0 Then
    MsgBox "Search word found!"
End If

The purpose of the above is to just check if the logic works. As you go through the records on the main form, if the word "Medicaid" is found in the table InsuranceList and that record is related to the current record, you should get a message box. Please confirm that it is coming up or not coming up at the correct time (based on your records data) and let us know, so we can continue.

Remember, this is just a test phase...

wow, Thank you
Yes this worked like a charm...onto the next step...

 

[theDBguy]

wow, Thank you
Yes this worked like a charm...onto the next step...

Excellent! Can you understand the logic? Will you be able to use it to apply to your actual situation?

If you want to check a box, you would simply replace the MsgBox line with something like this:

Me.CheckboxName = True

PS. Oh, and probably need to reverse it also, right?

If DCount(...) > 0 Then
    Me.Checkbox = True
Else
    Me.Checkbox = False
End If

 

[Happytobealive]

Excellent! Can you understand the logic? Will you be able to use it to apply to your actual situation?

If you want to check a box, you would simply replace the MsgBox line with something like this:

Me.CheckboxName = True

PS. Oh, and probably need to reverse it also, right?

If DCount(...) > 0 Then
    Me.Checkbox = True
Else
    Me.Checkbox = False
End If

it worked great, this is what i did:

Private Sub Form_Current()
If DCount("*", "InsuranceList", "InsType Like '*Medicaid*' AND DrID=" & Nz(Me.DrID, 0)) > 0 Then
Me.medicaidcheckbox = True
Else
Me.medicaidcheckbox = False

End If
End Sub

Thank you!

 

[theDBguy]

it worked great, this is what i did:

Private Sub Form_Current()
If DCount("*", "InsuranceList", "InsType Like '*Medicaid*' AND DrID=" & Nz(Me.DrID, 0)) > 0 Then
Me.medicaidcheckbox = True
Else
Me.medicaidcheckbox = False

End If
End Sub

Thank you!

Hi. Congratulations! Good luck with your project.

 

[CJ_London]

you could simplify your code

Me.medicaidcheckbox= DCount("*", "InsuranceList", "InsType Like '*Medicaid*' AND DrID=" & Nz(Me.DrID, 0)) > 0

 

[Happytobealive]

Thank you,
I have another question maybe someone can help.

on the same form i want to add a button that will pop up on the continuous form when it has info from another table, here are the details:

Link fields: DrID
Main form: DRNMzipexpand
subform name: InsuranceListsbfrm
name of table: InsuranceNotes
When the column - InsuranceNotes - Contains any data a button (btnopeninsurancenotes) should pop up on the subform (InsuranceListsbfrm).

I hope i was clear with my Q.

 

[theDBguy]

You should still be able to use DCount() for that. Give it a shot.

 

[Happytobealive]

You should still be able to use DCount() for that. Give it a shot.

Hi,
Thank you, i'm still new at this, you think you can help with the code?

 

[theDBguy]

Hi,
Thank you, i'm still new at this, you think you can help with the code?

It would look the same as the one I gave you earlier. I'm not in front of a computer now to do it again. If you can't figure it out, I'll see what I can post later.

Sent from phone...

 

[Happytobealive]

It would look the same as the one I gave you earlier. I'm not in front of a computer now to do it again. If you can't figure it out, I'll see what I can post later.

Sent from phone...

Thank you!

 

[theDBguy]

Thank you!

I suggest you give it a shot though. It's almost bedtime here, so I won't be in front of a computer for a while. I hope to hear you managed to figure it out by the time I wake up. Night.

 

[Happytobealive]

I suggest you give it a shot though. It's almost bedtime here, so I won't be in front of a computer for a while. I hope to hear you managed to figure it out by the time I wake up. Night.

Thank you, it worked, this is what i did:

Private Sub Form_Current()

Me.medicaidcheckbox = DCount("*", "InsuranceList", "InsType Like '*Medicaid*' AND DrID=" & Nz(Me.DrID, 0)) > 0

If DCount("*", "InsuranceNotes", "InsuranceNotes <> null AND DrID=" & Nz(Me.DrID, 0)) > 0 Then
[InsuranceListsbfrm].Form![btnopeninsurancenotes].Visible = True
Else
[InsuranceListsbfrm].Form![btnopeninsurancenotes].Visible = False
End If

End Sub