Modules & VBA :: Controlling Font Within A String

Jul 20, 2015

I have spent the better half of the afternoon researching how to accomplish controlling a bold font in a string I am building and sending to Excel.

Code:
' set text for safety hazards and controls
Concretetxt = "Concrete Demolition & vbCrLf & Hazards - dust, flying debris and skin Irritation. & vbCrLf & Control - Respirator, goggles, gloves, inspect equipment prior to use."
Excavationtxt = "Excavations & vbCrLf & Hazards - Damage to underground wires, collapse, falling materials/equipment. & vbCrLf & Control - Ensure utilities have been marked and hand dig when in close proximity, shore properly, Keep clear when lowering materials."

This is a sample of how the strings will build. I will select a task like Concrete Demolition and string it together with other tasks I would like "Concrete Demolition" in bold with the blurb following it then the next task would follow with a bold heading.

My thought is now that it may be easier to control by putting this data into a table instead of building the strings.

View Replies


ADVERTISEMENT

Modules & VBA :: Checkbox Controlling Navigation Tab

Oct 10, 2014

I am trying to figure out how to control a navigation tab with a checkbox. I have a database for my fire dept and the check box is called "deceased". When a member passes away, the active box gets unchecked and deceased gets checked. Under the member subform, there is a tab, navigationbutton19, that is called deceased also and under this tab all their burial information is stored. What I am trying to do is, if the checkbox is not checked, I want the tab to be enabled = false.

View 1 Replies View Related

Modules & VBA :: Controlling Scroll Bar Position

Jul 5, 2013

So I've got a form:
- A set of unbound controls filters (using a dynamically-built WHERE statement) a subform in datatable view.
- A set of bound controls displays the currently selected subform record; the two reflect one another.
- A set of unbound checkboxes at the bottom allows the user to change which fields are displayed in the subform.

Turns out, all of this makes for a pretty big form. Some of the users have screens that will not fit the full form due to resolution settings, physical size, aspect ratio, etc. Whatever the reason, the result is one or both scrollbars appearing on the form.

The issue is this: Whenever the filter controls are changed, the form's Current event runs (because the After Update events for the filter controls all change the main form and subform's Recordsource). But the Current event causes the form to requery (or refresh?), meaning the focus returns to the control with Tab Index 1 (a "Home" button near the top of the form).

Thus, the form tends to "jump around" a lot when users are determining the records they want displayed.Is there a way to avoid this?

- Can I prevent the "Home" button from getting the focus during those times?
- Alternatively, can I set the position of the scroll bars so the user doesn't see that jumping around every time a control is updated? I found some interesting ideas that worked for continuous forms, but mine is a single form, so bookmarking methods won't solve my issue.

View 7 Replies View Related

Font Type In Form Changes When Change Font Colour

Aug 1, 2014

I am experiencing a problem in MS Access 2013. I have a form which was working just fine until now. It has form labels in Calibri font and their font colour is one shade lighter than black. Today, I changed the font colour of the form labels to be Automatic - black (shows as 'Text 1' colour in the form properties). I did this in Design view.

I find that when I switch to layout view, the font of the labels appears as Century Gothic. This is the font that appears in the Form view. So while the font colour is correctly changed, the font name is not what I had set in the Design view. When I go back to the Design view, and change the font colour back to the original colour which was one shade lighter than black (Text 1, Lighter 50%), the font is correct in the Layout view and the Form view (Calibri).

So it seems that there is some sort of binding between the font colour (black) and the font name (Century Gothic). When I change the font colour, the font also changes. When I restore the original colour (one shade lighter than black), the font Calibri is correctly retained.

View 4 Replies View Related

Modules & VBA :: Controlling Fields In A SubForm Displayed In DataSheet Mode

Dec 2, 2013

I have a Form to Display Projects that has a SubForm displaying Sub-Projects related to them. The main Project Form has a Combo Box which allows a user to select a ProjectID to display. The SubForm has a ProjectID Field as well. When the Project ID is selected in the Main Form, only the appropriate Sub-Projects are displayed in the Sub-Form.

On the SubForm, there is a Field named ActionID, which uses the following VB Code to limit the availability of the Fields in the SubForm that apply to Dates. If the selected Action is Install, only Install related Dates should be available. Likewise, if the selected Action is Remove only Remove related Dates should be available.

Code:

If Me.ActionID.Column(1) = "Install" Then
Me.AssetInstallMonth.Enabled = True
Me.AssetInstallYear.Enabled = True
Me.AssetRemovalYear.Enabled = False
Me.AssetInstallMonth.Locked = False

[Code] ....

