Solved

Module Outside Form: Variable Form Name?

Posted on 2008-10-20
2
445 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
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now