Full Version: Need some form advise
UtterAccess Forums > Microsoft® Access > Access Forms
austenr
I have been asked to yet again revise this form. Currently there are two option boxes and numerous combo boxes. What the client wants now is for the option boxes and combo boxes to work either independently or as one.
o lets say in the top left option box you want to select Division Projects and thats all. Then have a button that is currently there that will bring up just those projects. Or, if you want Division Projects that are Completed projects which is in the right option box have those two work in conjunction.
Lastly, be able to have any of the two option boxes or both interact with the combo boxes below. I really have no idea how hard this will be. I have 1 week to do it. Any guidance or suggestions are greatly appreciated.
Sample DB attached with just the form in question contained within.
pere_de_chipstick
Try:
CODE

im stLinkcriteria As String
Dim strSQL As String
If Me![Frame66] < 3 Then stLinkcriteria = "ProjectType = " & Me![Frame66]
If Me![Frame79] < 3 Then
    If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
    stLinkcriteria = stLinkcriteria & " Complete = " & Me![Frame79] - 2
End If
DoCmd.OpenForm "frmProject", , , stLinkcriteria
If stLinkcriteria <> "" Then strWhere = "WHERE (" & stLinkcriteria & ")"
'Then for [color="red"]  EACH [/color] Combo box:
strSQL = "Row Source SQL For ComboBox  " & strWhere
Me![ComboBox].RowSource = strSQL
Me![ComboBox].Requery
Me![ComboBox] = Null

"frmProject"should be bound to the table 'Project' without any criteria

HTH

Edited by: pere_de_chipstick on Wed May 28 18:20:56 EDT 2008.
austenr
Errors out on the following line
CODE
Me![ComboBox].RowSource = strSQL

Says it cant find the combo box
pere_de_chipstick
Hi
Sorry I wasn't clear; where it says
Me![ComboBox].RowSource = strSQL
Then you need to substitute the name for each of your combo boxes e.g.
Me![Combo48].RowSource = strSQL
Me![Combo50].RowSource = strSQL
Me![Combo52].RowSource = strSQL
Me![Combo53].RowSource = strSQL
Me![Combo54].RowSource = strSQL
Me![Combo55].RowSource = strSQL
Me![Combo63].RowSource = strSQL
Has appropriate for each combo box (as well as the .requery and '= Null' statements)
The line "Row Source SQL For ComboBox " would be the row source in each for these combo boxes, and (obviously) is different for each one.
You will have to ensure that the tables in the row source for each combo box contain the fields that appear in the strWhere statement to allow it to filter corectly.
HTH
austenr
ok thanks. Will post back with any other questions. Thanks.
austenr
Now its asking for a parameter for Completed Projects.
austenr
Current code:
!--c1-->
CODE
Dim stLinkcriteria As String
Dim strSQL, strwhere As String
If Me![Frame66] < 3 Then stLinkcriteria = "ProjectType = " & Me![Frame66]
If Me![Frame79] < 3 Then
    If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
    stLinkcriteria = stLinkcriteria & " Complete = " & Me![Frame79] - 2
End If
DoCmd.OpenForm "frmProject", , , stLinkcriteria
If stLinkcriteria <> "" Then strWhere = "WHERE (" & stLinkcriteria & ")"
strSQL = "Row Source SQL For ComboBox  " & strWhere
Me![ComboBox48].RowSource = strSQL
Me![ComboBox48].Requery
Me![ComboBox50].RowSource = strSQL
Me![ComboBox50].Requery
Me![ComboBox52].RowSource = strSQL
Me![ComboBox52].Requery
Me![ComboBox53].RowSource = strSQL
Me![ComboBox53].Requery
Me![ComboBox54].RowSource = strSQL
Me![ComboBox54].Requery
Me![ComboBox55].RowSource = strSQL
Me![ComboBox55].Requery
Me![ComboBox63].RowSource = strSQL
Me![ComboBox63].Requery

Took out the null after the requery because it deletes the info from the control boxes.
pere_de_chipstick
Hi austenr

You need to construct the SQL string for each of the combo boxes;

"Row Source SQL For ComboBox " meant the row source for each combo box e.g.

for Me![ComboBox48] the current row source =
"SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName FROM qlkpContactsForSelectProject ORDER BY qlkpContactsForSelectProject.ContactName;"

so your code for ComboBox48 might read

strSQL ="SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName FROM qlkpContactsForSelectProject ORDER BY qlkpContactsForSelectProject.ContactName " & strWhere

