Where to put count query?

[Guest]

I have a form that has a command button to add a complaint. You can only have
3 complaints. I have a query that counts the complaints. I would like to
display a message in the event that the user tries to add a 4th complaint.
How would I do this using this count query? The form the command button is on
is based on the complaint table.

Thanks,
RandyM

 

[Guest]

The count should be in the Click event code of the command button to add a
complaint. You don't really want to use a query for this, you need to use
the DCount function.

If DCount("*", "ComplaintTable","[Complainer] = '" & Me.txtWhiner & "'" >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

You will need to have information on your form that will be used to
determine who is complaing to use in your criteria for your DCount function.
In the example above, [Complainer] is who has filed the complaints and
Me.txtWhiner is a text box on the form the tells who is wanting to enter a
complaint. You may need to change the field and control names, but the
message in the message box is required

 

[Guest]

Douglas,
Thanks for the reply. I'm having a bit of a problem with getting it to
work. I'm relatively new to Access and somethings that are simple aren't
simple to me, yet. I've tried various ways of entering the DCount and I keep
getting compiler errors. Also, what is the purpose of the single quotes. And,
I think there's a right pararenthis that goes somewhere, but I've put it
after the 3 and before the > and it doesn't like any of these. I'll give you
the names of the fields I'm using and maybe that will help.

Domain - tblComplaints
Criteria - the field in the table to count is AnimalID_ComplaintTbl and the
field on the form to match is AnimalID.

This is a database for Animal licenses for a city and that's what the
complaints are. 3 complaints on and animal and the shoe will fall, so to
speak. Although, I do like your message.
I really do apprecitate yours and everyone else's time and patience yall
have with people like me. Sometimes I feel so stupid asking some of these
simple questions, but I can't figure some of them out. Some I manage to
finally figure out, but others..I've learned a great deal from yours and
others answers to mine and other peoples questions and am creating a booklet
that I can refer back to. Anyhow, just wanted to let you know I appreciate
you.

Thanks,
RandyM

The count should be in the Click event code of the command button to add a
complaint. You don't really want to use a query for this, you need to use
the DCount function.

If DCount("*", "ComplaintTable","[Complainer] = '" & Me.txtWhiner & "'" >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

You will need to have information on your form that will be used to
determine who is complaing to use in your criteria for your DCount function.
In the example above, [Complainer] is who has filed the complaints and
Me.txtWhiner is a text box on the form the tells who is wanting to enter a
complaint. You may need to change the field and control names, but the
message in the message box is required

I have a form that has a command button to add a complaint. You can only have
3 complaints. I have a query that counts the complaints. I would like to
display a message in the event that the user tries to add a 4th complaint.
How would I do this using this count query? The form the command button is on
is based on the complaint table.

Thanks,
RandyM

 

[Guest]

Sorry about the syntax error with the right paren. This should correct it:
If DCount("*", "tblComplaints","[AnimalID_ComplaintTbl] = '" &
Me.AnimalID & "'") >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

The single qoutes are needed if the AnimalID_ComplaintTbl is a text field.
If it is a numeric field, they should be left out - like this:

If DCount("*", "tblComplaints","[AnimalID_ComplaintTbl] = " &
Me.AnimalID) >= 3

And, one other thing - I am not Douglas

He lives in Canada and drinks beer
I live in Texas and drink Tequilla

Douglas,
Thanks for the reply. I'm having a bit of a problem with getting it to
work. I'm relatively new to Access and somethings that are simple aren't
simple to me, yet. I've tried various ways of entering the DCount and I keep
getting compiler errors. Also, what is the purpose of the single quotes. And,
I think there's a right pararenthis that goes somewhere, but I've put it
after the 3 and before the > and it doesn't like any of these. I'll give you
the names of the fields I'm using and maybe that will help.

Domain - tblComplaints
Criteria - the field in the table to count is AnimalID_ComplaintTbl and the
field on the form to match is AnimalID.

This is a database for Animal licenses for a city and that's what the
complaints are. 3 complaints on and animal and the shoe will fall, so to
speak. Although, I do like your message.
I really do apprecitate yours and everyone else's time and patience yall
have with people like me. Sometimes I feel so stupid asking some of these
simple questions, but I can't figure some of them out. Some I manage to
finally figure out, but others..I've learned a great deal from yours and
others answers to mine and other peoples questions and am creating a booklet
that I can refer back to. Anyhow, just wanted to let you know I appreciate
you.

Thanks,
RandyM

The count should be in the Click event code of the command button to add a
complaint. You don't really want to use a query for this, you need to use
the DCount function.

If DCount("*", "ComplaintTable","[Complainer] = '" & Me.txtWhiner & "'" >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

You will need to have information on your form that will be used to
determine who is complaing to use in your criteria for your DCount function.
In the example above, [Complainer] is who has filed the complaints and
Me.txtWhiner is a text box on the form the tells who is wanting to enter a
complaint. You may need to change the field and control names, but the
message in the message box is required

I have a form that has a command button to add a complaint. You can only have
3 complaints. I have a query that counts the complaints. I would like to
display a message in the event that the user tries to add a 4th complaint.
How would I do this using this count query? The form the command button is on
is based on the complaint table.

Thanks,
RandyM

 

[Guest]

My deepest apologies Klatuu. I had been reading a question Douglas had
answered and I guess it was still in my head. Probably, something to do with
those senior moments I have quite often. Jose Cuervo Gold, I presume, or I
guess living in Texas you have access (there's that word) to the 'real
deal'. I'll try this out and thanks again for repyling. On once again my
humblest pardons for my slip-up. I stay so dazed and confused wandering thru
this vast Access desert, it's a wonder I know who I am. And, on top of that,
I'm trying to learn an extremely nice jazz arrangement of "Here, There, and
Everywhere" to play for a wedding reception and between all those notes and
these bits, bites, and nibbles...oh well. Anyhoo, thanks again Klatuu.

Peace,
RandyM

Sorry about the syntax error with the right paren. This should correct it:
If DCount("*", "tblComplaints","[AnimalID_ComplaintTbl] = '" &
Me.AnimalID & "'") >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

The single qoutes are needed if the AnimalID_ComplaintTbl is a text field.
If it is a numeric field, they should be left out - like this:

If DCount("*", "tblComplaints","[AnimalID_ComplaintTbl] = " &
Me.AnimalID) >= 3

And, one other thing - I am not Douglas

He lives in Canada and drinks beer
I live in Texas and drink Tequilla

Douglas,
Thanks for the reply. I'm having a bit of a problem with getting it to
work. I'm relatively new to Access and somethings that are simple aren't
simple to me, yet. I've tried various ways of entering the DCount and I keep
getting compiler errors. Also, what is the purpose of the single quotes. And,
I think there's a right pararenthis that goes somewhere, but I've put it
after the 3 and before the > and it doesn't like any of these. I'll give you
the names of the fields I'm using and maybe that will help.

Domain - tblComplaints
Criteria - the field in the table to count is AnimalID_ComplaintTbl and the
field on the form to match is AnimalID.

This is a database for Animal licenses for a city and that's what the
complaints are. 3 complaints on and animal and the shoe will fall, so to
speak. Although, I do like your message.
I really do apprecitate yours and everyone else's time and patience yall
have with people like me. Sometimes I feel so stupid asking some of these
simple questions, but I can't figure some of them out. Some I manage to
finally figure out, but others..I've learned a great deal from yours and
others answers to mine and other peoples questions and am creating a booklet
that I can refer back to. Anyhow, just wanted to let you know I appreciate
you.

Thanks,
RandyM

The count should be in the Click event code of the command button to add a
complaint. You don't really want to use a query for this, you need to use
the DCount function.

If DCount("*", "ComplaintTable","[Complainer] = '" & Me.txtWhiner & "'" >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

You will need to have information on your form that will be used to
determine who is complaing to use in your criteria for your DCount function.
In the example above, [Complainer] is who has filed the complaints and
Me.txtWhiner is a text box on the form the tells who is wanting to enter a
complaint. You may need to change the field and control names, but the
message in the message box is required
:

I have a form that has a command button to add a complaint. You can only have
3 complaints. I have a query that counts the complaints. I would like to
display a message in the event that the user tries to add a 4th complaint.
How would I do this using this count query? The form the command button is on
is based on the complaint table.

Thanks,
RandyM

 

[Guest]

Not a problem. Actually, I consider it a complement being confused with
Douglas Steel.

My deepest apologies Klatuu. I had been reading a question Douglas had
answered and I guess it was still in my head. Probably, something to do with
those senior moments I have quite often. Jose Cuervo Gold, I presume, or I
guess living in Texas you have access (there's that word) to the 'real
deal'. I'll try this out and thanks again for repyling. On once again my
humblest pardons for my slip-up. I stay so dazed and confused wandering thru
this vast Access desert, it's a wonder I know who I am. And, on top of that,
I'm trying to learn an extremely nice jazz arrangement of "Here, There, and
Everywhere" to play for a wedding reception and between all those notes and
these bits, bites, and nibbles...oh well. Anyhoo, thanks again Klatuu.

Peace,
RandyM

Sorry about the syntax error with the right paren. This should correct it:
If DCount("*", "tblComplaints","[AnimalID_ComplaintTbl] = '" &
Me.AnimalID & "'") >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

The single qoutes are needed if the AnimalID_ComplaintTbl is a text field.
If it is a numeric field, they should be left out - like this:

If DCount("*", "tblComplaints","[AnimalID_ComplaintTbl] = " &
Me.AnimalID) >= 3

And, one other thing - I am not Douglas

He lives in Canada and drinks beer
I live in Texas and drink Tequilla

Douglas,
Thanks for the reply. I'm having a bit of a problem with getting it to
work. I'm relatively new to Access and somethings that are simple aren't
simple to me, yet. I've tried various ways of entering the DCount and I keep
getting compiler errors. Also, what is the purpose of the single quotes. And,
I think there's a right pararenthis that goes somewhere, but I've put it
after the 3 and before the > and it doesn't like any of these. I'll give you
the names of the fields I'm using and maybe that will help.

Domain - tblComplaints
Criteria - the field in the table to count is AnimalID_ComplaintTbl and the
field on the form to match is AnimalID.

This is a database for Animal licenses for a city and that's what the
complaints are. 3 complaints on and animal and the shoe will fall, so to
speak. Although, I do like your message.
I really do apprecitate yours and everyone else's time and patience yall
have with people like me. Sometimes I feel so stupid asking some of these
simple questions, but I can't figure some of them out. Some I manage to
finally figure out, but others..I've learned a great deal from yours and
others answers to mine and other peoples questions and am creating a booklet
that I can refer back to. Anyhow, just wanted to let you know I appreciate
you.

Thanks,
RandyM
:

The count should be in the Click event code of the command button to add a
complaint. You don't really want to use a query for this, you need to use
the DCount function.

If DCount("*", "ComplaintTable","[Complainer] = '" & Me.txtWhiner & "'" >= 3
Then
MsgBox "Quit Your Bitching, You have already complained way to much"
Else
'Do whatever to add a new complaint here
End If

You will need to have information on your form that will be used to
determine who is complaing to use in your criteria for your DCount function.
In the example above, [Complainer] is who has filed the complaints and
Me.txtWhiner is a text box on the form the tells who is wanting to enter a
complaint. You may need to change the field and control names, but the
message in the message box is required
:

I have a form that has a command button to add a complaint. You can only have
3 complaints. I have a query that counts the complaints. I would like to
display a message in the event that the user tries to add a 4th complaint.
How would I do this using this count query? The form the command button is on
is based on the complaint table.

Thanks,
RandyM