Adding new objects Network error

[nick243]

Hi everyone, I have recently converted over someones code to fit my own needs. I am adding in a 2 queries, 1 form, 1 report, and 1 module. When I add the new components, it causes a network connection error. I can't quite figure out what Is causing the issue. I have had to replace the database with a copy several times. All the components are working great in the original database. The scenario is a cross-tab query feeding into an unbound report based on a form section date. What can I include to help solve my problem?



Thanks,
Nick

[June7]

Have you run Compact & Repair?

Is this a split db?

Exactly when does the error occur? After adding which object? When saving? When running C&R?

[nick243]

This what I get when I import all the objects at once. then I compacted and repaired. I tried to click a button on the main form and get the second picture. I believe this is not a split database. It has linked tables though to other databases on the network. After I click OK on the second picture. It takes me to some random blank code. The error occurs right when I open up the database. It cannot run the open form code.


Thanks,
Nick

[June7]

By 'add' components you mean you are importing them? You are importing into 2002 file? What version is the source file?

Instead of import could trying opening both dbs and manually copy/paste or drag/drop each object. See if that makes a difference.

You get the Action Failed error when opening the db?

Open with Shift Key to bypass whatever is trying to run when the db opens. Still get the Action Failed error? Open form - button click still errors.

Very weird stuff. Never encountered. But I never used Access 2002, started with 2003.

[nick243]

Actually I labeled my access wrong. I have access 2000 version here at work. Yes I am importing them. They are both .mdb files. When I hold the shift key down. It has no errors until I click on a form. I get the same error. I tried just dragging and dropping to begin with. That gave similar results. I get the action failed statement when I open the database. I will try to drag and drop again.

Thanks,
Nick

[June7]

Is the original db set to open a form by default or has AutoExec macro? If not, why would importing objects change this behavior?

Try adding one object at a time.

[nick243]

The original database that I built the new objects in has the start up display as the form. No macros that I could find. I will try to add one object at a time to figure out which object is causing the error.

[nick243]

So I was able to resolve the problem, The form I was using website linked object that losed its connection. Thanks for talking me through this. I was able to bring all the objects over but now I am having a data type mismatch. I cannot get the report to set to the defined querydef in the function. I am going to run some test but can't seem to find the error right now.

[nick243]

Here is where I am getting the issue

Private Sub Report_Open(Cancel As Integer)


    '  Create underlying recordset for report using criteria entered in
    '  EmployeeSalesDialogBox form.
    
    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form
            
    '   Don't open report if EmployeeSalesDialogBox form isn't loaded.
    If Not (IsLoaded("EmployeeSalesDialogBox1")) Then
        Cancel = True
        MsgBox "To preview or print this report, you must open " _
        & "EmployeeSalesDialogBox1 in Form view.", vbExclamation, _
        "Must Open Dialog Box"
        Exit Sub
    End If


    '  Set database variable to current database.
    Set dbsReport = CurrentDb
    Set frm = Forms!EmployeeSalesDialogBox1
    
    
    
    '  Open QueryDef object.
   '   Set qdf = dbsReport.QueryDefs("EmployeeSales")
    
      Set qdf = dbsReport.QueryDefs(Me.RecordSource)
    
    
    ' Set parameters for query based on values entered
    ' in EmployeeSalesDialogBox form.
    qdf.Parameters("Forms!EmployeeSalesDialogBox1!BeginningDate") _
        = frm!BeginningDate
    qdf.Parameters("Forms!EmployeeSalesDialogBox1!EndingDate") _
        = frm!EndingDate


    '  Open Recordset object.
    Set rstReport = qdf.OpenRecordset() 'Here is where my error is
    
    '  Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count
    
End Sub

It says rst report = to nothing

[June7]

I've never used QueryDef to set a recordset object. Since rstReport is based on qdf and qdf is based on Me.RecordSource, why not just count the fields of Me.RecordSource? How does counting fields of this dataset determine the number of columns for a CROSSTAB?

