Subform Search by Date Range and Return Main Form 2

[gimmyd]

I have a one-to-many relationship between Customers (Primary Key=ContractID) and Projects. The relationship is displayed in a tabbed page Form/subform setup. I use a separate unbound search form to filter the qryCustomers querydef for the main form. However, on the subform I have ContractStartDate and ContractExpireDate fields. Is there a way to use Between #Beginning Date# and #Ending Date# to search these subform (or its recordsource) dates by range and then return the main form with unique ContractIDs keeping in mind that there may be many Projects for each Customer. I tried to achieve this using a query that combined Customer and Project tables but of course got duplicate Customer records on the main form. Any direction would be greatly appreciated.

 

[scriverb]

If I understand your question correctly you want to be able to use a query to select the projects that fall between a date range for the subform. But, you only want the main form to have those Customers that have a project within the selected date range.

Is this what you are looking for?

If so, then you would create a query for the subform that would select the project records that fall between your begin date and the end date. This would be the recordsource of the subform form. The RecordSource of the Main form would use the Customers table and match to a list of Customer/Project records from the subform query. This would limit the Main Form to display only the customers that have records matching the Projects selected.

Post back if this is what you are looking for and I will provide you with some sample SQL for the queries.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gimmyd]

You understand perfectly what I'm trying to achieve. An SQL example would be much appreciated. Thanks for responding I had pretty much given up.

 

[scriverb]

As you are going to opening this main form through VBA code and we have to have a way to prompt for the beginning and ending dates, copy and paste the following code into a database module:

Global vStartDate as Date
Global vEndDate as Date
Function StartDate() as Date
StartDate = vStartDate
End Function
Function EndDate() as Date
EndDate = vEndDate
End Function

The SQL for the subform query should look something like this:

SELECT A.*
FROM Projects as A
WHERE (A.ContractStartDate >= BeginDate() and A.ContractStartDate <= EndDate) and (A.ContractExpireDate >= BeginDate());

This logic here assumes that you want to show all projects that any of the days of the contract falling within the promped BeginDate and EndDate. If a Project starts before the date range but ends after the beginning of the date range then it is included. Also, if a project starts anywhere within the date range then it is included. Projects that start after the end of the date range are excluded.

Now using this same query logic we should use the following SQL to select the records for the Main Form:

SELECT A.CustomerID, A.Field1, A.Field2, A.Field3, A.Field4, . . .(listing all fields needed)
FROM Customer as A INNER JOIN Project as B ON A.CustomerID = B.CustomerID
WHERE (B.ContractStartDate >= BeginDate() and B.ContractStartDate <= EndDate) and (B.ContractExpireDate >= BeginDate())
GROUP BY A.CustomerID, A.Field1, A.Field2, A.Field3, A.Field4, . . .(listing all fields listed above in the select);

Now using this above SQL for a query, this query should be the RecordSource for the Main Menu. This will set this form up as a bound form rather than unbound as you had it setup before.

Finally, we need to prompt for the date range and open the form. Put the following VBA code behind a button on the form that is making the call to open this form:

