How to click yes in a macro Results

I have a form (EventInviteF) with the following properties:

Data:
Allow Additions - No
Allow Deletions - No
Allow Edits - Yes

EventInviteF is opened by a button (Command20) on another form (MainMenuF). Command20 has the following properties:

Event:
On Click [Embedded Macro]
- Form Name (EventInviteF)
- View (Form)
- Data Mode (Edit)
- Window Mode (Dialog)


When I open the form by double-click, it doesn't allow additions (see pic).



When I open with the form, it does (see pic).



Sorry the pics are so small inline. Didn't know how to make them bigger!

Thanks for any thoughts! Much appreciated!

--ak

I built an Access 2010 database about two years ago for a client. My client wanted to scan customer contracts and save them with the database. I knew there were two methods; save the scanned documents in an Attachment file or save just the path to the file as a Hyperlink. At the time, (maybe I didn’t do enough research), I decided to use the Attachment file method. I probably also underestimated the quantity of scanned documents the customer would have and the impact on the database file size. From the start, the database was split into a Front End and a Back End. At inception, the Back End was about 3MB in size. Today, it is over 660MB in size and growing with every scanned document.

I started doing research online to figure out how I could export all of the scanned documents from the Attachment field, save them to a folder, and then add their location to a new table and field so the customer could still access them. It’s important to note that each scanned contract is associated with a customer order and I would need to ensure that whenever my customer went to a customer’s order, they would see only those scanned contracts. I never found in one place a “How To Export Attachments” article so I decided to summarize here what I did in the hope that it helps others that need to reverse stored documents/pictures/etc. from an Attachment field and save them in a folder. Maintaining the relationship to the location in the database are now being saved was critical.

Before you begin, make sure you back up your Back End data as well as your Front End. Also, please hold the feedback on how I named my fields and/or tables. I’m comfortable with it and it works for me. Yes, I know sometimes I need to use brackets on my field/table names.

One sample code that I used was from the Microsoft Access 2010 Programmer’s Reference manual. I modified the code slightly and it is shown below:

Code: Public Function SaveAttachmentsTest(strPath As String, Optional strPattern As String = "*.*") As Long Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim rsA As DAO.Recordset2 Dim rsB As String Dim fld As DAO.Field2 Dim OrdID As DAO.Field2 Dim strFullPath As String 'Get the database, recordset, and attachment field Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Order Table") Set fld = rst("Scan") Set OrdID = rst("OrderID") 'Navigate through the table Do While Not rst.EOF 'Get the recordset for the Attachments field Set rsA = fld.Value rsB = OrdID.Value 'Save all attachments in the field Do While Not rsA.EOF If rsA("FileName") Like strPattern Then 'To Export the data, use the line below strFullPath = strPath & "" & rsA("FileName") 'Make sure the file does not exist and save If Dir(strFullPath) = "" Then rsA("FileData").SaveToFile strFullPath End If 'Increment the number of files saved SaveAttachmentsTest = SaveAttachmentsTest + 1 End If 'Next attachment rsA.MoveNext Loop rsA.Close 'Next record rst.MoveNext Loop rst.Close dbs.Close Set fld = Nothing Set rsA = Nothing Set rst = Nothing Set dbs = Nothing End Function Some key points.
The table that contains the attachments field is [Order Table]. The field for each order is [OrderNbr]. The Attachment field’s name is [Scan]. My Attachment field when viewed in the Query By Entry, (QBE), shows:


Scan.FileDataScan.FileNameScan.FileType

I created a temporary Form with two Command Buttons on it:


