Style question: Sharing code between forms

I am new to Access programming and was giving the job of cleaning up the code of an old project. In this project, there a lot of forms that provide similar functionality using copy-and-paste coding, which I dislike. What is the normal way to handle such cases in Access?

To make things more concrete, here is an example. I have two forms that both have a button that, when pressed, opens a MsgBox into which a user enters the name of a record he would like to search for. If found, the form jumps to that record, otherwise, it ignores the input. Currently, there is identical code in both forms:

Private Sub Search_Click()
recordName = InputBox("Enter Record Name")
filter = "name LIKE '" & recordName & "*'"
Set rs = Me.Recordset.Clone
rs.FindFirst filter
If Not rs.NoMatch Then
Me.bookmark = rs.bookmark
End If
Set rs = Nothing
End Sub

The best idea I have is to create a module with a method like this:

Public Sub Search(theForm as Variant)
recordName = InputBox("Enter Record Name")
filter = "name LIKE '" & recordName & "*'"
Set rs = theForm.Recordset.Clone
rs.FindFirst filter
If Not rs.NoMatch Then
theForm.bookmark = rs.bookmark
End If
Set rs = Nothing
End Sub

and then call that method from both Search_Click event handlers.

Is there a better way? Is there a way to use OO coding to create a common superclass of these two forms and then put the Search code in there? Is there a way to capture the fact that both forms have the same buttons too?


Details: I'm using a .mdb file in Access 2003

Sponsored Links:

Hey All,

I have a question regarding referencing a form using dates between.

Currently I have it setup as:

	Between [Start Date] And [End Date] 
on the query.

I want to reference a form, which i figured would be like this

	 Between [Forms]![frmSBReports]![txtStartDate] And [Forms]![frmSBReports]![txtEndDate]

But this doesnt work! What did I do wrong here? Any help as always is greatly appreciated!


Hello all - I have what I hope is a simple question. I'm looking for advice on switching between forms, specifically in access 2007 (though I suspect that the answer may be similar in 2003).

I have summaryFormA and UpdatingFormB. On summaryFormA there is an Updatingbutton that when clicked calls for UpdatingFormB to open. During the opening of UpdatingFormB the Load and Current events trigger code to run, which updates the data in several tables and ultimately reopens summaryFormA to allow viewing of the new details.
Problem(i think):
The tables will generate a locking error asUpdatingFormB still thinks that summaryFormA is still open. I believe the reason for this is that the btnClickEvent on summaryFormA never reaches the End Sub, and is actually waiting for UpdatingFormB to process all of the vba code before continuing.

Is there a graceful way to change between forms, making sure that formA is completely closed with no code running, before anything on formB starts? Any pointers on what to read in regards to this would also be appreciated.



I have 2 forms, Form “A” and Form “B”. I Update data in Form “A” while Form “B” is closed, when I open Form “B” all the updated data is in the controls. If I try to update the data in Form “A” while Form “B” is open, the data does not appear in the controls of Form “B”. I add the following code in form “A”
Private Sub Form_AfterUpdate()
End Sub
While both forms are open Form “B” updates OK. Now if I try to update Form “A” with Form “B” closed, I receive an error message because I have not opened Form “B”, I can overcome this by opening Form “B” using code then not making the form visible. My question- “Is there a way to update Form “B” no matter if it is open or closed.” I would appreciate any advise.

Hi Everyone,

I'm trying to pass a variable called MyFilter between forms but am having problems. I have created a Module and declared MyFilter as a public string.

The original code in my first form is:

	Private Sub Command65_Click()
Dim MyFilter As String
If Me.Filter = "" Then
        MsgBox "Please apply a filter to the form first."
ElseIf Me.Dirty Then
' Make sure the record is saved
RunCommand acCmdSaveRecord
    MyFilter = Me.Filter
    DoCmd.OpenReport "Temp", acViewPreview, , MyFilter
End If
End Sub

