Setting a limitation of record entry from a form (data entry)

sashi126

Registered User.
Local time
Yesterday, 17:43
Joined
Oct 13, 2015
Messages
16
Hi all,

I am a newbie to access. I need an assistance on how to set a limitation of data entry into a database.

For Example

I have created a database, a form for data entry (with 2 button Save and Exit) and a switchboard.

Let me explain on how it works. At the startup of the file when double click, it will automatically goes to switchboard (I have successfully created it and created an option linked it to the form for data entry.

In this form there are few dropdowns and 2 buttons.

So i am having difficulty to run the program in such way as below:

Scenario:
Once i filled up the form, I will click the button Save and the program run to check number of record in the database and prompt msgbox saying I am not able to enter more data (Database limited to 3 records only)

This is the part I am not sure how to do. Would appreciate anyone could help me on this the simplest and understandable way as I am still new for access.
 
WHY??? Why only 3? Makes no sense.

Well, if you must, At the SAVE , count the records via,
X= DCOUNT("*"',"table")
If X= 3 then MsgBox "you are at the limit."
 
WHY??? Why only 3? Makes no sense.

Well, if you must, At the SAVE , count the records via,
X= DCOUNT("*"',"table")
If X= 3 then MsgBox "you are at the limit."

I am sorry...i do not understand it...
Pls explain to me in detail step by step
 
can you upload your db.
 
can you upload your db.

I just created Table with entities ID (Autonumber),Q1,Q2,Q3...Q10
I am actually doing a survey form which user can rate 1-5 from Very Poor till Excellent.

And i created few tables for each department and forms for each department. Each forms connected to a switchboard as in to chose which the user belong to

Do you have a clear picture?
Basically I am creating a survey database.
 
copy and paste the code below to your form's code behind (VBE).
make sure to rename the Command4 with the name of your command save button.

Private wrkSpace As DAO.Workspace
Private intNumRecord As Integer

Private Sub Command4_Click()

intNumRecord = intNumRecord + 1

If intNumRecord < 3 Then
wrkSpace.CommitTrans

Else
intNumRecord = intNumRecord - 1
MsgBox "You are only allowed to add 3 records."
Me.Undo
wrkSpace.Rollback
End If
wrkSpace.BeginTrans

End Sub

Private Sub Form_Load()

Set wrkSpace = DBEngine.Workspaces(0)
wrkSpace.BeginTrans
intNumRecord = 0
End Sub


Private Sub Form_Unload(Cancel As Integer)
wrkSpace.Close
Set wrkSpace = Nothing
End Sub
 
copy and paste the code below to your form's code behind (VBE).
make sure to rename the Command4 with the name of your command save button.

Private wrkSpace As DAO.Workspace
Private intNumRecord As Integer

Private Sub Command4_Click()

intNumRecord = intNumRecord + 1

If intNumRecord < 3 Then
wrkSpace.CommitTrans

Else
intNumRecord = intNumRecord - 1
MsgBox "You are only allowed to add 3 records."
Me.Undo
wrkSpace.Rollback
End If
wrkSpace.BeginTrans

End Sub

Private Sub Form_Load()

Set wrkSpace = DBEngine.Workspaces(0)
wrkSpace.BeginTrans
intNumRecord = 0
End Sub


Private Sub Form_Unload(Cancel As Integer)
wrkSpace.Close
Set wrkSpace = Nothing
End Sub

Hi Arnelgp,

I tried with your code and it is not working. Maybe i better upload the database and give you see. Please help me on this. TQ so much
 

Attachments

so you have 3 button on your switchboard, user must fill each form on dep1 once, on dep2 form once, and on dept3 form once. am i correct?

how would you like to reset the counter for 3, when another user is invited for survey? how would you like your "Submit Survey" to be coded? is it mandatory to answer all dept on your switchboard? is it mandatory to answer all questions in each form?
 
Last edited:
so you have 3 button on your switchboard, user must fill each form on dep1 once, on dep2 form once, and on dept3 form once. am i correct?

A user only select the dept he/she working for. Once done the survey the user nid to submit it and click exit...and the application will be closed.

The reason why i am setting a limit for each dept because of the number of ppl working for each dept. So the function of clicking the submit button to check number of record in table...if exceed, it will prompt a msg saying it is exceeded.
 
ok got it, you can have your db tommorow, this i promise. its really late now here.
 
Okay sure...take a good rest and sleep buddy. I really appreciate your help. Will be waiting for your reply and the DB... God surely will bless you. Thanks again
 
i haven't got a solution on a bound form, but please before anything, give this one a try. it is unbound form. view the code behind. i also make another table tblRespondentLimit. MaxNumberOfRespondent field is the allowable number of respondents on each department. you may elect how many in each department. the other field, RespondentCount, counts the number of respondent already taken the survey.

you may want to reset the ResspondentCount to 0 before testing, and deleting some of the records in DB1 to DB3.

in any case, please post back.
 

Attachments

i haven't got a solution on a bound form, but please before anything, give this one a try. it is unbound form. view the code behind. i also make another table tblRespondentLimit. MaxNumberOfRespondent field is the allowable number of respondents on each department. you may elect how many in each department. the other field, RespondentCount, counts the number of respondent already taken the survey.

you may want to reset the ResspondentCount to 0 before testing, and deleting some of the records in DB1 to DB3.

in any case, please post back.

Yes this would be great. This is actually i wanted. Could you help me to bound it?
 
have you actually tried the attached db, although not bound but on the click of your "Submit survey" button the record is saved to its corresponding table (db1 for dept1, db2 for dept2, ...)
 
have you actually tried the attached db, although not bound but on the click of your "Submit survey" button the record is saved to its corresponding table (db1 for dept1, db2 for dept2, ...)

When some question not answered, the msgbox will prompt asking u wan to continue to submit or not...when I click yes, it does go to DB but when i click No and continue complete the form, then i click submit, it doesnt go into the DB and the respondantcount does not increase.

Please help me on this. TQ
 
Last edited:
When some question not answered, the msgbox will prompt asking u wan to continue to submit or not...when I click yes, it does go to DB but when i click No and continue complete the form, then i click submit, it doesnt go into the DB and the respondantcount does not increase.

Please help me on this.
 
sorry about that, you may try again.
 

Attachments

sorry about that, you may try again.

Hi Arnelgp,

Thanks a lot for the help...it works fine now
Just a small tweek needed. Previously i did combo list value list for the rating in form dropdown for each questions. Now i dont see the numeric number 1-5 in front.

I tried myself but it doesn't work as this is unbound. Hope you could help me on this

You may refer to my previous one.
 
how do you want your controls to be saved in table, numeric or string? your field is string so i supposed you want it to be saved as string. anyway working on it.
 

Users who are viewing this thread

Back
Top Bottom