form question

  • Thread starter Thread starter Larry Bernstein
  • Start date Start date
L

Larry Bernstein

Is there a way of creating something similar to a parameter query in a
form? What I mean is when you start the form it brings up a requester
asking you for the field.

My situation:

Table 1:

PhysicianID
LName
FName


Table 2:
EventID
EventType
StartDate
EndDate
HalfDaysOff
PhysicianID


The "EventType"s are vacation days, sick days, jury, administrative,
etc. What I envision is when starting the form, a requester would pop up
so I could input the physician's last and if need be first name. Then
the regular form that I add the events would come up to populate table 2.
 
You could do this in two different ways:

(1) Put unbound combo box in the form's header that will allow the person to
select the correct information (you can use a query as the Row Source for
this combo box, where this query provides a list of the physician names);
then, put a command button next to them that the user can click to "enter"
the values. This button would run code on its OnClick event that requeries
the form's recordsource. Then, in the form's recordsource query, you can put
criterion expression for the PhysicianID field:
[Forms]![FormName]![ComboBoxName]

An appropriate Row Source query for this combo box would be something like
this:
SELECT PhysicianID, [FName] & " " & [LName] As FullName FROM [Table
1] ORDER BY [LastName], [FirstName];

The benefit of this setup is that you don't have to close and reopen the
form in order to select a different physician; just select another physician
from the combo box to continue working.


(2) Use the OnLoad event of the form to open a form in dialog mode that has
the same setup on it as what is described above (combo box and command
button). In this case, the OnClick event would make this popup form
invisible. The code for the OnLoad event of the first form would look
something like this:
Private Sub Form_Load()
DoCmd.OpenForm "PopUpFormName", , , , , acDialog
Me.Requery
DoCmd.Close acForm, "PopUpFormName"
End Sub

In this case the criterion expression in the form's recordsource query would
look something like this:
[Forms]![PopupFormName]![ComboBoxName]

This setup will require you to close the form and reopen it in order to
select a different physician. It is possible to keep the form open and to
select a different physician, but we'd need another command button on the
form for "another physician", and the OnClick event of that button would be
essentially the same as the OnLoad code above:
Private Sub cmdButtonAnotherPhysician_Click()
DoCmd.OpenForm "PopUpFormName", , , , , acDialog
Me.Requery
DoCmd.Close acForm, "PopUpFormName"
End Sub
 
Back
Top