Export Attachments
This had a simple macro on the On Click event that was:
RunCode and the Function Name was: SaveAttachmentsTest(“R:Attachments”) where “R:Attachments” was the location of the folder where I was going to save the scanned contracts to. When the Function was executed, it exported each scanned document in the order they were input into the database to that folder. (Although I haven’t shown this, I modified the above code slightly to modify as the file name and show the [OrderNbr] just to be able to verify the order and the associations between the scanned contract file name and the [OrderID] were in the right order.)
Run Append Query
I created a new table that I called Attachments Table. It has three fields in it:
[AttachNbr] which is an AutoNumber field[OrderID] which is a Number field that will correspond/establish the relationship back to the [OrderID] field in the [Order Table][FileN] which is a Hyperlink field to store the location where the file is saved
The Append query used the following SQL:
INSERT INTO [Attachments Table] ( OrderID, FileN )
SELECT [Order Table].OrderID, [Scan].[FileName] & "#" & "R:Attachments" & [Scan].[FileName] & "#" AS FileN
FROM [Order Table] WHERE ((([Order Table].Scan.FileName) Is Not Null));

This Query appends to the [Attachments Table] the [OrderID] from the [Order Table], (to the [OrderID] field), and a string, “R:Attachments”, (see NOTE below), which is the folder location where I just exported the scanned contracts to, and concatenated to it is the file name from the [Scan].[FileName] part of the Attachments/[Scan] field. NOTE: In order to get the Hyperlink data to be correctly stored in the field, you must use the “#” symbols as show in the SQL string. A Hyperlink field contains three parts separated by pound signs “#”. The template is: Display Text # file name including the path # Any reference within the file, (i.e. a sheet name if you’re importing an Excel spreadsheet). I found a helpful reference to this at: http://allenbrowne.com/casu-09.html

I also found that in my VBA editor that I needed to go to Tools, References and check Microsoft Office 14.0 Object Library.

I then modified my Order Form to remove the Attachment field input/delete button and then added a new Command button to open up my new Attachment Form, (Default View: Continuous Forms). This form has a couple of events:


On Open it goes to a new recordOn Before Insert it triggers a macro to SetValue of the [OrderID] field to equal the [OrderID] field of the Order Form where this form was opened from. This links the hyperlink location and file with the OrderID.The form is based on a Query that uses as its only source the Attachments Table. The three fields from the Attachment Table are used in the query and the [OrderID] field has criteria that equals Forms![Customer Order Form]![OrderID] to make sure that only records for the customer in the form are shown. The form also has two Command buttons:
Attach: It uses this code:
Private Sub cmdPopulateHyperlink_Click()
'First, set a Reference to the Microsoft Office XX.X Object Library

Code: Dim strButtonCaption As String, strDialogTitle As String Dim strHyperlinkFile As String, strSelectedFile As String 'Define your own Captions if necessary strButtonCaption = "Save Hyperlink" strDialogTitle = "Select File to Create Hyperlink to" With Application.FileDialog(msoFileDialogFilePicker) With .Filters .Clear .Add "All Files", "*.*" 'Allow ALL File types 'Test line so I can debug/compile the code End With 'The Show Method returns True if 1 or more files are selected .AllowMultiSelect = False 'Critical Line .FilterIndex = 1 'Database files .ButtonName = strButtonCaption .InitialFileName = vbNullString .InitialView = msoFileDialogViewDetails 'Detailed View .Title = strDialogTitle If .Show Then For Each varItem In .SelectedItems 'There will only be 1 'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address) strSelectedFile = varItem strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile Me![FileLocX] = strHyperlinkFile Next varItem End If End With End Sub

Delete: Simple Delete macro
Note: the above VBA code I found on a few different sites

I tested the functionality and once I was satisfied everything works, I deleted my [Scan] field from the [Order Table], compacted the database and went from 665MB to 4.6MB.

I hope this is helpful to all who need to reverse an internally stored attachment to saving it externally.

Chuck

I'm working on an Access 2007 database and any changes I make to a form or control will not save. I click the save button, and try keyboard shortcuts but when I close the form I get a prompt "Do you want to save changes to ":

If I click "Yes" the changes still do not save and I when I close the form it starts the cycle over.If I click "No" the form closes and the changes are not saved (obviously).

