Open form based on query results

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

Guest

Hello,

I have created a form which gets its data from a parameter query. The
user opens the form which prompts them for a string which they type in
(employee number). The form opens and displays data which matches the string
(employee number). This same form also has a subform which displays other
data from a linked table that matches the string (employee number) originally
defined via the parameter query. This all works great as long as the string
(employee number) is already in the table to which the parameter query is
looking. If it is not things go awry.

Is there an easy way to have the original parameter query check to see if
any results are returned and if not instead open a different form (one which
will allow the user to add a new string (employee number) to the table?
However, if the parameter query does match results for the string it would
open the original form as usual?

Thanks in advance
 
Hello,

I have created a form which gets its data from a parameter query. The
user opens the form which prompts them for a string which they type in
(employee number). The form opens and displays data which matches the string
(employee number). This same form also has a subform which displays other
data from a linked table that matches the string (employee number) originally
defined via the parameter query. This all works great as long as the string
(employee number) is already in the table to which the parameter query is
looking. If it is not things go awry.

Is there an easy way to have the original parameter query check to see if
any results are returned and if not instead open a different form (one which
will allow the user to add a new string (employee number) to the table?
However, if the parameter query does match results for the string it would
open the original form as usual?

Thanks in advance
Click to expand...

Code the form's Open event:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records"
Cancel = True
DoCmd.OpenForm "OtherForm"
End if
 
A way to do what you are looking for would be to create a form with a combo
box. Set the control source to pull the list of employee numbers something
like:
select distinct [employee number] from [Your Table Name]
or the wizard can help you with this function. This will prevent typos and
other data entry errors. Then place a button on the form and set the code of
the on-click event to be something like:
If isnull(Me![ComboBoxName]) then
Docmd.Openform "The Form name to add new"
else
Docmd.Openform "Form Name"
end if

Me.visible = false

Then in the queary where the prompt is in the criteria, change that to:
Forms![YourNewFormName]![ComboBoxName]


Then on the close events of the other form add the cosed
Forms![YourNewFormName].visible = true

This is one way to do this. The use of the combo box is much prefered to
the prompt from the query.
 
I’m almost there but I must be missing something.

I did as “schasteen†suggested and created a new form (I called it selector
for now) with a combo box and a command button and coded as specified.

I then took “your/fredg†advice and coded the forms’ open event as suggested.

When I open the new “selector†form, choose or type a string which is in the
combo box/linked table and click the command button, it opens the form that I
desire. If I enter nothing into the combo box (isnull) and click the
command button, it opens the other form which I want. That is all perfect,
so far.

If I enter a string which is not in the combo box (not in the linked table)
I do get the msgbox (No records) however upon clicking the Ok in that message
box I get a Run-time error 2501 – The Openform action was cancelled and an
option to debug. Debug is showing the second of the two “DoCmd.openFormâ€
lines in “schasteens†code/suggestions as being the culprit (although this
obviously works fine when that particular code is being accessed if the
string entered into the combo box is valid and matches something in the
linked table).

What am I missing?
 
I¡¦m almost there but I must be missing something.

I did as ¡§schasteen¡¨ suggested and created a new form (I called it selector
for now) with a combo box and a command button and coded as specified.

I then took ¡§your/fredg¡¨ advice and coded the forms¡¦ open event as suggested.

When I open the new ¡§selector¡¨ form, choose or type a string which is in the
combo box/linked table and click the command button, it opens the form that I
desire. If I enter nothing into the combo box (isnull) and click the
command button, it opens the other form which I want. That is all perfect,
so far.

If I enter a string which is not in the combo box (not in the linked table)
I do get the msgbox (No records) however upon clicking the Ok in that message
box I get a Run-time error 2501 ¡V The Openform action was cancelled and an
option to debug. Debug is showing the second of the two ¡§DoCmd.openForm¡¨
lines in ¡§schasteens¡¨ code/suggestions as being the culprit (although this
obviously works fine when that particular code is being accessed if the
string entered into the combo box is valid and matches something in the
linked table).

What am I missing?
Click to expand...

Error 2501 is a common error generated when a form or report is opened
by code, but then canceled.
You need to trap that error in the error handling portion of the event
procedure.

On Error Goto Err_Handler
' Your code here....
DoCmd.OpenForm "OtherForm"

Exit_Sub:
Exit Sub
Err_Handler:
If Err = 2501 Then
Else
MsgBox "Error# " & Err.Number & " " & Err.Description
End If
Resume Exit_Sub
 
Back
Top