Solved

MS Access 2007 Query by form with combo box

Posted on 2009-05-20
18
1,720 Views
Last Modified: 2013-11-28
Hi Experts,

I have a Query by form using text boxes that works great.  When I try to turn the text boxes into combo boxes no data comes up.  I've attached the SQL statement from the query that the form uses.  I'm trying to create a combo box that grabs data from tblSecurityKeyDesignation.

FYI - I'm a big newb when it comes to this stuff :)
SELECT tblContactList.[First Name] & " " & tblContactList.[Last Name] AS Owner, tblSecurityKeyDesignation.[Key Designation], tblSecurityKeyList.[Key Number]

FROM (tblSecurityKeyDesignation INNER JOIN tblSecurityKeyList ON tblSecurityKeyDesignation.ID = tblSecurityKeyList.[Key Designation]) INNER JOIN (tblContactList INNER JOIN tblSecurityKeyLog ON tblContactList.[Employee ID] = tblSecurityKeyLog.Owner) ON tblSecurityKeyList.ID = tblSecurityKeyLog.[Key Number]

WHERE (((tblContactList.[First Name] & " " & tblContactList.[Last Name])=[Forms]![QBF_Key_Assignment]![Owner] Or [Forms]![QBF_Key_Assignment]![Owner] Is Null) AND ((tblSecurityKeyDesignation.[Key Designation])=[Forms]![QBF_Key_Assignment]![Key Designation] Or [Forms]![QBF_Key_Assignment]![Key Designation] Is Null) AND ((tblSecurityKeyList.[Key Number])=[Forms]![QBF_Key_Assignment]![Key Number] Or [Forms]![QBF_Key_Assignment]![Key Number] Is Null));

Open in new window

0
Comment
Question by:NEVAEHSIN
  • 9
  • 9
18 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24440562
SELECT NZ(C.[First Name],"") & " " & NZ(C.[Last Name],"") AS Owner, NZ(S.[Key Designation],"") AS DESIG, NZ(L.[Key Number],"") AS KEY_NO FROM (tblSecurityKeyDesignation S INNER JOIN tblSecurityKeyList L ON S.ID = L.[Key Designation]) INNER JOIN (tblContactList C INNER JOIN tblSecurityKeyLog K ON C.[Employee ID] = K.Owner) ON L.ID = K.[Key Number] WHERE NZ(C.[First Name],"") & " " & NZ(C.[Last Name],"")=IIF(fnName()="<All>",NZ(C.[First Name],"") & " " & NZ(C.[Last Name],""),fnName()) AND Nz(S.[Key Designation]"")=IIF(fnDesignation()="<All>",Nz(S.[Key Designation]""),fnDesignation())  AND Nz(L.[Key Number],"")=IIF(fnAssignment()="<All>",,fnAssignment());
And, have in a module:

Function fnName() As Variant
On Error GoTo 10
Dim Temp
'First try text box and then cbo box
Temp= Nz([Forms]![QBF_Key_Assignment]![Owner],"<All>")
''Temp= Nz([Forms]![QBF_Key_Assignment]![cboOwner],"<All>")

' check to make sure the value is being read, remove later
Debug.Print Temp & "<-- Owner"

fnName = Temp

Exit Function
10:
fnName = "<All>"

End Fuction

'=========

Function fnDesignation() As Variant
On Error GoTo 10
Dim Temp
'First try text box and then cbo box
Temp= Nz([Forms]![QBF_Key_Assignment]![Designation],"<All>")
''Temp= Nz([Forms]![QBF_Key_Assignment]![cboDesignation],"<All>")

' check to make sure the value is being read, remove later
Debug.Print Temp & "<-- Designation"

fnDesignation= Temp

Exit Function
10:
fnDesignation= "<All>"

End Fuction

'=========

Function fnAssignment() As Variant
On Error GoTo 10
Dim Temp
'First try text box and then cbo box
Temp= Nz([Forms]![QBF_Key_Assignment]![Designation],"<All>")
''Temp= Nz([Forms]![QBF_Key_Assignment]![cboDesignation],"<All>")

' check to make sure the value is being read, remove later
Debug.Print Temp & "<-- Assignment"

fnAssignment= Temp

Exit Function
10:
fnAssignment= "<All>"

End Fuction

This would allow you to see if your values from the combo boxes are read.

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24440581
correction...

