I am trying to find a way to show a selected a record in a specific field, from a subform to a text box.
The main form (frmMenu) of my database has the following 4 object:
- An unbound text box (txtInsuranceNo).
- A combo box (cboCriteria) which is used to select the search criteria (InsuranceNo, First Name and Last name).
- An unbound Sub form (frmSubMaster) with a query (qryMaster) as Source Object.
- A command button that runs the following code:
Whan exactly I am trying to achieve is show in txtInsuranceNo, the InsuranceNo of the record selected in the Sub Form (something similar what I have done before with a listbox ([listbox].Column(2). The field InsuranceNo is in the 1st column of the query.
Assuming I've interpreted your requirement and context correctly, all you need to do is set the controlsource of the textbox called txtInsuranceNo to the value of the column in the combobox cboCriteria which displays the InsuranceNo. You already state that it is the first column of the combo box. That would mean it is available as
=cboCriteria.Column(0)
The fact that you have done something similar in the past and are asking about it here, though, suggests we might be missing something. Can you share a sanitized copy of the database in question for review? Or give us some screenshots so we can see what the context is?
Thank you for helping out of this topic.
Sorry for not being too clear but the first column does not refer to a lstbox but to a subform.
I am attaching a sample db. You will also notice the two controls (cbo box and text box) that I use as query criteria.
Il looks like I am also having problems with the criteria itself has it should meet both while in my sample it pulls out the criteria in any field. Thank you
I see nothing in either table that appears to be an "InsuranceNo". How would you find it to display on the form?
The query in the subform control gets records from a table that has three fields:
FileNo
FName
LName
How would you retrieve an InsuranceNo using any of those three fields? Where would InsuranceNo come from? Or, is FileNo the same thing as InsuranceNo?
The logic in the query that is displayed as the subform escapes me. Please explain, as a narrative, how it is supposed to work. So far I've not figured out how to display any results.
You say you want to return an Insurance Number in the control in the parent form, but there is no such column in either of the tables in your file. You'll need to explain in more detail what data you want returning, where it comes from, and how the data returned will de determined by the parameter or parameters entered in the form.
I'm assuming the parent form is unbound, in which case you don't need a subform. You can base a single form on a query which references the parameters, and requery the form to execute the search. The unbound controls in which the parameter values are entered or selected would be placed in the form header.
The following is my stock answer describing how to return rows which match parameters entered or selected in a form, where any or all of those parameters can be left Null, allowing the search to be only on the basis of those controls in which values have been entered or selected. I hope you'll find this useful:
The best basis for restricting a query on multiple parameters where the parameters might be used singly or in combination, allowing one or more to be Null, is that in the WHERE clause each parameter is tested in this way:
WHERE (SomeColumn = [some parameter]
OR [some parameter] IS NULL)
AND (SomeOtherColumn = [some other parameter]
OR [some other parameter] IS NULL)
AND etc
The following can of course be used:
(SomeColumn LIKE "*" & [some parameter] & "*"
OR [some parameter] IS NULL)
where it is appropriate to use pattern matching rather than testing for equality. Bear in mind, however, that even when applied to a single column this can result in specious mismatches. Moreover, the LIKE operator does not allow use of the indexes, so can reduce performance significantly. In most cases it is better to reference a combo box in an unbound dialogue form, or in a bound form's header, as the parameter. The user can then select a value from a fixed set of known values, or by entering the first few characters in the combo box, progressively go to the first match by virtue of the control's AutoExpand property. The following query is an example:
SQL:
SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,
Country, Employer, LastName, FirstName, Contacts.ContactID
FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)
INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts
ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers
ON Contacts.ContactID = ContactEmployers.ContactID)
ON Employers.EmployerID = ContactEmployers.EmployerID)
ON Regions.RegionID = Cities.RegionID
WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity
OR Forms!frmReportDialogue!cboCity IS NULL)
AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer
OR Forms!frmReportDialogue!cboEmployer IS NULL);
The logic behind this approach is extremely simple and consequently, given good indexing in the table, very efficient. It also has the advantage of not having to bother about the data type of the column in question, so unlike when building an SQL statement in code, consideration does not need to be given as to whether the values need delimiting or not.
Each OR operation is enclosed in parentheses to force it to evaluate independently. These parenthesized expressions are then tacked together with AND operations. The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter or, if the parameter is left empty (NULL), for every row. By virtue of the AND operations the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.
Note that when you do this, parameters should only be declared in a PARAMETERS clause if they are of DateTime data type. If other types were declared they could never be Null. DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.
When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view. This applies to the initial saving of the query, and if any subsequent amendments are made. If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open. It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.
Note BTW that if searching on the basis of a date range this can be made a closed range or open ended in either direction by treating the start and end date parameters independently, rather than within a BETWEEN….AND operation:
WHERE (DateColumn >= [start date parameter]
OR [start date parameter] IS NULL)
AND (DateColumn < [end date parameter]+1
OR [end date parameter] IS NULL)
This also allows for date/time values in the table where the time of day element is not zero. Any rows with such values would not be returned by a BETWEEN....AND operation if the value is on the final day of the range.
sorry it was a typo. The FileNo field, should have been InsuranceNo.
The idea was only to use cboCriteria and textsearch box as source criteria for 1 unique query (qryMaster) and display the results in the form.
It looks like I am getting into something a bit too complex for me. I will look at your suggestions and try to start from there. Thank you
sorry it was a typo. The FileNo field, should have been InsuranceNo.
The idea was only to use cboCriteria and textsearch box as source criteria for 1 unique query (qryMaster) and display the results in the form.
It looks like I am getting into something a bit too complex for me. I will look at your suggestions and try to start from there. Thank you
I'm attaching an amended copy of your file in which I've done what I think you might be aiming at. Rather than using a subform I've used a bound parent form, and put the unbound controls in the form header. The way it works is that a criterion is selected in the first combo box in the header. This governs the RowSource of the second comb box, which lists the values from the column selected in the first combo box. One of these is selected from the list and the Go button clicked. This filters the form to the row or rows which match the value selected in the second combo box. If the second combo box is left empty (Null) and the Go button is clicked the filter is turned off, and all records are returned in the form.
The code for the form's module is as follows:
Code:
Option Compare Database
Option Explicit
Private Sub cboCriteria_AfterUpdate()
Dim strColumn As String
Dim ctrl As Control
Dim strRowSource As String
strColumn = Me.cboCriteria
If strColumn <> "" Then
strRowSource = "SELECT DISTINCT " & strColumn & _
" FROM tblClients" & _
" ORDER BY " & strColumn
Me.cboSearch = Null
Me.cboSearch.RowSource = strRowSource
End If
End Sub
Private Sub cmdSearch_Click()
If Not IsNull(Me.cboSearch) Then
Me.Filter = Me.cboCriteria & " = """ & Me.cboSearch & """"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub
I am trying to find a way to show a selected a record in a specific field, from a subform to a text box.
The main form (frmMenu) of my database has the following 4 object:
- An unbound text box (txtInsuranceNo).
- A combo box (cboCriteria) which is used to select the search criteria (InsuranceNo, First Name and Last name).
- An unbound Sub form (frmSubMaster) with a query (qryMaster) as Source Object.
- A command button that runs the following code:
Whan exactly I am trying to achieve is show in txtInsuranceNo, the InsuranceNo of the record selected in the Sub Form (something similar what I have done before with a listbox ([listbox].Column(2). The field InsuranceNo is in the 1st column of the query.
Using a subform is pointless when the parent form is unbound. Here's a further amended version of your file, in which the insurance number of the currently selected record in the detail section is shown in a text box in the form header. Why would you want to see it in the header when its already shown in the detail section, however?
Using a subform is pointless when the parent form is unbound. Here's a further amended version of your file, in which the insurance number of the currently selected record in the detail section is shown in a text box in the form header. Why would you want to see it in the header when its already shown in the detail section, however?
Thank you for your help and time on this. I know better understand the concept of sub forms with unbound parent forms.
Just one last question on your example and more specifically on the second combo (cboSearch). You indicate that it lists the values from the column selected in the first combo box. In the example I do not see the values. Should I be typing the actual criteria and after clicking on the Go cmd button only only the corresponding records? Thank you
My idea is to redesign an existing db. Please note records are not added to the db but imported weekly.
It's main function is merely to query data, print, run some stats and export to certain MS Word forms. The reason why I came up with un main unbound form (dashboard) which would allow me to query records and display them in the subform.
In the image below Lname has been selected from the list of column names in the first combo box's drop down list. This causes the second combo box's drop down list to return all distinct values from the Lname column in the tblClients table. In the image White has been selected, and the Go button clicked. This filters the form to Mary White's record, and her insurance number is also shown in the read-only text box in the form header immediately below the criteria combo box.
Note that, if the drop down list of the second combo box is a long one, rather than scrolling down through the list, you can type the desired value into the combo box and, by virtue of its AutoExpand property being True, the first match will progressively be selected as you type each character into the control.