(Note that the ';' from the end of the Combo Box row source sql has been removed)

Only this would not work because qlkpContactsForSelectProject will not contain the other fields that the strWhere string will be using as a criteria; therefore you must build the FROM clause of the SQL to include the tables with these fields in them.

Now I cannot construct the SQL strings for you as there are no tables in the download. However, as an example, the SQL for Combo48 might:

SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName
FROM qlkpContactsForSelectProject INNER JOIN tblProjects ON qlkpContactsForSelectProject.ContactID = tblProjects.ContactID
Order

You need to construct the SQL string for each of the combo boxes;

"Row Source SQL For ComboBox " meant the row source for each combo box e.g.

for Me![ComboBox48] the current row source =
"SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName FROM qlkpContactsForSelectProject ORDER BY qlkpContactsForSelectProject.ContactName;"

so your code for ComboBox48 might read

strSQL ="SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName FROM qlkpContactsForSelectProject ORDER BY qlkpContactsForSelectProject.ContactName " & strWhere

(Note that the ';' from the end of the Combo Box row source sql has been removed)

Only this would not work because qlkpContactsForSelectProject will not contain the other fields that the strWhere string will be using as a criteria; therefore you must build the FROM clause of the SQL to include the tables with these fields in them.

Now I cannot construct the SQL strings for you as there are no tables in the download. However, as an example, the SQL for Combo48 might:

SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName
FROM qlkpContactsForSelectProject INNER JOIN tblProjects ON qlkpContactsForSelectProject.ContactID = tblProjects.ContactID
ORDER BY qlkpContactsForSelectProject.ContactName;

where tblProjects is the list of projects and will contain the fields "ProjectType" and "Complete" for the criteria and "ContactID" to allow it to link to the table qlkpContactsForSelectProject

You will need to construct a query for each of the 7 combo boxes in your form.

Finally the command
Me![ComboBox] = Null
Is quite important; when you change the row source SQL and requery the combo box, the existing selection for the combo box may no longer be a valid setting for the specified criteria. However the combo box will still be set to that value therefore you should always reset the Combo box to null after changing the SQL and requerying it.

HTH
pere_de_chipstick
Hi austenr
Just looking at this, the strWhere clause should come before the Order By statement therefore when you construct the SQL you should have (for ComboBox48):
strSQL = "SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName " & _
"FROM qlkpContactsForSelectProject INNER JOIN tblProjects ON qlkpContactsForSelectProject.ContactID = tblProjects.ContactID " & _
strWhere & _
" ORDER BY qlkpContactsForSelectProject.ContactName; "
austenr
Sorry for the late response. Thanks and will give it a test.
austenr
Here is my query for the combo48 box:
!--c1-->
CODE
Me![Combo48].RowSource = strSQL = "SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName " & _
"FROM qlkpContactsForSelectProject INNER JOIN Project ON qlkpContactsForSelectProject.ContactID = Project.ContactID " & _
strwhere & _
" ORDER BY qlkpContactsForSelectProject.ContactName; "

Also when I attempt to run the selections and the form is trying to open I get an error on this line
CODE
DoCmd.OpenForm "frmProject", , , stLinkcriteria

THere is my code as it stands now.
[code]
Private Sub Command96_Click()
Dim stLinkcriteria As String
Dim strSQL, strwhere As String
If Me![Frame66] < 3 Then stLinkcriteria = "ProjectType = " & Me![Frame66]
If Me![Frame79] < 3 Then
If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
stLinkcriteria = stLinkcriteria & " Complete = " & Me![Frame79] - 2
End If

DoCmd.OpenForm "frmProject", , , stLinkcriteria
If stLinkcriteria <> "" Then strwhere = "WHERE (" & stLinkcriteria & ")"
strSQL = "Row Source SQL For ComboBox " & strwhere
Me![Combo48].RowSource = strSQL = "SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName " & _
"FROM qlkpContactsForSelectProject INNER JOIN Project ON qlkpContactsForSelectProject.ContactID = Project.ContactID " & _
strwhere & _
" ORDER BY qlkpContactsForSelectProject.ContactName; "

