Sub or Function not defined error.

[Chucklez]

First I will describe what my program does. We receive a CSV file from a customer, massage the data to fit our needs (add a delivery date, consecutive number, etc.), then exports the file in a semicolon delimited format to a printer that prints the information on a 8 1/2 X 14 inch paper. Each record in the file is a seperate page.

I have just modified the access program to 'Double Up', or print 2 pages at once by creating a new file with the same basic structure as the first, except for the fact that the fields are duplicated. This will enable us to now print a 17 X 14 inch form which can be cut in half later.

When the program first enters the sub, I get a 'Sub or Function not defined' error. I have the places where this error occurs in red in my code below.

The reason for the if statement that the error occurs in, is so that when the mailing carton changes (which is based on the Zip Code), we can insert a blank peice of paper (notice the ZZZZZZZZ's). This will help speed up the process of sorting.

I have almost beat my head silly against the wall, and I cant figure it out. Hopefully someone here can tell me what I am doing wrong. Im sorry this post is so long, but I wanted to give you the basic idea of what I am trying to do.

Thanks in advance.

Public Sub Populate_2Up_Table()
Dim DBS As DAO.Database
Dim rst1Up As DAO.Recordset
Dim rst2Up As DAO.Recordset
Dim strTray As String

On Error GoTo Error_Process_2Up
Set DBS = CurrentDb()
Set rst1Up = DBS.OpenRecordset(txtTable, dbOpenDynaset, dbSeeChanges)
Set rst2Up = DBS.OpenRecordset("TwoUp" & txtTable, dbOpenDynaset, dbSeeChanges)

rst1Up.MoveFirst
strTray = rst1Up!field3
Do While rst1Up.EOF <> True
If strTray <> rst1Up!field3 Then Even_Tray_Break
rst2Up.AddNew
rst2Up!DG1 = rst1Up!field1
rst2Up!AutoZip1 = rst1Up!field2
rst2Up!Tray1 = rst1Up!field3
rst2Up!Empty1 = rst1Up!field4
rst2Up!First1 = rst1Up!field5
rst2Up!Last1 = rst1Up!field6
rst2Up!Address1 = rst1Up!field7
rst2Up!City1 = rst1Up!field8
rst2Up!State1 = rst1Up!field9
rst2Up!Zip1 = rst1Up!field10
rst2Up!Barcode1 = rst1Up!field11
rst2Up!Year1 = rst1Up!field13
rst2Up!DelDate1 = rst1Up!DeliverDate
rst2Up!CustID1 = rst1Up!CustomerID
rst2Up!CutOffDate1 = rst1Up!CutOffDate
rst1Up.MoveNext
' If strTray <> rst1Up!field3 Then Odd_Tray_Break
rst2Up!DG2 = rst1Up!field1
rst2Up!AutoZip2 = rst1Up!field2
rst2Up!Tray2 = rst1Up!field3
rst2Up!Empty2 = rst1Up!field4
rst2Up!First2 = rst1Up!field5
rst2Up!Last2 = rst1Up!field6
rst2Up!Address2 = rst1Up!field7
rst2Up!City2 = rst1Up!field8
rst2Up!State2 = rst1Up!field9
rst2Up!Zip2 = rst1Up!field10
rst2Up!Barcode2 = rst1Up!field11
rst2Up!Year2 = rst1Up!field13
rst2Up!DelDate2 = rst1Up!DeliverDate
rst2Up!CustID2 = rst1Up!CustomerID
rst2Up!CutOffDate2 = rst1Up!CutOffDate
rst2Up.Update
rst1Up.MoveNext
Loop

Exit Sub

Even_Tray_Break:
rst2Up.AddNew
rst2Up!DG1 = "ZZZZZZZZ"
rst2Up!AutoZip1 = "ZZZZZZZZ"
rst2Up!Tray1 = "ZZZZZZZZ"
rst2Up!Empty1 = "ZZZZZZZZ"
rst2Up!First1 = "ZZZZZZZZ"
rst2Up!Last1 = "ZZZZZZZZ"
rst2Up!Address1 = "ZZZZZZZZ"
rst2Up!City1 = "ZZZZZZZZ"
rst2Up!State1 = "ZZZZZZZZ"
rst2Up!Zip1 = "ZZZZZZZZ"
rst2Up!Barcode1 = "ZZZZZZZZ"
rst2Up!Year1 = "ZZZZZZZZ"
rst2Up!DelDate1 = "ZZZZZZZZ"
rst2Up!CustID1 = "ZZZZZZZZ"
rst2Up!CutOffDate1 = "ZZZZZZZZ"
rst2Up!DG2 = "ZZZZZZZZ"
rst2Up!AutoZip2 = "ZZZZZZZZ"
rst2Up!Tray2 = "ZZZZZZZZ"
rst2Up!Empty2 = "ZZZZZZZZ"
rst2Up!First2 = "ZZZZZZZZ"
rst2Up!Last2 = "ZZZZZZZZ"
rst2Up!Address2 = "ZZZZZZZZ"
rst2Up!City2 = "ZZZZZZZZ"
rst2Up!State2 = "ZZZZZZZZ"
rst2Up!Zip2 = "ZZZZZZZZ"
rst2Up!Barcode2 = "ZZZZZZZZ"
rst2Up!Year2 = "ZZZZZZZZ"
rst2Up!DelDate2 = "ZZZZZZZZ"
rst2Up!CustID2 = "ZZZZZZZZ"
rst2Up!CutOffDate2 = "ZZZZZZZZ"
rst2Up.Update
rst1Up.MoveNext
strTray = rst1Up!field3
Resume Next

Odd_Tray_Break:
rst2Up!DG2 = "ZZZZZZZZ"
rst2Up!AutoZip2 = "ZZZZZZZZ"
rst2Up!Tray2 = "ZZZZZZZZ"
rst2Up!Empty2 = "ZZZZZZZZ"
rst2Up!First2 = "ZZZZZZZZ"
rst2Up!Last2 = "ZZZZZZZZ"
rst2Up!Address2 = "ZZZZZZZZ"
rst2Up!City2 = "ZZZZZZZZ"
rst2Up!State2 = "ZZZZZZZZ"
rst2Up!Zip2 = "ZZZZZZZZ"
rst2Up!Barcode2 = "ZZZZZZZZ"
rst2Up!Year2 = "ZZZZZZZZ"
rst2Up!DelDate2 = "ZZZZZZZZ"
rst2Up!CustID2 = "ZZZZZZZZ"
rst2Up!CutOffDate2 = "ZZZZZZZZ"
rst2Up.Update
GoTo Even_Tray_Break
Resume Next

Error_Process_2Up:
MsgBox Err.Number & Err.Description
Exit Sub
End Sub

 

[TheAceMan1]

How are ya Chucklez . . . .

To my knowledge the DAO [blue]object.OpenRecordset[/blue] does not have a type [blue]dbSeeChanges[/blue]. Try [purple]dbOpenDynaset[/purple] . . . .

Also [blue]txtTable[/blue] is a control on the form. Yes?

See Ya! . . . . . .

 

[Kleinen]

It should be:
If strTray <> rst1Up!field3 Then GoTo Even_Tray_Break

 

[CajunCenturion]

The following line of code:
[tt]
If strTray <> rst1Up!field3 Then Even_Tray_Break
[/tt]
is trying to invoke Even_Tray_Break as either a Sub or Function, but it does not exist. Even_Tray_Break is a Program Label, and you cannot "call" to a Program Lable, but as Kleinen suggests, you can "GoTo" label.

That will lead to another error in your case, that being "Resume without Error" because you cannot Resume without first encountering a trappable error. It appears that you are trying to use the Resume statement as a Return statement and that it not what Resume does.

You need to take both the Even and Odd Tray Break sections and turn them into separate subs, and either pass the recordsets into them as parameters, or declare the recordsets in the declarations section of this module or object.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TheAceMan1]

