Solved

Module Outside Form: Variable Form Name?

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

695 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