Form should only submit if number of entries in the table's field is less than 3

[Twinnie]

I am a newbie to Access and require assistance on this:



I have a form (called User_Input)with 2 text boxes, 5 combo boxes and a Submit button, which is bound to a table (called Submssion table) i.e the form submits its entries into the table called Submission.

I want the submit button on the form to work based on this condition:

1. Check current submission of four fields (corresponding to entries from 4 out of the 5 combo boxes on the form) in the table and confirm if the current selection on the form already occurs in the table up to two times. If this is the case, the Message Box should pop up on the form informing user to make another selection as maximum number of entries for that particular selection is already in the Submission table.

2. The checks for the four fields will be done simultaneously as the selection will be done together on the same form.

3. However, if the entries on the Submission table is not up to 2, the entries should be saved to the table and Messagebox should display "Thank you for your Submission"

I need to use Macros for this being a web database which will be published using Sharepoint and VB is incompatible with the web database.

Thank you for your assistance

[June7]

Could try a DCount() expression in macro. It will be a long expression. In VBA (assumes the fields are text datatype):

If DCount("*", "Submission", "field1='" & combo1 & "' AND field2='" & combo2 & "' AND field3='" & combo3 & "' AND field4='" & combo4 & "'") < 2 Then
'do this
Else
'do this
End If

I don't use macros so I am not sure how the macro will reference the controls on form. Might need TempVars.

[Twinnie]

Hello June 7,

Thanks for your help however, DCount is not compatible with web applications hence I am still stuck.

Kindly assist with steps on using TempVars in this context.

Could try a DCount() expression in macro. It will be a long expression. In VBA (assumes the fields are text datatype):

If DCount("*", "Submission", "field1='" & combo1 & "' AND field2='" & combo2 & "' AND field3='" & combo3 & "' AND field4='" & combo4 & "'") < 2 Then
'do this
Else
'do this
End If

I don't use macros so I am not sure how the macro will reference the controls on form. Might need TempVars.

[June7]

If you can't use any domain aggregate functions then there may be no context in which to use TempVars. Search Access Help for more info about TempVars.

Never built a web database. Can you use SQL aggregate functions on forms (Count, Avg, Sum, etc)?