Posts by ik2zok

ik2zok · Mar 23rd 2016

Re: Stop Access taking focus when Esc is pressed in other software

The second SW is opened by Access with automation... or is Access Dependent...?
Have you any Procedure Running in some Form Timer Event...?
ESC PressKey fire on Focused window... so i presume, that some process in MsAccess take the focus...

ik2zok · Jan 17th 2016

Re: How to Subtract columns in Access 2007

In Access(Jet) you have not COLUMN as in Excel, but Fields in Tables.
If your 2 Fields are both FieldType=DateTime you have to use DateDiff(...<F1 for Help>...), here you could chose if you want the difference in second or minute.
If your 2 Fields are some different Type... pleas think about this and convert them to DateTime, or just for calculation...!

ik2zok · Jan 11th 2016

Re: Query - Between Public Variable Dates

No, but you can use Public Function... why not...?

Code

Public Function GetToDate() As String
' Here the code to GET Value
GetToDate=Value
End Function

ik2zok · Dec 27th 2015

Re: MS Access VBA to open the Get External Data Wizard and set the options

TO allow File selection, you should use FileDialog Object.
Add Office Reference library on your Project, and search on OnlineHelp for FileDialog, you will find some code...

If you have to use RUNTIME, this could be a problem, if the End users have not Office installed..., in this case the only option will be API... but as last chance...

ik2zok · Dec 10th 2015

Re: Alter order of imported fields without writing line by line.

Not sure to undertand the meaning...
If you need to change ORDER Sequence... is available "ORDER BY" SQL Statement...

Code

Dim strSQL As String
strSQL="SELECT TOP 997 * FROM [CI_DATA] WHERE [Business_Area] ='" & ans & "'"
If OrderByChoisCriteria... Then strSQL=strSQL & " ORDER BY " & FieldName
Set rex = db.OpenRecordset(strSQL)

If you need to switch Columns, so Fields Order position... you have to build a SQL Statement changing ordinal position...

Code

"SELECT TOP ... Field1, Field4, Field3 FROM...."

ik2zok · Nov 20th 2015

Re: DLookup Issues - Textbox as Variable Criteria

The syntax is correct... check for the [User Name] or [Username] because in the first post you used [username] as FieldName, but in the Function your reference was to [User Name].

Just one more suggestion, don't use SPACE_CHAR in Field(Name)... or Control(Name)... you are obliged to use square bracket.

ik2zok · Nov 20th 2015

Re: DLookup Issues - Textbox as Variable Criteria

2 Errors and 1 Suggestion:

1st Error
On ControlSource Property the SINTAX are the same as in the QBE, similat to VBA but NOT EQUAL.
In Your Case the [,] must be replaced with [;]

2nd Error
In the controlSource Property also the explicit Reference to the Object are the same as in the QBE, you can't refere to shortform [Me]

The Final Syntax is

Code

=DLookup("[First Name]"; "tblUser"; "[User Name] = '" & [Text25] & "'")

1st Suggestion
Instead of use bad name as [text25] use the correct way to rename Object... [txtName] or [txtJob] .... where [txt] is abbreviation of Control TextBox and 2nd part [Name/Job] help you to recongnize the specification of this control...

Hope thi help.

alex

ik2zok · Aug 20th 2015

Re: Add Values to Cascading Dropboxes

Have you considered using the event NotInList...?
Set theProperty "Only in list"(sorry this is from Italian translation...) in Data TAB Properties... to No(False), this will Fire the OnNotInList ComboBox Event when you digit a new code... here you cuold try to implement your Code to ADD the New Data...

On the OnLineHelp you could find this Sample

Code

Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control
' Return Control object that points to combo box.
Set ctl = Me!Colors
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

Display More

Just a suggestion... in your case, with RowSourceType="Table/Query"(not List Value as in Help Example)... to ADD a new Record, you could use a Action Query(Insert Into...) or open Recordset on RowSource and Add a NewData.
If the RowSource is a Read/Write Source, so if SQL Statement is Writable, you coud also use Recordset Obbject of Combo...

Finally you need to execute Requery Merthod on Combo...

ik2zok · Jul 23rd 2015

Just a code to Compact a MsAccess Database:

Code

