Incorporte Query into VBA or Pass VBA Value to Query

J

James Stephens

This is a follow up on a calender question that I posted yesterday under
queries, but I put it into a new thread because I am going a different route
with this (I think). As a brief description this is what I have set-up. I
have a table with 5 fields: StartDate, EndDate, Requested, Approved, Denied.
This is for leave requests. I am trying to get the calender form that I
built to show this data. The calender is built and works. If you need to
see it I can send it to you. It will put the values into labels under each
date. What I need to do is take the current table and get it into a format
of: Date, Requested, Approved, Denied, so that I can sum up for each date,
how much leave is requested and its status. I have two querys that work
together to get this. Here are the Sql of each of them:


PARAMETERS EnterDate DateTime;
SELECT IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Requested),0) AS Requested,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Approved),0) AS Approved,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Denied),0) AS Denied
FROM Calender1;

This will prompt for a date. And then return all leave where that date
falls between the start and end dates. Then I use this to get the sum.

SELECT DISTINCTROW Sum([Calender1 Query].Requested) AS Requested,
Sum([Calender1 Query].Approved) AS Approved, Sum([Calender1 Query].Denied) AS
Denied
FROM [Calender1 Query];

What I need to find out how to do is pass the value from the VBA code that
creates the calender to the query as the [EnterDate]. The calender code has
a loop that creates the calender, and in that I will have the date value for
each day, which I could then pass to this query and get the three values to
put into the Calender. But I can't figure out how to do this. I could
really use some help here. I don't know if I need to put the SQL directly
into VBA or just pass the value to the query from VBA, and I am not sure
exactly how to do either.

Here is the part of the calender code where the value needs to get assigned.

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1

For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter
Set ctl = Controls(ctlName)
ctl.Caption = ""
ctl.Tag = thisDate
thisDate = thisDate + 1
Next dayCounter

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1
For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter

Set ctl = Controls(ctlName)
ctl.Caption = <=== Here is where I need to assign the values from
the query
ctl.Tag = thisDate
End If
If thisDate = Date Then
ctl.BackColor = Red
ElseIf Month(thisDate) = Month(dateVar) Then
ctl.BackColor = White
Else
ctl.BackColor = Grey
End If

thisDate = thisDate + 1
Next dayCounter


Any help would be greatly apprecited as I know I am close on this one. If
you need any other information to assist with this let me know.

Thanks,
James Stephens
 
B

Brian

I didn't read through all the code in depth, so I'm not sure I entirely
understand, but when I need to get a variable from VBA to a query, I do it by
using an invisible text box on the form instead of a variable to hold the
value generated by VBA for use by the query. Try this (assuming you have a
form open when running this code):

1. Create an invisible text box on your form. Let's call it EnterDate.
2. In VBA, when you have arrived at the value of that date, add a line that
makes EnterDate equal to the value it needs to be: EnterDate =
<DateFromCalendarCode>
3. In the query, instead of [EnterDate], put [Forms]![FormName]![EnterDate]
4. Run the query using DoCmd.OpenQuery "<QueryName>"

This way, the value gets passed from VBA to the control, and then the query
looks at the control to find the value. If you have multiple dates, just
created multiple text boxes EnterDate1, EnterDate2, EnterDate3, etc.

James Stephens said:
This is a follow up on a calender question that I posted yesterday under
queries, but I put it into a new thread because I am going a different route
with this (I think). As a brief description this is what I have set-up. I
have a table with 5 fields: StartDate, EndDate, Requested, Approved, Denied.
This is for leave requests. I am trying to get the calender form that I
built to show this data. The calender is built and works. If you need to
see it I can send it to you. It will put the values into labels under each
date. What I need to do is take the current table and get it into a format
of: Date, Requested, Approved, Denied, so that I can sum up for each date,
how much leave is requested and its status. I have two querys that work
together to get this. Here are the Sql of each of them:


PARAMETERS EnterDate DateTime;
SELECT IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Requested),0) AS Requested,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Approved),0) AS Approved,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Denied),0) AS Denied
FROM Calender1;

This will prompt for a date. And then return all leave where that date
falls between the start and end dates. Then I use this to get the sum.

SELECT DISTINCTROW Sum([Calender1 Query].Requested) AS Requested,
Sum([Calender1 Query].Approved) AS Approved, Sum([Calender1 Query].Denied) AS
Denied
FROM [Calender1 Query];

