Open Calendar on one Form from Command Button on another Form

I have a date field on a Subform (ECNDetail) Mainform (ECNBCNVIPtbl). When the date field is clicked I want
to open another form that has instructions (EcnPopup1)(no problem with that). I have a Command Button
(CloseForm) that I would like to serve two purposes if possible. I would like for it to close the form and open
up a Calendar (CalMain1) on my Subform (ECNDetail). Is this possible and if so How. Below is the code that
I currently have. I appreciate all help with this. What our purpose is, before a date is put in the [Pending Date]
Field I want the (EcnPopupp1) to come up with work instructions. Then when the Ok or Close Form is clicked I
want the Calendar to popup so a date can be selected. I am open to try another way if someone has other
suggestions. Thanks for all help.

Please see code below. I have rem'd out the Close form part of the code. I know I somehow much call out
the form where the Calendar is located but I have not been able to accomplish this.

	Private Sub CloseForm_Click()
   CalMain1.Visible = True
   If Not IsNull([Pending Date]) Then
      CalMain1.Value = [Pending Date].Value
      CalMain1.Value = Date
   End If
'On Error GoTo Err_CloseForm_Click

'    DoCmd.Close

'    Exit Sub

'    MsgBox err.Description
'    Resume Exit_CloseForm_Click
End Sub

Post your answer or comment

comments powered by Disqus
Good morning All,
I have a form with a command button to open a report ( based on a query ) for the currently displayed record. here is the code I have used:

Private Sub CS_notes_Click()
On Error GoTo Err_CS_notes_Click
Dim stDocName As String

stDocName = "InternalSNwithRMAprodMASData"
DoCmd.OpenReport stDocName, acPreview, , "TLAUnit = " & Me.UnitSN & "'"

Exit Sub

MsgBox Err.Description
Resume Exit_CS_notes_Click

End Sub

I believe this came from this forum sometime. When the button is clicked I receive the error:
"Syntax error (missing operator) in query expression '(TLAUnit = 26712B')'

TLAUnit is the report field, UnitSN is the form field.
Any help with the error?

I am trying to update a check box on the form from VBA code and it is not happennig. The code is as follows:

	DoCmd.RunCommand acCmdRefresh
DoCmd.SetWarnings False
Dim rs As New ADODB.recordSet
Dim strSql As String
Dim cmd As ADODB.Command
Dim Userid As String
Dim NewUser As String
Dim count As Integer
Dim currentDate As String
Dim clientMessage As String
Dim clientSubject As String
Dim cm1 As String
Dim cm2 As String
Dim cm3 As String
Dim invheader As String
Dim e As String
Dim flag As String
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con = CurrentProject.Connection
e = Chr$(13)
flag = True
currentDate = CStr(Date)
Userid = [Forms]![FRM_Shippers_Past_Due]![Userid]
    strSql = "SELECT [TBL_E-Mail_Shippers].E_Mail_Sent, [TBL_E-Mail_Shippers].Select,
             "TBL_Select_Shippers.* " & _
            "FROM [TBL_E-Mail_Shippers] " & _
            "INNER JOIN TBL_Select_Shippers ON [TBL_E-Mail_Shippers].CTC_SYS_NR = TBL_Select_Shippers.CTC_SYS_NR " & _
            "WHERE ((([TBL_E-Mail_Shippers].[Select])=-1) AND (([TBL_E-Mail_Shippers].Network_ID)= " & "'" & Userid & "'" &
            '"WHERE ((([TBL_E-Mail_Shippers].E_Mail_Sent)=0) AND (([TBL_E-Mail_Shippers].[Select])=-1) AND
    count = 0
    rs.Open strSql, CurrentProject.Connection
    Do While Not rs.EOF
 'string extraction
        If rs!E_Mail_Sent = 0 Then
            Call FnTestSafeSendEmail(collectorEmail, EmailSubject, clientMessage, supervisorEmail, BCCEmail)
            [Forms]![FRM_Shippers_Past_Due]![E_Mail_Sent] = -1
            count = count + 1
       End If
        MsgBox CStr(count) & " records emailed"
    Set rs = Nothing   
End Sub

I am trying to update the field [Forms]![FRM_Shippers_Past_Due]![E_Mail_Sent] = -1 once the email is sent.

[Forms]![FRM_Shippers_Past_Due]![E_Mail_Sent] updates [TBL_E-Mail_Shippers].E_Mail_Sent

[TBL_E-Mail_Shippers].[Select] gets the input from the form.

User updates this field form the from to select the records to be emailed.

Right now its only updating only one record and I want to update multiple records. Thanks

I can't seem to get my brain around this:

I have a simple calendar that, of course, I found here, that I want to use on three different forms. I have it working on one as long as I just want the dates to be entered on that particular form, but am assuming that, in order to use it in multiple forms I need to create a module. I tried the following in a module but get an error when selecting the date in pop up calendar on my form.

