Modules & VBA :: Displaying Content / Value Of A Couple Of Variables
Sep 24, 2013
Debugging my VBA code. I'm trying to track/display the content/value of a couple of variables to make sure they have the right value at a certain point.
What is the VBA code to say "display content of variable Var1" ? Where exactly will it display the value when instruction is executed?
I know I can just hover the mouse on a variable to see its content but some string varables like SELECT commands are too long and you cannot see the whole string.
View Replies
ADVERTISEMENT
Feb 4, 2015
how many elements matching to my primary elements from any records of my query and count match, if some element doesn't match then I need to add it to my primary elements, then at the end (rst.eof) count how many primary elements I have.
E.G
id colours
1 blue;red
2 purple;blue;green
3 red;violet;purple;blue
dim matching_elements as long
dim primary_elements as string
dim TheNumberOfPrimaryElements as long
First of all, if I open recordset primary_elements is empty so I need to assign a value form first record.
primary_elements = rst!colours (so primary_elements = blue;red)
Now I can start comparing my primary_elements with second record:
matching_elements= 1
primary_elements = blue;red;purple;green
comparing my primary_elements with third record:
matching_elements= 3
primary_elements = blue;red;purple;green;violet
It's my last record so I need to count primary_elements
TheNumberOfPrimaryElements = 5
I need "matching_elements" to count other function in my application.
View 7 Replies
View Related
Apr 16, 2007
Can I create contents of one field based on the contents of others?
I have a database of pc systems which we've tested and was wanting to generate a single text line to identify the individual pc
The single filed would contain data from the following fields
Job Number
CPU Type
CPU Speed
RAM
HDD size
eg
So the new field would contain "Job345-P3-1000-256-20"
Can this be done?
View 3 Replies
View Related
Oct 15, 2014
I have a module which generates an autonumber based on a table content (Tbl:Numbervalues) and a prefix (prefix held in Tbl: TextValues)
the module itself works fine and after every getprefix & getnextnumber it updates the number within numbervalues table to the next unique figure.
However what i want it to do is within my database i want to loop through all the records and update a column based on the getprefix() & getnextnumber()
so at the moment lets say column A has values of SHA000001 (in all 100 records) i want it to loop through those 100 records and update to be SHA(from the prefix table) and 000001 all the way to 000100.
Code:
Public Function GetPrefix() As String
On Error GoTo GetPrefix_ErrorHandler
GetPrefix = DLookup("Value", "TextValues", "Description = ""InvoicePrefix""")
If Len(GetPrefix) <> 2 Then
msgbox "The Invoice Prefix Value in the Text Values Table is not 2 characters long!", vbCritical, "Critical Warning"
End If
[Code] ....
View 5 Replies
View Related
Jul 8, 2013
I am working on a report that will have 16 sub-reports, one right below the other. Because of the complex nature of the calculations in the groupings it seemed easier to "build" the report using vba rather than using the report's built-in grouping ability. The issue I am running into is that there seems to be no simple way to control the height of the sub-reports. I had hoped that setting "can grow" to yes would change the height of the sub reports and move all subsequent sub reports down (Allas)
As an alternative I thought too that I might be able to set the height of the sub-report control, which would make for a tricky but not impossible bit of programming, but (Again allas) I can't seem to find a reasonable way to determine the required height of the sub-report's control.
View 3 Replies
View Related
Nov 25, 2013
As I began thinking about the data that I need to include in one of my reports I relized that I need to gather some extra data.Each design change has a lifecycle with 7 basic states from not started through to closed. States 4, 5, and 6 have two posible sub-states that I need to capture and report. It is almost like having options.
My data entry form already records the 7 basic states. What I would like to do is have another field that records the sub-states if the design change is in one of those three states.
Will a ComboBox do this?
Do I need a test routine for the After Update event in the first text box? Something that will check for states 1 - 3 and 7 move on to the next field and if states 4 -6 require users to enter the sub-state.
Would a nested if-then-else routine do the job?
View 1 Replies
View Related
Jul 11, 2015
Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:
Dim WorkoutDate as Date
Dim datecount as integer
datecount = DCount("[WorkOut Date]", "tblworkoutlogs", "[workout date]= " & WorkoutDate)
Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.
View 5 Replies
View Related
Jan 13, 2014
I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. I've also tried calling it from another sub in the same module but get the same compile error - see below.
Code:
Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String)
'code to use variables passed in
End Sub
[Code]....
View 4 Replies
View Related
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
Jul 15, 2015
Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.
Like:
Function GetDatabasePath() = currentproject.path
possible?
View 5 Replies
View Related
Sep 11, 2014
How can I assign some type of index to the below variables (data representation) ? I'm using VBA to build a .RFT file for MS Word.
"Line # 1 data"
"Line #2 data"
"Line #3 data"
. . . .
"Line #7 data"
"Line #8 data"
"Line #9"
. . . .
"Line # 22 data"
"Line #23 data"
"Line #24 data"
View 14 Replies
View Related
Oct 9, 2013
I need to set some 'global' variables with default values when my Access 2007 database is loaded. Depending on the user etc these values may be modifed after Access starts but defaults need to be set.
I declared the variables as 'Public' in a Module, then put a function in the same Module (to set the default values) then tried to call the function from an "AutoExec" macro, so it's the first thing that runs when Access loads.The macro throws an error - it can't 'find' the function(?)
View 2 Replies
View Related
Apr 10, 2014
What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.
Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"
[code]....
I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?
View 3 Replies
View Related
Oct 29, 2014
I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.
Code:
Public Sub txtSearch_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant
Dim i As Integer
Dim IngLen As Long
[Code] .....
These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way.
View 14 Replies
View Related
Apr 9, 2015
I have a Change Management database with several fields, the key fields that I need to talk to work together are as follows:
[Patch] , [CVE] & [Patch Approved]
When opening a form I have I can sort it by the patch number which will then sort out all other CVE's that are not associated with that patch. What I would like to be able to do is approve one patch under the [Patch Approved] field and have it carried through the rest of the filtered CVE's that have the same [Patch] number/ field.
View 1 Replies
View Related
Jul 9, 2014
I wrote this module
Option Compare Database
Option Explicit
Public Function OdometerInput(varodometer As Variant) As Long
Dim varKilometres As Variant
varKilometres = varodometer * 1.609344
OdometerInput = CLng(varKilometres)
End Function
It works fine in the immediate window (although I haven't just fathomed what to do with null values and such) But my question which I am sure will be 'easy when you know' is how do I pass the variable to it from a text box on a form and retrieve the data in another text box on a form.
View 4 Replies
View Related
Sep 30, 2013
I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)
Code:
Private Sub btnDateStamp_Click()
' UserInit is global variable
Me!Notes.SetFocus
Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _
" -" & vbCrLf & Me!Notes
[code]...
I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.
Code:
Function DateStamp(varFormName As String, varMemoName As String)
'varInitials, varFormName, varMemoName are global variables
Forms!varFormName.Controls!varMemoName.SetFocus ' Error here
'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _
[code]...
how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.
View 3 Replies
View Related
Jun 6, 2013
I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then
DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())"
AgentFilter = "CASEOWNER ='" & Me.txtName & "'"
DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter
Exit Sub
End If
View 13 Replies
View Related
Jul 18, 2013
I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.
Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.
Here is a brief idea of what my Table would look like, where where the data to import would be obtained from
Fields:
PromoID, DiscountID, PromoCode, AddedBy, DateAdded, UsedBy, DateUsed
Spreadsheet
PromoCode
Form:
DiscountID, AddedBy (environ("username") query), Date ( DATE() )
Note, the PromoID is a autonumber.
View 2 Replies
View Related
Aug 26, 2014
I am trying to set a filter on a form with VBA using variables and having no success at all. The code I tried last, which seemed to be "close, but no cigar", was:
Code:
Dim sFilterValue As String
Me.FilterOn = False
sBiller = Me.txtbiller.Value
sLastSource = Me.txtbiller.ControlSource
sLastSource = "[" & sLastSource & "]"
sFilterValue = sLastSource & "=" & sBiller
Me.Filter = sFilterValue
Me.FilterOn = True
sLastSource and sBiller are global variables. When I debug this code the sFilterValue is exactly what I would plug in manually and the Me.Filter shows up as "[Field Name] = Filter Value". But it crashes on Me.Filter = sFilterValue. I have tried all sorts of combinations but nothing I've tried works. What is the proper syntax for using variables as filters?
View 2 Replies
View Related
May 29, 2015
I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.
Code:
Dim vrt As Variant
Dim upSQL As String
For vrt = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(vrt) = True Then
Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))
[Code] ....
View 3 Replies
View Related
Jul 1, 2014
I am writing a process for a hospital application in the UK
We have a concept of "Takes" which are periods to which patients coming into hospital are assigned to and thus the consultant (specialist) who looks after them.
They are fixed times between 0800-2000 (Day take) and 2000-0800 (+1) Night take.
I have written the following function to try and determine which take a patient should be assigned to based on the time now to power various queries to enable the end user to quickly see "today's day take" and "yesterday's take"
Testing it I seem to not be able to hit the right "Take times" in my output. I suspect it's a problem with the way I'm initiating my variables as Today 0800 and Today 2000
Code:
Function GetTakeStarttime()
Dim StartTime As Long
Dim EndTime As Long
Dim CurrentTime As Long
Dim YestStartTime As Long
[Code] .....
View 2 Replies
View Related
Mar 12, 2014
I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.
Dim strModule As String
Dim strSQL As String
Dim strQty As String
Dim strEye As String
Dim strTest As String
[code]....
This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?
View 5 Replies
View Related
Sep 28, 2013
Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.
Private Sub Test2_Click()
Dim strSQL As String
Dim strSalesman As String
Dim strContentArea As String
DoCmd.SetWarnings False
[Code] ....
Error
Microsoft Visual Basic popup
Run-time error '3061'
Too few parameters. Expected 1.
View 5 Replies
View Related
Mar 2, 2015
I want to check to see if ALL textboxes are blank, then issue a message. My "Select Case" statement and "If" statement does not work. How can I accomplish this ? Below is what I have.
Blank = Null
Select Case Blank
Case strChartOfAccts1,
strChartOfAccts2, strChartOfAccts3, strChartOfAccts4, strChartOfAccts5 & _
[Code] .....
View 5 Replies
View Related
Apr 22, 2015
Is there any way of opening recordsets as public or global in a form? E.g. if i do something like
Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)
the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.
View 6 Replies
View Related