Chucklez . . . . .

There's alot wrong with the code here. I extracted/consolidated the flow of the code for a better view. Below is what it looks like. Note: in the line [blue]' If strTray <> rst1Up!field3 Then Odd_Tray_Break[/blue], I assume the hyphen [blue]'[/blue] is a typo!?

[blue]Public Sub Populate_2Up_Table()
      
On Error GoTo Error_Process_2Up
   rst1Up.MoveFirst
   strTray = rst1Up!field3
   
   Do While rst1Up.EOF <> True
      [purple][b]If[/b][/purple] strTray <> rst1Up!field3 Then [purple][b]Even_Tray_Break[/b][/purple]
         rst2Up.AddNew
         [green]'Add rst2Up Fields1 Data[/green]
         rst1Up.MoveNext
         
         [purple][b]If[/b][/purple] strTray <> rst1Up!field3 Then [purple][b]Odd_Tray_Break[/b][/purple]
         [green]'Add rst2Up Fields2 Data[/green]
         rst2Up.Update
         rst1Up.MoveNext
   Loop
   
   Exit Sub
   
Even_Tray_Break:
      rst2Up.AddNew
      [green]'Add rst2Up Fields1 Data "ZZZZZZZZ"[/green]
      rst2Up.Update
      rst1Up.MoveNext
      strTray = rst1Up!field3
      [purple][b]Resume Next[/b][/purple]
      
Odd_Tray_Break:
      [green]'Add rst2Up Fields2 Data "ZZZZZZZZ"[/green]
      rst2Up.Update
      GoTo Even_Tray_Break [purple][b]'Sure you want to do this?[/b][/purple]
      [purple][b]Resume Next 'Never Exexuted[/b][/purple]
      
Error_Process_2Up:
   MsgBox Err.Number & Err.Description
   [purple][b]Exit Sub 'Not Needed![/b][/purple]
End Sub[/blue]

