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

How to create sub query in where clause while opening docmd.openform

P: 1
Q: How to create sub query in where clause while opening docmd.openform

-I want to display only items to resolve which will openitem - knownissue

What I have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txttodefectssolved_Click()
  2. On Error GoTo ErrorHandler
  4.         DoCmd.openForm "frm_defects", , , "[samName]= '" & Me.SameName & "'" & " AND [Status1] in('Open') and [Severity] = 'Major' And [Defecttype]='HFC'", , acDialog
  6. ExitHandler:
  7.     Exit Sub
  9. ErrorHandler:
  10.     MsgBox "Error: " & Err.Number & " Description: " & Err.Description & ".", vbInformation, pstrAppTitle
  11.     Resume ExitHandler
  12. End Sub
Mar 10 '17 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 5K+
P: 5,397
You're not telling us what you're errors are nor have you really provided enough detail about your data to be able to really help you. We may be able to salvage your question; however, you may want to look at our FAQ section on how to ask a good question (link)

The DoCmd.openForm syntax can be found here:

So that on doesn't have to hunt down the link:
DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

From the above link the WhereCondition is the same as the SQL where clause omitting the WHERE key word.

The easiest way for new users to create the static string for use in the VBA code is to use the Query editor and build a basic select query that pulls the records of interest and then switch to the SQL view of that query, copy the where clause and paste it into the VBA

Looking at the code you've posted I would pull your current where clause out and create it first as a string so that you can troubleshoot it:

Expand|Select|Wrap|Line Numbers
  1. Dim strWhereSQL As String
  2. strWhereSQL  = "[samName]= '" & Me.SameName & "'" & _
  3.    " AND [Status1] in('Open')" & _ 
  4.    " AND [Severity] = 'Major'" & _
  5.    " AND [Defecttype]='HFC'"
This string is where you will add or modify the condtion(s) to show your "knownissue" requirement; however, you have not provided enough detail to be able to tell if this information is present in one of the fields shown, [Status1], [Severity], or [Defecttype] or on some other field.

The best thing about this method is that you can insert
Debug.Print strWhereSQL immediately following the string, <ctrl><g> to open the immediate pane to review the resolved string, a vast majority of errors in these cases are a result of malformed strings...

Once you have the string modified then insert it into the open form method, notice here that I use the named parameters method writing the code as it allows you to avoid all those nasty commas, thusly:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm FormName:="frm_defects", _
  2.     WhereCondition:=strWhereSQL, _
  3.     WindowMode:=acDialog
So, take a look at the above and see what you can do with the information, post back and we'll see what we can do...

A few other little things,
the field [Status1] gives me pause, it implies that your database may lack proper normalization, you should read through: home > topics > microsoft access / vba > insights > database normalization and table structures
It's also a weird construct you have:
[Status1] in('Open')
with a single condition here it might be better to write:
[Status1]= 'Open'
however, this may depend entirely upon what the [Status1] field actually contains... just not enough detail here...

Next, please make sure that when you post scripts such as VBA or SQL that you format them using the [CODE/] tool in the post toolbar. This format will also hold the formatting for tables provided you use spaces and not [tabs].

You should also check your>Inbox, I've forwarded you a copy of a general listing of resources, tutorials, and tools that I've found useful.
Mar 11 '17 #2

Post your reply

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