Solved

MS Access 2007 Query by form with combo box

Posted on 2009-05-20
18
1,737 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
18 Comments
 
LVL 34

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 34

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 34

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 34

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
 
LVL 34

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 34

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 34

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 34

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 34

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

752 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