To start, [purple]If[/purple] strTray <> rst1Up!field3 Then [purple]Even_Tray_Break[/purple] and [purple]If[/purple] strTray <> rst1Up!field3 Then [purple]Odd_Tray_Break[/purple] are both referencing line labels without the [blue]Goto Statement[/blue]. In this way the [blue]exact error you received[/blue] will be raised (Sub or Function not defined). I have no idea why the compiler did'nt stop here first. This could be fixed by adding [purple]Goto[/purple] for the LineLabels, but don't bother, there are other problems.

The [blue]If[/blue] statements have no corresponding [blue]End If[/blue] and will raise another compile error.

Now . . . . . I understand your intent in using [blue]Resume Next[/blue], but its not gonna work this way. Instead, you should have [blue]Goto ReturnLabel[/blue], but don't bother. There are flow errors even if [blue]Resume Next[/blue] did work. There are several routes through the code that result in two [blue]rst2Up.Update[/blue] (assuming Resume Next is relaced with an equavilent [blue]Goto ReturnLabel[/blue]).

What you need to do, is sit down, take your time, and layout the logic/schema of what you want to do here. What ever you do, use [blue]Structured Control statements[/blue] to keep from hopping all over the place. If ya have any trouble, come back with the schema, and we'll get ya thru it.

See Ya! . . . . . .

 

[Chucklez]

I figured it out last night. I forgot to add the [blue]GoTo[/blue] statement as several of you advised. After I got past the sub/function error the code syntax was correct, but there was a logic error as theAceMan had suggested. I had to add a label to my main processing body (Process_Records), and had to switch the odd and even tray break routines around. Other than that, the routine worked perfectly. I also know that once the program finishes the [purple]Odd_Tray_Break:[/purple] Routine, it will fall thru to the Even_Tray_Break: Routine. This is what I want to accomplish. This way a break will occur on both sides of the record if rst1Up!field3 changes.

AceMan, You can have a IF statement without a EndIf, If the If is followed by a Then. Such as I have below. Also, [blue]txtTable[/blue] is a text box on the form that is populated once a value in a listbox is dblclicked.

CajunCenturion, I did get the [red]Resume Without Error[/red] Error, and removed the [blue]Resume[/blue] statements. You are correct in that.

   Dim DBS                  As DAO.Database
   Dim rst1Up               As DAO.Recordset
   Dim rst2Up               As DAO.Recordset
   Dim strTray              As String

   On Error GoTo Error_Process_2Up
   Set DBS = CurrentDb()
   Set rst1Up = DBS.OpenRecordset(txtTable, dbOpenDynaset, dbSeeChanges)
   Set rst2Up = DBS.OpenRecordset("TwoUp" & txtTable, dbOpenDynaset, dbSeeChanges)

   rst1Up.MoveFirst
   strTray = rst1Up!field3
   GoTo Process_Records
   
[COLOR=purple]Process_Records: [/color]
   Do While rst1Up.EOF <> True
      If rst1Up!field3 <> strTray Then GoTo Even_Tray_Break
      rst2Up.AddNew
[COLOR=green]        'Add rst2Up Fields1 Data[/color]
      rst1Up.MoveNext
         If strTray <> rst1Up!field3 Then GoTo Odd_Tray_Break
[COLOR=green]        'Add rst2Up Fields1 Data [/color]
      rst2Up.Update
      rst1Up.MoveNext
   Loop
   MsgBox ("Processing Complete")
   Exit Sub
   
[COLOR=purple]Odd_Tray_Break:[/color]
      rst2Up.AddNew
[COLOR=green]        'Add Tray Break Data[/color]
      rst2Up.Update
      GoTo Even_Tray_Break

[COLOR=purple]Even_Tray_Break:[/color]
      rst2Up.AddNew
[COLOR=green]        'Add Tray Break Data[/color]
      rst2Up.Update
      strTray = rst1Up!field3
      GoTo Process_Records
      
[COLOR=purple]Error_Process_2Up:[/color]
   If Err.Number = 3021 Then rst2Up.Update
   Exit Sub
End Sub

 

[CajunCenturion]

Chucklez - I am happy for you that you got around these issues. However, if you intend to become a professional programmer, then you need to learn the basic concepts of Structured Programming. There is a reason why these techniques were developed and they will save you considerable anguish, time, and money if used properly.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TheAceMan1]

OK Chucklez . . . .

In parallel with [blue]CajunCenturion[/blue], I'm certainly glad you got thru the rigors of fixing it yourself. However I'd hate for ya to get into bad habits that will drive you crazy down the road.

No professional programmer would ever use [blue]Goto Label[/blue]. Besides adding unneeded complexity to your code,it makes it harder to troubleshoot/decipher code flow.

[blue]AceMan, You can have a IF statement without a EndIf, If the If is followed by a Then.[/blue]

Yes I'm certainly aware, and hope you realize, [blue]this is a seriously bad habit to get into[/blue].

What ever your future endeavors with Access, stay [purple]Structured[/purple] and you'll enjoy it better . . . . I promise.

See Ya! . . . . . .