Combo box for queries

  • Thread starter Thread starter Corinne
  • Start date Start date
C

Corinne

I have a database (Access 2000) which stores information on SEN student. As
I have been the only one using it I have done any queries I need as I go
along. But now we would like to put a read only version on the school
network so staff can have access to the data. This would mean me having to
design a user friendly interface that anyone could use. So what I would like
to do if possible is to have a form that opens with a combo box that list
the queries, when one is selected either the query will run or a command
button is used to run the query. Is this possible? If so how is it done?
Thank you for help in advance
 
Corinne,

Make a new form and place a combo box on it (for query selection); I'll
assume you keep the default name Combo0 for it. Disoplay the combo's
properties, and on the Data tab, property Row Source, paste in the
following:

SELECT [MSysObjects].[Name] FROM MSysObjects WHERE ((([MSysObjects].[Name])
Not Like "~*") And (([MSysObjects].[Type])=5));

This will make the combo display the names of all the existing queries in
your database, and new ones will be added utomatically as you add queries,
or removed as you delete queries.

Next, add a command button on the form to open the selected query, display
properties, tab Events; place the cursor next to On Click and click on the
little button with the three dots on the right; select Code Builder; you
will be taken to a VBA editor window, and the cursor will be between two
lines that read something like:

Private Sub Command0_Click()

End Sub

Paste these lines of code between them:

If IsNull(Me.Combo0) Then
MsgBox "Please select a query to view!"
Exit Sub
End If
DoCmd.OpenQuery Me.Combo0

(remember to change Combo0 to the actual name of the combo if different).

Close the VBA window. You're done.

HTH,
Nikos
 
Wow Nikos, thanks for that. It took me a little while to work out (I was
putting the code in Row Source Type) but it worked famously once I got it
right.

Corinne

Nikos Yannacopoulos said:
Corinne,

Make a new form and place a combo box on it (for query selection); I'll
assume you keep the default name Combo0 for it. Disoplay the combo's
properties, and on the Data tab, property Row Source, paste in the
following:

SELECT [MSysObjects].[Name] FROM MSysObjects WHERE
((([MSysObjects].[Name])
Not Like "~*") And (([MSysObjects].[Type])=5));

This will make the combo display the names of all the existing queries in
your database, and new ones will be added utomatically as you add queries,
or removed as you delete queries.

Next, add a command button on the form to open the selected query, display
properties, tab Events; place the cursor next to On Click and click on the
little button with the three dots on the right; select Code Builder; you
will be taken to a VBA editor window, and the cursor will be between two
lines that read something like:

Private Sub Command0_Click()

End Sub

Paste these lines of code between them:

If IsNull(Me.Combo0) Then
MsgBox "Please select a query to view!"
Exit Sub
End If
DoCmd.OpenQuery Me.Combo0

(remember to change Combo0 to the actual name of the combo if different).

Close the VBA window. You're done.

HTH,
Nikos


Corinne said:
I have a database (Access 2000) which stores information on SEN student. As
I have been the only one using it I have done any queries I need as I go
along. But now we would like to put a read only version on the school
network so staff can have access to the data. This would mean me having
to
design a user friendly interface that anyone could use. So what I would like
to do if possible is to have a form that opens with a combo box that list
the queries, when one is selected either the query will run or a command
button is used to run the query. Is this possible? If so how is it done?
Thank you for help in advance
Click to expand...
Click to expand...
 
Back
Top