Solved

Module Outside Form: Variable Form Name?

Posted on 2008-10-20
2
449 Views
Last Modified: 2013-11-28
I have several lines of VBA code that is used in 3 different forms. So in an attempt avoid code duplication, I put it into a subroutine in a module outside the forms. Unfortunately, I have to reference controls in the forms and now I find that "Me" only works in form modules.  Before I cut and paste this code back into the form subs, is there a way to make the form name variable? To complicate things a bit more, the name of one of the controls is variable. Here's the code:


Public Sub modSaveNewResults(intCount As Integer)
Dim rs As DAO.Recordset, j As Integer, query As String, rs1 As DAO.Recordset, rsForm As DAO.Recordset

query = "Select ALL SampleID, Analyte, Results, DateRun FROM tblResults WHERE SampleID=" & txtNumSearch & ";"

Set rs1 = CurrentDb.OpenRecordset("tblTempResults")
Set rs = CurrentDb.OpenRecordset(query)

  For j = 1 To intCount
    rs.MoveFirst
    AnalyteFound = False
      Do Until rs.EOF Or AnalyteFound = True
        If rs!Analyte = Me("Analyte " & j).Tag Then
          rs1.AddNew
          rs1!SampleID = Me!txtNumSearch
          rs1!Analyte = Me("Analyte" & j).Tag
          rs1!DateRun = Me!datAnRun
          rs1!Results = Me("Analyte" & j)
          rs1.Update
          AnalyteFound = True
        End If
      rs.MoveNext
      Loop
    Next j

rs.Close
rs1.Close
0
Comment
Question by:Gazaway
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22761574
Try something like this:



Public Sub modSaveNewResults(intCount As Integer, FormName As String)
Dim rs As DAO.Recordset, j As Integer, query As String, rs1 As DAO.Recordset, rsForm As DAO.Recordset

query = "Select ALL SampleID, Analyte, Results, DateRun FROM tblResults WHERE SampleID=" & txtNumSearch & ";"

Set rs1 = CurrentDb.OpenRecordset("tblTempResults")
Set rs = CurrentDb.OpenRecordset(query)

  For j = 1 To intCount
    rs.MoveFirst
    AnalyteFound = False
      Do Until rs.EOF Or AnalyteFound = True
        If rs!Analyte = Forms(FormName).Controls("Analyte " & j).Tag Then
          rs1.AddNew
          rs1!SampleID = Forms(FormName).Controls("txtNumSearch").Value
          rs1!Analyte = Forms(FormName).Controls("Analyte" & j).Tag
          rs1!DateRun = Forms(FormName).Controls("datAnRun").Value
          rs1!Results = Forms(FormName).Controls("Analyte" & j).Value
          rs1.Update
          AnalyteFound = True
        End If
      rs.MoveNext
      Loop
    Next j

rs.Close
rs1.Close
0
 

Author Comment

by:Gazaway
ID: 22762157
That did the trick! Thanks for the prompt and complete reply!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question