Query help??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a query with 3 fields (Date, Name, Dept) I want to display data by
selecting a Dept by a drop down (selection). Basically, only displaying data
by Department by picking the Dept on the Drop down list. Is this possible??
Please go easy on me, thanks all.
 
I created a query with 3 fields (Date, Name, Dept) I want to display data by
selecting a Dept by a drop down (selection). Basically, only displaying data
by Department by picking the Dept on the Drop down list. Is this possible??
Please go easy on me, thanks all.
Click to expand...

Sure.
But first a word of caution.

If you really do have fields named 'Name' and 'Date' I would strongly
suggest you change them to something else.
'Name' and 'Date' are reserved words in Access/VBA and should not be
used as field names.

See Microsoft KnowledgeBase articles:

109312 "ACC: Reserved Words in Microsoft Access"
209187 "Access2000:Reserved words in Microsoft Access"
286335 "Access2002:Reserved words in Microsoft Access"
321266 "ACC2002: Microsoft Jet 4.0 Reserved Words"
======
Create an unbound form.
Add a combo box that shows the departments.
Add a command button.
Code the Command button:

DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name

In the query, as criteria for the Dept field, write
forms!FormName!ComboName

Open the form.
Select the department.
Hit the command button.
The query will run and display.
 
I did exactly as you wrote. My query products a blank every time. Only think
I changed was "DoCmd.Close acForm, "Test"
 
It does Work! I'm sorry, I had to change a name. My next question, I want to
create a report for this to printout what is my next step. Thanks
 
It does Work! I'm sorry, I had to change a name. My next question, I want to
create a report for this to printout what is my next step. Thanks
Click to expand...

When you post a question, please include ALL the pertinent facts.
You'll need to change some of what I've suggested.

Leave the query and it's criteria as you now have it.
Create a report, using the query as it's record source.

Leave the form as you now have it, EXCEPT change the code behind the
command button to simply:
Me.Visible = False

Code the Report's Open event:
DoCmd.OpenForm "FormName", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, FormName

Now run the report.
The report will open the Form.
Enter the parameter wanted.
Click the Form's command button.
The report will display.
When you close the report, it will also close the form.
 
The form work just fine. The report however has a problem. When I run the
report, a dialog box appears and says "Enter Parameter Value" If I enter the
value as what was stated in the form combo box the report works fine. Did I
do something wrong?
 
The form work just fine. The report however has a problem. When I run the
report, a dialog box appears and says "Enter Parameter Value" If I enter the
value as what was stated in the form combo box the report works fine. Did I
do something wrong?
Click to expand...

Did you change the code behind the form's command button?
It should only read:

Private Sub CommandName_Click()
Me.Visible = False
End Sub

CommandName is whatever your command button name is.
 
This is what I put:

Private Sub Command2_Click()

Me.Visible = False

DoCmd.OpenQuery "Query2"
DoCmd.Close acForm, "test"

End Sub

Even if I put the Me.Visible =False after the DoCmd acform, "test" I get
expression error.
 
This is what I put:

Private Sub Command2_Click()

Me.Visible = False

DoCmd.OpenQuery "Query2"
DoCmd.Close acForm, "test"

End Sub

Even if I put the Me.Visible =False after the DoCmd acform, "test" I get
expression error.
Click to expand...

You're not doing as I wrote!!!

This is the ENTIRE code behind the Form's command button.
I've used your command button name.

Private Sub Command2_Click()
Me.Visible = False
End Sub

Delete the DoCmd.OpenQuery and the DoCmd.Close lines completely from
the Form's code.
 
I had the exact same question, as bladelock. I have done everything fredg
described and my reports work perfectly. Thanks fred. I have one problem
though, when I hit the close box on the report preview I get this error:

Run-time error '2493':
This object requires an Object Name argument.

When I hit debug it points to this line:

DoCmd.Close acForm, Form2

Any suggestions?

FYI: the form is closing before the report opens, which is fine, but not as
you describe.
 
I had the exact same question, as bladelock. I have done everything fredg
described and my reports work perfectly. Thanks fred. I have one problem
though, when I hit the close box on the report preview I get this error:

Run-time error '2493':
This object requires an Object Name argument.

When I hit debug it points to this line:

DoCmd.Close acForm, Form2

Any suggestions?

FYI: the form is closing before the report opens, which is fine, but not as
you describe.
Click to expand...

You need to surround the report name with quotes:

DoCmd.Close acForm,"Form2"

The form must be open when the report is run to avoid a query
parameter prompt.
 
OK everything is working great, except for one annoyance.

If you happen to cancel out of your Form, you are left with the underlying
query combobox asking for a parameter. Anyway to make this not happen?

TIA
 
What did you mean you had to change a name? I have the proper field name
entered. When using VB to debug I am finding that my values are coming across
as the ListIndex value and not a String. I have declaired the variable as a
String and it is returned as "".

Form name is acParticipantSelect
Field name is cmbxPartSelect
Based on RowSourceType: Table/Querry
Row Source: SELECT [Participant].[ParticipantID], [Participant].[Name] FROM
Participant;
Bound Column: 2
Limit to List: Yes

I have tried to use a different order to the same result as above.

I have even tried to redefine the value in VB by
Dim cmbxPartSelect(cmbxPartSelect.ListIndex) As String
and receive a Compile Error: Constant Expression Required

I have tried Row Source: SELECT [Participant].[Name] FROM Participant; but
the combo box does not display any values

My querry is working correctly for the information passed to it. It is just
the wrong info being passed.

Any help is appriciated
 
Nevermind, after about an hour futzing with it Access decided it liked my
original syntax and started to work. Go figure.

Tahlmorrah said:
What did you mean you had to change a name? I have the proper field name
entered. When using VB to debug I am finding that my values are coming across
as the ListIndex value and not a String. I have declaired the variable as a
String and it is returned as "".

Form name is acParticipantSelect
Field name is cmbxPartSelect
Based on RowSourceType: Table/Querry
Row Source: SELECT [Participant].[ParticipantID], [Participant].[Name] FROM
Participant;
Bound Column: 2
Limit to List: Yes

I have tried to use a different order to the same result as above.

I have even tried to redefine the value in VB by
Dim cmbxPartSelect(cmbxPartSelect.ListIndex) As String
and receive a Compile Error: Constant Expression Required

I have tried Row Source: SELECT [Participant].[Name] FROM Participant; but
the combo box does not display any values

My querry is working correctly for the information passed to it. It is just
the wrong info being passed.

Any help is appriciated

bladelock said:
It does Work! I'm sorry, I had to change a name. My next question, I want to
create a report for this to printout what is my next step. Thanks
Click to expand...
Click to expand...
 
Back
Top