What I need to find out how to do is pass the value from the VBA code that
creates the calender to the query as the [EnterDate]. The calender code has
a loop that creates the calender, and in that I will have the date value for
each day, which I could then pass to this query and get the three values to
put into the Calender. But I can't figure out how to do this. I could
really use some help here. I don't know if I need to put the SQL directly
into VBA or just pass the value to the query from VBA, and I am not sure
exactly how to do either.

Here is the part of the calender code where the value needs to get assigned.

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1

For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter
Set ctl = Controls(ctlName)
ctl.Caption = ""
ctl.Tag = thisDate
thisDate = thisDate + 1
Next dayCounter

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1
For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter

Set ctl = Controls(ctlName)
ctl.Caption = <=== Here is where I need to assign the values from
the query
ctl.Tag = thisDate
End If
If thisDate = Date Then
ctl.BackColor = Red
ElseIf Month(thisDate) = Month(dateVar) Then
ctl.BackColor = White
Else
ctl.BackColor = Grey
End If

thisDate = thisDate + 1
Next dayCounter


Any help would be greatly apprecited as I know I am close on this one. If
you need any other information to assist with this let me know.

Thanks,
James Stephens
Click to expand...
 
J

John Nurick

Hi James,

Often the simplest thing to do is to put an unbound textbox on your
form. Have your code put the parameter value into the textbox, and then
have the query get the value from there. If you don't want the users to
see the textbox, just set its Visible property to false.

This is a follow up on a calender question that I posted yesterday under
queries, but I put it into a new thread because I am going a different route
with this (I think). As a brief description this is what I have set-up. I
have a table with 5 fields: StartDate, EndDate, Requested, Approved, Denied.
This is for leave requests. I am trying to get the calender form that I
built to show this data. The calender is built and works. If you need to
see it I can send it to you. It will put the values into labels under each
date. What I need to do is take the current table and get it into a format
of: Date, Requested, Approved, Denied, so that I can sum up for each date,
how much leave is requested and its status. I have two querys that work
together to get this. Here are the Sql of each of them:


PARAMETERS EnterDate DateTime;
SELECT IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Requested),0) AS Requested,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Approved),0) AS Approved,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Denied),0) AS Denied
FROM Calender1;

This will prompt for a date. And then return all leave where that date
falls between the start and end dates. Then I use this to get the sum.

SELECT DISTINCTROW Sum([Calender1 Query].Requested) AS Requested,
Sum([Calender1 Query].Approved) AS Approved, Sum([Calender1 Query].Denied) AS
Denied
FROM [Calender1 Query];

What I need to find out how to do is pass the value from the VBA code that
creates the calender to the query as the [EnterDate]. The calender code has
a loop that creates the calender, and in that I will have the date value for
each day, which I could then pass to this query and get the three values to
put into the Calender. But I can't figure out how to do this. I could
really use some help here. I don't know if I need to put the SQL directly
into VBA or just pass the value to the query from VBA, and I am not sure
exactly how to do either.

Here is the part of the calender code where the value needs to get assigned.

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1

For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter
Set ctl = Controls(ctlName)
ctl.Caption = ""
ctl.Tag = thisDate
thisDate = thisDate + 1
Next dayCounter

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1
For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter

Set ctl = Controls(ctlName)
ctl.Caption = <=== Here is where I need to assign the values from
the query
ctl.Tag = thisDate
End If
If thisDate = Date Then
ctl.BackColor = Red
ElseIf Month(thisDate) = Month(dateVar) Then
ctl.BackColor = White
Else
ctl.BackColor = Grey
End If

thisDate = thisDate + 1
Next dayCounter


Any help would be greatly apprecited as I know I am close on this one. If
you need any other information to assist with this let me know.

Thanks,
James Stephens
Click to expand...
 
J

James Stephens

Ok, I have gotten is setup that way now. Now my question is how do I take a
value returned from a query and put it into the code. What is the syntax to
do this, as everything I have tried won't work. The Query Name will be
"DataForCalender Query" and the field will be "Requested".

Thanks,

Jim

John Nurick said:
Hi James,

Often the simplest thing to do is to put an unbound textbox on your
form. Have your code put the parameter value into the textbox, and then
have the query get the value from there. If you don't want the users to
see the textbox, just set its Visible property to false.

This is a follow up on a calender question that I posted yesterday under
queries, but I put it into a new thread because I am going a different route
with this (I think). As a brief description this is what I have set-up. I
have a table with 5 fields: StartDate, EndDate, Requested, Approved, Denied.
This is for leave requests. I am trying to get the calender form that I
built to show this data. The calender is built and works. If you need to
see it I can send it to you. It will put the values into labels under each
date. What I need to do is take the current table and get it into a format
of: Date, Requested, Approved, Denied, so that I can sum up for each date,
how much leave is requested and its status. I have two querys that work
together to get this. Here are the Sql of each of them:


PARAMETERS EnterDate DateTime;
SELECT IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Requested),0) AS Requested,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Approved),0) AS Approved,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Denied),0) AS Denied
FROM Calender1;

This will prompt for a date. And then return all leave where that date
falls between the start and end dates. Then I use this to get the sum.

SELECT DISTINCTROW Sum([Calender1 Query].Requested) AS Requested,
Sum([Calender1 Query].Approved) AS Approved, Sum([Calender1 Query].Denied) AS
Denied
FROM [Calender1 Query];

What I need to find out how to do is pass the value from the VBA code that
creates the calender to the query as the [EnterDate]. The calender code has
a loop that creates the calender, and in that I will have the date value for
each day, which I could then pass to this query and get the three values to
put into the Calender. But I can't figure out how to do this. I could
really use some help here. I don't know if I need to put the SQL directly
into VBA or just pass the value to the query from VBA, and I am not sure
exactly how to do either.

Here is the part of the calender code where the value needs to get assigned.

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1

For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter
Set ctl = Controls(ctlName)
ctl.Caption = ""
ctl.Tag = thisDate
thisDate = thisDate + 1
Next dayCounter

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1
For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter

Set ctl = Controls(ctlName)
ctl.Caption = <=== Here is where I need to assign the values from
the query
ctl.Tag = thisDate
End If
If thisDate = Date Then
ctl.BackColor = Red
ElseIf Month(thisDate) = Month(dateVar) Then
ctl.BackColor = White
Else
ctl.BackColor = Grey
End If

thisDate = thisDate + 1
Next dayCounter


Any help would be greatly apprecited as I know I am close on this one. If
you need any other information to assist with this let me know.

Thanks,
James Stephens
Click to expand...
Click to expand...
 
B

Brian

In your VBA, add a VarName= DLookup("[DataForCalender
Query]","[Requested]","<Criteria?>"). This runs the query automatically and
assigns the returned value of Requested to the VarName variable. If the query
returns only one record, you can omit the criteria; if it returns more than
one, you will need to enter the Criteria to filter it down to a single record
or possibly change the DLookup to DFirst if that gives an acceptable result.

James Stephens said:
Ok, I have gotten is setup that way now. Now my question is how do I take a
value returned from a query and put it into the code. What is the syntax to
do this, as everything I have tried won't work. The Query Name will be
"DataForCalender Query" and the field will be "Requested".

Thanks,

Jim

John Nurick said:
Hi James,

Often the simplest thing to do is to put an unbound textbox on your
form. Have your code put the parameter value into the textbox, and then
have the query get the value from there. If you don't want the users to
see the textbox, just set its Visible property to false.

This is a follow up on a calender question that I posted yesterday under
queries, but I put it into a new thread because I am going a different route
with this (I think). As a brief description this is what I have set-up. I
have a table with 5 fields: StartDate, EndDate, Requested, Approved, Denied.
This is for leave requests. I am trying to get the calender form that I
built to show this data. The calender is built and works. If you need to
see it I can send it to you. It will put the values into labels under each
date. What I need to do is take the current table and get it into a format
of: Date, Requested, Approved, Denied, so that I can sum up for each date,
how much leave is requested and its status. I have two querys that work
together to get this. Here are the Sql of each of them:


PARAMETERS EnterDate DateTime;
SELECT IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Requested),0) AS Requested,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Approved),0) AS Approved,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Denied),0) AS Denied
FROM Calender1;

This will prompt for a date. And then return all leave where that date
falls between the start and end dates. Then I use this to get the sum.

SELECT DISTINCTROW Sum([Calender1 Query].Requested) AS Requested,
Sum([Calender1 Query].Approved) AS Approved, Sum([Calender1 Query].Denied) AS
Denied
FROM [Calender1 Query];

What I need to find out how to do is pass the value from the VBA code that
creates the calender to the query as the [EnterDate]. The calender code has
a loop that creates the calender, and in that I will have the date value for
each day, which I could then pass to this query and get the three values to
put into the Calender. But I can't figure out how to do this. I could
really use some help here. I don't know if I need to put the SQL directly
into VBA or just pass the value to the query from VBA, and I am not sure
exactly how to do either.

Here is the part of the calender code where the value needs to get assigned.

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1

For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter
Set ctl = Controls(ctlName)
ctl.Caption = ""
ctl.Tag = thisDate
thisDate = thisDate + 1
Next dayCounter

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1
For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter

Set ctl = Controls(ctlName)
ctl.Caption = <=== Here is where I need to assign the values from
the query
ctl.Tag = thisDate
End If
If thisDate = Date Then
ctl.BackColor = Red
ElseIf Month(thisDate) = Month(dateVar) Then
ctl.BackColor = White
Else
ctl.BackColor = Grey
End If

thisDate = thisDate + 1
Next dayCounter


Any help would be greatly apprecited as I know I am close on this one. If
you need any other information to assist with this let me know.

Thanks,
James Stephens
Click to expand...
Click to expand...
Click to expand...
 
J

James Stephens

Thanks, that was exactly what I need. My calender works now. It shows the
condensed points in the calender form, and the days are clickable to bring up
more detailed info.

Thanks again, this is great.

Jim

Brian said:
In your VBA, add a VarName= DLookup("[DataForCalender
Query]","[Requested]","<Criteria?>"). This runs the query automatically and
assigns the returned value of Requested to the VarName variable. If the query
returns only one record, you can omit the criteria; if it returns more than
one, you will need to enter the Criteria to filter it down to a single record
or possibly change the DLookup to DFirst if that gives an acceptable result.

James Stephens said:
Ok, I have gotten is setup that way now. Now my question is how do I take a
value returned from a query and put it into the code. What is the syntax to
do this, as everything I have tried won't work. The Query Name will be
"DataForCalender Query" and the field will be "Requested".

Thanks,

Jim

John Nurick said:
Hi James,

Often the simplest thing to do is to put an unbound textbox on your
form. Have your code put the parameter value into the textbox, and then
have the query get the value from there. If you don't want the users to
see the textbox, just set its Visible property to false.

On Thu, 11 Nov 2004 07:34:05 -0800, "James Stephens"

This is a follow up on a calender question that I posted yesterday under
queries, but I put it into a new thread because I am going a different route
with this (I think). As a brief description this is what I have set-up. I
have a table with 5 fields: StartDate, EndDate, Requested, Approved, Denied.
This is for leave requests. I am trying to get the calender form that I
built to show this data. The calender is built and works. If you need to
see it I can send it to you. It will put the values into labels under each
date. What I need to do is take the current table and get it into a format
of: Date, Requested, Approved, Denied, so that I can sum up for each date,
how much leave is requested and its status. I have two querys that work
together to get this. Here are the Sql of each of them:


PARAMETERS EnterDate DateTime;
SELECT IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Requested),0) AS Requested,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Approved),0) AS Approved,
IIf(Calender1!StartDateCode<=[EnterDate] And
Calender1!EndDateCode>=[EnterDate],Abs(Calender1!Denied),0) AS Denied
FROM Calender1;

This will prompt for a date. And then return all leave where that date
falls between the start and end dates. Then I use this to get the sum.

SELECT DISTINCTROW Sum([Calender1 Query].Requested) AS Requested,
Sum([Calender1 Query].Approved) AS Approved, Sum([Calender1 Query].Denied) AS
Denied
FROM [Calender1 Query];

What I need to find out how to do is pass the value from the VBA code that
creates the calender to the query as the [EnterDate]. The calender code has
a loop that creates the calender, and in that I will have the date value for
each day, which I could then pass to this query and get the three values to
put into the Calender. But I can't figure out how to do this. I could
really use some help here. I don't know if I need to put the SQL directly
into VBA or just pass the value to the query from VBA, and I am not sure
exactly how to do either.

Here is the part of the calender code where the value needs to get assigned.

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1

For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter
Set ctl = Controls(ctlName)
ctl.Caption = ""
ctl.Tag = thisDate
thisDate = thisDate + 1
Next dayCounter

firstDate = dateVar - Day(dateVar) + 1
thisDate = firstDate - Weekday(firstDate) + 1
For dayCounter = 1 To 42
ctlName = "CalText" & dayCounter

Set ctl = Controls(ctlName)
ctl.Caption = <=== Here is where I need to assign the values from
the query
ctl.Tag = thisDate
End If
If thisDate = Date Then
ctl.BackColor = Red
ElseIf Month(thisDate) = Month(dateVar) Then
ctl.BackColor = White
Else
ctl.BackColor = Grey
End If

thisDate = thisDate + 1
Next dayCounter


Any help would be greatly apprecited as I know I am close on this one. If
you need any other information to assist with this let me know.

Thanks,
James Stephens
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Statement error 2
Change a web query 7
Query Question 0
Query Between 2 numbers ("dates") 1
Another Query Modification Question 6
How to Change criteria on a saved query using VBA 0
Pass parameter to query from code 5
Line-Break VBA to pass to a Query 6

Top