Private Sub CommandButton_Click()
On Error GoTo Err_CommandButton_Click
DoCmd.SetWarnings False
StartOver:
vStartDate = DateValue(InputBox("Enter Starting Date(i.e. mm/dd/yyyy): &quot)
vEndDate = DateValue(InputBox("Enter Ending Date(i.e. mm/dd/yyyy): &quot)
DoCmd.OpenForm "frmYour_Main_FormName"
DoCmd.SetWarnings True
Exit_CommandButton_Click:
Exit Sub
Err_CommandButton_Click:
If err = 13 then
MsgBox "The Date must be entered in the format mm/dd/yyyy. & vbCrLf & vbCrLf & "Try Again."
GoTo StartOver
Else
Resume Exit_CommandButton_Click
End If
End Sub

This code will prompt for the two dates and require that they be entered in "mm/dd/yyyy" format. If an error occurs in the entry a message will popup and force the user to start over.

Post back if you have any questions here. I have thrown a lot out here for you to digest so it may be a bit confusing and I may have made a typo or left something out. Just let me know.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gimmyd]

Bob,
I won't have time to implement until later but can see the logic of your approach and it should be good from here. I've given you a star for "opening my window." Thanks again for your time.

Jim Dalton

 

[scriverb]

Jim, thanks for the star. Please post back if you run into any snags here. Will work with you to solve any problems. Good luck.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[abinade]

scriverb,

I am trying to do something similar. I am a team leader, and am having my team members keep track of their work using an Access Database. I use a query to create a report on the logs that were entered in the last eight days (I run the report on Mondays). However, I would really like to be able to click on the Report, have Access prompt me for a date range, then display the daily logs that are in that date range. I think that the answer you provided above is really close to what I want to do, but I'm not sure how to adapt it for my specific situation. Any suggestions??

 

[scriverb]

What you are describing is really much simpler that what is described above. All we need to do is modify the criteria portion of your query.

Here is an example:

Select A.*
FROM tblYourTableName as A
WHERE A.DateField Between [Enter Begin Date: ] and [Enter End Date: ];

If you can modify your query from this great. If you need some additional help just post your query SQL back here and I will take a look. Good luck.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[abinade]

Thank you. We had already tried something similar; I'm not sure what we did wrong, but this works GREAT. You were VERY helpful!! Thanks again!

 

[abinade]

Ok. Now I am working with two forms: DailyLog and Log_sub. On DailyLog I have the end-users select their name. The following event procedure runs AfterUpdate:

Private Sub cbName_AfterUpdate()
Log_sub.Visible = True
Form_Log_sub.cbProject.RowSource = "SELECT Projects.projectName, Projects.userTypeID FROM Projects Where Projects.userTypeID = " & Form_DailyLog.cbName.ItemData(Form_DailyLog.cbName.ListIndex) & " ORDER BY Projects.projectName"
End Sub

What I want to have happen is this: When the user selects their name from cbName(on DailyLog main form), the combo box on the sub form (cbProjects) only displays projects whose userTypeID is the same as the userTypeID as the Employee.

I have an Employees table with the following fields:
-userTypeID
-EmployeeID
-name, etc. fields

I have an Projects table with the following fields:
-userTypeID
-ProjectID
-ProjectName

Does anybody see where my problem is? Any suggestions on how I might make this work?

Thanks in advance for your help!

Paul Pehrson

 

[abinade]

Let me add two things:

First: I realized this is probably in the wrong thread. Sorry about that. I meant to place it in a new thread.

Second: I know where the problem IS in the code. I just don't know how to fix it. When the user is selected, the program takes the user's Employee ID, and shows the Projects with the UserID that = the EmployeeID. I want the Projects with the UserID that equals the Employee's UserID, not the Employee's EmployeeID.

In the part of the following code that is red:

Form_Log_sub.cbProject.RowSource = "SELECT Projects.projectName, Projects.userTypeID FROM Projects Where Projects.userTypeID = " [COLOR=red]& Form_DailyLog.cbName.ItemData(Form_DailyLog.cbName.ListIndex) [/color]& " ORDER BY Projects.projectName"

I need to figure out how to make the program look at the value of cbName, (which is a list of Employees) and go to the Employee table and get the UserTypeID. Right now it is going to the Employee table and getting the EmployeeID.

Does that help illuminate my problem?

 

[scriverb]

Please display the contents of the Row Source for your Main form where you are picking the Employee. How many columns and what is in each column. Are you using a query or Table. If a query display the SQL for the query. Then we can come up with a solution to the problem.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[abinade]

The Row Source for the Employee combo box on the main form is:

SELECT Employees.EmployeeID, Employees.FullName, Employees.teamID FROM Employees WHERE Employees.teamID=7 ORDER BY Employees.FullName;

When the form is first opnened, the Employee field's visible property is set to false. The sub-form's visible property is also set to false.

The only visible field is the Team field. Users select their team name from the combo box. At that point, the employee field becomes visible, and is populated with the names of the employess on the team that was selected.

So the AfterUpdate event procedure on the Team combo box is:

Private Sub cbTeam_AfterUpdate()
cbName.RowSource = "SELECT Employees.EmployeeID, Employees.FullName, Employees.teamID FROM Employees Where Employees.teamID = " & cbTeam.ItemData(cbTeam.ListIndex) & " ORDER BY Employees.FullName"
cbName.Visible = True

Is that enough information? Thanks again (in advance) for your help!!

 

[scriverb]

If you expand the SQL that you posted for the Employee combobox to include the field userTypeID, then you can use that value in the SQL Select used for the SubForm. You see you have indicated for it to use the EmployeeID and that is not what you want to do. So, add an additional field to the SQL as follows:

SELECT Employees.EmployeeID, Employees.FullName, Employees.teamID, Employee.userTypeID FROM Employees WHERE Employees.teamID=7 ORDER BY Employees.FullName;

Now expand the properties of the combobox as follows:
Column Count: 4
Column Widths: add an addition ;0" at the end to what you have already.

Now you can modify the SQL for the SubForm as follows:

Form_Log_sub.cbProject.RowSource = "SELECT Projects.projectName, Projects.userTypeID FROM Projects Where Projects.userTypeID = " & Form_DailyLog.cbName.column(3) & " ORDER BY Projects.projectName"

Hopefully I have interpreted your posting correctly. Please post back if you have any issues that still need help.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[abinade]

Hi Bob. Thanks so much for your help.

I'm not sure where I'm supposed to modify the SQL for the SubForm, using the code you gave me.

I added it to the BeforeUpdate for the subform itself; the project list is populated, but with every entry in the project table (the list isn't limited to the userTypeID. So I tried adding the event procedure to the Click() event for the project combo box. Same result. The cb is populated, but not constrained. Am I pasting the code in the wrong location? Where is the right place?

Also: in the code you sent me, I added an "s" to the code (see red below)

SELECT Employees.EmployeeID, Employees.FullName, Employees.teamID, Employee[red]s[/red].userTypeID FROM Employees WHERE Employees.teamID=7 ORDER BY Employees.FullName;

I did this because the employee table is called "Employees"; I assume that is what you intended. Is that correct?

 

[scriverb]

First the extra "s" is just fine. That was a typo on my part. Sorry.

Secondly, after looking at your code we do have to change this a bit. You see on the main you have a subform control. That subform control has a property called Source Object. The form that you designate here has a name and also has a Record Source. To update this record source with the new SQL requires a special syntax to reference and update it properly. Now this code would be put in the AfterUpdate event procedure of the Employee combobox.

Forms![DailyLog]![Log_Sub].Form.RecordSource = "SELECT Projects.projectName, Projects.userTypeID FROM Projects Where Projects.userTypeID = " & Form_DailyLog.cbName.column(3) & " ORDER BY Projects.projectName"
Me![Log_Sub].Requery

This may take a little tweaking here as we have to make the reference to the right names. Is the subform control named and same as the Source Object form? If they are the same they this should work correctly.

Give it a try and post back.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[abinade]

I pasted this code in to the AfterUpdate() event procedure of the cbEmployee on the main form.

When I run the form, and I select an employee from the combo box, I get the following error:

Run-time error '3075':

Syntax error (missing operator) in query expression 'Projects.userTypeID ='.

When I click Debug, the first line you sent me (Forms![DailyLog]! etc.) is highlighted.

Unfortunatley, I don't know enough (yet) about Access to understand your question about the names of the name of the subform control, however, the record source of the main form is "Daily" and the record source of the sub form is "Log", though as far as I can tell, those are the tables where the informaiton from the forms is being stored. Is that what you are asking?

Paul Pehrson


P.S.: I'm off early today, so I probably won't be able to see your reply until tomorrow morning. I can't tell you how much I appreicate your help, though. I'll add a line of tribute to you in the code when this is all done!! --P.P.