Solved

MS Access 2007 Query by form with combo box

Posted on 2009-05-20
18
1,733 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.

828 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