[Matthew P]

I am making a database and am wondering if it is possibleto make a parameter query with a drop down list in?, any help wou;d be handy but i could really do with a guide on how to do it :-)

[Manny]

Don't know if this helps, but in some mdb applications I update the query that dropdowns are using then use the requery to refresh.

At runtime I use to construct my SQL and pass it to the form or other controls like this. I pass the parameters from other controls in the interface or from variables in memory.

sSQLPeriod = "SELECT * FROM [Tbl: Input Hours]" & _
" WHERE BOP=#" & Me.dtBegOfPeriod & "#" & _
" AND [LAST NAME]=" & Chr(39) & CurrentUser & Chr(39) & _
" ORDER BY [DATE], [ID] ASC"

frmDSCASTimeReport.Form.RecordSource = sSQLPeriod
frmDSCASTimeReport.Requery


In the code below I have a linked combo box (drop down) that changes depending on the user selection.

Private Sub cboSection_AfterUpdate()
cboSubSection.RowSourceType = "Table/Query"
cboSubSection.RowSource = "SELECT * FROM TblSubSection WHERE SECTION=" & Chr(39) & cboSection.Value & Chr(39)
cboSubSection.Requery

If cboSection = "Audits" Then
'default to ANICO and set RowSource for cboAuditNo
cboSubSection.Value = "ANICO"


cboAuditNo.BackColor = 16777215
cboAuditNo.RowSourceType = "Table/Query"
cboAuditNo.RowSource = "SELECT A.[Number] & B.[ID] AS AuditKey, A.Number, Acronym, Audits" & _
" FROM [Tbl: Company Numbers and Acronyms] as A INNER JOIN [Tbl: Audits] as B ON A.Number = B.Number" & _
" WHERE [Year Completed] = 0 AND A.[Number]='101'" & _
" ORDER BY Audits, A.[Number] ASC"
cboAuditNo.Requery

'enable section
cboAuditNo.Enabled = True
txtAuditSubsidiary.Enabled = False
txtAuditProject.Enabled = False
cboPhases.Enabled = True
Else
'clear cboSubSection
cboSubSection.Value = Empty

cboAuditNo.BackColor = 16777088
'disable section
cboAuditNo.Enabled = False
txtAuditSubsidiary.Enabled = False
txtAuditProject.Enabled = False
cboPhases.Enabled = False

If cmdSaveRecord.Caption = "Update" Then

Else
'clear section
cboAuditNo = Empty
txtAuditSubsidiary = Empty
txtAuditProject = Empty
cboPhases = Empty
End If

End If

End Sub

Here is a nice trick to change an actual query code at runtime from a dynamically constructed SQL statement. After the code is executed you can go to the query section and open it up and see the query is now the statement you passed at runtime.

Sub UpdateQDefBiWeekRpt(sSQL As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryRptPeriod")

qdf.SQL = sSQL

Set qdf = Nothing
Set db = Nothing

End Sub

Sub UpdateQDefDynamic(sSQL As String, sQdef As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs(sQdef)

qdf.SQL = sSQL

Set qdf = Nothing
Set db = Nothing

End Sub

This is all old DAO and I don't know if you are building your app base on ADO.NET, ADP, or other.

I program in ASP.NET from time to time and find my answers in asp.net website forums.

Cheers.