Replies

Thank you Hans and Steve for your assistance. Turns out, I apparently solved the ‘riddle’ before I got your answers. First time that’s ever happened. Here is what I came up with on my own before I read your posts:

    With Sheets("Rate Chart").Shapes.AddShape(msoShapeRectangle, 460, 270, 175, 30)
        .Name = "RateBox"
        With .TextFrame.Characters
                .Text = "Rate = " & Range("Spring_Rate_Result").Text & " lbs/in"
                .Font.Bold = True
                .Font.Name = "Verdana"
                .Font.Size = 14
        End With
        .TextFrame.HorizontalAlignment = xlHAlignCenter
        .TextFrame.VerticalAlignment = xlVAlignCenter
        .Line.Visible = 0
        .Fill.ForeColor.RGB = RGB(255, 255, 215)
    End With

Basically the same result. Thank you for your help just the same. This Lounge has been a GREAT tool for me!!

Drew

Thank you Hans and Steve for your assistance. Turns out, I apparently solved the ‘riddle’ before I got your answers. First time that’s ever happened. Here is what I came up with on my own before I read your posts:

    With Sheets("Rate Chart").Shapes.AddShape(msoShapeRectangle, 460, 270, 175, 30)
        .Name = "RateBox"
        With .TextFrame.Characters
                .Text = "Rate = " & Range("Spring_Rate_Result").Text & " lbs/in"
                .Font.Bold = True
                .Font.Name = "Verdana"
                .Font.Size = 14
        End With
        .TextFrame.HorizontalAlignment = xlHAlignCenter
        .TextFrame.VerticalAlignment = xlVAlignCenter
        .Line.Visible = 0
        .Fill.ForeColor.RGB = RGB(255, 255, 215)
    End With

Basically the same result. Thank you for your help just the same. This Lounge has been a GREAT tool for me!!

Drew

Thanks for your assistance Hans. I did finally get the task accomplished. The problems occured where the reports that were being opened had associated events tied to them “On Open”. I had to replicate those reports and forms and remove the events connected with them. It now works perfectly.

Drew

Thanks for your assistance Hans. I did finally get the task accomplished. The problems occured where the reports that were being opened had associated events tied to them “On Open”. I had to replicate those reports and forms and remove the events connected with them. It now works perfectly.

Drew

Edited by HansV to break very long lines that caused horizontal scrolling

Hans,

Thanks for taking a look at my problem. I had, by the way, already read through the other posting by CaptainKen to try and learn something from that dialog. However, I am still stuck. I am about to try something similar to his approach, but I think I need to create copies of the original forms and reports, etc. as there are events associated with them opening. I was able to get it to load each report in turn, but it’s prompting for the part number two more times for each record (this is related to the issue I mentioned in my previous sentence). The copies will then need to be edited in order to remove the associated events which are causing the part numbers to be requested again. At least that’s my guess. I figured it should be a simple loop such as the following (this is straight from the existing module):

    Do Until .EOF 'sets through recordset, creating setup sheet for each record
        If Dir("C:My Documents", vbDirectory) = "My Documents" Then
            If Dir("C:My DocumentsPackagingSpecs", vbDirectory) = "PackagingSpecs" Then
                strPartNumber = rstPackagingSpec!CompanyNo
                'assign current PartNumber to variable
                strFileName = "C:My DocumentsPackagingSpecs" & strPartNumber & ".snp"
                'create file name
                DoCmd.OpenReport "rpt,PackagingSpecForSnapshotOutput", acViewPreview, , _
                    "qry,PackagingSpecs" 'open report for current record
                DoCmd.SelectObject acReport, "rpt,PackagingSpecForSnapshotOutput"
                'select report
                DoCmd.OutputTo acOutputReport, , strOutputFormat, strFileName, False
                'create .snp file of report
                DoCmd.close acReport, "rpt,PackagingSpecForSnapshotOutput", acSaveNo
                'close report
               .MoveNext 'move to next record
            Else
                MkDir ("C:My DocumentsPackagingSpecs")
            End If
        Else
            MkDir ("C:My DocumentsPackagingSpecs")
        End If
    Loop

