Combining two types of functions. Select Query and DLookup

A

Ayo

I would like to write a code that performs the following action:
1. If the value in a comboBox Project Number is 19912
2. Perform a select query on comboBox Task Number and comboBox National Site
ID
3. If the value in a comboBox Project Number is not 19912
4. Perform a select query on comboBox Task Number and a DLookup () on
comboBox National Site ID

I tried the code below but it's not working. Any ideas on how to do this?

If Me.cmbProjectNumber.Value="19912" then
cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID] FROM
[InScope Table] WHERE [Project Number]='" & Me.cmbProjectNumber.Value
& "'" & " ORDER BY [National Site ID]"
cmbNationalSiteID = Null
cmbNationalSiteID.Requery
Else
cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope
Table]","[Project
Number]='" & Forms![InscopeSiteSummary]!cmbProjectNumber & "'" & " And
[Task Number]='" & Forms![InscopeSiteSummary]!cmbTaskNumber & "'"),"")
End If

Any help will be greatly appreciated.
Thanks.
 
J

Jeanette Cunningham

Ayo,
try this.
I have changed some of the quotes
and added Me.to cmbNationalSiteID
added a couple of variables to make the code easier to read

Dim strSQL as String
Dim strCriteria as String

Me.cmbNationalSiteID = Null

With Forms![InscopeSiteSummary]
strCriteria = "([Project Number]=""" & .cmbProjectNumber & """) AND
([Task Number]=""" & .cmbTaskNumber & "'")"
End With

If Me.cmbProjectNumber.Value="19912" then
strSQL= "SELECT DISTINCT [National Site ID] FROM
[InScope Table] WHERE [Project Number]=""" & Me.cmbProjectNumber & """
ORDER BY [National Site ID]"
Debug.Print strSQL
Me.cmbNationalSiteID.RowSource = strSQL
Else
Me.cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope Table]",
strCriteria), "")
End If

Jeanette Cunningham
 
A

Ayo

Thanks Jeanette. I figured out out another way to do what I wanted to do.

Jeanette Cunningham said:
Ayo,
try this.
I have changed some of the quotes
and added Me.to cmbNationalSiteID
added a couple of variables to make the code easier to read

Dim strSQL as String
Dim strCriteria as String

Me.cmbNationalSiteID = Null

With Forms![InscopeSiteSummary]
strCriteria = "([Project Number]=""" & .cmbProjectNumber & """) AND
([Task Number]=""" & .cmbTaskNumber & "'")"
End With

If Me.cmbProjectNumber.Value="19912" then
strSQL= "SELECT DISTINCT [National Site ID] FROM
[InScope Table] WHERE [Project Number]=""" & Me.cmbProjectNumber & """
ORDER BY [National Site ID]"
Debug.Print strSQL
Me.cmbNationalSiteID.RowSource = strSQL
Else
Me.cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope Table]",
strCriteria), "")
End If

Jeanette Cunningham

Ayo said:
I would like to write a code that performs the following action:
1. If the value in a comboBox Project Number is 19912
2. Perform a select query on comboBox Task Number and comboBox National
Site
ID
3. If the value in a comboBox Project Number is not 19912
4. Perform a select query on comboBox Task Number and a DLookup () on
comboBox National Site ID

I tried the code below but it's not working. Any ideas on how to do this?

If Me.cmbProjectNumber.Value="19912" then
cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID] FROM
[InScope Table] WHERE [Project Number]='" & Me.cmbProjectNumber.Value
& "'" & " ORDER BY [National Site ID]"
cmbNationalSiteID = Null
cmbNationalSiteID.Requery
Else
cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope
Table]","[Project
Number]='" & Forms![InscopeSiteSummary]!cmbProjectNumber & "'" & " And
[Task Number]='" & Forms![InscopeSiteSummary]!cmbTaskNumber & "'"),"")
End If

Any help will be greatly appreciated.
Thanks.
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Glad you got it working.

Jeanette Cunningham

Ayo said:
Thanks Jeanette. I figured out out another way to do what I wanted to do.

Jeanette Cunningham said:
Ayo,
try this.
I have changed some of the quotes
and added Me.to cmbNationalSiteID
added a couple of variables to make the code easier to read

