DLookup Help

[matthewnsarah07]

I have a Health & Safety check database.

At the moment a manager enters and presented with frmmain, which contains subform frmsubmain which is a list of that managers staff based on a one to many relationship between field [TMID] in tbltm and tblstaff.

I have also got a query running in the background (qryppe) that list all the check entries made by that manager in tblppe.

The subform is continuous forms with a button next to each name to open the check form, using a DLookup or similar I would like to disable this button is that persons [staffname] appears within the query qryppe.

How would I write this DLookup?

Thank for your help as always

 

[CyberLynx]

In a Continuous Form.....If you disable one the Command Button for one record, you disable it for all records. The best you can do is make it non functional through the Command Button's OnClick event:

Private YouCommandButtonName_Click()
   [COLOR="DarkGreen"]'If the query has picked it up then get outta here.[/COLOR]
   If DCount("[staffname]","qryppe","[staffname]=[B][COLOR="Red"]'[/COLOR][/B]" & Forms("MainFormName").StaffMemberNameTextBoxName & "[B][COLOR="Red"]'[/COLOR][/B]") > 0 Then Exit Sub
  
   [COLOR="DarkGreen"]'Otherwise open the Form.[/COLOR]
   DoCmd.OpenForm ....................
End Sub

If you use a Text Box as a Command button then you can try and utilize Conditional Formating to make the Text Box Button appear disabled and then within the OnClick event for the Text Box button, just Exit Sub if the button is particular color.

Food for thought.

.

 

[matthewnsarah07]

That sounds like good advice

I will probably add a message box to the effect of why its diabled (sort of)

I shall give this a try

 

[matthewnsarah07]

Private Sub Command6_Click()
If DCount("[StaffName]", "qryppe", "[StaffName]=' " & Forms("MainForm").Staffname & "'") > 0 Then Exit Sub
DoCmd.OpenForm "frmppe", acNormal
End Sub

I used your code but have come to a problem, no matter which name is typed into the text box on the form the frmppe always opens?

What have I missed?

 

[CyberLynx]

The code provided goes into the command button that is in the SubForm (plase correct me if I am wrong):

Private Sub Command6_Click()
   If DCount("[StaffName]", "qryppe", "[StaffName]=' " & Forms("[COLOR="Blue"][B]frmsubmain[/B][/COLOR]").Staffname & "'") > 0 Then Exit Sub
   DoCmd.OpenForm "frmppe", acNormal
End Sub

another way:

Private Sub Command6_Click()
   If DCount("[StaffName]", "qryppe", "[StaffName]=' " & [COLOR="Blue"][B]Me.Staffname[/B][/COLOR] & "'") > 0 Then Exit Sub
   DoCmd.OpenForm "frmppe", acNormal
End Sub

.

 

[missinglinq]

As posted on your other thread on this question, the Domain has to be a table or a query that isn't a Parameter Query and the space between the Single Quote and the Double Quote needs to be eliminated.

"[StaffName]=' "

needs to be

"[StaffName]='"