Me![Combo48].Requery
Me![Combo50].RowSource = strSQL
Me![Combo50].Requery
Me![Combo52].RowSource = strSQL
Me![Combo52].Requery
Me![Combo53].RowSource = strSQL
Me![Combo53].Requery
Me![Combo54].RowSource = strSQL
Me![Combo54].Requery
Me![Combo55].RowSource = strSQL
Me![Combo55].Requery
Me![combo63].RowSource = strSQL
Me![combo63].Requery
End Sub
[code]
If I can get the first query to work and understand it I should be able to do the rest of them. Thanks
austenr
Here is the BE with the tables.
pere_de_chipstick
Hi Austen
Ysten
Your sql has got a bit mixed up! You need to change the code from 'If stLinkcriteria <> "" Then' up to and including the " ORDER BY qlkpContactsForSelectProject.ContactName; "
to read:
CODE

If stLinkcriteria <> "" Then
    strwhere = "WHERE (" & stLinkcriteria & ")"
End If
strSQL = "SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName " &   _
        "FROM qlkpContactsForSelectProject INNER JOIN Project ON qlkpContactsForSelectProject.ContactID = Project.ContactID " & _
        strwhere & _
       " ORDER BY qlkpContactsForSelectProject.ContactName; "

then comes the
Me![Combo48].RowSource = strSQL
It shouldn't read :
Me![Combo48].RowSource = strSQL = "SELECT qlkpContactsForSelectProject .....
Hope this helps on the First / Third part;
For the form; can you check that the record source of the form 'frmProject' is the table 'Project'?
HTH
pere_de_chipstick
Hi austenr:
On the stLinkcriteria string the word 'Complete' needs to be changed to 'Completed'
Hence
stLinkcriteria = stLinkcriteria & " Complete = " & Me![Frame79] - 2
should be
stLinkcriteria = stLinkcriteria & " Completed = " & Me![Frame79] - 2
pere_de_chipstick
Hi Austen
It is quite difficult to see what is going on here as (working on Combo48) there is no table with a contact ID other than 'qlkpContactsForSelectProject'.
From the Private Function BuildPredicate() ContactID appears to be [EmpProj].[EmployeeID]; Hence the SQL for combo48 should be:
CODE
strSQL = "SELECT Employee.EmployeeID, [EmployeeLast] & ', ' & [EmployeeFirst] AS Expr1 " & _
    "FROM Employee LEFT JOIN (Project RIGHT JOIN EmpProj ON Project.ProjId = EmpProj.ProjID) ON " & _  
    "Employee.EmployeeID = EmpProj.EmployeeID " & _
    strWhere  & _
    " GROUP BY Employee.EmployeeID, [EmployeeLast] & ', ' & [EmployeeFirst] " & _
    "ORDER BY [EmployeeLast] & ', ' & [EmployeeFirst];

In the Command45 button on Click event you need the following code to open form frmproject
CODE
Dim stLinkcriteria As String
If Me![Frame66] < 3 Then stLinkcriteria = "ProjectType = " & Me![Frame66]
If Me![Frame79] < 3 Then
    If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
    stLinkcriteria = stLinkcriteria & " Completed = " & Me![Frame79] - 2
End If
DoCmd.OpenForm "frmProject", , , stLinkcriteria

You should not need the Command88 button
Note that frmProjects must have its record source = 'Project'
In the Frame66 After Update event you need the code
CODE
Dim stLinkcriteria As String
Dim strSQL As String, strwhere As String
If Me![Frame66] < 3 Then stLinkcriteria = "ProjectType = " & Me![Frame66]
If Me![Frame79] < 3 Then
    If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
    stLinkcriteria = stLinkcriteria & " Completed = " & Me![Frame79] - 2
End If
If stLinkcriteria <> "" Then strwhere = "WHERE (" & stLinkcriteria & ")"
'1.Combo48
strSQL = "SELECT Employee.EmployeeID, [EmployeeLast] & ', ' & [EmployeeFirst] AS Expr1 " & _
         "FROM Employee LEFT JOIN (Project RIGHT JOIN EmpProj ON Project.ProjId = EmpProj.ProjID) ON " & _
         "Employee.EmployeeID = EmpProj.EmployeeID " & _
         strwhere & _
         " GROUP BY Employee.EmployeeID, [EmployeeLast] & ', ' & [EmployeeFirst] " & _
         "ORDER BY [EmployeeLast] & ', ' & [EmployeeFirst];"
Me![Combo48].RowSource = strSQL
Me![Combo48].Requery
Me![Combo48] = Null
' and similarly for each of the other combos
'2.Combo50
'3.Combo52
'4.Combo53
'5.Combo54
'6.Combo55
'7.Combo63

In the Frame79 After Update event you need the code
CODE
Frame66_AfterUpdate