Sub ShowCalendar(ByRef DateCompleted As TextBox)

On Error GoTo Err_ShowCalendar

Dim frmCal As Form ' calendar form
Dim ctlD As Control ' date control on this form

Set ctlD = DateCompleted
DoCmd.OpenForm "Calendar", , , , , acDialog

Set frmCal = Forms("Calendar")
With frmCal
Set .DateControl = ctlD
.InitialDate = ctlD.Value
.Visible = True
End With

Exit Sub

MsgBox Err.Description, vbCritical, "Error #" & Err.Number
Resume Exit_ShowCalendar

End Sub

Code for command button onclick event:

Private Sub Calendar_Click()

DateCompleted = Nz(DateCompleted, Date)
Call ShowCalendar(DateCompleted)

End Sub

This gives me the message "...can't find the form 'Calendar' referred to in a macro or expression or Visual Basic Code"

Any time I am using the calendar, the Date will go into the same table and same field "DateCompleted", just in different situations.

Any help would be appreciated!



Ok, I have two open forms. How would I go about setting the visible property on one form from inside the other?

I think this is the right part of the forum to post this and I hope someone can help.

I'm trying to create an access front end to an SQL Server backend, I've linked my tables and set forms to help view and edit the information, however on one form, and one form only I keep getting the message Login Failed for user " The user is not associated with a trusted SQL Server connection.

I can view the tables fine in access, and the continuous forms I've set up to view the data open fine, however the form I've set up that allows you to edit the data for a record most of the time has this message box poping up. I can after a few tries cancel out of this, and make edits to the records.

The way I've set the forms up is that I have a continuous form to display all the data, the user then selects the record and hits a button and another form opens with the data from the selected record ready to edit. However I have the same process set up for another table in the same database and I have had no issues when the form to edit the record is opened. So the issue seems to lie with that one particular form.

I have checked and double checked to see if the premissions for both tables are the same and as far as I can tell they are, does anyone have any ideas?

I hope that all made sense.

I know this is a topic that has been searched and asked alot since this afternoon I did it myself and found lots of questions and no simple answers.

Therefore I thought I would share the simple quick and dirty solution for displaying data from multiple sources on one form without recordset code I came up with.

It should be noted that for my purpose I only needed to return one line from each query and used TempVars as criteria from other actions before my form(s) were loaded. However a little creativity could make this method work for quite a few scenarios I persume.

1. Create a form of ListBoxes that contain the data you want. Create the query in the data source editor of the properties window.

2. Place this code in that forms "On Load" event:

Dim ctl As Control

'This selects the first record in each listbox. If column headers are set to yes the selected value should be (1).

For Each ctl In Me.Controls
If ctl.ControlType = acListBox Then
ctl.Selected(0) = True
End If
Next ctl

3. Trigger this form to open as hidden just before the command for your display form. In my case I am displaying the results on a tab control subform so my on click event looks like:

DoCmd.OpenForm "frmListBoxes", acNormal, "", "", , acHidden
Forms!frmMAIN.Sub.SourceObject = "frmLBsub"

*I also have a close command set on timer 50 for the listbox form since I dont need it once the values are loaded to my display form.

4. Now you can reference the data in those fields respectively such as:

Me.MyNumber = Forms!frmListBoxes.ListBox1.Column(0)
Me.SecondSource = Forms!frmListBoxes.AnotherBox.Column(9)

Like I said it's quick and dirty but works great for my application, was fast to put together, and is very flexible for me to expand in the future with little to no additional code.

The more experienced guru's here can tell me if I'm on the crazy train or not

To all,

I have a main form with command buttons that open other forms. The command buttons are set up to open the forms in filtered mode. I need to pass a value from the main form to the "other" form via the command button. any suggestions will be appreciated

Have managed to:
Create a form with command buttons
Have the command buttons open the form, document (hyperlink), or report asked for
Now want to have the original form with the command button disappear and only the form or report show on the screen.
What is the best way to accomplish this? Can you have 2 onclick events on the same button? Or would you put a close in something else?

I have a form called "Ticket Form". It has a combo box field called "Pool_Name". This combo box is populated with records from a table "Pool Info Table" and the field "Pool Name." I have a second form "Poll Info Form" that is based on the data in the "Pool Info Table".
When I have a record open in the "Ticket Form" I want to be able to double click on the name in the "Pool_Name" field and have it open the corresponding record in the "Pool Info Form." I have tried several different options form doing an OpenForm Macro and trying to set the Where field and have also tried a VBA with DoCmd.OpenForm both unsuccessful. The closest I got was getting the OpenForm to open the "Pool Info Form" to the first record.