The code above is a slightly modified version of some code that works in a similar situation in another database. I can’t see why it won’t work in this one.

Drew

Edited by HansV to break very long lines that caused horizontal scrolling

Hans,

Thanks for taking a look at my problem. I had, by the way, already read through the other posting by CaptainKen to try and learn something from that dialog. However, I am still stuck. I am about to try something similar to his approach, but I think I need to create copies of the original forms and reports, etc. as there are events associated with them opening. I was able to get it to load each report in turn, but it’s prompting for the part number two more times for each record (this is related to the issue I mentioned in my previous sentence). The copies will then need to be edited in order to remove the associated events which are causing the part numbers to be requested again. At least that’s my guess. I figured it should be a simple loop such as the following (this is straight from the existing module):

    Do Until .EOF 'sets through recordset, creating setup sheet for each record
        If Dir("C:My Documents", vbDirectory) = "My Documents" Then
            If Dir("C:My DocumentsPackagingSpecs", vbDirectory) = "PackagingSpecs" Then
                strPartNumber = rstPackagingSpec!CompanyNo
                'assign current PartNumber to variable
                strFileName = "C:My DocumentsPackagingSpecs" & strPartNumber & ".snp"
                'create file name
                DoCmd.OpenReport "rpt,PackagingSpecForSnapshotOutput", acViewPreview, , _
                    "qry,PackagingSpecs" 'open report for current record
                DoCmd.SelectObject acReport, "rpt,PackagingSpecForSnapshotOutput"
                'select report
                DoCmd.OutputTo acOutputReport, , strOutputFormat, strFileName, False
                'create .snp file of report
                DoCmd.close acReport, "rpt,PackagingSpecForSnapshotOutput", acSaveNo
                'close report
               .MoveNext 'move to next record
            Else
                MkDir ("C:My DocumentsPackagingSpecs")
            End If
        Else
            MkDir ("C:My DocumentsPackagingSpecs")
        End If
    Loop

The code above is a slightly modified version of some code that works in a similar situation in another database. I can’t see why it won’t work in this one.

Drew

Thanks for all your help. I did actually look at the reply above and fit it into our database and it worked great!! Sorry for not thanking you earlier, I just got swamped and neglected to go back to offer my thanks.

Drew

Thanks for all your help. I did actually look at the reply above and fit it into our database and it worked great!! Sorry for not thanking you earlier, I just got swamped and neglected to go back to offer my thanks.

Drew

DOH!!!!!! (as I smack my forehead) You are brilliant!! Thanks!! That was the issue. I checked the macro that was assigned to one menu item and it refered to a my PERSONAL.XLS file macro when it was stored on another partition. I did as you suggested and all of them work again.

THANKS AGAIN for your help!!

Drew

DOH!!!!!! (as I smack my forehead) You are brilliant!! Thanks!! That was the issue. I checked the macro that was assigned to one menu item and it refered to a my PERSONAL.XLS file macro when it was stored on another partition. I did as you suggested and all of them work again.

THANKS AGAIN for your help!!

Drew

(Edited by HansV to prevent horizontal scrolling.)

OK. I am going crazy. I have a query that returns the appropriate records from a table based on some mathematical criteria (i.e. the dates that I’ve already mentioned). I still can’t get the report formatted to do what I want. I’ve tried many different ideas and I either get errors or it just doesn’t return any data. Basically, I don’t know how I should be even approaching this task. Should I build a module, a function, an event procedure that runs when the report is loaded? I have no idea.

Here’s what I got and I know it isn’t right, but perhaps you can see what incorrect assumptions I am making: (BTW, I have only tried to accomplish the first
step of assigning the “SAMPLE” keyword under the appropriate time period column. I will also need the LAUNCH keyword under the appropriate column once I get this part figured out.)