Hope this helps
austenr
Here is the back end (tables)
austenr
Hi,
posted the tables for you.
pere_de_chipstick
Hi Austen
Yes got the tables, in your earlier post, which enabled me to construct the SQL for Combo48.
Try the code from my earlier post to see how it works and then have a go at constructing the remaining Combo box SQLs yourself. Let me know if you are need help with the rest of them.
It's midnight here so I will be off line until tomorrow, Hope it goes well. thumbup.gif
austenr
ok thanks
austenr
Ok, I think I added all of your code as suggested in the last post and am attaching it. I still cant get it to work though.
pere_de_chipstick
Hi Austen
ill take a look and get back to you later this evening (its 17.20 here!)
austenr
Thanks
pere_de_chipstick
Hi Austen
ad a quick look, the 'DoCmd.OpenForm "frmProject", , , stLinkcriteria' appears to be working correctly - can you confirm that you are happy with this?
On the setting of the combo box you have the line
Me![Combo48] = _
inserted before the strSQL = "SELECT ...."
Delete the line ' Me![Combo48] = _ '
(but make sure you retain the strSQL = "SELECT ....")
This should then make Combo48 work corectly.
Are you OK with generating the SQLs for the remainder of the Combo Boxes' row sources?
austenr
Hi,
Am not sure that is right either. The two main things I am having problems with are that if you select one person in combo48 after you select your criteria from the top two option boxes which appear to be working correctly, you get all contacts that meet the criteria in the top two option boxes. I only want the one selected.
The second thing I dont understand is why all of my data disappears from the combo boxes after you process once.
If you can get the combo box problem sorted out and the data to stay in the combo boxes, I think I can finish the rest. I've gone as far as I think I can without getting totally frustrated and giving up and I am not that kind of person to want to just throw in the towel. Thanks
pere_de_chipstick
Hi Austen;
don't understand your statment
Do you mean that the list in the combo boxes should meet the critieria from either the Completed Status OR the Division/Department but not both?
The data disappears because when you change the row source and requery the combo box the previously selected item in the combo box may no longer be a valid item in the new rtow source SQL. However the combo box value is not reset and may retain a non valid item hence the me![Combo48] = null.
You can change this to:
If Me![Combo48].ListIndex = -1 Then Me![Combo48] = Null
- which means (in english) if the current value is not in the list specified by the SQL then set the combo box to null.
austenr
Sorry for the confusion on this end. Ok So here is a scenerio. Say from the top two option boxes you choose Division Projects and Completed Projects. Now in the combo48 box from the drop down list choose Feiner, Joseph. When you execute it, only the Division Projects that are Complted by Joseph Feiner should show up in the project form.
ight now, all completed projects are showing up in addition to joseph feiner. HTH I understand the disappearing data and will use your suggestion to correct that. HTH
austenr
Update, when I choose Divisional Projects and Completed projects, my combo box data doesnt show up.
austenr
Here is my updated button code that runs all three selection choices.
CODE
Private Sub Command96_Click()
'runs all three selections
Dim stLinkcriteria As String
Dim strSQL, strwhere As String
If Me![Frame66] < 3 Then stLinkcriteria = "ProjectType = " & Me![Frame66]
If Me![Frame79] < 3 Then
    If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
    stLinkcriteria = stLinkcriteria & " Completed = " & Me![Frame79] - 2
End If
          
DoCmd.OpenForm "frmProject", , , stLinkcriteria
If stLinkcriteria <> "" Then
strwhere = "WHERE (" & stLinkcriteria & ")"
End If
strSQL = "SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName " & _
"FROM qlkpContactsForSelectProject INNER JOIN Project ON qlkpContactsForSelectProject.ContactID = Project.ContactID " & _
strwhere & _
" ORDER BY qlkpContactsForSelectProject.ContactName; "
strSQL = "SELECT Employee.EmployeeID, [EmployeeLast] & ', ' & [EmployeeFirst] AS Expr1 " & _
"FROM Employee LEFT JOIN (Project RIGHT JOIN EmpProj ON Project.ProjId = EmpProj.ProjID) ON " & _
"Employee.EmployeeID = EmpProj.EmployeeID " & _
strwhere & _
" GROUP BY Employee.EmployeeID, [EmployeeLast] & ', ' & [EmployeeFirst] " & _
"ORDER BY [EmployeeLast] & ', ' & [EmployeeFirst];"
Me![Combo48].Requery
'If Me![Combo48].ListIndex = -1 Then Me![Combo48] = Null
Me![Combo50].RowSource = strSQL
Me![Combo50].Requery
Me![Combo52].RowSource = strSQL
Me![Combo52].Requery
Me![Combo53].RowSource = strSQL
Me![Combo53].Requery
Me![Combo54].RowSource = strSQL
Me![Combo54].Requery
Me![Combo55].RowSource = strSQL
Me![Combo55].Requery
Me![combo63].RowSource = strSQL
Me![combo63].Requery
End Sub
pere_de_chipstick
Hi Austen
o cover each of your points:
1.>>Update, when I choose Divisional Projects and Completed projects, my combo box data doesnt show up. <<
Obelieve this is because you have the line
Me![Combo48] = _
in the Frame79_AfterUpdate event.
You can delete all the code in the Frame79_AfterUpdate and replace it with 'Frame66_AfterUpdate' ie should look like:
CODE
Private Sub Frame79_AfterUpdate()
    Frame66_AfterUpdate
