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?

Thanks,
Matt

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


Post your answer or comment

comments powered by Disqus
Hey All,

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

Currently I have it setup as:

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

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

	Code:
	 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!

Thanks!
Mateo

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).


Overview:
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.

Question:
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.

Thanks,

Mike

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()
Forms![F_SearchParts]![Combo10].Requery
Forms![F_SearchParts]![Combo14].Requery
Forms![F_SearchParts]![Combo16].Requery
Forms![F_SearchParts]![Combo20].Requery
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.
kevsim

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:


	Code:
	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
Else
    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?

Thanks,

Hello!

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!


Thanks!

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.
Joe

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 it...as 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.

Frank

Hello,

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:
code:
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,
Aneta

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.

SQL:
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 ;


	Code:
	[weeklyCaseSales]=[forms]![frmRetailerQuery]![txtWeeklyCaseSales] Or [forms]![frmRetailerQuery]![txtWeeklyCaseSales] Is
Null

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,
StepOne

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


	Code:
	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


	Code:
	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?

Thanks,
Jim

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.


	Code:
	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.



	Code:
	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
Forms!frmSwictboard!frmDatabaseReports!txtEndDate"
        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
        Next
        
        '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
            .EntireColumn.AutoFit
        End With
        
        oApp.Visible = True
        oApp.UserControl = True
        
    'Close the Database and Recordset
    rs.Close
    db.Close

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

Can anyone point out any obvious flaw!

Thanks.

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:

Information:
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:

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

field: enddate

	Code:
	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...

Question?
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.

Hello,

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
two

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??

Thanks!!

Scott

Hi all,

I'm a novice to Access. Any help is appreciated.

I've got a database now with 40,000 entries in it. I want to sort them by an expiration date, which is a column called "xdate." I've formatted the xdate column to modify how the date appears. The format is m/dd, because these entries are not "year specific."

For ease of use, I'd like to create a form that runs a Query to display a certain range of dates that I have entered. For example:

START DATE: 1/1
END DATE: 1/31

Would display all records matching between (and including) these dates.

Here's what I have... I have the form setup to run the query ("DateRangeQuery"). My query Criteria is this:


Code: Between [Forms]![DateRange]![txtStartDate] And [Forms]![DateRange]![txtEndDate]
Unfortunately, this is not filtering my values. Running the query still gives me all 40k of my records, so it's not filtering whatsoever.

What am I doing wrong?

In my .accdb, DAO Db, I'm trying to use an AfterUpdate or OnLostFocus event procedure in a class module to populate a date on two tables. My form control is bound to the "MovieCodeSendDate" field on my master table, and I want the field of the same name on my detail table to be populated by the event procedure. I have the following code in place which neither produced errors nor works. It first bookmarks the relevant form control and detail table field by looking up a pivot value ("MovieCode") in a listbox, then (attempts to) populate it with a DLookUp statement. I'm not sure if there's a problem in the environment, my references, or what. Like I said, the code doesn't error in run-time, yet doesn't work when I check the table for the date that supposed to populate next to the correct movie code.

- My form is based on a query that includes every field from both tables (".*").
- The "MovieCode" field for both tables is the primary key.
- I took out the join on "MovieCode" between them because the relationship between them will always be one to one and I think the join was messing up my Form control source query statement in relation to this procedure.

