Full Version: Form to look up data held in a query based on two fields
UtterAccess Forums > Microsoft® Access > Access Forms
Kento
Hi all,
’m building a form to look-up data held in a query. I want to use two fields to filter the data and display the results on a series of text boxes and radio buttons on tabbed sheets within the form. Currently, on entering a postcode from a combo box the Local Authority area is displayed in a text box. I want to use this Local Authority value and another value manually selected from a combo box as the criteria of the query.
The query containing the fields required is as follows:
SELECT LocalAuthorityLookUp.LocalAuthority, tblSchemeTypeLookUp.SchemeType, tblGrantInfo.SchemeName, tblGrantInfo.ContactName, tblGrantInfo.ContactNumber, tblGrantInfo.OwnerBuying, tblGrantInfo.RentPrivately, tblGrantInfo.CouncilHouse, tblGrantInfo.TiedHouseOther, tblGrantInfo.RentingFromHousingAssocaition, tblGrantInfo.CosySeal, tblGrantInfo.Dyson, tblGrantInfo.Heatpac, tblGrantInfo.JPS, tblGrantInfo.KNW, tblGrantInfo.MGI, tblGrantInfo.[Miller Pattison], tblGrantInfo.Millfold, tblGrantInfo.TheInsulationCompany, tblGrantInfo.VNR, tblGrantInfo.CavityWallInsulation, tblGrantInfo.LoftInsulationVirgin, tblGrantInfo.[LoftInsulationTopUpUnder2"], tblGrantInfo.[LoftInsulationTopUpUnder3"], tblGrantInfo.[LoftInsulationTopUpUnder4"], tblGrantInfo.DraughtProofing, tblGrantInfo.HotWaterTankJacket, tblGrantInfo.CentralHeatingSystem, tblGrantInfo.CentralHeatingSystemRepairs, tblGrantInfo.OpenFireToGlassFrontedFire, tblGrantInfo.BoilerRebate, tblGrantInfo.CostCavityWallInsulation, tblGrantInfo.CostLoftInsulationVirgin, tblGrantInfo.[CostLloftInsulationTopUpUnder2"], tblGrantInfo.[CostLloftInsulationTopUpUnder3"], tblGrantInfo.[CostLloftInsulationTopUpUnder4"], tblGrantInfo.CostDraughtProofing, tblGrantInfo.CostHotWaterTankJacket, tblGrantInfo.CostCentralHeatingSystem, tblGrantInfo.CostCentralHeatingRepairs, tblGrantInfo.CostGlassFrontFireConversion, tblGrantInfo.CostBoilerRebate, tblGrantInfo.Notes
FROM ((tblSchemeLocalAuthorityLookUp LEFT JOIN tblGrantInfo ON tblSchemeLocalAuthorityLookUp.SchemeCode = tblGrantInfo.SchemeCode) LEFT JOIN LocalAuthorityLookUp ON tblSchemeLocalAuthorityLookUp.LocalAuthroityCode = LocalAuthorityLookUp.LocalAuthorityCode) LEFT JOIN tblSchemeTypeLookUp ON tblSchemeLocalAuthorityLookUp.SchemeTypeCode = tblSchemeTypeLookUp.SchemeTypeCode;
Any idea of the code required allowing the form to query this?
Cheers
Sam.
Colby
put 2 combo/list boxes on the form with one command button.
the 2 combo/list boxes will contain the values needed to make the query parameter values.
On the command button - onClick Event
check to make sure both boxes hold values
then update the query by

