Solved

MS Access 2007 Query by form with combo box

Posted on 2009-05-20
18
1,726 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
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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba DCount with 2 criteria 3 34
ms/access hyperlink/ftp 7 35
Track name AutoCorrect info 14 46
Access Excel export not behaving 2 25
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

910 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

22 Experts available now in Live!

Get 1:1 Help Now