Link to home
Start Free TrialLog in
Avatar of NEVAEHSIN
NEVAEHSIN

asked on

MS Access 2007 Query by form with combo box

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

Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

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
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());
Avatar of NEVAEHSIN
NEVAEHSIN

ASKER

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.
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?
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.

It will probably take me a day or two to go through this and try to figure it out.  Please be patient with me :)
Do the combo boxes need specific names or controls?  I used the wizard to create them.
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.
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...

Click at:

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

Press F9 to mark it and then press F8 for each step forward. my bad
re:> 'Undefined function 'fnName'

Make sure the code is copied in a module (under module tab in database window).
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
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
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
No luck. Please zip it and then upload. Although bak is not listed, go ahead try it first. You neve know.
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
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I learned the extension thing from this website :p - Thank you for completing the sample for me!