Ok, say I have a table with the following fields, custid, mainnum, subnum
whereas the data would look like this:
Code:Cust Main SubA101 1 2A101 2 1A101 3 4A101 4 2A101 5 3B202 1 2B202 2 4B202 3 1C303 1 2C303 2 1
The main number always counts up from 1 on each new customer, I already have code to do this. I am running a for next loop based on the highest main number (I already have this part), for instance, for Cust A101, the for next loop would essentially be: for x = 1 to 5.... like I said, I have this part.
What I want to do when x is counting, to pull up the mainnum value that equals the value in the for/next (in other words equals X), and run a for next loop based on the sub number based for that record. For example using C303's data:
Code:For X = 1 to MaxOfMainNum '<---I already have this 'code to pull up subnum where mainnum=X For Y=1 to SubNum_For_Selected_Mainnum 'I will be doing an insert to table here custid,x,y Next YNext X
The parts in Bold in the above code is what I'm needing an answer to.
I tried to be as concise as possible, but I know it might be confusing.
In a form I have a button which creates (in preview) an outlook message with a pdf report. It opens and displays just as I want it to, but if I chose to save that message in outlook and either send or cancel (i.e. allowing user to save to drafts and send later) then MS Access crashes.To ensure this wasn't to do with any compile / corruption within my full application I mirrored the process in a very simple 1 table / 1 form / 1 report db and still the same - access crashes after saving the outlook message and either sending the message or cancelling out the message.
Form code is as follows;Private Sub Command0_Click() On Error Resume Next DoCmd.SendObject acSendReport, "Table1", acFormatPDF, "someemaddress", , , , "REQUEST FOR INFORMATION" DoEvents On Error GoTo 0 End Sub
In my main application this process is embedded within a loop to send various filters of the report to different recipients, which works fine if 'save' is not used within Outlook..
I am needing to develop an audio catalog and would like to know if anyone has experience with embedding audio in an Access database. The ideal situation would be to have all audio contained within the database, however, some of the audio files may be quite large and I don't want to bog down the entire database by having 100MB files in there either.
So I guess my question simply is; if my audio files are 50MB or larger, embed or link?
In the form view of the db I have a bound box for an embedded ole object I use it to embed Word documents for each db entry (The database is for works of art and the Word document is the description of each work of art which often contains Chinese Characters, hence the Word Document)
The box is set to show an icon (The Word Icon) Upon entering the data and moving off the record, subsequent return to to the record sometimes shows that the icon is gone from the box, it is blank. Clicking the blank box brings up the Word Document but then closing the Word Document and re-clicking the blank box dos not bring up the Word Document. The standard message saying that the ole object is empty appears and all the data from the Word Document is lost.
this has only happened since moving to Office 2007. I upgraded the db to 2007 format and we are using Word 2007.
A Google of the symptoms doesn't bring any meaningful results, does anyone have any ideas?
I tried to keep this as short as possible (which wasn't easy) if you need more info just ask
I have an embedded excel workbook on an Access form (Access 2002)
I would like to change the zoom of the excel "view".
I've tried this:
' xlCharts is an embedded XL workbook xlCharts.Object.Parent.ActiveWindow.Zoom = 100 I get an error that says: "Unable to set the Zoom property of the Window class"
However, if I READ the zoom level, it works fine. The zoom property of the window class in excel is read/write, but this may not be the case for embedded OLE objects. Is there another way? Does anyone have any ideas?
I have a form with a calculated time difference between a "Start" and "Stop" time I have managed to get the correct formula however I would like to display this value in a more civilized forma as HH:MM on the example attached I have some values that appear as 4:8 and it should be 04:08.
The second problem is I need to embed this calculated value on my tblTime as doing so I can run more easily some queries.
pretty Excel sheet full of conditional formatting that is used as a vehicle assignment board where I work. I know I can embed the file into an Access form using an unbound object, and therefore "integrate" it into Access, however this creates a copy of the file, and therefore when people change the sheet, the original Excel file is not updated. Is there any way to embed the excel file in such a way that the Excel document itself is also updated? Basically some sort of an active, two-way link rather than what amounts to just importing a copy of it? I need to be able to pull data from various cells in that Excel file so it needs to be kept up-to-date.
I would like to know if it is possible in Access 2007 to add a button/hyperlink where by the order number (as in the example below) is being sourced from another field [ORDNR]
i.e. replacing the 0996653 for example with a lookup to the ordernumber held in another column?!
[URL] .....
this link is one from a web url - I want to copy this in my DB.
I apologise in advance for my newbish question - I'm very green at Access. I'm trying to build a contact database for our company that lists Jobs done by customer.
I'll confess I "borrowed" the sample database provided with Access to help me out - which has worked well up until this issue.
The problem I am having is when I am working on our "Client Service History" form. (Basically the Workorders by Customer from the template - I can take a screen grab or something if it helps).
This form includes a sub-table which lists the WorkOrder ID by customer, the Date Entered, the Job Type, the Engineer and whether it was chargeable. The latter three options are choices controlled by combo boxes from the Workorders Form/Table. Where the status for "Chargeable" always comes up correctly, the Job Type and Engineer always show a number - which I presume is the ID of the label of each type - I.e. "5" refers to "Warranty Repair".
There are separate Data Tables for Engineers (EmployeeID) and Job Types (JobTypes). How do I get it to display each item correctly; as a summary of jobs done for each customer?
I need command button(s) to switch between subforms on a mainform in only one subform window (one subform already set as default). So in other words, I don't want a command button to open up new forms (I know how to do that); I need to minimize the amount of subforms and forms I have open by only having a few major user interfaces.
Sorry for the terse beginning, I thought ^ would help when u run your mouse over the topic and get the first line of the message so ppl knew what I was on about instead of "hello please help me" hehe. Now for greetings: Hi folks, great site, helped me silently for awhile but now I gotta ask for help.
In the scheme of things, I'm still a newb; I've learnt a whole lot real fast, but I need a "short cut". I've looked in the forum and found one relevant message but it was a little too brief (a higher knowledge level than mine).
The message I referred to earlier mentioned "toggling visibile property" (?) and also "setfocus" - I need that clarified and fleshed out please. *Note* My Visual Basic: IsNull, Else If Not IsNull (Me.ShockingandBasic). Me.Junkee Brewster = learns really fast when shown. I know a bit about command buttons and I don't usually have problems with forms or anything like that, but I cannot conceptially understand how I can have extra subforms linked to show up in my default subform window
I would rain diamonds (I'm working on it) on the person who has the time/patience to give a little "step-by-step" dialogue (including how to chuck in those extra subforms); don't be afraid to patronize me, spell it out if you like (err, please do infact) :) Thankyou kindly.
I'm trying to create a simple embedded macro for a checkbox control on a form [Company?]. The default value for this checkbox is No.
I want to write an If statement that basically says "If[Company?] = Yes, then GotoControl[CompanyorNameCombo], Else GotoControl[CustomerFN]
Although the checkbox field label and the control itself IS named [Company?] in the table and set as a Yes/No field, when I try to write the If statement I get an error that says "Microsoft Access cannot find the name 'Company?' you entered in the expression"
That's the name of the control and it is included in the table and shows in the field list.
I need to replace the embedded image on a large number of reports and forms. I'm looking for a way to loop through all of the controls in my forms and reports and if the control is an image, replace the embedded image with a new one. Can this be done using vba?
I've been developing a new db and have been adding buttons to forms all along without any issue.
I would create a button and under the event tab in the properties sheet all the buttons used to indicate "On Click" would produce an [Event Procedure]. Suddenly the "On Click" now indicates an [Embedded Macro] is going to run, which is not what I really want to have happen......
I am having a terrible time getting this to work. I have a mainform that contains 11 multi-list boxes. That mainform when I select whatever I want in any of the 11, select all items in each of the 11, or select nothing and click my show results works great. In my detail section it displays the information it should. I need to now take that and put it in a report with a pivot graph. I created a subform that is my pivotgraph and the reason for a subform is the end-user still wants the ability to filter more should they choose.
In addition, the regular graph you can put in the report does not allow me to put in multiple items. For instance I need to have sumofmbrstargeted and sumofmbrsconverted in the values and it will say I can only choose 1 value and then it will say up to 6 items and I have a 8. So, I opted for a subformpivotchart. If I create the following code, the subform updates based on the filters on this mainform everytime. Works like a charm.
Code: Private Sub cmdGetGraph_Click() DoCmd.OpenForm "Graph", acFormPivotChart, , GetFilterFromListBoxes End Sub
The problem is, I want the Graph in my Report. If I take the Report and do the same basic thing:
Code: Private Sub cmdGetReport_Click() DoCmd.OpenReport "Search", acViewPreview, , GetFilterFromListBoxes End Sub
It asks for the parameters again. Example, LOB, Plan, Prod_NM. I tried just disregarding the report and created yet another subform for the reporting piece and embedded the graph and that still asked for the parameters. It works great for the subform as the graph and I want to have the ability to use the GetFilterFromListBoxes, apply it to the Search Report with the embedded Graph.
I'd read a few places that embedding pics wasnt the greatest of ideas, but I thought that 1 JPG embedded onto 2 forms would be ok and would prevent the pic from being deleted or moved. (Theyre always screwing with the server here.) But it's making my DB huge.
No pics embedded Size: 808kB
1 Pic Embedded into 2 forms Pic Size: 156k JPG DB Size: 135 MB
Is this normal? I could understand a 156k pic adding somewhere around 156k to my DB, but 134 megs? Anyone have any insight?
Hi Folks, I'm sorry to be stupid, here, but I can't get this to work. I have a parameter query that forms the basis for a report. To get the value for the query, I have a form on which the user selects the value they want from a combo box (cboProgram). The values in the combo box include all of the programs used in the file plus a value called "All" which means - just include all the records. My problem is that when "All" is selected in the combo box, no records are returned.
Things to mention: "All" has a value of 1 in the lookup table. I tried the query with the actual values and it works fine. The problem is when I try to do it from the combo box.
My first attempt was to put: iif([forms]![frmFormName]![cboPrograms]=1, "*", [forms]![frmFormName]![cboPrograms]) in the criteria for that field in the criteria box for lngPrograms.
Access' response was that the criteria value was too complicated to resolve.
So, I noticed that I wasn't saying 'Like "*"' in the first part, but if that's the problem then I can't get the numbers of quotes right. I also tried switching the iif statement around and saying <>1,[forms]..., else some version of 'Like "*", but that didn't work either.
Then I queried around the forum and found a recommendation to make a function to return the value, so I did the following:
In the criteria for the field in the query: IfAll([forms]![frmFormName]![cboPrograms])
And in a module:
Public Function IfAll(ProgSel) Select Case ProgSel Case 1 IfAll = "Like ""*""" Case Else IfAll = ProgSel End Select End Function
I'm still not getting it. If "All" is selected, value = 1, then no recorsd are returned. Can anyone help me out please?
I thought this would be pretty simple, but I cannot figure out how and wasn't able find the solution here or anyway.
I have a source form. (I can resort to having a Table with OLE Object if I must, but prefer not to.) On this source form I wish to include different things such as company logo. This will determine what logo shows up in all the reports, so all the reports will have an image linking back to this embedded image on the source form.
Pseudo-code Example: frmSource contains imgLogo rptSample contains an image whose control source = Forms!frmSource!imgLogo
How can I do this or achieve the same effect in a more-or-less simple way?
I'm using ACCESS 2010. I have a form which is having a embedded word document. What I'm trying to do is i want to enable user to format a mail in the embedded word document which will contain rich text and screenshots then with a click of button an outlook mail should open and content of this document should be pasted there. Once user sends this email, I want to save content of embedded document in the database.
I have code that automatically send emails out from an Access Customer Contacts Database. I am using Access and Outlook 2007 but the code needs to work with later versions of Access and Outlook.
I have very poor knowledge of coding and usually manage to cobble something together from looking at other code on the net but don't understand most of it.
I have the following code which works perfectly except I want to be able to embed an image in the email body (not have the image as an attachment but actually show it in the body of the email).
Most of the code I have found around this topic is too complex for me to understand and utilise within the context of the code I have.
Ideally I want to take the image from an attachment field in a table returned by the "tblMailingList_Query".
Code: Private Sub Command10_Click() Dim MyDB As Database Dim MyRS As Recordset Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem
I have a report that i export to pdf from access 2010 using OutputTo. The report is about 10 pages long. Every time i run this report, at page 5 and beyond, random letters become distorted, sometimes with a box sometimes with a question mark. All fonts I've tested are embedded. Times New Roman, Calibri, Arial, Georgia and others.
Attached is a sample of of the distorted pdf output.
I'm working on a database for my holiday pictures. The pictures are organized in folders per trip/date/location.
When viewing the data of a particular trip, I want the form to display a random image from the respective folder (path stored in database).
I already found a function to count the files in the folder:
Code: Function GetFileCount(folderspec As String) As Integer ' Returns a count of files in folderspec, or -1 if folder does not exist Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(folderspec) Then GetFileCount = fso.GetFolder(folderspec).Files.Count Else GetFileCount = -1 End If End Function
I am using Access 2007 with Windows XP. I have a scatter chart embedded in a form that is plotting data from a subform with a trendline that is extracted and used on the form for calculations. After having the table set up correctly and running fine, I closed the database and then reopened it to have the table plotting the data in a made up 1 to 1 relationship rather than what is in the datasheet. The only way I have been able to fix this error is to change the chart type to something else then back to a scatter plot and remake the trendline. Why this is happening and how to prevent it?