How to I change this so that it now stores the value in the Public variable instead of the Private one which it is doing above?



Is there any way to keep information from one form to another, like sessions in web pages???

Also, how can I navigate between forms?? Only with the buttons?? Can I close a form when I open a new one??? How would I do it??

Lots of questions! But im so new, and need to have it done!


I don't know why this is so difficult - but I am sure someone here will find my error(s)!

I would like to pass the selected value (in this case a 4 digit year) from one unbound combobox on Form1 to another unbound combobox on Form2. The comboboxes are identical on both forms. Usually when I want to pass data between forms I use the DoCmd.OpenForm from a command button OnClick event in Form1:

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[cbo2] =" & Me![cbo1]
stDocName = "Form2"
DoCmd.OpenForm stDocName, , , stLinkCriteria

This does not work and I have tried every possible iteration of the stLinkCriteria to no avail. The only way I can get Form2 to open with the correct year in cbo2 is to use the OnLoad event in Form2 with this code:

Me!cbo2 = Forms!Form1!cbo1

This works perfectly as long as Form1 is still open. I would rather not use the OnLoad event because Form2 can be also opened by other forms when Form1 is closed. If I try to open Form2 from another form it gives an error message because the OnLoad event can't find Me!cbo2 = Forms!Form1!cbo1.

Obviously there is a gap in my understanding of how the stLinkCriteria functions. Can you use the stLinkCriteria to pass data between unbound combo boxes?

I suppose I could check to see if Form1 is open in the OnLoad event and if not ignore the Me!cbo2 = Forms!Form1!cbo1 statement, but I am not sure how to best do that.

Please advise, and thank you!

OK guys. Thanks in advance to anyone who can help with this issue. I am a newbie to the access world so bear with me. I am trying to design patient lists for use in a hospital setting. I would like to create 3 basic forms (or patient lists) and be able to move a patient (or record) between these forms. I have successfully created the forms and associated table. My initial thoughts were to put a control button on the form which would activate a macro to "cut" the record from one form and "paste" it to another. However, I have not been able to do that. I have tried multiple different ways, all unsuccessful. I guess my first question is, should these forms be in the same database or different datatbases, or does it not matter? Seocnd, is this the right approach to my problem? If so, can someone help with the macro actions? Maybe I just haven't designed the right macro yet. People have suggested changing the status of a record but that means that I would have to sort through all the records all the time. That would be very cumbersome with time. I am desingning this for a department and I need to make this as simple as possible. Thanks again.

Hi, Everyone,

I'm trying to adapt code for an .accdb form button in the main form class module that moves a piece of data from a field in one table to the same named field in another table. Not copies it, but actually moves if it were being cut and pasted. The first table is full of "unused" movie codes, and I want the second to hold them as "used". I want to keep them separated with "a table between them" because I don't want any of these movie codes wasted (they cost money). I understand that programming between two tables instead of one is more complex, but from the user-end point of view, I think one table could get messy much more easier. Also, having the data move from table to table will simply be more aesthetic for my users who don't know much about Access. I understand that I could add a movie code "Status" field to the first table and write code that populates it as "used" once I have done doing with it what I'm going to, but I really, really want to move it between the two tables. I can't find public code that can execute this move, so I'm trying to adapt code that just copies between tables. So far, the block of code I have to "cut/paste" move it is as follows:

Code: Option Explicit Option Compare Database, Private Sub, etc. ..... ' This block moves (not copies) that same movie code from "Unused Movie Code Table" to "Used Movie Code Table". If gcfHandleErrors Then On Error GoTo PROC_ERR Public Const gcfHandleErrors As Boolean = False CurrentDb.Execute "INSERT INTO [Used Movie Code Table].MovieCode" & "SELECT TOP 1 [Unused Movie Code Table].MovieCode" & "FROM [Unused Movie Code Table]" & "ORDER BY MovieCode ASC", Debug.Print ("Move moviecode from Unused to Used table") PROC_EXIT: Exit Sub PROC_ERR: MsgBox ("Error moving movie code from Unused to Used table." & Err.Number & ") " & Err.Description, vbExclamation + vbOKOnly, _”Move Movie Code Between Tables” Resume PROC_EXIT Would this code move it or just copy it? Or not work at all? (I can't test it because it's a block in the middle of a bunch of other button automation code).