My database has two primary forms. The first form has a group of command buttons that allows me to select a plant. Clicking on one of the command button opens the second form, which contains information about that specific plant. I achieved this selection by writing a macro for each CB on the first form. Each macro contains a plant specific identifier (one macro – one plant) and the macro opens the second form. The control source for the second form is a select query that returns plant specific data.

Now I would like to use a CB on the second form that runs an application to open a *.pdf file. How do I pass the plant identifier to the macro so the desired graphic is opened? I tried setting the “where condition” of the macro to the plant identifier but this approach only opens the first instance in line. Thanks.

Office 2007
Win XP


I have been having severe problems with Macros in my DB. Macros were set up to run code but are now returning 2950 errors on 70% of them. The db is trusted so i am not sure of the problem.

To get around this i wanted to run my code (functions) directly from command buttons.

Can someone give me some guidance on this please?



Hello folks,
This is probably no brainer for most of you, but I got stuck.
Basically I have created a few queries (some of them just show a number, let's say "total number of active clients, etc). I have a few queries producing similar results; I want to show all those results on one form; so you would have one form showing data from multiple queries... not sure if this is doable, but one thing I notices is that if I use unbound combo or list box, I would use the "Row Source" in "Properties" and link to the respective query; however there is no "Row Source" in a Text what do I do here? Any help is appreciated!
P.S. I did spend a good amount of time searching the forum, but did not come accross similar case...

Thanks again.

I have two forms. I have some txtboxes on one form and a button on another. When i click the button i want the text on the textboxes on the other form to change. How do I write a code that changes for example:

Private Sub cmd_Click()


I am trying to use two options groups on one form, but I do not want to be able to select an option from each group.
If a selection is made in one group then I want to ensure that any selection that may have been made in the other group is removed without disabling the group entirely and vice versa. I need both groups to function but cannot have selections made at the same time....

Thanks in advance!

I am using a form for users to enter search criteria. They can enter criteria into any of about 20 fields on the form. A command button then displays the results in a parent form. This was not developed by me.

What I want to do is to add a command button that clears the criteria from the last previous search which remains when you return from viewing the results. This is the same as the clear grid toolbar button in query by form. I want the button to be generic or use a public function since I have several search forms and don't want to have to remember to change a form name in a function.

Thanks Much, Tom

Hello -

I am new to forums and not sure how to start this thread - so please be patient. I have however, up to this point, thought myself to be fairly proficient with Access, that is until this issue.

I found a thread (Add Multiple Records on One Form 8/13/10) from William McKinely that is similar to my problem. He was able to get an answer from PBaldy - but it was a little short on details.

I am looking for the details of PBaldy's solution to create / run an append query to pre-populate a table in order to create / show a form for data entry with multiple new records for easy update.

Since I did not know if was appropriate to contact them directly, I thought I would post my request for more information in a new thread.


Hi All,

I am trying to create a form for my supervisors to update their daily employee production hours. The supervisors have requested that all of their employees show up on one form, allowing them to simply tab down to the next person to add the employees daily hours. I've tried a few things but I am coming up short. Attached is the form design for my closest attempt. I added a bunch of different text boxes linked to the table that I want to populate with each 'User ID' field having a default value of each employee. The form looks the way I want it to when opened, however when I try to edit the first record it switches all of the fields to that particular employee. I guess it thinks all the text boxes are one record? I attached a few examples of how I need it to look and what happens when I try to add a record.

Any ideas? Attached Thumbnails     Reply With Quote 08-13-2010, 03:45 PM #2 ajetrumpet Banned Windows Vista Access 2007 Join Date Mar 2010 Location N/A Posts 2,698 the continuous form is perfect for this type of thing. try using it

Hi all,

This is my first post, so please bear with me if I forget something out which you need to help me!

I'm creating a database for my school to monitor pupils progress. All of the information I need for this problem is in one table.

I want to be able to show overviews of children progress for 5 different areas - Reading, writing, numeracy, science, and speaking and listening. I've worked out how to display an average of these 5 columns, so that bit's sorted!

However, I also want to be able to isolate groups of pupils and display their average progress on the same form for easy comparison. For example, I want to isolate all of the males, and then show their average progress on the same form.

I hope this is making sense so far!

I have created a number of queries which (independently) do the job of isolating groups and averaging them - so I have qryMale which searches the Gender column for "M", and then used the Totals section to get an average of the ReadingProg, NumeracyProg, etc colums.

My issue is getting them all to display on one form. Do I need to link them all into some kind of overviewing Uber query? And if a query returns no results (for example there stops being any dyslexic children within the school), will that cause my form to freak out and display #Error all over again!?

Any help would be greatly appreciated. I apologise that this has rambled a little, and if I have missed anything crucial for a solution, just let me know!


Auto update info on Main Form from Subform

Been stuck for a long time, searched everwhere and tried a lot of code with no luck.

Would like to update information on a Form from a subform if conditions are met
eg Subform
[ProductName] widgets are ordered and [Quantity] 10 are required
I would like the [Quantity] 10 to appear on the main form.
Same OrderID
[ProductName] wombats are ordered and [Quantity] 5 are required

The subform is still required as different products may be delivered at different times.
It can be done by simply placing duplicate fields in the main form but this requires double entry.

Any help would be most appreciated and thanks heaps in advance.

Hello All,

I was intersted in a text box entry on one form populating two different tables. The one field in both tables are identified by the same name. Is this possible?

Thanks in advance.

Let me first say that I have been siting with this program learning as I go for about 14 hours. I pick up quick but before I waste countless hours trying to make this work I'd like to see if its even possible in the confines of Access.

I am working for a research lab and have been tasked with reconstructing the subject database. It essentially holds all of the contact information of the children that participate in our experiments. What we are trying to achieve is creating a new table for full families with the individual children retaining there own individual ID's. At the table level I can do this rather simply by linking the experiment list to a child's ID and then linking each individual entry for the children to the family table by adding multiple fields to contain the individual child ID's.

Experiment Table
Experiment ID: 8 Participant in Exp. 1: Yes ...
Experiment ID: 9 Participant in Exp. 1: No ...

Child Table
ID: 8 Name: Sample Date of Birth: 1/1/01 Gender: M Experiment ID 8...
ID: 9 Name: Xmple Date of Birth: 2/2/02 Gender: F Experiment ID 9...

Family Table
Family ID: 3 Phone: 999-999-9999 Email: x@x.x Child ID1: 8 Child ID2: 9

In this way I can link the different children to the families in the tables. So what my question is, is can I create a single Form that will allow me to view all of the children in the family and their demographic information (date of birth and so on)?

I have attempted setting up a reference to the child table on the family Form. It works fine for one child entry, but I cannot get multiple children with separate ID's to be displayed. I also, attempted embedding a separate child form on 3 different tabs on the family form. However this didn't effectively link the children to the family. I have also attempted setting up multiple queries for the same table to allow for separate reference links on the form page but the links never seem to be valid when I manually create them to a query in the properties pane. That is, they always show up on the form (in form view) with "#name" in the entry fields.

Essentially what I would like to do is create one form which allows the creation of an entry in the family table and multiple children in the family. This form should also reference the kid's info when I go back to search for a particular family.

Is this something that is possible to achieve on one Form? Or will it require me to create clones of the Child table in order to to reference three different entries on a single form?

Hello I have a question About DISTINCT The Records by a command button. I searched but, can't find anything like that..

I have a Continuous form based on a query and I use "SELECT DISTINCT ........ it works perfectly DISTINCTS the records as needed, but i want to add on the form a command button and being clicked must remove distinct
I thought and decided to create two query for that form and by command button or toggle button change the destination record source. Is that possible or there is another way...

Thank you in advanced

Hi all

I've never used the calendar in Access before, but I'm sure someone out there can quite easily answer my questions - in simple terms please!

1. I want to use a calendar on a form firstly to select a date and populate a 'Date Received' field. I presume you can just have an icon to bring up the calendar?

2. Can I use the same calendar to populate a different date in another field or will I have to set up a new calendar?

3. Ok, I've worked out how to do the calculation to bring up the number of days between two fields. What I need to know is if I can calculate 'WORKING DAYS' only between the two fields.

Your help As Soon As Possible would be greatly appreciated!

(I posted it in the wrong place before so apolgies if your reading this twice!)

Let the St*rs shine bright!

I have tried my hand at the VBA code to fix this, but I admit I am a novice in VBA. Hopefully one of you can help me out. Here is what I want to do:

I have a form that only has command buttons that represent pieces of equipment in a laboratory floor plan layout (i.e. one button represents one piece of equipment in that laboratory). I want to be able to click on a certain equipment's command button and launch a data entry form AND have the data entry form filtered to the exact piece of equipment.

I figured the easiest way would be to add an "ApplyFilter" line of code for each command button in my VBA console, but I am not sure how to tell VBA to apply the filter to the form the command button is supposed to open. Right now I believe VBA is applying the filter to the form with all the command buttons on it, so it isn't helping me out.

Here is the code from one command button (for equipment # IN-1021), I want that particular command button (IN_1021_Datasheet) to open Form "Instruments TRM" and filter that opened form for "IN-1021" in the "Unique Tag" control field:

	Private Sub IN_1021_Datasheet_Click()
On Error GoTo Err_IN_1021_Datasheet_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Instruments TRM"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.ApplyFilter "Forms!Instruments TRM![Unique Tag]" = "IN-1021"
    Exit Sub

    MsgBox Err.Description
    Resume Exit_IN_1021_Datasheet_Click
End Sub

Not finding an answer? Try a Google search.