Dim strSQL as String
Dim strCriteria as String

Me.cmbNationalSiteID = Null

With Forms![InscopeSiteSummary]
strCriteria = "([Project Number]=""" & .cmbProjectNumber & """) AND
([Task Number]=""" & .cmbTaskNumber & "'")"
End With

If Me.cmbProjectNumber.Value="19912" then
strSQL= "SELECT DISTINCT [National Site ID] FROM
[InScope Table] WHERE [Project Number]=""" & Me.cmbProjectNumber &
"""
ORDER BY [National Site ID]"
Debug.Print strSQL
Me.cmbNationalSiteID.RowSource = strSQL
Else
Me.cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope Table]",
strCriteria), "")
End If

Jeanette Cunningham

Ayo said:
I would like to write a code that performs the following action:
1. If the value in a comboBox Project Number is 19912
2. Perform a select query on comboBox Task Number and comboBox National
Site
ID
3. If the value in a comboBox Project Number is not 19912
4. Perform a select query on comboBox Task Number and a DLookup () on
comboBox National Site ID

I tried the code below but it's not working. Any ideas on how to do
this?

If Me.cmbProjectNumber.Value="19912" then
cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID]
FROM
[InScope Table] WHERE [Project Number]='" & Me.cmbProjectNumber.Value
& "'" & " ORDER BY [National Site ID]"
cmbNationalSiteID = Null
cmbNationalSiteID.Requery
Else
cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope
Table]","[Project
Number]='" & Forms![InscopeSiteSummary]!cmbProjectNumber & "'" & "
And
[Task Number]='" & Forms![InscopeSiteSummary]!cmbTaskNumber &
"'"),"")
End If

Any help will be greatly appreciated.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

diop jim

je vous trouve tous sympa

Jeanette Cunningham said:
Glad you got it working.

Jeanette Cunningham

Ayo said:
Thanks Jeanette. I figured out out another way to do what I wanted to do.

Jeanette Cunningham said:
Ayo,
try this.
I have changed some of the quotes
and added Me.to cmbNationalSiteID
added a couple of variables to make the code easier to read

Dim strSQL as String
Dim strCriteria as String

Me.cmbNationalSiteID = Null

With Forms![InscopeSiteSummary]
strCriteria = "([Project Number]=""" & .cmbProjectNumber & """) AND
([Task Number]=""" & .cmbTaskNumber & "'")"
End With

If Me.cmbProjectNumber.Value="19912" then
strSQL= "SELECT DISTINCT [National Site ID] FROM
[InScope Table] WHERE [Project Number]=""" & Me.cmbProjectNumber &
"""
ORDER BY [National Site ID]"
Debug.Print strSQL
Me.cmbNationalSiteID.RowSource = strSQL
Else
Me.cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope
Table]",
strCriteria), "")
End If

Jeanette Cunningham

I would like to write a code that performs the following action:
1. If the value in a comboBox Project Number is 19912
2. Perform a select query on comboBox Task Number and comboBox
National
Site
ID
3. If the value in a comboBox Project Number is not 19912
4. Perform a select query on comboBox Task Number and a DLookup () on
comboBox National Site ID

I tried the code below but it's not working. Any ideas on how to do
this?

If Me.cmbProjectNumber.Value="19912" then
cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID]
FROM
[InScope Table] WHERE [Project Number]='" &
Me.cmbProjectNumber.Value
& "'" & " ORDER BY [National Site ID]"
cmbNationalSiteID = Null
cmbNationalSiteID.Requery
Else
cmbNationalSiteID=Nz(DLookup("[National Site ID],"[Inscope
Table]","[Project
Number]='" & Forms![InscopeSiteSummary]!cmbProjectNumber & "'" & "
And
[Task Number]='" & Forms![InscopeSiteSummary]!cmbTaskNumber &
"'"),"")
End If

Any help will be greatly appreciated.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Furstrated!!! Help. 3
Can't understand the error message 6
set query crieria by reference of form control in access project 1
dlookup and dao.recordset 4
How to select the first combobox item (ie. Set Combobox.ListIndex = 0) 2
DLookup vs. query result 6
What is the best way to query data and put into a report? 2
Sum of Totals Query 0

Top