Alternately, I was advised to set-up the above code using sSQL statements first, followed by the CurrentDb.Execute statement...

Code: ' This block moves (not copies) that same movie code from "Unused Movie Code Table" to "Used Movie Code Table". sSQL = "INSERT INTO [Used Movie Code Table].Movie Code" sSQL = sSQL & " SELECT TOP 1 [Unused Movie Code Table].Movie Code" sSQL = sSQL & " FROM [Unused Movie Code Table]" sSQL = sSQL & " ORDER BY Movie Code ASC" Debug.Print ("Move moviecode from Unused to Used table") CurrentDb.Execute sSQL PROC_EXIT: Exit Sub PROC_ERR: MsgBox ("Error moving movie code from Unused to Used table." & Err.Number & ") " & Err.Description, vbExclamation + vbOKOnly, _”Move Movie Code Between Tables” Resume PROC_EXIT Is this just a layout thing to read the code easier? Will the code run better using this sSQL structure with the Execute command at the end? I thought that method was outmoded?

Thank you so much for taking the time to look at this code problem. You guys have been consistantly educating me in the past month, and saving my neck on a surprise coding project that is a true trial by fire. You'll never know how your unselfishness with your knowledge is appreciated. Any thoughts on this are welcomed.



On my database I have a login system. The login system restricts the number of failed attempts, locks account, expires password and encrypts the password with MD5 encryption before writing them in the table. I pass the username between forms in order to restrict certain controls on each form based on the users' access level. The way I have it right now is that upon the user's successful login, a form is opened invisibly and the username is passed on to an unbound control on this form. The appropriate event of each control that I want to restrict, runs a DLookup for the access level of the username in the unbound field of the invisible form and then proceeds accordingly. The code is similar to the following:

If DLookup("AccessLevel","tblUsers","[Username]=' " & Forms!frmInvisible.txtUnboundField & " ' ")= ... then ...

This works great most of the time, however I have experienced that occasionally this will not work at all. The event of some of the controls will not do anything at all as if there were no code behind them, which I think might be because the unbound field on the invisible form is blank. And some controls give a message saying the referenced form was not found, as if the form never opened or got closed.

I am certain that the form is not closed by any other event except the on timer event of the invisible form itself, which closes all the opened forms if a 15 minute idle time is detected and puts the users behind the login screen. But, the login form re-opens the form and pass on the username to it when the login is successful.

I wonder why this method does not always work, and if there is a more reliable and effective method to do this. Is it possible to have a function holding the username and can be checked by events of my controls? If yes, how can I do it.

Thanks in advance.

I have a report that runs off a paramater based query. The parameter is a date range. I use a form with 2 Microsoft Date and Time Picker ActiveXCtl’s, which allow for selecting a start and end date from a drop-down calendar.

The query that drives the report uses:
Between Forms![frmDatePicker]![StartingDate] And Forms![frmDatePicker]![EndingDate]

I also display the date range at the top of the report.

For some reason the selected starting date is not picked up correctly. For instance, when I select 1/4/04 as a starting date, the report skips 1/4/04 and starts with displaying data for 1/5/04 (despite the fact that I have records for 1/4/04). If I select 1/5/04 it starts with 1/6/04, and so on.

The date range at the top of the report displays correctly.

However, when I open the report directly and enter the parameters in a parameter dialog box, everything works fine.

Can anyone please help me?
Thank you,

Morning all, I wondered if nayone else had experienced this - I'm using Access 2007 at the moment. I'm getting huge delays when doing the following (in the order of 30 seconds - 1 min):
Switching between form/design/pivotchart view Switching between property sheet and field list in design view Changing focus between form and subform in layout view Anyone else encountered problems like this?

