query info from the form

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a query that shows records by dates on my form.
This query prompts for the date when I open the form. I
want the query to obtain the date from the form as it
opens and not prompt me. The form opens and todays date
is obtained in "on Load". I have tried to use this date
name in the query but it does not work...still prompts
me...what am I missing?
 
Jeff

Are you saying that you want today's date to be automatically inserted in
the parameter query you use as a datasource for a form? If so, why wrassle
with the form, which, by the way, hasn't finished opening when you tell it
to use itself to populate the parameter in the query that it needs to open
the rest of the way?!

Instead, couldn't you just insert the function that delivers today's date AS
the criterion in your query? (see Date() function -- unless you need the
exact date AND time, do not use the NOW() function).

Good luck

Jeff Boyce
<Access MVP>

By the way, why did you also post this in the tablesdbdesign 'group?
 
Do you mean the Query is the RecordSource of the Form you
are trying to open and the Date Control is on this Form as
well?

In this case, it is not going to work the way you set up
as the Query is processed before the Open Event which
happens before the Load Event and the Controls on the Form
don't exist yet. Access / JET cannot resolve your
reference to the Control and therefore it asks you for the
value.

With your criterion, simply set the criterion in the Query
to:

Date()

in your Query and the Expression Service will pass Date()
to VBA for calculation before passing the Query to JET for
processing.

HTH
Van T. Dinh
MVP (Access)
 
-----Original Message-----
Do you mean the Query is the RecordSource of the Form you
are trying to open and the Date Control is on this Form as
well?

In this case, it is not going to work the way you set up
as the Query is processed before the Open Event which
happens before the Load Event and the Controls on the Form
don't exist yet. Access / JET cannot resolve your
reference to the Control and therefore it asks you for the
value.

With your criterion, simply set the criterion in the Query
to:

Date()

in your Query and the Expression Service will pass Date ()
to VBA for calculation before passing the Query to JET for
processing.

HTH
Van T. Dinh
MVP (Access)



Van, Jeff, thanks for the replies,
OK...What I have is a query that uses StartDate and End
Click to expand...
Date to determine records. I want my form to have text
boxes with StartDate and EndDate and "upon update" update
the query. On form load I want the current date to
display in both StartDate and EndDate. Did not know the
query is executed first.
 
If you want to use the same Form, start the Form unbound
with the [StartDate] and [EndDate] Control with a
CommandButton "Bind Form" to check for valid entries for
[StartDate] and [EndDate] and then bind the Form /
Controls to the Query / Fields in the Query.

Personally, I prefer to use a "GetParameters" Form with a
CommandButton to open the main Form with the parametrised
Query as the RowSource.

HTH
Van T. Dinh
MVP (Access)
 
-----Original Message-----
If you want to use the same Form, start the Form unbound
with the [StartDate] and [EndDate] Control with a
CommandButton "Bind Form" to check for valid entries for
[StartDate] and [EndDate] and then bind the Form /
Controls to the Query / Fields in the Query.

Personally, I prefer to use a "GetParameters" Form with a
CommandButton to open the main Form with the parametrised
Query as the RowSource.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Van, Jeff, thanks for the replies,
OK...What I have is a query that uses StartDate and End
Date to determine records. I want my form to have text
boxes with StartDate and EndDate and "upon update" update
the query. On form load I want the current date to
display in both StartDate and EndDate. Did not know the
query is executed first.
.
Click to expand...
.
humm... My query shows in the date column "Criteria": >=
Click to expand...
[startdate] and <=[EndDate]. Now, this query prompts
these dates, although I want the form to have the two
fields StartDate and EndDate, and a subform showing the
query. This seems like a simple outcome.
 
Now... this is different. You haven't mentioned about the Subform until the
last post. You can create a CommandButton on the MainForm to set the
RecordSource of the Subform after the [StartDate] and [EndDate] have been
filled in.

--
HTH
Van T. Dinh
MVP (Access)



jeff said:
humm... My query shows in the date column "Criteria": >=
Click to expand...
[startdate] and <=[EndDate]. Now, this query prompts
these dates, although I want the form to have the two
fields StartDate and EndDate, and a subform showing the
query. This seems like a simple outcome.
Click to expand...
 
-----Original Message-----
Now... this is different. You haven't mentioned about the Subform until the
last post. You can create a CommandButton on the MainForm to set the
RecordSource of the Subform after the [StartDate] and [EndDate] have been
filled in.

--
HTH
Van T. Dinh
MVP (Access)



jeff said:
humm... My query shows in the date column "Criteria":
Click to expand...
=
[startdate] and <=[EndDate]. Now, this query prompts
these dates, although I want the form to have the two
fields StartDate and EndDate, and a subform showing the
query. This seems like a simple outcome.
Click to expand...