There is no VBA in the project but I've still tried a recompile as suggested for older versions of Access but no luck. There are quite a few embedded macros but I'm not sure how they could be causing this.




Any help please......

How to remove all the Warning when the Macro run an click Yes,

some of the warning message are

You are about to run a delete query that will modify data in your table
You are about to delete 200 row(s) from the specified table
You are about to run an append query that will modify data in your table
Microsoft Office Access can't append all the records in the append query (150 fields to Null fue to a type conversion failure....)

In my A2007 database, I have various reports for which I wish to use a custom msgbox in the 'On No data' event. The message is working fine, but after I click OK, I get another message telling me "The OpenReport action was cancelled". This is now a superfluous message so I'd like to get rid of it.

Attempt #1 Macro

	Code:
	Msgbox:  "There is no data...etc etc"
SetWarnings  No
CancelEvent
SetWarnings  Yes

Attempt #2 VBA

	Code:
	MsgBox "There is no data to report for the parameters you specified.  Please select a different Division/subDivision, or
different dates or persons, or select a different report to run."
DoCmd.SetWarnings False
DoCmd.CancelEvent
DoCmd.SetWarnings True

Both of these appear to work identically, but neither has any effect on the second message. How do I turn the darn thing off?

Hi guys,

I want to export a couple of graphs to a single excel file. The graphs are
made in Microsoft Graphs 2000. Can this be done using VBA code. If yes, can
you explian how?

-The graphs are Microsoft Graph Object.
-Embedded in a Access sub-form.

This is my code to export the graphs to a folder called 3PP Exported Charts. Then i plan to use an Excel Macro and move the graphs to an Excel file.


	Code:
	Private Sub Export_Click()

Dim graphExport As Object
Dim vendor as string

vendor = cmbVendor.Value 'Gets the selected name of the vendor from the combo box

Set graphExport = Me.Ctl3PPContractChart.Object
graphExport.Export "C:3PP Exported Charts" & vendor & ".jpg", "JPEG"
Set graphExport = Nothing
Me.Ctl3PPContractChart.Action = acOLEClose

End Sub

The Unbound Object Frame which contains the graphs has properties Enabled set to No and locked set to Yes. Previously, when they had their default values (i.e. Enabled=Yes, Locked=No), and i click the Export button, the graphs would mess up i.e. font changed, size changed, overall orientation changed. But after i set these porperties the graphs come out fine. But then i get the error that you have to re-install the OLE server and the Database crashes.

Please guy, can you help me do this?


PS: If there are anymore questions on this issue, i will be glad to answer.

I have a access 2000 .mdb database being run on windows 7, access 2010. I did not design this database program.

When the database is opened, this window pops up, and if YES is selected, another 7 follow; and then it repeats.
------
Microsoft Visual Basic for Applications:An error occurred while loading Form_frmReports, Do you want
to continue loading the project?

Yes No Help
-------
If yes is answered to all the pop ups, the program seems to work OK. But it makes me nervous.

If you answer no to the pop-up, the following messages comes up:
------
Microsoft Access

The database cannot be opened because the VBA project
contained in it cannot be read. The database can be
opened only if the VBA project is first deleted. Deleting
the VRA project removes all code from modules, forms and
reports. You should back up your database before attempting
to open the database and delete the VBA project.

To create a backup copy, click Cancel and then make a
backup copy of your database. To open the database and
delete the VBA project without creating a backup copy,
click OK.

[ 0K ] [__Cancel__] [ Help ]
--------
I don't want to do this!

I have compacted, repaired, decompiled the database. I have opened the pop-up'd forms and reports, they open without errors. I have run the macros that open the forms and they run without errors.

If I open VB the macros do not appear in the project, just the forms and report objects (in the pop-ups).

I do not know how to proceed. I was thinking of re-creating the macros and possibly their associated forms and reports to see if the re-creations would have the same issues.

Help is appreciated; Thank you.

