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

Sponsored Links:

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.