Please help with report and search issue

[simpsondm]

Could I please get someone to look at my database (Send e_mail address for me to e-mail)and provide me some help? Once you look at the database you will see I don’t know databases and I’m learning as I go. I have 4 problems I would like to resolve with this database (d24simpson@aol.com)

Here’s my problem or what I’m trying to do
1) Using a command button I would like to generate a report based on a input variable I enter (I put a name in a field and it then generates a report showing me all records associated with that name and only that name) One of the problems is in my staff table a person may be assigned a different job function on different projects.
2) Is it possible to make it were on my resource form when a name is entered that if that name is associated with 5 or more projects (a warning will popup saying this person has been assigned to 5 or more projects) click continue to insert name or cancel to select another name?
3) How to create a search that will pull up all records based on 1 of 2 variables being project Id or project category?
4) When I click on one of my form date field I have a pop calendar but the calendar doesn’t line up with my date fields is they’re a way to make it line up just under the field?

I left some data in my database so you can have more of an understanding of what I’m doing. Any help is and will be greatly appreciated.

Thanks
DMS

 

[dakota81]

I can tackle a few of those problems here:

1)
The easiest way I find to create a report is to use the report wizard to initially set it up then modify the layout yourself. This way all the fields are in the report and they're all named the same as the underlying field. Anywho, say you've got a textbox that you enter a company name into, named CompanyName, and the field in the report is also CompanyName. To open up the report with only records that match the given name is:

DoCmd.OpenReport "ReportName", , , "[CompanyName] = '" & [CompanyName] & "'"

2)
Say you select a company name from a combo box named cmboCompanyName, and your table is named Projects and has the field CompanyName in it. From the properties of the text box you can go to "Events" and create an event for "after update" and use the code builder. Here's a sample that should do the trick

Private Sub Command_Click()

Dim rstProjects As Recordet, strQuery As String

strQuery = "SELECT * FROM Projects WHERE CompanyName = '" & CompanyName & "'"

Set rstProjects = CurrentDb.OpenRecordset(strQuery, dbOpenSnapshot)
If Not rstProjects.EOF Then
rstProjects.MoveLast
If rstProjects.RecordCount >= 5 Then
MsgBox "Warning!"
End If
End If

End Sub


I'm running Access 97. If you've got a newer version I think you have to declare rstProjects as DAO.Recordset


3)
Have a form bound to that table either as form view or datasheet view, whichever you prefer. Then have like a check box or maybe two command buttons or something to specify which you want to filter by. Say you've got a checkbox named UseID and textboxes named ProjectID & Product. You might be able to just open up the table directly with the filter, using the OpenTable command, but I've never used that command, it's usually better to hide the tables from the user.

Have the command button code like:

If UseID = True Then
DoCmd.OpenForm "FormName", , , "ProjectID = " & ProjectID
Else
DoCmd.OpenForm "FormName", , , "Product = '" & Project & "'"
End if


4)
I've never worked with calenders.

 

[dakota81]

I don't know what I was thinking when I was ansering the second one... Make the field a text box, not a combo box, and the name being CompanyName, not cmboCompanyName, and we're creating an before_update event on a text box, not a click event on a command button, and I never read far enough to handle the message box warning.

Someone else'll have to post how to cancel the update, 'cause I'm not sure, I think you just have to do DoCmd.Cancel, but anywho, this is what the code should look like:

Private Sub CompanyName_BeforeUpdate(Cancel As Integer)

Dim rstProjects As Recordet, strQuery As String

strQuery = "SELECT * FROM Projects WHERE CompanyName = '" & CompanyName & "'"

Set rstProjects = CurrentDb.OpenRecordset(strQuery, dbOpenSnapshot)
If Not rstProjects.EOF Then
rstProjects.MoveLast
If rstProjects.RecordCount >= 5 Then
If MsgBox ("Warning Message!", vbOkayCancel) = vbCancel Then
<code to cancel change>
End If
End If
End If

End Sub

 

[Bry12345]

An alternate solution to opening a report based upon criteria. See thread703-234319

In that example, the criteria were dates, but from that you should be able to see the logic and adapt the input form for the criteria you wish. - - - -

Bryan