Function ShellCompact(strSourceFile As String, Optional CloseOnEnd as Boolean=True) As Boolean
On Error Resume Next
Dim lngPID As Long
lngPID = Shell(SysCmd(acSysCmdAccessDir) & "MsAccess.Exe " & _
"""" & strSourceFile & """" & "/compact")
If CloseOnEnd then Call ClosePID(lngPID)
ShellCompact = (Err = 0)
End Function
Function ClosePID(lngPID As Long) As Boolean
On Error Resume Next
Dim objWMIref As Object
Dim colProcessList As Object
Dim objProcess As Object
Set objWMIref = GetObject("winmgmts:\\.\root\cimv2")
Set colProcessList = objWMIref.ExecQuery("Select * from Win32_Process Where ProcessID = " & lngPID)
For Each objProcess In colProcessList
objProcess.Terminate
ClosePID = True
Next
Set objProcess = Nothing
Set colProcessList = Nothing
Set objWMIref = Nothing
End Function

Display More

ik2zok · Jul 18th 2015

Re: Automated process for copying data from Access to Excel on a regular basis

So, sorry to all for Reply mistake...

I Copy and paste my INFO in this new One.

You have lot of mistakes in to your code...
1° This is ACCDB not MDB, so the ADO Ole Provider must be ACE not JET..., correct with this.. VB:

Code

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strPath & strDB & ";"

2 ° The way you use CopyFromRecordset settings... is quite strange, try with this modification to understand: VB:

Code

worksheet Worksheets("Raw").Range("A2").CopyFromRecordset adoRst

Now it work...

Hope this help...

ik2zok · Jul 18th 2015

Re: Automated process for copying tabular data from Access to Excel on a regular basi

You have lot of mistakes in to your code...
1° This is ACCDB not MDB, so the ADO Ole Provider must be ACE not JET..., correct with this..

Code

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strPath & strDB & ";"

2 ° The way you use CopyFromRecordset settings... is quite strange, try with this modification to understand:

Code

' Post Data to worksheet
Worksheets("Raw").Range("A2").CopyFromRecordset adoRst

Hope this help...

EDIT: ik2zok pm'ed regarding replies to moderated posts.

ik2zok · Jul 17th 2015

Re: Duplicate Record in VBA

This revision should be more efficient:

Code

Private Sub Command16_Click()
On Error GoTo Err_Duplicate
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strMsg As String
Dim strsql As String
Dim varCTL As Variant
strsql = "Combo4|Combo18|Text6"
For Each varCTL In Split(strsql, "|")
If Len(Me!Controls(CStr(varCTL)).Value & vbNullString) = 0 Then
Me!Controls(CStr(varCTL)).SetFocus
MsgBox "Please fill required fields!", vbExclamation
Exit Sub
End If
Next
strsql = vbNullString
strsql = strsql & "Select * from TBL_COURSES "
strsql = strsql & "[COURSE_NAME] = '" & Me![Combo4].Value & "' AND [START_DATE] = #" & Me![Text6].Value & "# AND [PLANT] = '" & Me![Combo18].Value & "'"
Set rec = DBEngine(0)(0).OpenRecordset(strsql)
If rec.EOF = True Then
strMsg = "A Course Name of " & Me![Combo4] & " with a Start Date of " & Me![Text6] & " for plant " & Me![Combo18] & " already exists!"
'A Duplicate based on your definition
MsgBox strMsg, vbExclamation, "Duplicate Record"
Else
rec.AddNew
rec("COURSE_NAME") = Me!Combo4.Value
rec("PLANT") = Me!Combo18.Value
rec("START_DATE") = Me!Text6.Value
rec.Update
MsgBox "Course Added Successfully!", vbInformation
[Combo4].Value = ""
[Combo18].Value = ""
[Text6].Value = ""
End If
Exit_Here:
On Error Resume Next
rec.Close
Set rec = Nothing
Exit Sub
Err_Duplicate:
Select Case Err.Number
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Exit_Here:
End Sub

Display More

ik2zok · Jul 16th 2015

Re: Interpolate missing values in MS Access

I can suppose you could have a Missing or Broken Reference in VBA... may be you do not have DAO but ADO... or something else...?

ik2zok · Jul 16th 2015

Re: Interpolate missing values in MS Access

Sorry, i don't understand if the problem still remain... or the suggestion solve it.

ik2zok · Jul 16th 2015

Re: Interpolate missing values in MS Access

If you change the Object Name, you must be sure to change it in all places...
Didi you change also the Table Name in UDF as code shown below...?

Code

Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM 1G WHERE GID=" & GID & " ORDER BY RP")

ik2zok · Jul 15th 2015

Re: Interpolate missing values in MS Access

I find this easy solution, probably is possibile also with only SQL statement... but this is one of many solutions...
So you have a Source Table, [TT] for example... containing all Data as Excel Sheet.
Build a Query in QBE with this SQL statement:

SQL

SELECT TT.GID,
getInterpolateValue([GID],100) AS RP_100, _
getInterpolateValue([GID],475) AS RP_475, _
getInterpolateValue([GID],2500) AS RP_2500
FROM TT
GROUP BY TT.GID
ORDER BY TT.GID;

You can call this Query as you prefer...
Now you must write the UDF User Defined Function in a standard Module[getInterpolateValue(....)]

Code

Function getInterpolateValue(GID As Integer, TargetValue As Double) As Double
Dim rs As DAO.Recordset
Dim lngMIN As Double
Dim lngMAX As Double
Dim lngMINP As Double
Dim lngMAXP As Double
Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM TT WHERE GID=" & GID & " ORDER BY RP")
Do Until rs.EOF
If TargetValue >= rs.Fields("RP").Value Then
lngMIN = rs.Fields("RP").Value
lngMINP = rs.Fields("PGA").Value
Else
lngMAX = rs.Fields("RP").Value
lngMAXP = rs.Fields("PGA").Value
Exit Do
End If
rs.MoveNext
Loop
' Interpolation Calc... Straight line passing through two points (y=m*x+q)=(y2-y1)/(x2-x1)*(x-x1)+y1
getInterpolateValue = ((lngMAXP - lngMINP) / (lngMAX - lngMIN)) * (TargetValue - lngMIN)+lngMINP
rs.Close
Set rs = Nothing
End Function

Display More

You will obtain

20	0,168233321726374	0,221579826015437	0,297028149070928
21	0,166293623501173	0,224055743400377	0,32735096153983
22	0,167512870556529	0,224797728457201	0,329019955773444

ik2zok · Jul 14th 2015

Re: Need Help with calculating cumulative sum in Access

I think you can understand that with NO PK or Unique Field used as OrderBy is not possible obtain in a Query the runningTotal as your request, because you don't know where you are each Row.
So ADD a Counter FIELD named [IDCounter] just to have a test, and try

SQL

SELECT IDCounter,ID,Prob, DSum("Prob","Tablename","ID=" & [ID] & "AND IDCounter<=" & [IDCounter]) AS RunningTotal
FROM Tablename ORDER BY IDCounter, ID;

This give you the same result of XLS Table sample you attached.

ik2zok · Jul 14th 2015

Re: Need Help with calculating cumulative sum in Access

Quote from B.Coleman;751586

This one deals with dates. I am getting confused.

There is almost 2 way to do Running TOTAL, this are two easy example that you can adapt
I assume:
T1 Main table
ID is PK Field
FieldValue is the Field we want RunningTotal

SubQuery
SQL
1. SELECT
2. a1.ID,
3. a1.FieldValue
4. (SELECT Sum(a2.FieldValue) FROM T1 AS a2 WHERE a2.ID <= a1.ID ) AS RunningTotal
5. FROM T1 AS a1;
Aggregation Function (DSUM)
SQL
1. SELECT ID, FieldValue, DSum("FieldValue","T1","ID<=" & [ID]) AS RunningTotal
2. FROM T1;

ik2zok · Jul 14th 2015

Re: Need Help with calculating cumulative sum in Access

Hope this Help:
http://https://support.microsoft.com/en-us/kb/290136

Posts by ik2zok

Stop Access taking focus when Esc is pressed in other software

How to Subtract columns in Access 2007

Query - Between Public Variable Dates

MS Access VBA to open the Get External Data Wizard and set the options

Alter order of imported fields without writing line by line.

DLookup Issues - Textbox as Variable Criteria

DLookup Issues - Textbox as Variable Criteria

Add Values to Cascading Dropboxes

[Office VBA] Compact MsAccess Database

Automated process for copying data from Access to Excel on a regular basis

Automated process for copying tabular data from Access to Excel on a regular basis

Duplicate Record in VBA

Interpolate missing values in MS Access

Interpolate missing values in MS Access

Interpolate missing values in MS Access

Interpolate missing values in MS Access

Need Help with calculating cumulative sum in Access

Need Help with calculating cumulative sum in Access

Need Help with calculating cumulative sum in Access