Code: Private Sub A1_Tracking_Form_Movie_Code_Send_Date_Control_On_Lost_Focus() ' When the "Movie Code Send Date" control text box is tabbed off of, this code will find the same code in the Movie Code Table that is ' populated in the listbox of the current record... ' First, the fields that have already been data-entered are saved for good measure. DoCmd.RunCommand acCmdSaveRecord On Error GoTo PROC1_ERR Dim rs as DAO.Recordset Set rs = Me.RecordsetClone rs.FindFirst "[A1 Movie Code Table].[MovieCode]= '" & Me.[A1 Tracking Form Movie Code List Box] & "'" Forms![A1 Onboarding Tracking Form].Bookmark = rs.Bookmark If rs.NoMatch Then Msgbox "No match found.", vbInformation + vbOKOnly Else Me.Bookmark = rs.Bookmark End If Debug.Print ("Find matching movie code between form control and movie code table") PROC1_EXIT: Exit Sub PROC1_ERR: MsgBox "Error finding matching movie code between form control and movie code table." & _ vbCrLf & "Check in table to see if code you picked is already used." & vbCrLf & Err.Number & " " & _ Err.Description, vbExclamation + vbOKOnly, "Find Matching Control Code In Movie Code Table" Resume PROC1_EXIT rs.Close ' If found, code will next populate the movie code send date cell right next to ' the correct code. This will ensure only new codes are available in the movie code listbox during ' data entry and prevent the sending of one code to more than one employee. On Error GoTo PROC2_ERR Dim dt As Date dt = DLookup("[MovieCodeSendDate]", "[A1 Movie Code Table]", "[MovieCode] =" & Forms![A1 Onboarding Tracking Form]!MovieCodeSendDate) Debug.Print ("Populate moviecodesenddate from form control to movie code table") PROC2_EXIT: Exit Sub PROC2_ERR: MsgBox "Error populating moviecodesenddate from from control to movie code table." & _ vbCrLf & "Check in table to see if code you picked is already used." & vbCrLf & Err.Number & " " & _ Err.Description, vbExclamation + vbOKOnly, "Populate Form Send Date In Movie Code Table" Resume PROC2_EXIT End Sub I can't for the life of me figure out what is going on. No results, no run-time errors. To my understanding the bookmark procedure only marks the cells to share values, and the DLookup procedure actually populates the receiving cell. Any ideas about what is going wrong here are greatly appreciated. Thank you.

* This topic is also posted here...
http://bytes.com/topic/access/answer...ng#post3701442
and
http://www.utteraccess.com/forum/Run...-t1981711.html

Frank

I am currently working on a database and I am having issues with form synchronization.

The forms that I have created consist of a main form that displays "Patient" data (Last Name, First Name, MRN - unique identifier), and a subform that displays a specific kind of data related to that "Patient" (Discharges, Resources, Vitals, etc.). When I switch between forms, I want the information being displayed in the second form to be related to the same patient from the first form (the patient info. listed in the main form should not change).

I have tried using Macros and Modules to accomplish this but I am totally confused at this point. Does anyone have suggestions?

Hello, I'm relatively new to Access (and the forum) and I was wondering if someone could give me a hand with some VBA Code and procedural stuff. I've created a form to enter customers into workshops. The form is called Attendees and the information entered into this form is recorded on to the table Attendees by clicking the Add Customer button (just an add new record button). In addition to several textboxes I have two combo boxes, one of which pulls the workshop names from a table called Workshops. What I would like to do is on the Workshops table I have 3 fields/columns (ID, Workshop, Open), when a workshop has reached it's limit I'd like to be able to change the corresponding Open field to Closed. Basically, all workshops will start as Open and when they reach their maximum, the Open value will be changed to closed.

What'd I'd like to happen (and consequently where I need the programming help) is I'd like the combo box (AfterUpdate I'm assuming) to have an event that if a workshop is selected that has a Closed status, the Add Customer button is disabled. I did a whole bunch of Googling yesterday but couldn't find the answer. I'm not sure how to code VBA to look up a value from another table and disable the button accordingly.

So for example: In the workshops table I have a workshop (field) called Resume Improvement - 8/2/12 (value) and that workshop has a limit of 25 people. In my Open (Field/Column), next to that workshop it says Open (value), when the limit is reached I will change the open to closed and at that point if someone were to select Resume Improvement - 8/2/12 from the combo box the Add Customer button would be disabled, thus disallowing them to add the record (or at the very least make it more difficult, I dealing with extremely computer illiterate people for the most part so security's not top priority).

Based on my Googling I'm thinking the VBA code should be something like

If Me.Workshops.Column(2) = True then
Me.Add_Customer.Enabled = True Else
Me.Add_Customer.Disabled = True

Or something like that, but as I said I'm fairly new at Access and completely new to VBA. Sorry for the length but I wanted to provided as much info as I could. Any help would be greatly appreciated, THANKS!

EDIT: If anyone knows how to code the form to disallow the addition of a new record until all fields are filled in/selected that'd be super helpful too!

Here is a sample database that uses public variables to pass between forms/queries/reports that each work independantly from each other. In other words one does not have to rely on the other being open to read the criteria settings.

I have also supplied a word document to support the demo by means of introduction to its uses.

David Attached Files PublicVariables.mdb (232.0 KB, 351 views) PassingPublicVariables.doc (91.0 KB, 274 views) Reply With Quote 02-11-2010, 09:10 PM #2 SCreaney Novice Windows XP Access 2003 Join Date Feb 2010 Posts 1 thank you I just want to say thanks, the example file was a great help


Not finding an answer? Try a Google search.