Easy Question (I think) 1

[JJOHNS]

This should be an easy question, but I don't know how to do it and haven't found it in a book yet...

I have a form that is a shift report. If someone wants to go back and edit their report, they enter the date, unit, and shift to open the form to the correct report.

Right now, if the person enters information that is not found, the form will open to a new blank form.

Instead, I want to give the user a warning message (That report not found) so they can go back and correct the entry.

Easy, right?

 

[nickjar2]

Hi,

The way I normally do this is to run some SQL containing the criteria entered by the user. Check whether any recs were returned, if not, inform user, and cancel opening the report.
I may have misunderstood the Q. Are u asking how to check all the reports in your database for an exact match to the criteria added? If so, this may work:

dim db as database
dim rpt as report

set db = currentdb

for each rpt in db.reports
if rpt.name = "some criteria" then
docmd.openreport "reportname"
else
msgbox "report doesn't exist"
end if
next rpt Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[JJOHNS]

Nick,

Thanks for the help, but I'm not sure I'm understanding how to use this code. Let me be a little more clear.

My "Shift Report" is a form (not a report). It has several fields, three of which are ReportDate, Unit, and Shift. I have another form in which the person enters the date, unit and shift for the report they are interested in. A query compares the fields and a report is open.

I've been trying to change your code to make it work for me, but I'm not sure how. I know I can't dimension rpt As Report.

 

[BoxHead]

J,

To clarify, your users want to edit a record in the form's underlying table? {That word 'report' gets confusing in Access-ese.}

I'd suggest eliminating the errors. Instead of using textboxes for the Users to input ReportDate, Unit, and Shift, I'd use unbound combo boxes. I'm guessing there may be a command button (eg: CmdFilter) to run the filter event. I'd disable it on the popup form's Open event until the selections have been made.

The text below is untested and intended to impart the idea behind the suggestion, not the syntax.

cbo1.rowsource = SELECT DISTINCT ReportDate FROM YourTableName

cbo2.rowsource = SELECT DISTINCT Unit FROM YourTableName WHERE YourTableName.ReportDate = Me.cbo1

cbo3.rowsource = SELECT DISTINCT Shift FROM YourTableName WHERE YourTableName.ReportDate = Me.cbo1 AND YourTableName.Unit = Me.cbo2

On the AfterUpdate of cbo1, run

   cbo2.Requery
   cbo2 = ""
   cbo3 = ""

On the AfterUpdate of cbo2, run

   cbo3.Requery
   cbo3 = ""

On the AfterUpdate of cbo3, run

   If Not IsNull(cbo1) And Not IsNull(cbo2) And Not IsNull(cbo3) Then
     CmdFilter.Enabled = True
   Else
     CmdFilter.Enabled = False
   End If

With cbo1, the user can only select a date from dates that have records. With cbo2, they can only select a Unit if there is a record for that Unit on the date they selected in cbo1. cbo3 only allows them to select a shift that has a record from the unit in cbo2.

The '.Requery' updates the combo boxes so only appropriate selections are available. Setting the values to nothing ("&quot prevents the user from backtracking to make changes.


HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[JJOHNS]

Thanks John,

I actually already had the Unit and Shifts set up as combo boxes and the date as an unbound textbox. My coworker found another Tek-tips entry that made this really simple, though. It's done by counting the number of results of the query. If the number is >=1, then it opens the form. Otherwise the msgbox appears. It's done like this:

If DCount("[ReportDate]", "ShiftReportEditQuery&quot>= 1 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "No report to edit. Please check your entries."
End If

The idea of creating each combo box after the previous has been selected works great, though.

Thanks Again.