Main menu

[BSJoJaMAx4]

I have a table called exiting staff data and a field called Follow up required. I want to count the number of Yes entries and display it on the main menu. Have tried

=Count([Exiting Staff Data].[Follow up required]="Yes")

and using dcount but keep getting error messages. Any thoughts?

[BSJoJaMAx4]

Also, on the same menu, I have a quick find box for previous records. It uses a combo box to select all current ID records in the database. When i find the one I want (or type ahead) I get a 2465 error code.

Private Sub Quick_Record_Click()DoCmd.OpenForm "Booking Master Data", acNormal, , "[ID]=" & Nz([ID], 0)
End Sub

[NoellaG]

Hi,

to use DCount: (assuming the field [Follow up is required] is a Yes/No field)

=DCount("[Fieldname PK of the table]","Existing Staff Data", "[Follow up required]=0")

kind regards
NG

[JamesDeckert]

Something else you can try
=-sum([Exiting Staff Data].[Follow up required])
yes in a table is equal to -1, no is 0 so if you simply add the column and reverse the sign you should have a count of all yes'
or you can do DCount but I think it could be formatted as
=DCount("*","Exiting Staff Data","[Follow up required]=true")
NoellaG's would work also if you correct the table name spelling and check for -1 (true) instead of 0 (false)

[BSJoJaMAx4]

Hi James

=DCount("*","Exiting Staff Data","[Follow up required]=true")

worked

[BSJoJaMAx4]

Hi NoellaG - are you answering the first or second issue I had?

[NoellaG]

Hi,

that was an answer to the first issue. I think the second issue has to do with the naming of the controls:
You must be sure that in the second part of the statement you refer to an existing control on the open form.

Private Sub Quick_Record_Click()
      DoCmd.OpenForm "Booking Master Data", acNormal, , "[ID]=" & Nz(Me.[ID], 0)
End Sub

This would work if the control name on the form is [ID]

kind regards
NG

[BSJoJaMAx4]

Hi NoellaG

Sorry but I got my form infom incorrect. Correct code is

Private Sub Quick_Record_Click()DoCmd.OpenForm "Fm-Booking Master Data", acNormal, , "[ID]=" & Nz([ID], 0)
End Sub

But Im still getting the same error when I select the record ID. Control Source is blank, Row source is Booking Master Data (which is where the info is stored). The label is showing up as unbound.

[NoellaG]

Hi,

put a text box on your form and bind it to the field [ID]. If you don't want it to show up on your form, set the 'Visible' property on false.
Refer to this textbox in the third part of your expression:

Private Sub Quick_Record_Click() 
DoCmd.OpenForm "Fm-Booking Master Data", acNormal, , "[ID]=" & Nz([Name of the textbox control on your form], 0)
End Sub

Tip: try not to use spaces in your object names, you can replace them with underscores.

kr
NG

[BSJoJaMAx4]

The only items I can see in control source is linked to the switchboard menu options. Is that right?

[BSJoJaMAx4]

The only items I can see in control source is linked to the switchboard menu options. Is that right?

[BSJoJaMAx4]

@James - I have had to change the record to a text field. I have changed the code to

=DCount("*","Exiting Staff Data",[Followuprequired]="Yes")

but it returns an error?

[June7]

=DCount("*","[Exiting Staff Data]","[Followuprequired]='Yes'")

[BSJoJaMAx4]

Thanks June - that got it to work.

Thanks James for your help as well

Happy Sunday all