Can code for form be simplified

[netrusher]

Below is the code that I currently have attached to an AfterUpdate Event. This code pulls information from a
query and populates unbound fields on a form for viewing. Can some of the experts out there think of a more
simple way to obtain this information?

Private Sub PartNumberSelect_AfterUpdate()

Forms("MainEntryFrm").Requery
Me.ProdDC = DLookup("PRODUCT_DC", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.PartNumber10 = DLookup("PartNumber", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.PrimarySupplier10 = DLookup("PrimarySupplier", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.OPERATION10 = DLookup("Operation", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.Usage10 = DLookup("USAGE_LOCA", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.KBQty10 = DLookup("PROD_KB_QT", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.FrozenKbQty10 = DLookup("KB_QUANTIT", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.CONTAINER10 = DLookup("CONTAINER_", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.PiecesPer10 = DLookup("PIECES_PER", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.NumberContainers10 = DLookup("NUMBER_OF", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.PullStep10 = DLookup("SURROGATE_", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.ReplenishTime10 = DLookup("REPLENISHM", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Me.PartNumber20 = DLookup("PartNumber", "MainEntryQrySelect", "Surrogate_ = 2")
Me.PrimarySupplier20 = DLookup("PrimarySupplier", "MainEntryQrySelect", "Surrogate_ = 2")
Me.OPERATION20 = DLookup("Operation", "MainEntryQrySelect", "Surrogate_ = 2")
Me.Usage20 = DLookup("USAGE_LOCA", "MainEntryQrySelect", "Surrogate_ = 2")
Me.KBQty20 = DLookup("PROD_KB_QT", "MainEntryQrySelect", "Surrogate_ = 2")
Me.FrozenKbQty20 = DLookup("KB_QUANTIT", "MainEntryQrySelect", "Surrogate_ = 2")
Me.Container20 = DLookup("CONTAINER_", "MainEntryQrySelect", "Surrogate_ = 2")
Me.PiecesPer20 = DLookup("PIECES_PER", "MainEntryQrySelect", "Surrogate_ = 2")
Me.NumberContainers20 = DLookup("NUMBER_OF", "MainEntryQrySelect", "Surrogate_ = 2")
Me.PullStep20 = DLookup("SURROGATE_", "MainEntryQrySelect", "Surrogate_ = 2")
Me.ReplenishTime20 = DLookup("REPLENISHM", "MainEntryQrySelect", "Surrogate_ = 2")

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Me.PartNumber30 = DLookup("PartNumber", "MainEntryQrySelect", "Surrogate_ = 3")
Me.PrimarySupplier30 = DLookup("PrimarySupplier", "MainEntryQrySelect", "Surrogate_ = 3")
Me.OPERATION30 = DLookup("Operation", "MainEntryQrySelect", "Surrogate_ = 3")
Me.Usage30 = DLookup("USAGE_LOCA", "MainEntryQrySelect", "Surrogate_ = 3")
Me.KBQty30 = DLookup("PROD_KB_QT", "MainEntryQrySelect", "Surrogate_ = 3")
Me.FrozenKbQty30 = DLookup("KB_QUANTIT", "MainEntryQrySelect", "Surrogate_ = 3")
Me.Container30 = DLookup("CONTAINER_", "MainEntryQrySelect", "Surrogate_ = 3")
Me.PiecesPer30 = DLookup("PIECES_PER", "MainEntryQrySelect", "Surrogate_ = 3")
Me.NumberContainers30 = DLookup("NUMBER_OF", "MainEntryQrySelect", "Surrogate_ = 3")
Me.PullStep30 = DLookup("SURROGATE_", "MainEntryQrySelect", "Surrogate_ = 3")
Me.ReplenishTime30 = DLookup("REPLENISHM", "MainEntryQrySelect", "Surrogate_ = 3")

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Me.PartNumber40 = DLookup("PartNumber", "MainEntryQrySelect", "Surrogate_ = 4")
Me.PrimarySupplier40 = DLookup("PrimarySupplier", "MainEntryQrySelect", "Surrogate_ = 4")
Me.OPERATION40 = DLookup("Operation", "MainEntryQrySelect", "Surrogate_ = 4")
Me.Usage40 = DLookup("USAGE_LOCA", "MainEntryQrySelect", "Surrogate_ = 4")
Me.KBQty40 = DLookup("PROD_KB_QT", "MainEntryQrySelect", "Surrogate_ = 4")
Me.FrozenKbQty40 = DLookup("KB_QUANTIT", "MainEntryQrySelect", "Surrogate_ = 4")
Me.Container40 = DLookup("CONTAINER_", "MainEntryQrySelect", "Surrogate_ = 4")
Me.PiecesPer40 = DLookup("PIECES_PER", "MainEntryQrySelect", "Surrogate_ = 4")
Me.NumberContainers40 = DLookup("NUMBER_OF", "MainEntryQrySelect", "Surrogate_ = 4")
Me.PullStep40 = DLookup("SURROGATE_", "MainEntryQrySelect", "Surrogate_ = 4")
Me.ReplenishTime40 = DLookup("REPLENISHM", "MainEntryQrySelect", "Surrogate_ = 4")

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Me.PartNumber50 = DLookup("PartNumber", "MainEntryQrySelect", "Surrogate_ = 5")
Me.PrimarySupplier50 = DLookup("PrimarySupplier", "MainEntryQrySelect", "Surrogate_ = 5")
Me.OPERATION50 = DLookup("Operation", "MainEntryQrySelect", "Surrogate_ = 5")
Me.Usage50 = DLookup("USAGE_LOCA", "MainEntryQrySelect", "Surrogate_ = 5")
Me.KBQty50 = DLookup("PROD_KB_QT", "MainEntryQrySelect", "Surrogate_ = 5")
Me.FrozenKbQty50 = DLookup("KB_QUANTIT", "MainEntryQrySelect", "Surrogate_ = 5")
Me.Container50 = DLookup("CONTAINER_", "MainEntryQrySelect", "Surrogate_ = 5")
Me.PiecesPer50 = DLookup("PIECES_PER", "MainEntryQrySelect", "Surrogate_ = 5")
Me.NumberContainers50 = DLookup("NUMBER_OF", "MainEntryQrySelect", "Surrogate_ = 5")
Me.PullStep50 = DLookup("SURROGATE_", "MainEntryQrySelect", "Surrogate_ = 5")
Me.ReplenishTime50 = DLookup("REPLENISHM", "MainEntryQrySelect", "Surrogate_ = 5")

 

[PHV]

Why not use a bound form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

To follow on with PHVs thought, why not 5 bound subforms?

 

[netrusher]

Thanks everyone but I am new to subforms and really do not have time to recreate everything. I was just hoping that
someone would be able to possibly simplify existing code.

 

[JoeAtWork]

There's nothing simpler than binding a form to a query. Doesn't require any code.

 

[TheAceMan1]

How are ya netrusher . . .

In all the controls of interest except [blue]ProdDC[/blue] enter the fieldname in the [blue]Tag[/blue] property of the control ([purple] no quotations please![/purple]). Example:
For PartNumber10 enter [blue]PartNumber[/blue]
For FrozenKbQty30 enter [blue]KB_QUANTIT[/blue]

I know some are repeats but this greatly simplifies the code. [Blue]Be careful of your spelling here[/blue] and don't forgot you can group select those that are common.

When your done try the following:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, Cri As String
   Dim Surr As Integer, ctl As Control
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("MainEntryQrySelect", dbOpenDynaset)
   Surr = 1
   
   Do Until Surr > 5
      If Surr = 1 Then
         Cri = "[Surrogate_] = " & Surr & " OR " & _
               "[Surrogate_] IS NULL"
      Else
         Cri = "[Surrogate_] = " & Surr
      End If
      
      rst.FindFirst Cri
      
      If Not rst.NoMatch Then
         If Surr = 10 Then Me!ProdDC = rst!PRODUCT_DC
         
         For Each ctl In Me.Controls
            If ctl.Tag <> "" And [purple][b]Val(Right(ctl.Name, 2)) = Surr * 10[/b][/purple] Then
               ctl = rst(ctl.Tag)
            End If
         Next
      End If
      
      Surr = Surr + 1
   Loop
         
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[TheAceMan1]

netrusher . . .

There's an error:

[blue]
Change:
   If Surr = 10 Then Me!ProdDC = rst!PRODUCT_DC
To:
   If Surr = [purple][b]1[/b][/purple] Then Me!ProdDC = rst!PRODUCT_DC[/blue]

Sorry about the mess . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[netrusher]

AceMan1,

The code keeps blowing up on:

   Set rst = db.OpenRecordset("MainEntryQrySelect", dbOpenDynaset)

I do not know what I am doing so I do not know what the
issue is but I appreciate any and all advice.

 

[TheAceMan1]

netrusher . . .

In your post origination in all [blue]DLookUps[/blue] . . . the 2nd arguement is either a [blue]Table Or Query name![/blue] . . . comprising [blue]MainEntryQrySelect[/blue]

Just what is [blue]MainEntryQrySelect[/blue] since you say it fails! From the description I believe a query Name! . . . Check the spelling here! . . .

See Ya! . . . . . .

Be sure to see thread181-473997