Yesterday morning I had my search form working and now for some unknown reason the query doesn't pull ANY records, the report pulls ALL the records and I am about here right now!

I tried compacting and repairing the database, deleting and re-entering everything, scrapped the whole database and started on a new test back up and NOTHING is working.

Here is the SQL for the query which the report is based off of. Is there something I'm missing, entering wrong, doing wrong? I took out the automatic export to excel that I had been working on yesterday too because the form worked before that.

SELECT tblEmpRating.EmpRatingID, tblEmp.EmployeeID, tblEmp.EmployeeName, tblEmp.EmploymentStatus, tblEmpRating.CraftCode, tblEmpRating.EvalDate, tblEmpRating.StatusChange, tblEmpRating.SuptName, tblRating.Rating, tblRating.Code, tblSkills.SkillDescription, IIf([Forms]![SkllForm]![cboSupt]="Self",1,2) AS [Key]
FROM tblSkills INNER JOIN (tblEmp INNER JOIN (tblEmpRating INNER JOIN tblRating ON tblEmpRating.EmpRatingID = tblRating.EmpRatingID) ON tblEmp.EmployeeID = tblEmpRating.EmployeeID) ON tblSkills.SkillCode = tblRating.Code
WHERE (((tblEmp.EmploymentStatus)=[Forms]![SkllForm]![cboEmpStat]) AND ((tblEmpRating.CraftCode) Like "*" & [Forms]![SkllForm]![cboCraft]) AND ((tblEmpRating.SuptName)="Key") AND ((tblRating.Rating) Between [Forms]![SkllForm]![cboFromRate] And [Forms]![SkllForm]![cboToRate]) AND ((tblRating.Code) Between [Forms]![SkllForm]![cboFromSkill] And [Forms]![SkllForm]![cboToSkill]) AND ((IIf([Forms]![SkllForm]![cboSupt]="Self",1,2)) Like IIf([Forms]![SkllForm]![cboSupt]="Yes",1,IIf([Forms]![SkllForm]![cboSupt]="No",2,"*"))));

Thank you so much. Again I don't know what I would without this forum...You all are amazing!

Hello all,

I have a query which pulls its criteria from a form. There are about a dozen combo/text boxes on the form, and each one has a corresponding column in the query definition which goes something like this ;

	[weeklyCaseSales]=[forms]![frmRetailerQuery]![txtWeeklyCaseSales] Or [forms]![frmRetailerQuery]![txtWeeklyCaseSales] Is

This is great for searching for single values, e.g. "weeklyCaseSales = 10", but the user also wants to search for ranges, e.g. "weeklyCaseSales > 5 and weekly Case Sales < 20". This means I will need two text boxes on the form e.g. txtMinCaseSales and txtMaxCaseSales. But how do I then use these in the query def (and also allow for the fact that one or both of them could be Null ?).

Thanks for any help,

I have a report query (qry_Data) that contains a field called "StartDate". I have a report form (frm_Data) where I want to put 2 text boxes (Text101 and Text102). I want to create the Query Criteria code for the StartDate so that it will look for start dates between the dates listed in Text101 and Text102 and if the user does not put anything into Text101 or Text102 it just searches for all dates.

I tried to use the code below

	Between ([Forms]![frm_Data]![text101] Or Like [Forms]![frm_Data]![text101] & "*") and ([Forms]![frm_Data]![text102] Or Like
[Forms]![frm_Data]![text102] & "*")

However, Access modified my code to this

	Between (([tbl_Data].[Start])=[Forms]![frm_Data]![text101] Or ([tbl_Data].[Start]) Like [Forms]![frm_Data]![text101] & "*")
And (([tbl_Data].[Start])=[Forms]![frm_Data]![text102] Or ([tbl_Data].[Start]) Like [Forms]![frm_Data]![text102] & "*")

And it does not work. Any suggestions on how to do this?


