Assign a number to a variable from a query

[Judi]

Hello, I'm using Access 2003 and Windows XP.

I have a database that is being used to keep track of and schedule quotes. I
am trying to have a message pop up for a user if they schedule more than 3
quotes to be due on the same day.

What I have so far is a query that pulls up how many quotes are due for that
day already. I want to assign this number to a variable and then put the
variable in a before_update event for the control on my form.

I feel like I have the right logic, but an improper knowledge of the
language necessary to accomplish this.

I also want the user to be able to either go back and change the date or
keep the selection and move to the next control on the form.

Here is my pig-latin version of the before_update event:

Private Sub SchdDate_BeforeUpdate(Cancel As Integer)
Dim IntX As Integer
DoCmd.OpenQuery "qryTooMany", , acReadOnly
IntX = ("CountofID" from "qryTooMany") <===I don't know how to do
this part at all

If IntX >= 3 Then
If MsgBox("You already have " & IntX _
& "quotes scheduled for that date." _
& "Do you want to reschedule this quote?", vbYesNo + vbDefaultButton2) =
vbYes Then
Cancel = True
Me!SchdDate.Undo
Me!SchdDate.SetFocus
End If
Me!DateQuoted.SetFocus 'Where Date quoted is the next control on the form
End If

End Sub
----------------------------------------------------------

I know this is off in more than one way, but like I said, I feel like I'm on
the right lines with it... I also want to close the query when it's done
feeding the integer to the if statement... maybe after the msgbox is
closed...?

If anyone can help me, I would very much appreciate it!!

Thanks, Judi <><

 

[Lord Kelvan]

try this

Private Sub SchdDate_BeforeUpdate(Cancel As Integer)
Dim IntX As Integer
dim db as database
dim rst as dao.recordset

set db = currentdb()
set rst = db.openrecordset("qryTooMany", dbOpenDynaset)
IntX = rst!countfield


If IntX >= 3 Then
If MsgBox("You already have " & IntX _
& "quotes scheduled for that date." _
& "Do you want to reschedule this quote?", vbYesNo +
vbDefaultButton2) =
vbYes Then
Cancel = True
Me!SchdDate.Undo
Me!SchdDate.SetFocus
End If
Me!DateQuoted.SetFocus 'Where Date quoted is the next control on the
form
End If


End Sub

is your query structured to be

select count(secuduledate) from secuduletable where secuduledate =
[forms]![secuduleform]![DateQuoted].[value]

so that when you run the query you only get one value

if not then you need to restructure your query to do so so the above
code will work

Regards
Kelvan

 

[Noëlla Gabriël]

Hi Judi,

try

.......

IntX = DCount("ID","qryTooMany")


.....

 

[Judi]

My query opens, but I don't get my message box, can you see a reason why that
doesn't happen? Also, how do I check to see if IntX has been given the value
assigned?

 

[Judi]

I am not sure about the query question. I turned on the totals and changed it
to count the ID's, so when I run it, there is only one line returned with the
total number of quotes scheduled for that day returned. Is this what you mean?

I will try this code.

 

[Judi]

Here is my query, I am also calling the name of the estimator and the date
scheduled as well as the ID of the record.

SELECT Main.Estimator, Main.SchdDate, Count(Main.ID) AS CountOfID
FROM Main
GROUP BY Main.Estimator, Main.SchdDate
HAVING (((Main.Estimator)=[Forms]![MainInput]![Estimator]) AND
((Main.SchdDate)=[Forms]![MainInput]![SchdDate]));

 

[John W. Vinson]

Private Sub SchdDate_BeforeUpdate(Cancel As Integer)
Dim IntX As Integer
DoCmd.OpenQuery "qryTooMany", , acReadOnly
IntX = ("CountofID" from "qryTooMany") <===I don't know how to do

Unless you have some reason to open the query datasheet (you probably DON'T),
just use

intX = DCount("*", "qryTooMany")

and don't open the query at all.

 

[John W. Vinson]

Here is my query, I am also calling the name of the estimator and the date
scheduled as well as the ID of the record.

SELECT Main.Estimator, Main.SchdDate, Count(Main.ID) AS CountOfID
FROM Main
GROUP BY Main.Estimator, Main.SchdDate
HAVING (((Main.Estimator)=[Forms]![MainInput]![Estimator]) AND
((Main.SchdDate)=[Forms]![MainInput]![SchdDate]));

Revising my initial suggestion: discard this query altogether and use

DCount("*", "[Main]", "[Estimator] = '" & [Estimator] & _
"' AND SchdDate = #" & Me!SchdDate & "#")

This assumes that the code is on the MainInput form (hence the Me!) and that
Estimator is a text field which will never contain an apostrophe (e.g.
O'Niell).

 

[Judi]

There will never be an apostrophe there, so that won't be a problem.

Is there something wrong with the rest of the sub? I am not getting my
message box when I have more than 3 quotes scheduled for an estimator.

Here is my query, I am also calling the name of the estimator and the date
scheduled as well as the ID of the record.

SELECT Main.Estimator, Main.SchdDate, Count(Main.ID) AS CountOfID
FROM Main
GROUP BY Main.Estimator, Main.SchdDate
HAVING (((Main.Estimator)=[Forms]![MainInput]![Estimator]) AND
((Main.SchdDate)=[Forms]![MainInput]![SchdDate]));

Revising my initial suggestion: discard this query altogether and use

DCount("*", "[Main]", "[Estimator] = '" & [Estimator] & _
"' AND SchdDate = #" & Me!SchdDate & "#")

This assumes that the code is on the MainInput form (hence the Me!) and that
Estimator is a text field which will never contain an apostrophe (e.g.
O'Niell).

 

[John W. Vinson]

There will never be an apostrophe there, so that won't be a problem.

Is there something wrong with the rest of the sub? I am not getting my
message box when I have more than 3 quotes scheduled for an estimator.

You'll need to step through the code and see what value is being assigned to
intX and follow the code flow. Set a breakpoint by clicking in the grey bar to
the left of the code window (e.g. to the left of the Intx= line) and run the
routine; it'll stop at that line, and you can view variable values, use F8 or
the menu to step through records, etc.

 

[Judi]

I got it working finally, I had to change a couple of brackets and such from
your code that you sent, but I would never have figured it out without your
help.

Here is what I ended up with that works.
------------------------------------------------------------------------------
Private Sub SchdDate_BeforeUpdate(Cancel As Integer)
Dim IntX As Integer
IntX = DCount("[ID]", "Main", "Estimator = '" & Me!Estimator & _
"' AND SchdDate = #" & Me!SchdDate & "#") 'where ID is the primary key
and where estimator and SchdDate are on the form that holds this sub

If IntX >= 3 Then
If MsgBox("You already have " & IntX _
& " quotes scheduled for that date." _
& "Do you want to reschedule this quote?", vbYesNo + vbDefaultButton2) =
vbYes Then
Cancel = True
Me!SchdDate.Undo
End If
End If

End Sub