Pass value to subform from search form

[Guest]

I have a subform with combo box. User's are having difficulty selecting
values from the combo box directly because so many of the values are similar
(many beginning with the word "university"). Also, the hard copy entry form
often has only an approximation of the correct name and the user often can't
find the correct name in the combo box. This is leading to a lot of incorrect
names being added. What I did was create a search form
(frmSchoolNameCriteria) based on a paramenter query and the results display
in yet another form (frmSchoolName). I would like users to be able to
double-click on an entry in frmSchoolName and have the subform combo box
(cboMedSchool) show the selection. Right now users are have to re-type or
cut/paste the correct name which is clunky.

Rowsource for Combobox (cboMedSchool) is table/query:
SELECT MedSchools.MedSchoolID, MedSchools.[SchoolName],
MedSchools.[SchoolLocation] FROM MedSchools ORDER BY [SchoolName],
[SchoolLocation];

Record Source for the form frmSchoolName is qrySchoolName:
SELECT MedSchools.[SchoolName], MedSchools.[SchoolLocation],
FROM MedSchools
WHERE (((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
((MedSchools.[SchoolLocation]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityLocation] & "*")) OR
(((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
(([Forms]![frmSchoolNameCriteria]![tboFacilityLocation]) Is Null))
ORDER BY MedSchools.[SchoolName];

Thanks in advance for any guidance.

 

[Guest]

Hi

Sorry to say that you seem to have come up with a quite complicated solution
to a simple problem.

I "think" you are simply trying to open the correct record so that your
users can input data ??

Here is a suggestion.
Create a new query based on your table “MedSchoolsâ€
Bring in all the fields that you want to be on your final input data form
Ensure that the MedSchoolID is one of these fields
Save the query
Create a new form or use the original based on the new query.

Create a “Text Box†on your form (new or old) called it FindSchool

On the AfterUpdate action of the FindSchool text box place this code – which
will find the school and then set the focus to [SchoolName] - ensure that
SchoolName "is" somewhere o the form.

Private Sub FindSchool_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ MedSchoolID] = " & Str(Me![FindSchool])
Me.Bookmark = rs.Bookmark

Me.SchoolName.SetFocus

End Sub


To clear the search put this on the GotFocus action

Private Sub FindSchool_GotFocus()
Me.FindSchool.Value = ""
End Sub

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

I have a subform with combo box. User's are having difficulty selecting
values from the combo box directly because so many of the values are similar
(many beginning with the word "university"). Also, the hard copy entry form
often has only an approximation of the correct name and the user often can't
find the correct name in the combo box. This is leading to a lot of incorrect
names being added. What I did was create a search form
(frmSchoolNameCriteria) based on a paramenter query and the results display
in yet another form (frmSchoolName). I would like users to be able to
double-click on an entry in frmSchoolName and have the subform combo box
(cboMedSchool) show the selection. Right now users are have to re-type or
cut/paste the correct name which is clunky.

Rowsource for Combobox (cboMedSchool) is table/query:
SELECT MedSchools.MedSchoolID, MedSchools.[SchoolName],
MedSchools.[SchoolLocation] FROM MedSchools ORDER BY [SchoolName],
[SchoolLocation];

Record Source for the form frmSchoolName is qrySchoolName:
SELECT MedSchools.[SchoolName], MedSchools.[SchoolLocation],
FROM MedSchools
WHERE (((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
((MedSchools.[SchoolLocation]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityLocation] & "*")) OR
(((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
(([Forms]![frmSchoolNameCriteria]![tboFacilityLocation]) Is Null))
ORDER BY MedSchools.[SchoolName];

Thanks in advance for any guidance.

 

[Guest]

Wayne, thanks for the input. The problem is simple - How to pick the correct
School/Hospital name from a combo box? I didn't explain it very well. I
could have just left it as a combo box, but a lot of incorrect, redundant
entries were being made. For instance, depending on how a doctor filled out a
form you could end up with "Los Angeles County/USC Medical Center" (the
correct name), or "USC/Los Angeles County Medical Center" or LA County/USC
Medical Center or other permutations being entered. Don't get me started on
the foreign names. The idea behind the search form was to allow the user to
enter "USC" and/or a location "Los Angeles" (or, if lazy, even "Los A") to
populate a form so the user could select from it.

Anyway, the problem was how to go from the "search result" form where the
user would pick the correct item and then set focus back to the subform with
the picked selection appearing. The subform has more fields to fill in from
there.

I found this from Ken Snell at
http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1,
which is essentially what I need, but I can't get the syntax correct:

....So what you want to do is to let the user select a value on the second
form,
and then you want to pass that value to the combo box on the first form --
so that the combo box would then show that value in its display? Will the
value from the second form be one that is in the combo box's RowSource
records? If not, then the code may need to reset the combo box's RowSource
query too.

Passing a value from one open form to another can be done in various ways,
but the method that I prefer involves opening the second form in dialog
mode, letting the user select the value, and the making that form invisible
when the user clicks an "ok" button on the second form. The first form then
reads the value from the second form's control and then closes the second
form. I usally also give the user a "cancel" button on the second form so
that he/she can return to the first form without doing anything.

Here is some basic code, assuming that the user clicks a button to make the
second form appear:


Private Sub cmdButtonName_Click()
Const strFormName As String = "NameOfSecondForm"
Const strFormName_ControlName As String = "NameOfControlOnSecondForm"
DoCmd.OpenForm strFormName, , , , acDialog
If IsTheFormOpen(strFormName) = True Then
Me.ComboBoxName.Value = _
Forms(strFormName).Controls(strFormName_ControlName).Value
DoCmd.Close acForm, strFormName, acSaveNo
End If
End Sub


Note that this code uses the following function (put it in a regular
module):




' *********************************
' ** Function IsTheFormOpen **
' *********************************

Public Function IsTheFormOpen(ByVal xstrFormName As String) As Boolean
'Returns False if form is not open or is open in design view; returns
' True if it is open in form or datasheet view
On Error Resume Next
' Set default value for the function
IsTheFormOpen = False
If SysCmd(acSysCmdGetObjectState, acForm, xstrFormName) <> 0 Then
If Forms(xstrFormName).CurrentView <> 0 Then IsTheFormOpen = True
End If
Exit Function
End Function



The code in the second form for the two buttons ("ok" and "Cancel") would be
this:

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

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
--

Ken Snell
<MS ACCESS MVP>

Hi

Sorry to say that you seem to have come up with a quite complicated solution
to a simple problem.

I "think" you are simply trying to open the correct record so that your
users can input data ??

Here is a suggestion.
Create a new query based on your table “MedSchoolsâ€
Bring in all the fields that you want to be on your final input data form
Ensure that the MedSchoolID is one of these fields
Save the query
Create a new form or use the original based on the new query.

Create a “Text Box†on your form (new or old) called it FindSchool

On the AfterUpdate action of the FindSchool text box place this code – which
will find the school and then set the focus to [SchoolName] - ensure that
SchoolName "is" somewhere o the form.

Private Sub FindSchool_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ MedSchoolID] = " & Str(Me![FindSchool])
Me.Bookmark = rs.Bookmark

Me.SchoolName.SetFocus

End Sub


To clear the search put this on the GotFocus action

Private Sub FindSchool_GotFocus()
Me.FindSchool.Value = ""
End Sub

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

I have a subform with combo box. User's are having difficulty selecting
values from the combo box directly because so many of the values are similar
(many beginning with the word "university"). Also, the hard copy entry form
often has only an approximation of the correct name and the user often can't
find the correct name in the combo box. This is leading to a lot of incorrect
names being added. What I did was create a search form
(frmSchoolNameCriteria) based on a paramenter query and the results display
in yet another form (frmSchoolName). I would like users to be able to
double-click on an entry in frmSchoolName and have the subform combo box
(cboMedSchool) show the selection. Right now users are have to re-type or
cut/paste the correct name which is clunky.

Rowsource for Combobox (cboMedSchool) is table/query:
SELECT MedSchools.MedSchoolID, MedSchools.[SchoolName],
MedSchools.[SchoolLocation] FROM MedSchools ORDER BY [SchoolName],
[SchoolLocation];

Record Source for the form frmSchoolName is qrySchoolName:
SELECT MedSchools.[SchoolName], MedSchools.[SchoolLocation],
FROM MedSchools
WHERE (((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
((MedSchools.[SchoolLocation]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityLocation] & "*")) OR
(((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
(([Forms]![frmSchoolNameCriteria]![tboFacilityLocation]) Is Null))
ORDER BY MedSchools.[SchoolName];

Thanks in advance for any guidance.

 

[Guest]

OK, I got it to work. Ken's code was fine. All I had to do was include the
key field (MedSchoolID) on the "SearchResultForm" as visible=no. These lines
of code (genericized), run from a button on the search form, did what I
wanted:

[Forms]![MainFormName]![SubformName]![MedSchoolID].Value =
Me.MedSchoolID.Value
[Forms]![MainFormName]![SubformName]![NextDataEntryField].SetFocus
DoCmd.Close acForm, "SearchResultForm", acSaveNo

Thanks.

Wayne, thanks for the input. The problem is simple - How to pick the correct
School/Hospital name from a combo box? I didn't explain it very well. I
could have just left it as a combo box, but a lot of incorrect, redundant
entries were being made. For instance, depending on how a doctor filled out a
form you could end up with "Los Angeles County/USC Medical Center" (the
correct name), or "USC/Los Angeles County Medical Center" or LA County/USC
Medical Center or other permutations being entered. Don't get me started on
the foreign names. The idea behind the search form was to allow the user to
enter "USC" and/or a location "Los Angeles" (or, if lazy, even "Los A") to
populate a form so the user could select from it.

Anyway, the problem was how to go from the "search result" form where the
user would pick the correct item and then set focus back to the subform with
the picked selection appearing. The subform has more fields to fill in from
there.

I found this from Ken Snell at
http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1,
which is essentially what I need, but I can't get the syntax correct:

...So what you want to do is to let the user select a value on the second
form,
and then you want to pass that value to the combo box on the first form --
so that the combo box would then show that value in its display? Will the
value from the second form be one that is in the combo box's RowSource
records? If not, then the code may need to reset the combo box's RowSource
query too.

Passing a value from one open form to another can be done in various ways,
but the method that I prefer involves opening the second form in dialog
mode, letting the user select the value, and the making that form invisible
when the user clicks an "ok" button on the second form. The first form then
reads the value from the second form's control and then closes the second
form. I usally also give the user a "cancel" button on the second form so
that he/she can return to the first form without doing anything.

Here is some basic code, assuming that the user clicks a button to make the
second form appear:


Private Sub cmdButtonName_Click()
Const strFormName As String = "NameOfSecondForm"
Const strFormName_ControlName As String = "NameOfControlOnSecondForm"
DoCmd.OpenForm strFormName, , , , acDialog
If IsTheFormOpen(strFormName) = True Then
Me.ComboBoxName.Value = _
Forms(strFormName).Controls(strFormName_ControlName).Value
DoCmd.Close acForm, strFormName, acSaveNo
End If
End Sub


Note that this code uses the following function (put it in a regular
module):




' *********************************
' ** Function IsTheFormOpen **
' *********************************

Public Function IsTheFormOpen(ByVal xstrFormName As String) As Boolean
'Returns False if form is not open or is open in design view; returns
' True if it is open in form or datasheet view
On Error Resume Next
' Set default value for the function
IsTheFormOpen = False
If SysCmd(acSysCmdGetObjectState, acForm, xstrFormName) <> 0 Then
If Forms(xstrFormName).CurrentView <> 0 Then IsTheFormOpen = True
End If
Exit Function
End Function



The code in the second form for the two buttons ("ok" and "Cancel") would be
this:

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

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
--

Ken Snell
<MS ACCESS MVP>

Hi

Sorry to say that you seem to have come up with a quite complicated solution
to a simple problem.

I "think" you are simply trying to open the correct record so that your
users can input data ??

Here is a suggestion.
Create a new query based on your table “MedSchoolsâ€
Bring in all the fields that you want to be on your final input data form
Ensure that the MedSchoolID is one of these fields
Save the query
Create a new form or use the original based on the new query.

Create a “Text Box†on your form (new or old) called it FindSchool

On the AfterUpdate action of the FindSchool text box place this code – which
will find the school and then set the focus to [SchoolName] - ensure that
SchoolName "is" somewhere o the form.

Private Sub FindSchool_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ MedSchoolID] = " & Str(Me![FindSchool])
Me.Bookmark = rs.Bookmark

Me.SchoolName.SetFocus

End Sub


To clear the search put this on the GotFocus action

Private Sub FindSchool_GotFocus()
Me.FindSchool.Value = ""
End Sub

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

I have a subform with combo box. User's are having difficulty selecting
values from the combo box directly because so many of the values are similar
(many beginning with the word "university"). Also, the hard copy entry form
often has only an approximation of the correct name and the user often can't
find the correct name in the combo box. This is leading to a lot of incorrect
names being added. What I did was create a search form
(frmSchoolNameCriteria) based on a paramenter query and the results display
in yet another form (frmSchoolName). I would like users to be able to
double-click on an entry in frmSchoolName and have the subform combo box
(cboMedSchool) show the selection. Right now users are have to re-type or
cut/paste the correct name which is clunky.

Rowsource for Combobox (cboMedSchool) is table/query:
SELECT MedSchools.MedSchoolID, MedSchools.[SchoolName],
MedSchools.[SchoolLocation] FROM MedSchools ORDER BY [SchoolName],
[SchoolLocation];

Record Source for the form frmSchoolName is qrySchoolName:
SELECT MedSchools.[SchoolName], MedSchools.[SchoolLocation],
FROM MedSchools
WHERE (((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
((MedSchools.[SchoolLocation]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityLocation] & "*")) OR
(((MedSchools.[SchoolName]) Like "*" &
[Forms]![frmSchoolNameCriteria]![tboFacilityName] & "*") AND
(([Forms]![frmSchoolNameCriteria]![tboFacilityLocation]) Is Null))
ORDER BY MedSchools.[SchoolName];

Thanks in advance for any guidance.