I've started getting this vague and in no way helpful message every time I attempt to look at any of the VBA behind my forms, modules, etc.

Luckily, the front end is stored separately from the data, but it's still preventing me from making changes to the forms.

I assume it means corruption of some kind has occurred, but I'm unclear as to what has caused it. Clicking on 'Debug' does nothing. Immediately below the above message it asks if I want to compact and repair the database. I say 'yes' and this also achieves nothing.

In addition, I have tried the following to fix it:

1) Opening the database window and go to Compact and Repair, via the Tools menu. This appears to fix the problem, in that I can then view the VBA code. However, the next time I log out and back into the db, I get the same problem all over again.
2) I am currently importing all of the items (forms, tables, menus, macros - the lot) from the problematic databasse into a blank one, in the hope that this will fix things. This is either taking an extremely long time or the process is hanging. Does anyone know how long this typically takes and what 'Task Manager' should say is happening? The db is 42.5Mb, if that makes a difference.

The above are the only suggestions I've been able to find.

1) Is there anything else that I can try to fix this?

2) What has happened to start causing this problem? I've run through suggested things like suddenly closing the db incorrectly, opening it using another application, etc. and none of these apply.

Hi
I have a command button on my form which when clicked changes a date in a field of evey record in a table. I have updated the code so that an extra msgbox comes up asking if it was the current user who did teh work. If the user clicks yes a field changes to their login if they click no then it changes to unknown.

All this work but I want to be able to change evry record the same as i have with the date ( i have an update query for the date) but how can i do this if there are two possible outcomes?

Here is the code incase it help you know what i mean

Private Sub Command65_Click()
If MsgBox("Are you sure you want to change this?", vbQuestion + vbYesNo, "change Requested") = vbYes Then
DoCmd.RunMacro "macro3"
End If
If MsgBox("Did you carry out the 100% check?", vbQuestion + vbYesNo, "change requested") = vbYes Then
Me.user = [CurrentUser]
Else
Me.user = "unknown"
End If
End Sub

Macro 3 runs my update query for the date field

Thank you

I need to create (code or macro) a button on my form that will revert all entries under one field on a subform back to "No".

I have a form with a search function in it. The user can search for various parts of an address or name and click search. The results are shown in the subform. In this subform there is an option for to "assign" an entry (or 10). The user will then select the entries they want and click the preview report button. The report only shows the entries that are switched to "yes". When they have printed their report I need the "yes" selections to revert back to no so the next user doesn't ahve a lot of "yes" entries messing up their choices.

Is this possible? Does somebody have an example database of how to do this?

I appreciate the help.

Greetings everyone,
I developed a form (frmLog) based on several tables including a table called tblLog. On tblLog, I've have several (5) yes/no fields which I will be using on frmLog. These fields are "Alarm", ""Lost", "Call In", "Routine", "Required". I want the user to have to physically answer with either a Yes or a No. If I use a simple check box, many users will have a tendency to rush thru a form and never check anything off. So by making this field a required field, they will be forced to specify one way or another. Using the Wizard, I created 5 Option Groups, using the fields from tblLog as my field sources. Each group contains both a yes and a no button. My problem is I'm not able to check any of these buttons. I see the buttons but they remain blank after I click on them. I've either missed something in the Properties or there's some coding I need to use to make these buttons fully functional.
BTW, I'm barely getting by using the Macros and I'm clueless on coding. I would need detailed information on how to go about writing any necessary code.
Regards,
Sandra

Hi,

I have a main table with three linked tables. I have set the relationships to include cascading deletes i.e. if a record in the main table is deleted, those in the linked tables are also deleted. This is working OK.

Now I have put a "Delete this record" button on the form for the users. I just used the wizard to create it. This button also works fine and all required records are being deleted.

However, Access is not displaying the warning message or asking the user to confirm the delete. One click and the records are gone - that's it. This strikes me as dangerous. The user could accidentally click the button even though I've tucked it away in a corner of the form and make the font bold red.