SELECT NZ(C.[First Name],"") & " " & NZ(C.[Last Name],"") AS Owner, NZ(S.[Key Designation],"") AS DESIG, NZ(L.[Key Number],"") AS KEY_NO FROM (tblSecurityKeyDesignation S INNER JOIN tblSecurityKeyList L ON S.ID = L.[Key Designation]) INNER JOIN (tblContactList C INNER JOIN tblSecurityKeyLog K ON C.[Employee ID] = K.Owner) ON L.ID = K.[Key Number] WHERE NZ(C.[First Name],"") & " " & NZ(C.[Last Name],"")=IIF(fnName()="<All>",NZ(C.[First Name],"") & " " & NZ(C.[Last Name],""),fnName()) AND Nz(S.[Key Designation]"")=IIF(fnDesignation()="<All>",Nz(S.[Key Designation]""),fnDesignation())  AND Nz(L.[Key Number],"")=IIF(fnAssignment()="<All>",Nz(L.[Key Number],""),fnAssignment());
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24441138
Wow!  That's a lot of work for some crummy combo boxes...  I added the module without problems - except all your 'End Function' are 'End Fuction' - No problem though.

However, when I try to apply the sql statement to the query I get the following error:

"Syntax error (missing operator) in query expression 'NZ(C.[First Name],"") & " " & NZ(C.[Last Name],"")=IIF(fnName()="<All>",NZ(C.[First Name],"") & " " & NZ(C.[Last Name],""),fnName()) AND Nz(S.[Key Designation]"")=IIF(fnDesignation()="<All>",Nz(S.[Key Designation]""),fnDesignation())  AND Nz(L.[Key Number."

I'm super new to this stuff and this will be very difficult to try to figure out - but, I'll try - awaiting on your response.
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24441279
Figured out the query statement (I think), there were a couple missing commas.  Now where I het the search button - all records come up - not just records corresponding to the key designation I selected.

Any ideas?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24442043
in each of the functions, with the cursor at

Temp= Nz([Forms]![QBF_Key_Assignment]!...

Hit F8 and run it. It will stop at these lines and then by each F8 one step will go forward in the code window. It is necessary to see if it is able to read the intended values. It appreas it errors and then return "<All>".
We need to find the eroor(s). Also remove

'On Error GoTo 10

for now.

0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24445332
It will probably take me a day or two to go through this and try to figure it out.  Please be patient with me :)
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24445470
Do the combo boxes need specific names or controls?  I used the wizard to create them.
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24445501
I can't figure out how to run it using F8...  But, when I click my search button I get the error message 'Undefined function 'fnName' in expression.'  I only selected a designation and left the other two combo boxes blank.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24446000
re:> Do the combo boxes need specific names or controls?

        combo box name----------------------------------------v-- on your form
''Temp= Nz([Forms]![QBF_Key_Assignment]![cboDesignation],"<All>")

Change cboDesignation to whatever you have on your form.
More...

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24446027
Click at:

Temp= Nz([Forms]![QBF_Key_Assignment]!...

Press F9 to mark it and then press F8 for each step forward. my bad
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24446041
re:> 'Undefined function 'fnName'

Make sure the code is copied in a module (under module tab in database window).
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24447095
I'm having a heck of a time with this... So, instead of me wasting hours trying to figure it out and you wasting hours trying to explain it.  I've made an extremely basic accdb that kind of mimics what I'm looking for - I can't attach mine as there is too much confidential information in it.  If you can create what I'm looking for in the attached sample I'm pretty confident I will be able to apply it to my accdb without wating anymore of you time.  I've already learned a lot from what you've done, but think I can learn more from something that actually works :)

One request I have is that you don't (i don't know the terminology, but when you change the name of a table within a query to a single letter to make it easier to write the code) 'abbreviate' the tables as that really confuses me - I understand the concept, but don't understand the statements that do it.

Your expertise are greatly appreciated!
sample.accdb.txt
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24447252
I am not being able to open it. I am using Access 2003. After you have it as sample.mdb, rename it as sample.bak and then upload it.

Your point  on the alias names for the tables is valid. I used it to better understand the SQL string. It is not necessary to have it that way.

Thanks,

Mike
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24451013
file extension .bak is not in the list of acceptable file extensions.  Please let me know if you need more.
sample.mdb.txt
sample.bak.txt
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 24454671
No luck. Please zip it and then upload. Although bak is not listed, go ahead try it first. You neve know.
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 24455938
Mike,

I tried .bak and it didn't work.  To open it - after you download it simply delete the .txt extension.  Leaving .mdb.  If file extensions are not visible in your folders, go to 'tools', 'folder options', 'view' and uncheck the box beside 'Hide extensions for known file types'.  Then simply rename the file, deleting the .txt extension.

I've attached a .zip 'just in case' :)
sample.zip
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 24456189
Here you are. I learned something from you regarding txt extension.
 
Thanks,
 
Mike

sample1.mdb.txt
0
 
LVL 1

Author Closing Comment

by:NEVAEHSIN
ID: 31583691
I learned the extension thing from this website :p - Thank you for completing the sample for me!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Supress Detail 4 19
Access 2010 3 19
Search field on a form 7 14
DSum between dates 5 0
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now