Replies

I noticed the problem (bug) in Access97 SR2. It works great for returning an email address. If you want the carriage returns, you could try this:

If Eval(“MsgBox(‘You have just deleted the current record.” & chr(13) & “Click “”OK”” to confirm your delete or “”Cancel”” to undo your deletion.” & chr(13) & chr(13)‘,1, ‘Test Message Box’)”) = vbOK Then

‘Do somthing here
End If

That format worked for me when getting an email address from a text box and displaying it in the message part of the message box with other text and line breaks.

Ok, problem solved. I reread the posts and tried the recalc idea (once I figured out what it did). So I stuck “Me.Recalc” in the subform’s AfterInsert right before the check and bingo…works like a charm. Thanks again for all your suggestions.

Nope, no change.

Thanks for all your help, Mark. I tried DoEvents to no avail. I also forced the total box on the subform to requery, followed by the total box on the main form, then ran the check. Still no dice. I can see the focus go briefly to the total box to perform the check then continue with the code as if the check was false. It’s just that the calculation isn’t working fast enough (and it’s not that complicated). The next record entered picks up on the check being true, but what happens if the user has only one entry? I would like for it to be instantanious (or at least seemingly)

I tried it in the AfterInsert with the same results. The check is performed correctly, from what I can see, but the problem is that the check is made before the total is calculated. Would it be possible/wise to put in a 1 or 2 second delay between entering the record and performing the check in order to allow the calulated field to, um well, calculate?

I tried the format method and got an error. Evidently, Access 97 SR2 only wants you to use format with dates.

Ok, problem solved. I ended up just putting another field in the query to test if the part number was #5.

test5: iif([part_num] “#5”,1,2)

then I just grouped on the test5 field on the report and hid the field. I made a total field in the group footer, but since I only wanted to see the total for #5 once, I hid the footer if the detail was for #5 i.e.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If test5 = 2 Then
Reports!myreport.Section(6).Visible = False
Else
Reports!myreport.Section(6).Visible = True
End If
End Sub

From there I just put a “Grand Total” field in the report footer and voila!
Thanks for the ideas. They were just the spark I needed to get back on track. I had forgotten that the user doesn’t need to see everything that’s going on…just the final product.

This is an inventory report. . Part #5 is like a generic part number for “in transit” or “in production” parts (I didn’t design nor do I have any control over the numbering scheme). The reason for leaving it out initially is to get a total “on-hand”. Then #5’s total is added to the on-hand total to get a grand total. I’ll play around with the grouping option, but I’m not sure exactly how it works. In the mean time, I created a separate query that only totals part #5. I then created a new form that has a text box that displays that total. From there, I changed the report’s control source to exclude #5 and added a text box to the footer that displays the #5 total from the form and then adds it to the total for all the others. Kind of a crude work around but it works. Now I can concentrate more on your suggestions to help automate the process.

Thanks for the link to the report sample db. I had it once before and lost it. Were it not for the “Solutions”, “QrySample”, and “ReportSample” db’s I wouldn’t be anywhere close to where I am now in my knowledge of Access.

I tried your last example and it was almost there. It placed the formula in the first cell I actually wanted to calculate. But I played around with it and came up with a solution. For anyone else who may be wandering, the code I have now is:

Dim lastcell As Integer, bcell As Integer, ecell As Integer
lastcell = ActiveSheet.UsedRange.Columns.Count
bcell = (lastcell – 8 – ActiveCell.Column)
ecell = lastcell – 6 – ActiveCell.Column

ActiveCell.FormulaR1C1 = “=sum(rc[” & bcell & “]:rc[” & ecell & “])”

This sets the formula for the current cell (which was selected previously in the code) = the sum of the cells 8 to 6 cells from the last column. So if you have a sheet with 12 columns (A thru L), if B1 is active, this code will generate the formula for cell B1 as

=SUM(D1:F1)

This way, I think, no matter how many more months are added, only the last 3 are summed, since there will always be 6 columns from the last month to the end of the “row”.

Thanks again for your help.

Hi John. Thanks for a quick response. I tried your example, but I don’t think I did it right. Here’s what I have now:

Dim lastcell, bcell, ecell As Long
lastcell = ActiveSheet.UsedRange.Columns.Count
bcell = lastcell – 8
ecell = lastcell – 6
Range(Range(“F2”), Range(“F2”).End(xlDown)).Offset(-8, 0).FormulaR1C1 = “=SUM(RC[15]:RC[17])”

Still Get the Same error. I’m not familiar with the Offset property so I don’t really know what it’s doing. I will lookk at the Help file on it in the morning and see if I can make heads or tails of it. In the mean time, are there any clarifications I can make to the question to make it more sensible?