Modules & VBA :: Pull One Max Date From Value In Four Different Text Boxes On Continuous Subform
Jul 17, 2015
We're looking at a way that we can easily display what stage our clients' email marketing campaigns are at - in one section of our CRM our Campaign Manager will enter information on whether the campaign has broadcast (ie: emailed out to the required circulation list(s)), if we're waiting for artwork from the client or if we're chasing for that information.
I have four text boxes (date format) hidden on a subform that I need to pull ONE max date from.
So, as an example:
Email1 (our first email to the client chasing for artwork) = 01/01/2015
Email2 (our second email to the client chasing artwork) = 08/01/2015
Email3 (our third chase email) = 15/01/2015
Broadcast (the date the email campaign was finally sent) = 29/01/2015
I would need some code to show in an unbound textbox "Broadcast: 29/01/2015"
But, on the other hand, if the dates looked like this:
I would need some code to show the following in an unbound textbox "Last Chased: 08/01/2015"
So we can easily see the status of our marketing campaigns at any stage in the process.
I've tried using IF THEN ELSE statements in the subform's On Current event, but that populates the information from the record you've selected across all the other records on the sub as well.
So I'm wondering if it's worth setting the Control Source of the unbound textbox I want to display the campaign status in as a bunch of nested IIF statements instead?
I want it to filter out any matches between the 7 fields. Here is my code:
Code: Private Sub Command19_Click() Dim strFilter As Variant, _ strSDate As String, _ strEDate As String 'check Text13 Text13.SetFocus 'set focus to Text13 to be checked
[code].....
It will only filter for Build Date 1 and not for any of the other fields.
I have a continuous form (2003) with 6 text boxes (StartTime, StopTime, Comments etc...). I would like all the text boxes to have a gray background if the StartTime for that row is less than Today().
I have an unbound mainform and an unbound subform(datasheet). The source of the subform is a query which is dynamic(I have many queries with diffrent columns). the subform source is change using a combo box selection. I have text boxes in my mainform..how do I get the data from subform to the text boxes of the mainform?
I have various comboboxes within a continious subform where you select a main field, and then it should let you only select the relent fields below it.
Example:
1.Main Product (HS_level1): Live animals
2.Sub Product (HS_level2): Live sheep and goats
3.Product (HS_level3): Sheep, live
...other fields on amounts, etc.
Adding the first product to the list is easy. Query I use:
Private Sub HS_level1ID_AfterUpdate() 'make next HS levels empty and requery the list of products Me.HS_level2ID = Null Me.HS_level2ID.Requery 'the query that is been run selects only HS_level2 that are linked with HS_level1 Me.HS_level3ID = Null Me.HS_level3ID.Requery 'jump to next HS_Level field DoCmd.GoToControl ("HS_level2ID") End Sub
But when I add the second product to the CONTINUOUS form, all hell breaks loose and the previous record's values also gets reset.
How can I ensure that VBA only runs for the selected record?
I have a perplexing problem. I'm creating a simple db to enter borrower audit checklist data with the following tables:
Questions -- A list of 17 pre-defined audit questions, with fields QNum and Question Audits -- One record for each audit (pk AuditKey is an Autonumber), and some borrower fields AuditQuestions -- Linked to Audits, with pk of AuditKey and Qnum, and a Question and Answer (yes/no) field
The Audits form has the Audits table as its datasource, and an AuditQuestions subform. When I go to a new Audits record, there are initially no subform records attached. When I enter a borrower name, the field AfterUpdate event runs the following code:
Code: INSERT INTO AuditQuestions ( AuditKey, QNum, Question ) SELECT [Forms]![Audits]![AuditKey] AS Keyval, Questions.QNum, Questions.Question FROM Questions ORDER BY Questions.QNum;
I start out with both Audits and AuditQuestions tables empty. When I run the code by entering a borrower name, I get, "... can't append all the records in the append query ... didn't add 17 records due to key violations". It acts like it's getting a null value from Forms!Audits!Auditkey, but if I select the Debug option and check the value from the Immediate pane, it shows a valid number. Also, if I run the query manually with the form open, it loads the questions correctly.
The first Audits record is loaded with questions in the subform so you can see the desired results. To see the problem, go to a new record, enter a name, and hit tab. Another piece to the puzzle is, if you do this on an existing record, it works fine: Click No in response to the error message that appears, then click End on the Debug message. Go to a different audit record, then come back to the one you just created. Change the name, and it works correctly. It's as if it doesn't know what the AuditKey is the 1st time, but if you check it in debugger, it is loaded. In fact, I put in code in the AfterUpdate event to plug the LoanNumber field with the Forms!Audits!Auditkey value, and it worked, but the subsequent query still failed.
I have a continuous subform with an unbound Concat textbox and would like to populate another field for each record with the results with a main form button onClick event.
I have a continuous subform which essentially comprises of a textbox that shows a field from a query.
The text in that box is essentially a few letters and a few numbers - what I am wondering is if I can have an on click event for the textbox, that when a user clicks the text it takes them to the record (in a different form) that matches the text contained in the textbox they clicked?
I want to show an subform where in I would like to display images,txt,xlsx that are stored in a directory as icons which when clciked would open the respective files.The info related to the images are stored in t_CustomerFiles.
Record ID, CustomerID, DestinationPath,EventFileName, 1, A, C:UsersTestDesktopCustFiles, CustA1.jpg 2, A, C:UsersTestDesktopCustFiles, CustA2.xlsx 3, B, C:UsersTestDesktopCustFiles, CustB1.txt 4, C, C:UsersTestDesktopCustFiles, CustC1.jpg 5, C, C:UsersTestDesktopCustFiles, CustC2.jpg.
I would like to show them on a continusous sub form .. I am using image control by setting its control source property but it does not seem to work..Do I need something else for the xlsx and txt files..
I am trying to use this code to filter a continuous subform based on two criteria but getting a data type mismatch error.
It works fine if I filter by just one or another. Perhaps it is not possible
Code: Dim intSpouseEntityID As Integer intSpouseEntityID = Nz(DLookup("[EntityID]", "qryEntitiesLocations", "[ContactIDNumber] =" & Me.Spouse), 0) If intSpouseEntityID > 0 And Not IsNull(Me.subformContactsHomeAddress.Form.EntityID) Then MsgBox ("There are two spouse addresses please delete one and try again") DoCmd.Save DoCmd.OpenForm "frmContactAddresses", , , "EntityID=" & Me.txtEntityID Or "EntityID =" & intSpouseEntityID End If
I have a form on which there is a series of text box controls relating to different frequencies. At the moment I enter a value in to each of these manually.
However, I would like to have an unbound text box where I can paste in all the values (6 or 8 of them) from excel and then press a button and they would be copied in to the individual boxes.
So far I have my design:
I'm not sure where to start with regard to the code as I don't know how to handle delimited text - is it column delimited in excel?
I imagine some sort of loop, such as a do until will be required but again not sure.
Code: Dim ctlTextBox As Control For Each ctlTextBox In Me.Controls If TypeName(ctlTextBox) = "TextBox" Then ctlTextBox.Value = "" End If Next ctlTextBox
to clear all text boxes on a Main Form.When I try the same code in the Form Load of the sub form - it says I can't assign a value to this object.I just want the Main Form and Sub Form textboxes to be empty when they both Load up.There is a selection combo box that will populate the main form and the sub form [Master-Detail] once a selection is made.Right now the main form has all empty textboxes but the subform datasheet is showing detail irrelevant records belonging to another master record and I want no data till a cmb box selection is made.
If, on the startup form of the attached mdb, you type anything in any of the text boxes of the FPersonale subform(which, in form view, is labeled "Shift schedule") you get a message titled "Write Conflict" saying that during the current session the record had been modified by another user, asking to choose between saving the record, copying the edits to the clipboard or discarding the edits. I've tried to get rid of the message by putting Me.Requery at the end of the text boxes' AfterUpdate event code but the message still pops up.
I have a continuous form with a text field that says "Select". There are two other fields, one of which is Brand. When the Brand Combo box has nothing in it I want the text box to appear (instructing the user to select a Brand) But once the user selects a Brand and goes to the next records, I would like the "Select" in only that record to become not visible. I tried conditional formatting but can't apply that to an unbound control ( or at least it is grayed out when I select the text box) and any other possible solutions I have found changes all of the selects - not only the one in the changed record.
I have a subform in Continuous Records format (records displayed are determined by controls on the parent). I would like to create a button that prepares an email and copies the contents of that subform in to the body of an email message.
The email. I have a method to create a new email, set To, CC, Subject and even Message Text.
Code: strTo = txtName strCC = txtManager strSubject = "Something" strMessage = "Hi, please find below list of details from X Y and Z" & vbnewline & vbnewline
That all works. Thats fine
The Records: I have found ways to have them added as an attachment, either the the SendObject and OutputTo method but I would like the list of records to be converted to a text string so I can include it within my strMessage variable, not include it as an attachment.
Code: strMessage = strMessage & "Subform Records to go here"...
I have a tabbed form from which the user can select a contact's record from a subform on the first tab, click a edit command button, and the unbound text boxes on the top of that tab populate. The user can then click the second tab with employment history which has blank unbound text boxes and another subform which is linked by the contact id to the first tab.
The user can select a record in the second tab, click a command button and the text boxes populate no problem. The problem comes when the user changes the contact on the first tab, and then tries to edit a record on the second tab. Then I get a run-time error '-2147352567 (80020009)' saying the value you entered isn't valid for this field. Why it would work the first time by not the second?
I have a form that our operators use to do their hourly quality audits on. This is getting to be a huge burden on them because right now, they enter the date, the week ending date, the month every time they have to do an entry, and for me it is a nightmare because they can still enter the wrong information. So, what I was wondering is if there is a way that I can have them select the date from a combo box (easy enough), but from that, have the week ending date and the month fields automatically update as well. Any advice? I would really appreciate it! Thanks so much!!!!
I have a pair of captioned text boxes for taking the criteria for the search result. Currently the text boxes are tied with a date field. I would like to give the user's choice of a number of date fields. After some research, I believe option group control fits for this purpose. Unfortunately, I never try this function before.
I have about 25 text boxes that get populated based on a financial transaction; quite often about half of them are empty. i wanted to gray them out if they are emtpy (.enabled = false). I could put a series of IF statements in the vba for each one, but its bulky and time-consuming. I've done for each record in tables and recordsets. Is there a way to do a for each textbox on a form?
I am having a little problem with making an audit trail for a form with some unbound text controls on it.
Since .OldValue won't work on unbound text boxes, google suggested that I put the old value in the control's tag property. This is what I came up with:
Code: Dim ctl, tbox As Control Dim strName, strOLD, StrNew, NewTag As String Dim NextOne As Label For Each tbox In Me.Controls If TypeName(tbox) = "TextBox" Then Select Case tbox.Value
[Code] ....
The first loop is supposed to dynamically assign the tag value, with whatever the textbox value is, however it doesn't. I've asterisked out the line i think may be the issue.
I have a form with three text boxes and i am trying to figure out how to ensure atleast one of these boxes is required and would error if none of these are completed.
I have a listbox where the rowsource is a SQL statement. I didn't know if I should try that or just use a query for what I am doing. The listbox is based on our client table, which is a huge number of people, and is showing 3 out of 5 fields.
I have two text boxes, one for first name and one for last name.
I would like the listbox to filter as I search by either field OR by both. Right now, I have the on change event for each with a SQL statement with a Like '*" & Me.textboxname.text & "*' and then a Me.clientlist.rowsource= at the end. So I can get each text box to filter the listbox individually, but I can't get it to filter with both. I think it is a matter of the focus changing, but I could be wrong.
I have query that creates table with 2 records each with 2 columns (2x2) and they do not have indexed ID,and query is related to combo box in other form so results are not always same but it is always (2x2) and value types are always same,
So how to show those results in text box in form,lets say 4 text boxes ,every value in one text box, i assume that i need to use DLookup() but i was able only to show first record,did not know what criteria put to go to second record.
When i select that query and create report i get what i want but i cant copy those text boxes to form that i want.
I would like to be able to join 2 text boxes and then copy them to clipboard. My initial fumbling has had me to joing the 2 text boxes as a string and then set that string as the value of another hidden text box and then copy that text box.
This does work but I would like to format the output if possible to remove the element identifiers? The string copies out as below.
Title Information<div>Description Information</div>
Is it possible to remove the <div> and any other elements that may appear either using my method or another way. Not sure if they are appearing as the Title box is plain text and the description is Rich?