Don't understand reason for form object variable as opposed to just referencing the form directly.

Purpose of this code is to manage a CROSSTAB query as the RecordSource for a report? I never use dynamic parameterized saved query objects but might make an exception for CROSSTAB instead of modifying query object with code. I've never needed CROSSTAB in any db I built.

The procedure ends with setting a variable which I presume is declared somewhere else as a Global or Public.

How is user able to open report without the dialog form open? Do they have access to the Navigation Pane?

[nick243]

I've never used QueryDef to set a recordset object. Since rstReport is based on qdf and qdf is based on Me.RecordSource, why not just count the fields of Me.RecordSource? How does counting fields of this dataset determine the number of columns for a CROSSTAB?

Don't understand reason for form object variable as opposed to just referencing the form directly.

Purpose of this code is to manage a CROSSTAB query as the RecordSource for a report? I never use dynamic parameterized saved query objects but might make an exception for CROSSTAB instead of modifying query object with code. I've never needed CROSSTAB in any db I built.

The procedure ends with setting a variable which I presume is declared somewhere else as a Global or Public.

How is user able to open report without the dialog form open? Do they have access to the Navigation Pane?

The reason for the crosstab query to an unbound report so that I can have dynamically changing dates based on todays date in columns. It will change based on the users input. The form has to be opened before the report. The big thing that changed in the move over is the original table data. The dates changed to the current dates. I will create a button to access the form from a main form. I am ganna try to put on here a copy of my objects.

Thanks,
Nick

[June7]

Non-VBA approach for monthly dynamic crosstab:

http://www.access.hookom.net/Dynamic...rosstabRpt.htm

https://blogs.office.com/en-us/2012/...es-in-reports/

https://www.fmsinc.com/MicrosoftAcce...ort/index.html

[nick243]

Non-VBA approach for monthly dynamic crosstab:

http://www.access.hookom.net/Dynamic...rosstabRpt.htm

https://blogs.office.com/en-us/2012/...es-in-reports/

https://www.fmsinc.com/MicrosoftAcce...ort/index.html

I have seen 2 out of the 3 links before. I actually need weekly instead of monthly. Do you know of a way to modify these solutions to weekly?

Thanks
Nick

[June7]

Weekly grouping is more complicated. Do you want a week to be 7 days Sun-Sat regardless of month or year? In other words, what do you want to do about weeks that cross months and years? Also, months do not all have the same number of weeks. Can calculate the date of the first day of each week with DateAdd("d", Weekday(datefield)+1, datefield), and that ignores crossing month and year. However, that does not allow for dynamic quality in the CROSSTAB.

Calculate a week identifier with DatePart("ww", [datefield]) to use as ColumnHeader. A year seems to have 53 weeks. I tested with DatePart("ww", #12/31/2107#) with a variety of years and always get 53.

Also calculate year and month from the datefield to use as RowHeaders.

Then instead of the 12 months in the CROSSTAB, use numbers 1 through 53.

If 53 weeks does not fit your requirements will have to come up with some other algorithm to calculate weekly identifier.

Might find of interest:
https://www.accessforums.net/showthread.php?t=57472
https://www.accessforums.net/showthread.php?t=17555
https://www.accessforums.net/showthread.php?t=49247

[ArviLaanemets]

There are a lot of different week numbering systems in world. I myself use ISO weeknumber:
1. A week is always 7 days long;
2. A week starts with monday and ends with sunday;
3. A week belongs to year its Thursday belongs to (a year with most number of weekdays in this week);
4. As I need to divide weeks between months sometimes, I expanded rule 3. A week belongs to month its Thursday belongs to;
5. To avoid confusion at end/start of year, and to be able to use weeknumbers for longer periods, a weeknumber is given in format yyyy.ww;
6. To calculate weeknumber for any date, I ported an Excel formula I got from some German NG ~15 years ago into MS SQL (I use SQL Databases as back-end) as user-defined function.