Option Compare Database

Public Sub DeliveryForecastReport()

Dim db1 As Database
Dim rs1 As Recordset
Dim iSampleDueDiff As Long
Dim rd1 As Recordset

Set db1 = CurrentDb

'I know the statement below is wrong, but I don't know
'how to work with the results of the query
'The field name SampleDueDue is actually correct. The original developer used it

Set rd1 = db1.OpenRecordset("Select SampleDueDue From [qry,DeliveryForecast]")

    With rd1
    
        Do Until .EOF
        
        iSampleDueDiff = DateDiff("d", Nz(SampleDueDue, #1/1/100#), Date)
    
        Select Case iSampleDueDiff
        
            Case -60 To -30
                lblDaysAgo60.Caption = "SAMPLE"
            Case -30 To 0
                lblDaysAgo30.Caption = "SAMPLE"
            Case 0 To 30
                lblDaysAgoCurrent.Caption = "SAMPLE"
            Case 30 To 60
                lblDaysFuture30.Caption = "SAMPLE"
            Case 60 To 90
                lblDaysFuture60.Caption = "SAMPLE"
            Case 90 To 120
                lblDaysFuture90.Caption = "SAMPLE"
            Case 120 To 150
                lblDaysFuture120.Caption = "SAMPLE"
            Case 150 To 180
                lblDaysFuture150.Caption = "SAMPLE"
            Case 180 To 210
                lblDaysFuture180.Caption = "SAMPLE"
            Case 210 To 240
                lblDaysFuture210.Caption = "SAMPLE"
            Case 240 To 270
                lblDaysFuture240.Caption = "SAMPLE"
            Case 270 To 300
                lblDaysFuture270.Caption = "SAMPLE"
            Case 300 To 330
                lblDaysFuture300.Caption = "SAMPLE"
            Case 330 To 360
                lblDaysFuture330.Caption = "SAMPLE"
            Case 360 To 390
                lblDaysFuture360.Caption = "SAMPLE"
        End Select
        Loop
    End With
    
End Sub

(Edited by HansV to prevent horizontal scrolling.)

OK. I am going crazy. I have a query that returns the appropriate records from a table based on some mathematical criteria (i.e. the dates that I’ve already mentioned). I still can’t get the report formatted to do what I want. I’ve tried many different ideas and I either get errors or it just doesn’t return any data. Basically, I don’t know how I should be even approaching this task. Should I build a module, a function, an event procedure that runs when the report is loaded? I have no idea.

Here’s what I got and I know it isn’t right, but perhaps you can see what incorrect assumptions I am making: (BTW, I have only tried to accomplish the first
step of assigning the “SAMPLE” keyword under the appropriate time period column. I will also need the LAUNCH keyword under the appropriate column once I get this part figured out.)

Option Compare Database

Public Sub DeliveryForecastReport()

Dim db1 As Database
Dim rs1 As Recordset
Dim iSampleDueDiff As Long
Dim rd1 As Recordset

Set db1 = CurrentDb

'I know the statement below is wrong, but I don't know
'how to work with the results of the query
'The field name SampleDueDue is actually correct. The original developer used it

Set rd1 = db1.OpenRecordset("Select SampleDueDue From [qry,DeliveryForecast]")

    With rd1
    
        Do Until .EOF
        
        iSampleDueDiff = DateDiff("d", Nz(SampleDueDue, #1/1/100#), Date)
    
        Select Case iSampleDueDiff
        
            Case -60 To -30
                lblDaysAgo60.Caption = "SAMPLE"
            Case -30 To 0
                lblDaysAgo30.Caption = "SAMPLE"
            Case 0 To 30
                lblDaysAgoCurrent.Caption = "SAMPLE"
            Case 30 To 60
                lblDaysFuture30.Caption = "SAMPLE"
            Case 60 To 90
                lblDaysFuture60.Caption = "SAMPLE"
            Case 90 To 120
                lblDaysFuture90.Caption = "SAMPLE"
            Case 120 To 150
                lblDaysFuture120.Caption = "SAMPLE"
            Case 150 To 180
                lblDaysFuture150.Caption = "SAMPLE"
            Case 180 To 210
                lblDaysFuture180.Caption = "SAMPLE"
            Case 210 To 240
                lblDaysFuture210.Caption = "SAMPLE"
            Case 240 To 270
                lblDaysFuture240.Caption = "SAMPLE"
            Case 270 To 300
                lblDaysFuture270.Caption = "SAMPLE"
            Case 300 To 330
                lblDaysFuture300.Caption = "SAMPLE"
            Case 330 To 360
                lblDaysFuture330.Caption = "SAMPLE"
            Case 360 To 390
                lblDaysFuture360.Caption = "SAMPLE"
        End Select
        Loop
    End With
    
End Sub

It’s unlikely at best that the Sample date and the Launch date would ever fall in teh same month as the time period between these two developments would be at least 2 -6 months. However, that is a good point. Perhaps I’ll just refer to days instead of months. So, using that as an example, I would likely use:

 DateDiff("d", SampleDate, Date())  

However, I have tried to use this type of statement and I keep getting an error about attempting to assign a value of “Null” to a variable. I find the help included in Access to be limited at best. There is usually only one example for each command and that example only shows the few lines of code without showing how the variables are declared, etc. etc. This is where I need the help. I am not sure how to reference the textboxes on the form. I thought I would use something like the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim iSampleDueDiff As Integer
Dim iSampleDueDate As Date
Dim iLaunchDate As Date

iSampleDueDiff = DateDiff("d", Date, SampleDate) 

where the variable SampleDate is one of the fields in the Access database which contains the date. That’s where the code fails. How do I correctly refer to the SampleDate field in each record? What happens if there is no date in that field? How do I trap/skip over that error should this formula encounter an empty cell?

Drew (confused!)

It’s unlikely at best that the Sample date and the Launch date would ever fall in teh same month as the time period between these two developments would be at least 2 -6 months. However, that is a good point. Perhaps I’ll just refer to days instead of months. So, using that as an example, I would likely use:

 DateDiff("d", SampleDate, Date())  

However, I have tried to use this type of statement and I keep getting an error about attempting to assign a value of “Null” to a variable. I find the help included in Access to be limited at best. There is usually only one example for each command and that example only shows the few lines of code without showing how the variables are declared, etc. etc. This is where I need the help. I am not sure how to reference the textboxes on the form. I thought I would use something like the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim iSampleDueDiff As Integer
Dim iSampleDueDate As Date
Dim iLaunchDate As Date

iSampleDueDiff = DateDiff("d", Date, SampleDate) 

where the variable SampleDate is one of the fields in the Access database which contains the date. That’s where the code fails. How do I correctly refer to the SampleDate field in each record? What happens if there is no date in that field? How do I trap/skip over that error should this formula encounter an empty cell?

Drew (confused!)

Thanks for the help so far.

Can anyone advise me on the next stage of this little report. I have been asked to create a report that formats
the results of the former query into a table structure such as that shown below:

Part Number	2MonthsAgo	1MonthsAgo	ThisMonth	1MonthsAway	2MonthsAway
828222		Sample			Launch
548771	Sample			Launch

Basically, for each record returned in the query, tabulate it such that the SampleDate interval is shown as a word “SAMPLE”
under the appropriate column and the LaunchDate interval is shown as a word “LAUNCH” under the appropriate column. I
hope that is clear. Essentially it might be compared loosely to a Gantt Chart.

I was imagining a case statement for each record returned in which the appropriate textbox, in the report detail would contain
the associated keyword under the appropriate date column. So far I’ve constructed a report that has a row of unbound
textboxes for the records and the header that are associated with the appropriate interval from DATE(). That being, 2 months
ago, 1 month ago, now, 1 month away, etc.

Any advise you can provide would be most helpful!!

Drew