End Sub

2.>>Right now, all completed projects are showing up in addition to joseph feiner<<
The code behind the select projects command button (Command62) does not include any references to the state of Frame66 or Frame79, so it is functioning as the code is designed - you will need to add code for the additional criteria in the command62 code.
3. Code Command96
you do not need this button/code if you have the code behind Frame66, Frame79 and Command45.
Certainly by the time you press Command45, the ComboBoxes will allready be set by the code behind Frame66
Also in the Form On Load Event add
Frame66_AfterUpdate
Of course in Frame66_AfterUpdate you will need for each combo box:
strSQL = Whatever the row source is for ComboNN (This will be different for each Combo box)
Me![ComboNN).rowSource = strSQL
Me![ComboNN).Requery
If Me![ComboNN).ListIndex = -1 Then Me![ComboNN] = Null
hope this helps
austenr
Tinkered with this most of the night trying several things. Can you see why this will not filter the records to just one person?
!--c1-->
CODE
Private Sub Command96_Click()
Stop
Dim stLinkcriteria As String
Dim strGroupBy As String
Dim strSQL, strwhere As String
If Me![Frame66] < 3 Then stLinkcriteria = "ProjectType = " & Me![Frame66]
If Me![Frame79] < 3 Then
       If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
          stLinkcriteria = stLinkcriteria & " Completed = " & Me![Frame79] - 2
End If
If Not IsNull(Me.Combo48) Then
   'Filters on Contact name
      
       strwhere = "([EmpProj].[EmployeeID] = " & Me.Combo48 & ") AND ([EmpProj].[DateOff] IS NULL) AND "
       strGroupBy = "[EmpProj].[EmployeeID], [EmpProj].[DateOff], "
   End If
DoCmd.OpenForm "frmProject", , , stLinkcriteria
'If stLinkcriteria <> "" Then strwhere = "WHERE (" & stLinkcriteria & ")"
'strSQL = "SELECT qlkpContactsForSelectProject.ContactID, qlkpContactsForSelectProject.ContactName FROM qlkpContactsForSelectProject ORDER BY qlkpContactsForSelectProject.ContactName;"
'Me![Combo48].RowSource = strSQL
'Me![Combo48].Requery
  
    
  
End Sub
pere_de_chipstick
hi Austen
The filter criteria for the employee is stated as
strwhere = "([EmpProj].[EmployeeID] = " & Me.Combo48 & ") AND ([EmpProj].[DateOff] IS NULL) AND "
However there are two problems with this:
1. When you open the form, it is opened with the filter criteria string 'stLinkcriteria'
but nowhere do you add the strwhere clause to the criteria string 'stLinkcriteria'
2. The strWhere clause above is designed as part of the complete SQL string which
is constructed for the form's record source as in the BuildPredicate sub on your form;
this ends with the code
DoCmd.OpenForm "frmProject"
Forms![frmproject].RecordSource = strSQL

however you are opening the form with a filter i.e.
DoCmd.OpenForm "frmProject", , , stLinkcriteria
The form frmProject's record source is 'Projects' and is not changed by the filter.
HAs EmpProj.EmployeeID does not appear in frmProject's record source the strWhere
statement will not work in the filter.
You need to construct the filter for the employeeID as:
CODE
If Not IsNull(Me.Combo48) Then
    'Filters on Contact name
    If stLinkcriteria <> "" Then stLinkcriteria = stLinkcriteria & " AND "
    stLinkcriteria = stLinkcriteria & " ProjID IN (SELECT EmpProj.ProjID FROM EmpProj WHERE (((EmpProj.EmployeeID)=" & Me.Combo48 & ") AND ((EmpProj.DateOff) Is Null)))"
End If

Hope this helps
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.