sSQL = "existing query..."
dont forget to update the values being queried for by getting the values from the combo/list box
Set qdTemp = Currentdb.QueryDefs("query name")
qdTemp.SQL = sSQL
qdTemp.Close
now repaint/refresh the form.
Kind of a high level overview, but hth. Let me know if you have any more questions.
Kento
Cheers Colby,
This is how it looks, but the "Set qdTemp = CurrentDb.QueryDefs(qrySchemeLocalAuthorityLookUp)" is highlighted in yellow - meanining there is an error?
..................................
Private Sub commandDisplayInformation_Click()
sSQL = qrySchemeLocalAuthorityLookUp
Set qdTemp = CurrentDb.QueryDefs(qrySchemeLocalAuthorityLookUp)
qdTemp.SQL = sSQL
qdTemp.Close
End Sub
.......................................
Any ideas what I'm doing wrong?
Cheers,
Sam.
Colby
your close.
sSQL = "Select blah, blah, blah " & _
"From your table " & _
"WHERE field1 = your value AND field2 = your other value"
Set qdTemp = CurrentDb.QueryDefs(qrySchemeLocalAuthorityLookUp)
qdTemp.SQL = sSQL
qdTemp.Close
the first sSQL line is changing the actaul sql your query is based on; not just the reference. Maybe I should
have been a bit more clear.
Kento
Hi Colby,
M ade the following ammendment....
Private Sub commandDisplayInformation_Click()
sSQL = "SELECT LocalAuthorityLookUp.LocalAuthority, tblSchemeTypeLookUp.SchemeType, tblGrantInfo.SchemeName, tblGrantInfo.ContactName, "
sSQL = sSQL & "tblGrantInfo.ContactNumber, tblGrantInfo.OwnerBuying, tblGrantInfo.RentPrivately, tblGrantInfo.CouncilHouse, tblGrantInfo.TiedHouseOther, "
sSQL = sSQL & "tblGrantInfo.ContactNumber, tblGrantInfo.OwnerBuying, tblGrantInfo.RentPrivately, tblGrantInfo.CouncilHouse, tblGrantInfo.TiedHouseOther, "
sSQL = sSQL & "tblGrantInfo.RentingFromHousingAssocaition, tblGrantInfo.CosySeal, tblGrantInfo.Dyson, tblGrantInfo.Heatpac, tblGrantInfo.JPS, "
sSQL = sSQL & "tblGrantInfo.KNW, tblGrantInfo.MGI, tblGrantInfo.[Miller Pattison], tblGrantInfo.Millfold, tblGrantInfo.TheInsulationCompany, tblGrantInfo.VNR, "
sSQL = sSQL & "tblGrantInfo.CavityWallInsulation, tblGrantInfo.LoftInsulationVirgin, tblGrantInfo.[LoftInsulationTopUpUnder2Inches], "
sSQL = sSQL & "tblGrantInfo.[LoftInsulationTopUpUnder3Inches], tblGrantInfo.[LoftInsulationTopUpUnder4Inches], tblGrantInfo.DraughtProofing, "
sSQL = sSQL & "tblGrantInfo.HotWaterTankJacket, tblGrantInfo.CentralHeatingSystem, tblGrantInfo.CentralHeatingSystemRepairs, "
sSQL = sSQL & "tblGrantInfo.OpenFireToGlassFrontedFire, tblGrantInfo.BoilerRebate, tblGrantInfo.CostCavityWallInsulation, tblGrantInfo.CostLoftInsulationVirgin, "
sSQL = sSQL & "tblGrantInfo.[CostLloftInsulationTopUpUnder2Inches], tblGrantInfo.[CostLloftInsulationTopUpUnder3Inches], "
sSQL = sSQL & "tblGrantInfo.[CostLloftInsulationTopUpUnder4Inches], tblGrantInfo.CostDraughtProofing, tblGrantInfo.CostHotWaterTankJacket, "
sSQL = sSQL & "tblGrantInfo.CostCentralHeatingSystem, tblGrantInfo.CostCentralHeatingRepairs, tblGrantInfo.CostGlassFrontFireConversion, "
sSQL = sSQL & "tblGrantInfo.CostBoilerRebate, tblGrantInfo.Notes "
sSQL = sSQL & "FROM ((tblSchemeLocalAuthorityLookUp LEFT JOIN tblGrantInfo ON tblSchemeLocalAuthorityLookUp.SchemeCode=tblGrantInfo.SchemeCode) "
sSQL = sSQL & "LEFT JOIN LocalAuthorityLookUp ON tblSchemeLocalAuthorityLookUp.LocalAuthroityCode=LocalAuthorityLookUp.LocalAutho
rityCode) "
sSQL = sSQL & "LEFT JOIN tblSchemeTypeLookUp ON tblSchemeLocalAuthorityLookUp.SchemeTypeCode=tblSchemeTypeLookUp.SchemeTypeCode;"
sSQL = sSQL & "WHERE tblLocalAuthorityLookUp.LocalAuthority = txtLocalAuthority & tblSchemeTypeLookUp.SchemeType = cboSelectGrantType"
Set qdTemp = CurrentDb.QueryDefs(qrySchemeLocalAuthorityLookUp)
qdTemp.SQL = sSQL
qdTemp.Close
End Sub
How do I reference the fields on the tabbed sheets that I want to populate using the query??
Cheers,
Sam.
Colby
so you need to know how to uses the two combo/list box to ask for the right data?
OR
do you need to know how to reference the values of the query in your form?
Kento
The latter,
The "Where tblLocalAuthorityLookUp.LocalAuthority = txtLocalAuthority & tblSchemeTypeLookUp.SchemeType = cboSelectGrantType" tell the query what the criteria required is (yes?), so now I want to reference the values in the query to where I want them to appear on the form.
Will it look something like this:
Set qdTemp = CurrentDb.QueryDefs(qrySchemeLocalAuthorityLookUp)
qdTemp.SQL = sSQL
qdTemp.Close
Me.txtSchemeContact = tblGrantInfo.ContactName
Me.txtTel = tblGrantInfo.ContactNumber
Sorry it taking so long, I'm dead new to this VBA lark!
Sam.
Colby
no problem on the time.
believe you have it as far as referencing.
Are you referencing the query before you open the display form or are you calling it from the
display form?
if you call from the form you will need to have a form.repaint at the end of the stmt so
it will update the values.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.