I am new at VBA in access and I am working on an application I didn't develop but I am maintaining.
Code:
Private Sub Command122_Click()
Dim formName As String
formName = getFormName(tblName)
DoCmd.OpenForm (formName)
DoEvents
Forms(formName).FilterOn = False
[Code] ....
I am trying to to write afunction that translates the deptid into a table 'id (myreference)
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI am building an application using MS ACCESS for a local volunteer ambulance corps and have almost complete it except for the last challenge. I have built many applications like this, with an form to add, modify and delete option for a single table. This time, the accountant asked for data to be saved before being deleted to another table so he can review it after it was deleted by a user and then delete it from that "backup" table. I am at a loss to write the code. However, being a reader of this forum taught me many things in the past and I decided to join and ask this query of the community. I know it is an insert command and I have created a mirror table for the real one with all of the twenty or so fields. The fields are the usual name, address, city state and zip with telephone numbers and some other info.
View 1 Replies View RelatedI'm trying to insert a set of values into a table using SQL.
Here's my code:
DoCmd.RunSQL "INSERT INTO tblAuditTrail([DateTime], [UserName], [RecordID], [Action], [FieldName], [OldValue], [NewValue])VALUES (" & Now() & ", " & User & ", & Me.CSM & ", " & 'EDIT' & ", " & 'Location' & ", " & Me.txtTranFrom & ", " & Me.txtTranTo & ");"
However, this returns a compile error.
I reference User as Environ("USERNAME") earlier in the code. Everything else pulls from the current table ("tblInventory").
I'm lost, since I've never attempted to use SQL in VBA.
I'm new to vba programming and I've written a code which will insert a table in the current slide now i want to insert pictures in the cells of the table how can i do that?
This is my code:
Sub NativeTable()
Dim pptSlide As Slide
Dim pptShape As Shape ' code from google to create a table
Dim pptPres As presentation
Dim iRow As Integer
Dim iColumn As Integer
[Code] ......
I have a table named tbl_imagepaths with fields:
ImageID
File
Folder
The command button has the code below, it works great and pops up with a window with the file name and path. I now just need the code to insert the file name and path into the table....
Code:
Private Sub Toggle7_Click()
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)
[Code] .....
I am trying to write vba code to enter a form. A am using an SQL statement in vba to enter the form with it.
I want to include a string variable in the WHERE clause of the SQL statement. The string variable is introduced in the DIM statement before coming up to the SQL statement.
The following is the SQL statement. numrukarta is the string variable I want to introduce. I need to know the exact punctuation to be able to insert it into this statement.
StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _
& " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _
& " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null);" AND (Not (WorkItems.Roster) Is Null))
In a Access 2003 database, I have an "Inscriptions" (subscription) database with a primary key on 2 fields idPersonnel (employee) and idSession. I have made a form so that user can select a session (in a listbox), then one or more employee (another listbox) and suscribe them to that session by using a button, which, on VBA side, first check that there is enough room on the session (defined by "MaxParticipants" field on "Sessions" table, linked to "Inscriptions" table on idSession), then insert data in "Inscriptions" table
This is working fine in a single-user environnement, but fails if 2 people want to join some employees on the same session at the same time, as I have a confirmation message between check and insertion. Therefore 2 users can select employees, get the confirmation message (at this point both are told there is enough room), resulting in having more people than expected joined to the session. Fortuneatly, if both users try to insert the same employee(s) to that table, one will get a duplicate error, but insertion will be made if employees are different.
On another DB engine, such as SQL server, I would use a stored procedure that would lock the table, do the check and the insertion then unlock the table.
But it does not seem to be possible in MS Access. What are the possibilities in MS Access to prevent a session from having more than maximum number of participants ?
I have been sending plain text emails for a while, but now I have a project where the formatting of the text needs to be specific and I need to send as rich text.
I can use the rich text from a table, excel, or word, but I am at a loss on how to get the data to Lotus Notes.
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ********
Public Function GetTargetType() As Variant
GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")
End Function
************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ********
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()
End Sub
************************************************** ********
making sure I can insert this value once retrieved.
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
The string that works perfectly is:-
Code:
strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The one that generates the error message, which is identical except that I have removed the list of fields is:-
Code:
strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The execute command is:-
Code:
gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
the code I have started off with is
Code:
CurrentDb.Execute "INSERT INTO ColdTemperatures (ProductID, ColdTempDate, Temperature) VALUES (" & Lettuce & ", #" & Me.RealTime & "#, " & Me.Lettuce & ")"
which adds 1 successfully however if i repeat the code above for all 15 this Im assumming will create a potential bottleneck and slow the system down
is it possible to add all 15 records at once? do you think Im going at this the right way
I got the following problem.
This code doesn't calculate the exact value:
Code:
Sub TEST()
Dim strSQL As String
Dim b As Double
DoCmd.SetWarnings False
b = DLookup("MC", "COMMON_DISTRIBUTION", "YEAR = 2015")
[Code] ....
My variable b is 0.230.
The TNS is multiplied with 0.228.
It seems a problem with the Str(b).
I have a database which needs to be updated everyday. So I decided to make a subroutine to perform the update steps just by clicking on a button.
The problem is that I can run all the steps except one! At the end of the steps, there is a function/module that must be run to calculate and complete the job.
How I can run a function in a vba macro. This is my code:
Private Sub cmdUpdateDatabase_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry1AddNewStockSymbols"
DoCmd.OpenQuery "qry2AddDailyPrice"
DoCmd.OpenQuery "qry3UpdateStockDailyPrice"
DoCmd.OpenQuery "qry4AddOverallStockValue"
DoCmd.OpenQuery "qry5AllSymbolsActiveDaysTractions"
DoCmd.OpenQuery "qry6SortToCalculateDailyRemainedStock"
DoCmd.SetWarnings True
End Sub
Module name is: CalculateDailyRemainedStock
I have 2 database files.
database A has a button (buttonRun) that when clicked, it opens database B and runs the autoexec macro.
In database A, there's a label control (lblRun) next to the buttonRun that has its back style property set to transparent.
What I'm trying to accomplish is this:
After the procedure in database B completes, it exits the system. I want
lblRun's back style property to be set to normal.
I already have a function that does this and the function works fine from within the database A.
Code:
Dim frm As Form
Dim color As Variant
Set frm = Form_frmReportingDashboard
frm!lbl_cmslite.BackStyle = 1
I need to trigger that function from database B before it exits.
I have a module for importing several excel files from a folder in Access. The user is prompted to select the folder which contains the Excel files.The issue:The code works fine if I select a folder from my company network drive (strFile variable returns with correct file name).The code does not work if I copy and paste the same folder to my desktop and select this location (strFile variable returns empty)
Code:
Option Compare Database
Private Sub btn_ImportData_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
[code]....
I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.
Code:
Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database
[Code]......
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?
I have got a problem on one of our computers.
We use Microsoft Access databases to enter test data and eventually generate reports.
On 1 computers i get the following error when opening the report:
"Function is not available in expressions in query expression 'Left(.....)"
I figured this is a reference problem so the next thing i did was to pinpoint what reference was causing this error.
It turns out it is the Microsoft DAO 3.6 Object Library. Simply removing the reference and adding it again fixes it and allows you to open reports just fine.
The problem is though, this message comes back every now and then. Which is getting annoying and the person who is making the reports is about to throw his computer out of the window.
I'm having a problem with argument in custom function..My table is like this:
myValues
123.5
32.7
65.8
11.1
What I want to achieve is to multiply each value with the average of all values, so that at the end I get (avg(myValues) = 233.1):
myValues2
7196.963
1905.593
3834.495
646.8525
For this I would like to have a function (this is simplified example of bigger problem, that is why I need function, otherwise simple SQL would suffice as mentioned below also), so that I could do something like this:
'SELECT TestFn([myValues]) as myValues2 From MyTable' or
'SELECT TestFn("[myValues]") as myValues2 From MyTable'
and I should get the table above.
My code is like this
Code:
Public Function TestFn (FieldName) as String
Dim myAvg as Double
myAvg = Davg(FieldName, "myTable")
TestFn = FieldName * myAvg
End Function
I understand the problem is that Davg needs string, but if I change to string then the multiplication does not work. The 'FieldName' parameter does not have a Type specified because that is where the problem is if I give string Avg works ,but calculation does not. If I give Number, avg does not work.
P.S. "as String" at the end of function is not a mistake, I need number as a string
P.P.S I did not inculde NZ either as in my example it can never be Null.
I'm trying to delete a file in a network location. Sometimes the file is there, sometimes it's not, so I'm using the following code:
Code:
strPath = "etwork driveMy Folder"
strFilename = "MyFile.doc"
If Not Dir(strPath & strFilename) = "" Then Kill strPath & strFilename
Occasionally, I get a runtime 52 error when the file doesn't exist. I don't understand why I'm getting this when, if the file doesn't exist, the Dir function should return "".I searched the forums for Runtime 52, and didn't find anything pertaining to this.
I have one color scheme I want to use all through the database I am developing. The next examples have just one color defined, to make it simpler (a dark blue, that I would call B1)
Code:
private sub setlabel()
Dim B1
B1 = RGB (0,52,105)
me.label1.forecolor = B1
end sub
... however this means I have to repeat the color definition every sub, so I thought would be neater to define a function to set my color codes (I have 20 colors).
Code:
Function SetColor()
Dim B1
B1 = RGB (0,52,105)
End function
My objective, is when I'm working in forms, Iwould (ideally) call this function "setcolor" and just write my code for the blue. I tried the examples below:
Code:
Private sub setlabel()
SetColor()
me.label1.forecolor = B1
end sub
[code]...
Again, this is probably some definition of arguments or dimensions that I am not aware oh. How to predefine the colors in a function to give them a "short" code which I can call in any sub in the database?
I have the below function that works correctly. However, I would like the "Last_Nbr_Assigned" value to start at 0000 and increment by 1. So the very first number assigned would be 0001, then 0002, then 0003, and so on.
This number is automatically assigned so I haven't had luck with the validation properties. And if manually enter 0001 for the "Last_Nbr_Assigned", it will automatically cut off the 3 zeros, leaving me with 2 instead of the desired 0002.
Code:
Function NewRF() As String
Dim startRange_Var As Long, lastNum_Var As Long
On Error GoTo Err_Execute
'Retrieve last number assigned for RF
groupname_Var = Nz(DLookup("Group_Name", "Codes"), 0)
[Code] ....
I started using Access recently and I have an inquiry about VBA codes and sql queries. I have function which has the following signature: RAG(Stability as Integer, DS_SYNC_RATE as Integer, Profile as String) as String.
I would like to call this function in Insert sql query in order to calculate and add a new column to my table. I tried this basic query:
Insert INTO my_table VALUES (RAG(my_table.stability,my_table.ds_sync_rate, my_table.profile))
but it doesn't seem to work...