.
Click to expand...
Van, thanks again for the reply,
This is the code I use in the command button:

Private Sub Command39_Click()
txtStartDate.Value = StartDate
txtEndDate.Value = EndDate
Query5Subform.SourceObject = Query5Subform
End Sub

The error I get is: "Variable Not Defined"
also...will this query use the StartDate and EndDate
instead of prompting?
Again, thank you for the help..I am not a beginner to
coding VB but Access is somewhat new to me.
 
OK ... let's clear the terminology first otherwise it is very confusing:

* A Form (whether it is being used as a normal Form or a Subform) does NOT
have Fields. Form only have *Controls*. These Controls may be bound to the
Fields of the RecordSource (Table or Query) of the Forms.

* Only Tables or Queries have Fields or Columns.

Let's confirm what you have done:

* On your (main) Form "frmMain" (substitute with the Name of your main
Form), you have 2 TextBox Controls name [txtStartDate] and [txtEndDate].

* On this Form, you have a CommandButton Control named [Command39]. Much
better to use a meaningful name but leave it aside for the moment.

* You have a Form by the name [Query5Subform] which is being used as the
SourceObject for the Subform Control also by the name [Query5Subform].

* When the frmMain is opened, the SourceObject of the SubformControl is NOT
set.


Please do the following:

* Rename the *SubformControl* to [sfrQuery5] to make it different from the
Form name of the SourceObject. FYI: "sfr" is the prefix used for the
SubformControl and "fsf" is the prefix for the Form being used as the
SourceObject of a SubformControl.

* In the DesignView of your Query, change the criteria to:

BETWEEN [Forms]![frmMain]![txtStartDate]
AND [Forms]![frmMain]![txtEndDate]

(remember to change [frmMain])

* Still in DesignView of the Query, use the Menu Query / Parameters ... and
declare the 2 references used in the criteria above as of DateTime type.
Save & close the Query.

* Change your Procedure to:

Private Sub Command39_Click()
Me.sfrQuery5.SourceObject = "Query5Subform"
End Sub

* Now open your Form, fill in the dates and click the CommandButton and it
should work if you followed the steps correctly.
 
-----Original Message-----
OK ... let's clear the terminology first otherwise it is very confusing:

* A Form (whether it is being used as a normal Form or a Subform) does NOT
have Fields. Form only have *Controls*. These Controls may be bound to the
Fields of the RecordSource (Table or Query) of the Forms.

* Only Tables or Queries have Fields or Columns.

Let's confirm what you have done:

* On your (main) Form "frmMain" (substitute with the Name of your main
Form), you have 2 TextBox Controls name [txtStartDate] and [txtEndDate].

* On this Form, you have a CommandButton Control named [Command39]. Much
better to use a meaningful name but leave it aside for the moment.

* You have a Form by the name [Query5Subform] which is being used as the
SourceObject for the Subform Control also by the name [Query5Subform].

* When the frmMain is opened, the SourceObject of the SubformControl is NOT
set.


Please do the following:

* Rename the *SubformControl* to [sfrQuery5] to make it different from the
Form name of the SourceObject. FYI: "sfr" is the prefix used for the
SubformControl and "fsf" is the prefix for the Form being used as the
SourceObject of a SubformControl.

* In the DesignView of your Query, change the criteria to:

BETWEEN [Forms]![frmMain]![txtStartDate]
AND [Forms]![frmMain]![txtEndDate]

(remember to change [frmMain])

* Still in DesignView of the Query, use the Menu Query / Parameters ... and
declare the 2 references used in the criteria above as of DateTime type.
Save & close the Query.

* Change your Procedure to:

Private Sub Command39_Click()
Me.sfrQuery5.SourceObject = "Query5Subform"
End Sub

* Now open your Form, fill in the dates and click the CommandButton and it
should work if you followed the steps correctly.


--
HTH
Van T. Dinh
MVP (Access)



jeff said:
Van, thanks again for the reply,
This is the code I use in the command button:

Private Sub Command39_Click()
txtStartDate.Value = StartDate
txtEndDate.Value = EndDate
Query5Subform.SourceObject = Query5Subform
End Sub

The error I get is: "Variable Not Defined"
also...will this query use the StartDate and EndDate
instead of prompting?
Again, thank you for the help..I am not a beginner to
coding VB but Access is somewhat new to me.
Click to expand...


.
Van,
Click to expand...
Works perfectly..I wasn't referencing the controls
properly..makes sence now, Thanks again for the help.
 
Back
Top