Replies

That teaches me a valuable lesson in NEVER work late in the night ! After waking up after a good night’s sleep, now your suggestions make a lot of sense. I was too tired to notice that you were trying to suggest that I display the minutes in the conventional way (and using Hans’ example).

Also, was too tired to notice that indeed, one bit of code was missing “txt” (txtTotalHours) and that was what caused it to act strangely.

I’ve changed both combo boxes to Fixed format, do I need to change the table properties to Fixed too? (I can’t remember if I had set the Total Hours column to Fixed or not, it is at someone’s PC right now)

Many, many thanks for your help.

This is pretty new for me (setting rst and dbs to nothing), what’s the syntax? I tried rst = Nothing and got an error.

This is pretty new for me (setting rst and dbs to nothing), what’s the syntax? I tried rst = Nothing and got an error.

Pat,

If I set both dbs and rs to Nothing at the end of the code, wouldn’t this clear out the stored values? That bit of code actually stores the dates that I enter in a form and I pull those dates from the “stored” values and put them in the reports I need.

By setting dbs and rs to Nothing at the end of the code, wouldn’t that cause the FromDate() and ToDate() to “lose” the stored values?

Hans, thanks for your tip.

Pat,

If I set both dbs and rs to Nothing at the end of the code, wouldn’t this clear out the stored values? That bit of code actually stores the dates that I enter in a form and I pull those dates from the “stored” values and put them in the reports I need.

By setting dbs and rs to Nothing at the end of the code, wouldn’t that cause the FromDate() and ToDate() to “lose” the stored values?

Hans, thanks for your tip.

Link to MSKB article provided by mod – see Help 19

I’m using Helen Feddema’s Main Form Add-In and I was trying to use a part of her code called FromDate() and ToDate() and tried to use it in a crosstab query and when I did that, now when I open the crosstab query either by itself or in a report, I can’t close the Access program. It would “reopen” to a blank Access window. Only by removing those references in the query, I can stop the behavior.

I found this Microsoft Knowledge Base Article 164455 which pretty much describes my issue. Helen’s original code had “.Close” statements, I changed it to “rst.close” and it didn’t solve it. Can anyone help me fix the code so that it would force the recordset to close so that I can quit Access?

Here’s the code that Helen wrote (original, without my pitiful attempts to fix it).

Public Function ToDate() As Date
‘Written by Helen Feddema 9/14/98
‘Last modified 7-4-2002

On Error GoTo ErrorHandler

‘Pick up To date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblInfo”, dbOpenTable)
With rst
.MoveFirst
ToDate = Nz(![ToDate], “12/31/2004”)
.Close
End With

ErrorHandlerExit:
Exit Function

ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit

End Function

Link to MSKB article provided by mod – see Help 19

I’m using Helen Feddema’s Main Form Add-In and I was trying to use a part of her code called FromDate() and ToDate() and tried to use it in a crosstab query and when I did that, now when I open the crosstab query either by itself or in a report, I can’t close the Access program. It would “reopen” to a blank Access window. Only by removing those references in the query, I can stop the behavior.

I found this Microsoft Knowledge Base Article 164455 which pretty much describes my issue. Helen’s original code had “.Close” statements, I changed it to “rst.close” and it didn’t solve it. Can anyone help me fix the code so that it would force the recordset to close so that I can quit Access?

Here’s the code that Helen wrote (original, without my pitiful attempts to fix it).

Public Function ToDate() As Date
‘Written by Helen Feddema 9/14/98
‘Last modified 7-4-2002

On Error GoTo ErrorHandler

‘Pick up To date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblInfo”, dbOpenTable)
With rst
.MoveFirst
ToDate = Nz(![ToDate], “12/31/2004”)
.Close
End With

ErrorHandlerExit:
Exit Function

ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit

End Function

John,

You have been a huge help! I have few issues that I need your help with:

1) When you said to do this: Set the Row Source for cboQuarters to 0;0;.25/;1;.5;2;.75;3 and when I set the first column width to zero, the drop-down list shows 0,1, 2, 3 instead of .25, .5, .75. Is this intentional? Also, is the slash next to .25 a typo? Also, why do I need the “hidden” values of 0,1,2,3 when I don’t need it for cboHours? (I’m interested in learning how this process works)

2) When I enter this: “Hours: 1, Minutes: 0”, the TotalHours field shows 10 hours instead of 1.0? (I’m gonna try adding a period next to 0 which will work…) (EDIT: This worked. No need to worry about it anymore.)

3) When I enter something like “Hours: 1, Minutes: .25”, the TotalHours field shows 1.25, great…Then when I advance a record then go back one record or more to correct my previous entries, it shows combo boxes as “Hours: 1, Minutes: 0” but the TotalHours field shows 1.25. I’d like for the cboMinutes to continue showing the minutes I chose rather than resetting to zero (the cboHours does not reset even if I choose something like 3).

John,

You have been a huge help! I have few issues that I need your help with:

1) When you said to do this: Set the Row Source for cboQuarters to 0;0;.25/;1;.5;2;.75;3 and when I set the first column width to zero, the drop-down list shows 0,1, 2, 3 instead of .25, .5, .75. Is this intentional? Also, is the slash next to .25 a typo? Also, why do I need the “hidden” values of 0,1,2,3 when I don’t need it for cboHours? (I’m interested in learning how this process works)

2) When I enter this: “Hours: 1, Minutes: 0”, the TotalHours field shows 10 hours instead of 1.0? (I’m gonna try adding a period next to 0 which will work…) (EDIT: This worked. No need to worry about it anymore.)

3) When I enter something like “Hours: 1, Minutes: .25”, the TotalHours field shows 1.25, great…Then when I advance a record then go back one record or more to correct my previous entries, it shows combo boxes as “Hours: 1, Minutes: 0” but the TotalHours field shows 1.25. I’d like for the cboMinutes to continue showing the minutes I chose rather than resetting to zero (the cboHours does not reset even if I choose something like 3).

John,

Thanks. I didn’t realize that Hans’ solution had assumed I would be using Date/Time format. I was using Number format for the field.

John,

Thanks. I didn’t realize that Hans’ solution had assumed I would be using Date/Time format. I was using Number format for the field.

Thanks, Hans.

One quick question. Where do I store the code for Private Sub UpdateTime()?

Thanks, Hans.

One quick question. Where do I store the code for Private Sub UpdateTime()?

Actually, I was at a loss of words. Smile.

By “column”, I meant the field in the table. There is a field called “TotalHours” and it stores hours and its fractions such as 1, 1.25, etc. and by using separate combo boxes, I’d be forced to create two fields called Hours and Minutes, making it extremely difficult for me to do calculations on these fields.

I’d rather to have one field that stores both hours and minutes, I’ve found one solution but it works only with dates. If you could help me figure out how to combine two “unbound” combo boxes into one field, that’d be great.

Actually, I was at a loss of words. Smile.

By “column”, I meant the field in the table. There is a field called “TotalHours” and it stores hours and its fractions such as 1, 1.25, etc. and by using separate combo boxes, I’d be forced to create two fields called Hours and Minutes, making it extremely difficult for me to do calculations on these fields.

I’d rather to have one field that stores both hours and minutes, I’ve found one solution but it works only with dates. If you could help me figure out how to combine two “unbound” combo boxes into one field, that’d be great.