Replies

Could your code test for YourRecordset.EOF? (the end of file marker)

I’m not sure about the code, but … Couldn’t you use a query, that sorts the table records the way you want to see them, as the source of the form or report?

It seems as if you trying to do this in one query and that may be part of the problem. I believe you need two.

I think you can get the Open records more easily in one query by merely counting the number of records with an Is Null Close Date. Count these records by the key.

For the overdue records, you need a seperate query in which you should count the number of records by the key (even though the Due Date is required, don’t use count on it right now) and use the Where expression <= Date() in the criteria row for Due Date.

You should have two queries, each with two fields in the grid. Try it that way and see if it resolves the error. Then, if you need to do more, you can build from that.

It is difficult for me to understand exactly what you have placed in the query grid.

On the Totals row, use Is Null in the criteria row for Close Date and set the Totals row to Where for that field. Use <=[Due Date] in a second criteria row for Close Date.You seem to be using AND instead of OR and a record cannot meet both criteria. Make sure you are counting on a field that is present in every record, like the key. Also, turn off the show box for the Close Date field.

If this doesn't help, please define what is in the grid more clearly and give us the error message.

It’s not clear to me whether you want to see all the records or just the maximum date record. Nonetheless …

Instead of basing the form on the table, have you tried basing the form on a query for that table, sorted by date descending? This would show all the records in the order you want.

If you really wanted just the maximum date record, post back .

When you say the users are entering a new “line”, do you mean a new record? If so, and the Date Added field is Date/Time type, try using a Totals query, grouping on Product and using the Max function on Totals row for the Date Added field.

Charlotte: Thank you … your sample was exactly what I needed to satisfy a customer request to press Enter in a memo field (my memo field is named Notepad). I added the following code to the OnEnter event of the memo field so that a return/CurrentDate/:/space would be placed on a new line each time the user entered the field to add a subsequent note. The cursor then goes to the end of the current line, so the user can begin typing.

Private Sub Notepad_Enter()

Dim vDate As Date
Dim vColon As String

vDate = Format(Date, “Short Date”)
vColon = “: ”

Me![Notepad] = Me![Notepad] & vbCrLf & vDate & vColon
Me![Notepad].SelStart = Len(Me![Notepad]) + 1

End Sub

So far, so good. The problem is that placing the cursor at the end automatically scrolls the text box down, so that only the current date line is visible, even if there is plenty of space in the text box to see the entire contents of the memo field. The user has to scroll up to see prior notes. Is there a way to stop that from happening, that is; place the cursor at the current line without scrolling down unless it is necessary?

I can’t see your screen shot, but basically if the query is showing only the users who have a count >0, use this query (I’ll call it the Count query) in a new query. In that new query place the User table and link it to your Count query with an outer join, so that you see all User table records, regardless of their existence in the Count query. Place all the fields in the grid that you additionally need to see.
HTH
By the way, how can I see that screen shot?

Actually I just finished doing this. I do not know that this is the most elegant method, but it works. I use VB to Rename the report using the statement:

Name OldName as NewName

OldName is the (full path)name the report was given and NewName is a (full path)name that I build in VB, before this statement, which makes sense to the users.

This may be all you need, but if you would like me to post more complete code, let me know.

I believe you need to select 2 columns from your Location table in order to form the Union. Also, it is not clear to me why you Select Null. Nonetheless, here is the code I use for an All Regions choice in a combo box:

SELECT DISTINCTROW Region.[Region Code], Region.[Region Name] FROM Region UNION SELECT “*”, “All Regions” FROM Region;

HTH

I notice that the “A” and “c1″ in your code have no preceeding space, but the rest of the items do (as ” B”, ” c2″) Might that be the cause of your code only selecting the first value in each IN clause?

Sorry, I misunderstood. I did a test with the code:
SELECT * FROM YourTable WHERE ((YourTable.[YourField]) In (“Monday”,”Tuesday”,”Friday”));
and it returned records with any of the three values.
Hope this is more helpful.

In the Query Design View, try using the OR operator. In the column for your fieldname type “Monday” Or “Tuesday” Or “Friday” in the criteria row.

I’m not certain of a way to do it on every form at once, but did you try typing the text as the Caption Property of the form? The form Caption specifies the text that appears in the title bar of the Form in the Form view.
Someone else may know a better way …

Use DateDiff(“n”, Date1, Date2). The n will calculate the interval in minutes.