Everything seems to work as expected until there is more than one Sub-Project is the SubForm and they do not contain the same Action. In these cases, it appears as if the Sub-Projects in the SubForm are not being treated as individual records. If Sub-Project is Added or Modified to have an action of "Install", all records are treated as Install Records (only Install related Dates are available), regardless of their content.

Is there a way to update the properties for only the row that has been selected?

View 5 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Modules & VBA :: Choosing Specific Font For Emails

Jan 30, 2015

I would like to have Access launch an Outlook email with a specific font (Arial). How could that be accomplished?

Here's my code:

Private Sub cmdEmailAO_Click()
On Error Resume Next
Dim OApp As Object, OMail As Object, signature As String, Email As String, FirstName As String
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)

[Code] ....

View 9 Replies View Related

Modules & VBA :: Text Font Color Based On Tab Selected?

Aug 8, 2014

We have a navigation page with 5 tabs and several navigation buttons underneath their respective tabs linking to reports.

In the main part of the navigation page we have 17 search parameters (text boxes and combo boxes)that the user can use to sort through all the reports we have in the different tabs.

What we would like to do is to have the label text to change to "red" if one of the 17 fields are "required", remain "black" if it is included in the report but not a mandatory search parameter, or turn "light grey" if that parameter is not included in that report.

For example:

My search parameters are: people, phone, and cars

If I were looking at a report of people that included addresses, phones, etc...name and phone would be required search parameters. However, even though I can search by car, it is not in this particular report and the label text should be greyed out. (If the actual text box could go inactive that would be even better).

I have read about buttons being turned colors based on a drop down box choice, but I have not been able to find anything about using a navigation tab subform button to make the colors change in the main navigation form.

View 14 Replies View Related

Modules & VBA :: Edit Font Color Based On Field Value & Age

Mar 4, 2015

I am attempting to adjust the font color of a date field on a report based on the value of two other fields. I have the below code set in the "On Format" property of my detail section - however it does not work when I open the report to view.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Frequency = "Annually" Then
If Me.ClassDate < (DateAdd("yyyy", -1, Date)) Then
Me.ClassDate.ForeColor = vbRed
Me.ClassDate.FontBold
End If
End If
End Sub

View 1 Replies View Related

Modules & VBA :: Using Mid In A String

May 1, 2014

I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.

Here is my code.

trimmed_department = Trim(Replace(new_department, vbCrLf, ""))

Example value being "123 Point 5 Finance and Accounting"

I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.

View 8 Replies View Related

Modules & VBA :: How To Add A String To A Date

Feb 27, 2014

I want to add a string as year to a date.

Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.

Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

View 3 Replies View Related

Modules & VBA :: Add A String On Top Of The Calendar?

May 19, 2015

I have set calendar control 12.0 up and everything works how I want it to (click date and peoples names in a table to the left to show scheduled meetings on that day). What I want is to add a string on top of the calendar. For example, this monday I would like for it to say "Memorial Day" on the physical calendar itself.

View 3 Replies View Related

Modules & VBA :: Cut Off First 6 And Last 2 Characters Of String

Mar 24, 2014

I would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...

View 4 Replies View Related

Modules & VBA :: Splitting A String

Oct 5, 2013

I have made a system for managing service calls and fieldworks.Part of this is checking the boards we install/service are working correctly.When the engineer calls in, we check the board - enter serial number, via ODBC talks to main server DB and pulls back what is listed below, along with ID and date/time which is all displayed in a list box.

9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75

This works fine. In the string above are certain bits of information that need to be checked to ensure they are accurate and the board has been programmed correctly. What I want to do it, is when the user single clicks on the list box, it parses out the various sections of the above string and enters them into some text fields on the form. With some code these can then be checked to see if they are correct or not and alert the user if they are or not.

The checking part I can do, the part I am struggling with is parsing out the correct parts from the string.The parts will be the same parts required each time, and the string parts wont vary, just not sure how to go about it.

View 13 Replies View Related

Modules & VBA :: Converting String To Variables?

Jan 30, 2015

I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following

Code:
strleftB = Chr(34) & " & me."
strRightB = " & " & Chr(34)
strTemplate = Replace(strTemplate, "[", strleftB)

I then get a string but in need to convert part of the string into variables, before I capture the correct output for my email

Code:
"<div>RFC Submission: <strong>" & me.ChangeID & ", </strong> " & me.Details & "</div>"

My question is: what is the best way to split the string into strings and variables

View 7 Replies View Related

Modules & VBA :: Use String To Call Code?

Sep 12, 2014

I am trying to use a textbox value to call vba code and can not get it to work.

I keep getting an error on the call str1 line.

Code:

Sub formscript()
Dim str1 As String
str1 = [Forms]![fscripts2]![t3]
'MsgBox str1
If str1 = "" Then
str1 = "err1"
Exit Sub
Else
Call str1 ' this is where it call the script based on value in textbox
End If
End Sub

