By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Opening queries with command codes.

P: 3
I've got this problem here.
I'm trying to set up a form for filtering the data in access.
There's supposed to be many fields where the user can choose from and select them. When they click OK, it should open up the query for that field.
However, for mine, when they click OK, all queries are opened up.
How do I change the codes, so that it only open up the query relevant.
My current code is this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Done_Click()
  2.  
  3. If Date = False Then
  4.  
  5. Else
  6. DoCmd.OpenQuery "Date", acViewNormal, acEdit
  7. End If
  8.  
  9. If Location = "" Then
  10.  
  11. Else
  12. DoCmd.OpenQuery "Location", acViewNormal, acEdit
  13. End If
  14.  
  15.  
  16. If Company = "" Then
  17. Else
  18. DoCmd.OpenQuery "Company", acViewNormal, acEdit
  19. End If
  20.  
  21.  
  22. If [Parties_Involved] = "" Then
  23. Else
  24. DoCmd.OpenQuery "Parties_Involved", acViewNormal, acEdit
  25. End If
  26.  
  27.  
  28. If [Parties_Involved(2)] = "" Then
  29. Else
  30. DoCmd.OpenQuery "Parties_Involved(2)", acViewNormal, acEdit
  31. End If
  32.  
  33.  
  34. If [Action_by] = "" Then
  35.  
  36. Else
  37. DoCmd.OpenQuery "Action_by", acViewNormal, acEdit
  38.  
  39. End If
May 13 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Done_Click()
  2.  
  3.    If Me![Date] Then
  4.       DoCmd.OpenQuery "Date", acViewNormal, acEdit
  5.    End If
  6.  
  7.    If nz(Me!Location, "") <> "" Then
  8.       DoCmd.OpenQuery "Location", acViewNormal, acEdit
  9.    End If
  10.  
  11.    If nz(Me!Company, "") <> "" Then
  12.       DoCmd.OpenQuery "Company", acViewNormal, acEdit
  13.    End If
  14.  
  15.    If nz(Me![Parties_Involved], "") <> "" Then
  16.       DoCmd.OpenQuery "Parties_Involved", acViewNormal, acEdit
  17.    End If
  18.  
  19.    If nz(Me![Parties_Involved(2)], "") <> "" Then
  20.       DoCmd.OpenQuery "Parties_Involved(2)", acViewNormal, acEdit
  21.    End If
  22.  
  23.    If nz(Me![Action_by], "") <>"" Then
  24.       DoCmd.OpenQuery "Action_by", acViewNormal, acEdit
  25.    End If
  26.  
  27. End Sub
  28.  
May 13 '07 #2

JConsulting
Expert 100+
P: 603
I've got this problem here.
I'm trying to set up a form for filtering the data in access.
There's supposed to be many fields where the user can choose from and select them. When they click OK, it should open up the query for that field.
However, for mine, when they click OK, all queries are opened up.
How do I change the codes, so that it only open up the query relevant.
My current code is this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Done_Click()
  2.  
  3. If Date = False Then
  4.  
  5. Else
  6. DoCmd.OpenQuery "Date", acViewNormal, acEdit
  7. End If
  8.  
  9. If Location = "" Then
  10.  
  11. Else
  12. DoCmd.OpenQuery "Location", acViewNormal, acEdit
  13. End If
  14.  
  15.  
  16. If Company = "" Then
  17. Else
  18. DoCmd.OpenQuery "Company", acViewNormal, acEdit
  19. End If
  20.  
  21.  
  22. If [Parties_Involved] = "" Then
  23. Else
  24. DoCmd.OpenQuery "Parties_Involved", acViewNormal, acEdit
  25. End If
  26.  
  27.  
  28. If [Parties_Involved(2)] = "" Then
  29. Else
  30. DoCmd.OpenQuery "Parties_Involved(2)", acViewNormal, acEdit
  31. End If
  32.  
  33.  
  34. If [Action_by] = "" Then
  35.  
  36. Else
  37. DoCmd.OpenQuery "Action_by", acViewNormal, acEdit
  38.  
  39. End If

are all these parameters, if not null, supposed to apply to the same query? Seems you are possibly opening multiple queries....how does this help your user?
J
May 13 '07 #3

P: 3
I'm sorry but the code doesn't work.
Anyway, I've change the way of opening the query already.
There's this criteria [Forms]![Filter]![Date] so that the users can enter the date and they'll retrieve the records with the relevant date.
I used a drop down list for the users to choose the date from and a ok button when they've selected the date the wanted. However, when they click ok, a window asking for the parameter value appeared. How do I associate the button with the drop down list so that the date they choose in the drop down list will be the parameter value and they do not have to enter it again in the window?
May 14 '07 #4

JConsulting
Expert 100+
P: 603
I'm sorry but the code doesn't work.
Anyway, I've change the way of opening the query already.
There's this criteria [Forms]![Filter]![Date] so that the users can enter the date and they'll retrieve the records with the relevant date.
I used a drop down list for the users to choose the date from and a ok button when they've selected the date the wanted. However, when they click ok, a window asking for the parameter value appeared. How do I associate the button with the drop down list so that the date they choose in the drop down list will be the parameter value and they do not have to enter it again in the window?
What is the rowsource for your new dropdown? Your criteria looks ok. Your form name is Filter? and the name of the control is Date? (you should change that by the way, "Date" is a reserved word in Access and should be not be used as the name of any control...change it to something like cboDate...or myDate.
Also, if you have more than one field in your combo (like an ID and the date, then you want to change the Bound Column to the column with the actual date, and not the ID.

J
May 14 '07 #5

JConsulting
Expert 100+
P: 603
What is the rowsource for your new dropdown? Your criteria looks ok. Your form name is Filter? and the name of the control is Date? (you should change that by the way, "Date" is a reserved word in Access and should be not be used as the name of any control...change it to something like cboDate...or myDate.
Also, if you have more than one field in your combo (like an ID and the date, then you want to change the Bound Column to the column with the actual date, and not the ID.

J
Filter is also a reserved word...

You might find this link about Standard Naming Conventions useful

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

J
May 14 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.