I've used an autoexec macro in which I have SetWarnings to 'Yes' but that has had no effect. I'm sure I've done this in other databases and I can't see what I might have done differently this time. The message is supposed to say something like "Relationships that specify cascading ..(something) ...are about to cause 1 records in this table and records in underlying tables to be deleted. Do you wish to continue?"

How do I turn this on?

Hi,

I have information from three tables (A, B, C) that I am trying to combine into one form. The relationship between table A and B is one-to-many (hospital to patients in the hospital). Tables B and C are one-to-one, where depending on the answer to the last question in B, all values in C will be null or the person will enter values for C.

I have created a form and subform where "A" is the form and "B" is the subform. I have two problems. B form needs to look like a table (tabular or datasheet), but "B" has 47 fields which is too many for tabular, and if it is a datasheet then I'm worried about data entry errors since the user could click on any field from any record accidently. Is there a way to either create tabular (or a wrap-around) for all 47 fields or a way to lock each record in a datasheet once data have been entered?

The second issue is with table/subform "C". If I add this subform to the same form, how can I create an event (preferably using macros) that goes straight to the correct record in "C" if "Yes" is answered to the last question in "B".

Thanks!

E

Hello Code Hungry Vultures!

This is a database demo complete with source. It illustrates how to force a shutdown or automatically shutdown from inactivity for a specified number of minutes.

This allows forcing users out for emergency maintenance such as compacting & repairing because of partial corruption or whatever, and inactivity shutdown for those who loiter in the database when off hours scheduled maintenance is needed such as compacting & repairing, or updates to tables, etc.

It is a front end database with a linked back end database.

On first opening Shutdown.mdb use the shift key to prevent the Autoexec macro from executing.

Then refresh the link for table MsgCenter in BACK_END.mdb.

The fields in the MsgCenter table:

ShowMsg----------Yes/No---Set this to True/Yes to force shutdown
MSG--------------Text-----Message to display in ShutdownMsg form
MsgBxTitlBar-----Text-----Titlebar caption to display in ShutdownMsg form
MaxInactiveTime--Integer--Max Inactivity Minutes
StartMonitor-----Integer--Time of day to start monitoring inactivity - 0 (12AM) to 24 (12PM)
EndMonitor-------Integer--Time of day to end monitoring inactivity - 0 (12AM) to 24 (12PM)


After link is refreshed either exit Shutdown.mdb and open again to run the autoexec macro or just run the autoexec macro without exiting.

Then open the MsgCenter table & click ShowMsg & close the table.
Forced shutdown will occur immediately.


For inactivity shutdown:

Open the MsgCenter table.

Change StartMonitor to an hour earlier than the current time.
Change EndMonitor to an hour later than the current time.

Set MaxInactiveTime to 1 (1 minute) and close the table.

Inactivity shutdown will occur in 1 minute.

OpnSeason

Hi, I have made a table in which I have stored all the contacting details of the sellers. Then I made a query by which when you ask for a name of a seller, it will show you all the contacting details of that specific seller. I made a form based on that query and I put the names of the companies in a combo box. I wanted to enable the users to use this combo box to find the names of the companies and by clicking on the names all the contacting details of that seller would be shown on the below empty boxes. But, it does not work !!! Shall I link it to a macro or VB editor? If, yes, will you let me know how to do it? Thanks in advance...

I feel that these questions have somewhat simple solutions. However, in my haste to design this database I've skipped over some of the simpler tricks and tools Access has to offer. If the description isn't enough for you I can try and provide a sample DB. Fact is it's so big right now that it'd take a while to isolate what I'm doing right now. Sorry in advance for the lack of consistency with naming conventions.

Form: Resource Utilization
Actual Form Name: "Copy of Owners" (it started out as a test but I ended up liking it)
Purpose: Show contents of ResourceAllocation table
Current setup:
Record Source: ResourceAllocation tableContinuous FormsA text box for each field is linked to the proper control source in a rowLabels in the form header correspond to what eventually displays in the "column" belowIn this way its a nice, well formatted form that simply displays all of the records without looking like a datasheet

