Solved

Module Outside Form: Variable Form Name?

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

830 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