Hi, I'm having a problem with querydef!

I have looked at tried all sorts of different solutions and now i need your help.

I'm trying to export a query into excel, and this query has a parameter that looks at 2 date fields on a form.

	Between Forms!frmSwictboard!frmDatabaseReports!txtStartDate and Forms!frmSwictboard!frmDatabaseReports!txtEndDate

I cannot for the life of me pass through this parameter using QueryDef.

This is the code i'm using to create the excel report.

	Dim qdf As QueryDef
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

        Set db = CurrentDb
        Set qdf = db.QueryDefs("qryrptAllOverdueIncidents")
        qdf.Parameters("IncDateRaised") = "Between Forms!frmSwictboard!frmDatabaseReports!txtStartDate And
        Set rs = qdf.OpenRecordset
        'Start a new workbook in Excel
        Dim oApp As New Excel.Application
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Set oBook = oApp.Workbooks.Add
        Set oSheet = oBook.Worksheets(1)
        'Add the field names in row 1
        Dim i As Integer
        Dim iNumCols As Integer
        iNumCols = rs.Fields.Count
            For i = 1 To iNumCols
            oSheet.Cells(1, i).Value = rs.Fields(i - 1).name
        'Add the data starting at cell A2
        oSheet.Range("A2").CopyFromRecordset rs
        'Format the header row as bold and autofit the columns
        With oSheet.Range("a1").Resize(1, iNumCols)
            .Font.Bold = True
        End With
        oApp.Visible = True
        oApp.UserControl = True
    'Close the Database and Recordset

Is it even possible to pass through a parameter like this using querydef?

Can anyone point out any obvious flaw!


Hellow again,

I searched on datepicker or some searchkeywords like that, but didn't found anything of an answer which i could use, so please advise if you want:

1) in the db are two fields (begindate and enddate)
2) in my form are four DTPicker fields (begindate_from, begindate_to, enddate_from, enddate_to)
3) user is allowed to leave the enddate DTPicker fields blank

The enddate can be filled with information or not
The begindate is always filled with information

problem description:
on 3) i have a couple of questions:
a) how to leave the DTPicker fields for the enddate blank
(aa or..if possible, maybe a checkbox to de-activate the two dtpickerfields for the enddate)
b) how to finally use this blank field or unchecked dtpicker fields in the query.

Situation now:
In query:
field: Begindate:

	between [forms]![mainform]![begindate_from) AND [forms]![mainform]![begindate_to]

field: enddate

	Isnull([enddate] OR
between [forms]![mainform]![enddate_from) AND [forms]![mainform]![enddate_to]

The part IsNull([enddate]) is of course not used, because in my form the two fields (enddate from and enddate to) are always filled...

How to....

Thanks in advance for your answers/reactions!

Ok this is what i got i ahve a database with 5 fields. ID, Product, description, SRP, SELLING price. Really super simple. Then i Have one unbound form that has 10 combo boxes all labeld product 1-10 and looking up the Product and ID from the table above. I want to put a button at the bottum of this form that prints out a simple form list. That adds the SRP and Seeling Prices together of all the products that were selected in the drop down. I dont want to store them or anything I just want a simple caluator. but can't figure out the coding between form and the combo boxes. Please help. I can send you a copy of the database if you would like.


I have a question about navigating between forms that are used for data entry. I have created macros that work, but here's my problem:

A. FORM 1 --> FORM 2
*button opens form 2 with a new record

B. FORM 2--> FORM 1
*buttons opens form 2 showing the last record in form 1

C. FROM 1--> FORM 2
need the same button from A to now open to the last record in form

If someone could point me in the right direction that would be much appreciated!

Can someone tell me how to pass a variable between forms? I tried setting the variable up as a public variable but when I tried to refrence it in a called form it did not work. I even tried declaring it in a module to no success. Doing that the variable was still declared but if I changed its value in form a and called it again in form b, form b's value was still 0.

Any help??