What I want:
Display Condition 1: If User.usertype = 1, then display only records where Owner = User.username (an Owner logs in and should only see his owned resources)Display Condition 2: If User.usertype = 2, then display a combobox of the Owners, and filter records based on selection of combobox (a manager logs in and has a choice to view all the Owners)Display Condition 3: For both Display Condition 1 & 2, allow sorting in all of the fields for the records (sort by LastName, Project, etc)

Table: ResourceAllocation
Fields (important fields in bold):
LastName - Lookup from EmployeeData table"First Name" - autopopulates based on LastNameDeleteRecord - Yes/No for a delete queryProject - Lookup from Projects tableFunding Method - Value listOwner - Lookup from Owners tableStart Date - date/timeEnd Date -date/timePercent Utilization - number

So, with my current setup, how can I get it to filter and lock the Owner based on a usertype of 1? I have a With statement in my log in form that stores all the information needed to denote user type and look up the name or OwnerID. I simply don't know how to create the filter or the Where condition or whatever may be the best way to implement this. I also don't know how I can add sorting to these columns. I imagine I should change the form header labels to buttons, so that when you click it, a macro or VBA sorts the display for you?

Any and all help is much appreciated. If I have time I'll put together the sample tonight if no one understands the request.

I have two problems.
PROBLEM ONE - installable version
The right click sort/filter menu isn't provided in the installable version using runtime. I need to make my Access file in an installable version for users who don't have Access. I got around that by saving my file as a separate version that I use to make the installable version, and created my own menus with macros attached to the right-click event in every field in every form - yes, by hand, to EVERY field in EVERY form!

The macros are mini menus for text, dates, numbers & checkboxes using using RunCommand, with arguements of:
SortAscending, SortDescending, FilterBySelection, & ExcludeBySelection

The only differences between the different field types is the wording.
numeric: Sort Largest → Smallest
dates: Sort Oldest → Newest,
text: Sort A→Z
check boxes: Sort Ticked → Unticked
sorting works fine for all of these, and filtering works fine for the 1st three.

BUT, filter by selection gives a message ONLY for the checkboxes - something about 'this action cannot be executed...'
Any ideas why? How do I get around it?

PROBLEM TWO - ACCDE version.
For users with Access, I want my code/tables hidden/locked, so I've made ACCDE versions. Everything works exactly the same EXCEPT:
on forms, I've set the property FilterbyOnLoad = Yes & SortbyOnLoad = Yes, so users can retain their preferences, but these options ONLY function in the full Access version. Filtering & sorting can be DONE in the ACCDE version, but are NOT retained when the form is closed & reopened. They ARE retained (as expected) in the full Access version.

Any ideas why? How do I get around it?

Hello,

I have a Form with < Data entry> Yes. with some fields in it.
Also created a button: Cancel

Macro's 'OnClick':
CloseWindow: [The window I am now]
Save: No
OpenForm: "XYZ"

If I add just something to a field and click 'Cancel" there is a record added to my table. This is not suppossed to happen.
It just should 'throw away' that data and go to the form XYZ.

How do I make the Cancel button throw away those filled in letters and go to XYZ.

Hi,

I have a form with a button w/ macro which generates a report based on the entry currently shown in the form. Think of it as a "generate order receipt" function.

I am experiencing problems with data not being updated in the table when a user clicks on the button to generate a report. Not all of the update information is shown on the report when it is generated. Needless to say, this is problematic.

How can I force an update/save on the form before the report is generated so that the report will have the most updated data when it is displayed and pritned?

I tried adding "requery" to the macro and that worked fine, but when I am in the form in "Dataview=Yes" mode, it doesn't work.

Thank you in advance for any advice or pointers!


Not finding an answer? Try a Google search.