View 14 Replies View Related

Modules & VBA :: Concatenating Emails Into A String Via SQL

Sep 3, 2013

There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.

Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)

I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.

So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.

Also, I have created the string, called "emailsList"

Code:
Private Sub Email_Button_Click()
'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home"));
Dim emailsList As String
DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True
End Sub

Do I need to do a loop that concatenates?

View 11 Replies View Related

Modules & VBA :: Result From A Query To Specify String To Use

Jun 7, 2015

I have a query that returns the string to use but I am not sure how I can actually use the returned value to do what I want.

e.g.
my table:
Partner ID Backup Field
5023949 UPC
501354 GTIN

my query will return 1 [backup field] value from the table above, and I then want to use that value to get another value that was previously declared in my code.

eg
GTIN = "ABC123"
UPC = "123ABC"

if my query returns the value "GTIN" then need to use the value of GTIN (ABC123) in my next part of the code.

msgbox GTIN would result in a message box ABC123

But I am not sure how to get the result value. I can say msgbox [backup field] but that returns "GTIN" not "ABC123"

View 14 Replies View Related

Modules & VBA :: How To Construct String For Variable

Sep 10, 2013

I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf

The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.

mFilename = "C:RPR AccessPDF Reports Emailed" & Format(Now(), "ddmmyyyy-hhnnss") & WorkorderID & ".pdf"

DoCmd.OutputTo acOutputReport, mReportName, acFormatPDF, mFilename, True

I just can't figure out how to construct it correctly.

Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.

View 8 Replies View Related

Modules & VBA :: Ask For File Path To Put In A String

Mar 5, 2014

I'm importing a txt file using the following code.

Now my question is, how can I make visual basic ask me to select a file to get the path and put it in a string.

And use this string in the DoCmd.TransfetText

Code:
DoCmd.TransferText acImportDelim, "IN-specification", "tblimport", "C:usersKimdesktop extfile.txt"

View 1 Replies View Related

Modules & VBA :: How To Get Portion Of String Into Variable

Jun 19, 2015

How to I get a portion of a string into a variable?

I want to capture the table name of a recordsource.

The problem is sometimes the recordsource is "Select * from mytable where ID = 6" or "Select * from mytable" or "mytable"

I want to put "mytable" in a variable for later use.

View 11 Replies View Related

Modules & VBA :: How To Find A Table Name Containing Particular String

May 21, 2015

I have a command button on a continuous form(form 1) and I need this button to open another form(form 2) when I press on it. So far so good.

When I press the button, I need some VBA to open the form(form 2) , search for a particular table name based on the open form(form 1) current record and use that table name as the newly opened form (form 2) data source. I have ways to do most of those task but for one thing:

How do I make access search for a table name containing a particular string? Here's what I am working with:

Code:
Private Sub Commande26_Click()
On Error GoTo Err_Commande26_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim stDataSource As String

[Code] ....

View 7 Replies View Related

Modules & VBA :: String To DateTime - AM / PM Missing

Oct 10, 2013

I have a form which has a text box with date and a text box with a time. DD/MM/YYYY AND HH:NN:SS

In vba I have declared A as a date and done the following

A=Format([Textbox1] & " " & [Textbox2],"dd/mm/yyyy hh:nn:ss AM/PM")
msgbox (A)

msgbox shows everything but the AM/PM

Now I need need to pass this date to excel to do a vlookup which works if I do the following

A=#03/05/2013 11:26:00 AM#

but it wont work if I grab the date and time from the access form, I think it is because the AM/PM is missing.

View 13 Replies View Related

Modules & VBA :: Remote Connection String?

Aug 30, 2013

I am trying to put together a vba adodb connection string to a remote server. It is the first time I am using adodb in this context. I can get msysobj.connect but how do I format that information into a connection object connection string?

View 3 Replies View Related

Modules & VBA :: How To Make One String Contains All Of A Column

Jul 25, 2013

i want to make a string that contains all of the fields in a column.

I have a table called UserSelectedComponentT with a column called ComponentName.

I want a string that is essentially all the different component names seperated by a " + " .

View 4 Replies View Related

Modules & VBA :: Pull Numbers From String And Get Max Value?

Jan 3, 2014

I have a series of IDs in an 'articles' table stored as text, e.g.

hb-123456789-e-068
hb-123456789-e-0069
hb-123456789-e-70
hb-123456789-e-00027

and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.

The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.

Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:

Code:
selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))

Code:
Public Function MaxArticleERef(hbID As Long) As Variant
On Error GoTo err_handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim maxERef As Variant

[code]....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved