Modules & VBA :: Access 2010 / Loop Through Query To Assign A Value To A New Field?
Aug 16, 2013
Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.
I have my query connected to VBA code.
I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.
Code:
Function BdMbrNumber()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
Dim ORG_NAME As String
[code]....
View Replies
ADVERTISEMENT
May 29, 2015
I have a query that formats data into a list of personnel who are coming due for refresher training. The query is formatted to provide me all the details that I need to create an outlook task, and assign it to the person. I tried creating a button on my form to create a record set of that query, and then create / assign a task to each person based on the values from the recordset.
Code:
Private Sub SendTasks_Button_Click()
'Create the recordset and set it to the appropriate query
Dim rst As DAO.Database
Dim rstData As DAO.Recordset
Set rst = CurrentDb
Set rstData = rst.OpenRecordset("SendTasks_qry")
[Code] .....
Basically, the compiler stops at "Dim myOlApp As New Outlook.Application" with the error: "Compile error: User-defined type not defined"
View 14 Replies
View Related
Jul 28, 2014
In MS Access i want to be able log jobs and be able to assign my stock to the jobs i have logged is this possible ? how to create a database to log jobs but how can i make it so when i add stock to the job it will remove it from my stock pool and assign it to the customer thus taking it out of stock.
View 3 Replies
View Related
Dec 18, 2014
how to make this two fields in my form to say Y or N if the field is Not Null. Like if the field is not null = Y Else = N for the two fields. I have a picture to show what i'm talking about.
View 2 Replies
View Related
Jul 1, 2013
I am using Access 2010.Most of the time a tenant pays his exact rent. When that happens, I currently type in the payment (taken form the Rent field) and date of payment (current date) into a form. The date should not change.
I would like to place a checkbox into the form. If checked, the rent would be entered into the payment field and payment date (current date) would be entered automatically. Otherwise, I would just enter in the payment and date manually.
FIELDS
Rent
Payment
PaymentDate
on form chkPayment
View 4 Replies
View Related
Mar 4, 2015
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
View 10 Replies
View Related
Jul 19, 2014
have a query which I would like to export to excel 2010 and would like separate files saved using a unique field called [Brokerage]. The code below exports the query however does not export separate worksheets as I am missing something perhaps the OutputTo function.
Private Sub Commission_Excel_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim temp As String
Dim mypath As String
[code]....
View 14 Replies
View Related
Dec 17, 2013
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
View 3 Replies
View Related
Feb 6, 2015
I have 2 tables, "Counselors" and "Appeals". There are several names in the Counselor table but it is not a fixed number of names (rows) usually around 7. There is also some variable number of records in the Appeals table, could be 0, could be 50, depending on the day.
I need to, in a round robin fashion, assign the counselor names one by one to the records in the Appeals table. How to loop through the 2 tables and update the name field in the Appeals table with the name in the Counselor table.
View 1 Replies
View Related
Feb 25, 2014
I want to create a table of all fields in a form, their datatype and their controlsource.
I have done the first 2 parts easily enough using the forms.formname.controls.name property
and
TypeName(forms.formname.controls)
But I am unable to pragmatically read the controlSource.
EG
forms.formname.controls.txtname.controlsource works
but when In a for loop
I cannot use the name of the control to return the control source.
Code:
Sub PopTab
Set frm = forms.FrmCases
for each CTl in frm.controls
MyName = ctl.name
MyType = TypeName(ctl)
MySource = ctl.name.controlsource
end if
next ctl
end sub
View 3 Replies
View Related
Mar 10, 2015
I would like Access to open an Excel file count the worksheets and then give each tab the name of the column heading in cell A1 of each worksheet. I had no problem with the code in Excel but when I tried to add it to Access I am getting an error on the WS_Count = obj.ActiveWorkbook.Worksheets.Count line. The error message is "Object variable or With block variable not set".
Here is the code:
Code:
'This block of code will cycle through each tab and name them
Dim WS_Count As Integer
Dim I As Integer
[Code]....
View 4 Replies
View Related
Mar 9, 2014
I have 192 databases that I need to redact certain information in certain columns. Generally, I'm looking for field names like name, first name, last name, address, address2, shipping address, mailing address, and phone numbers. Not sure how to get this information without going into every database and every local table inside the databases. Is there a way to get this information programmatically and just access the databases and table that I need to redact info in?
View 6 Replies
View Related
Apr 30, 2015
Is it possible to run a loop through each record in query, but refresh the query in between loops?
The query I am looping needs to be updated before moving to the next record.
t can't figure out how to get the query to refresh before looping.
View 3 Replies
View Related
Sep 20, 2013
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
View 2 Replies
View Related
Aug 23, 2013
I am trying to improve my code by making it more readible. The following code works, but it is certainly not the most efficient way. I`m trying to write a loop to make certain elements in an Access report visible/invisible, but I can`t address the visibility property of these items while iterating over i.
Code:
DoCmd.OpenReport "tblInterval subreport", acViewDesign, , , acHidden
Reports![tblInterval subreport]!BoxInsp1.Visible = False
Reports![tblInterval subreport]!BoxInsp2.Visible = False
Reports![tblInterval subreport]!BoxInsp3.Visible = False
[code]...
View 2 Replies
View Related
Aug 12, 2015
Created a new Query (called qry_Temp) from and existing Query (qry_Test that has fields Field1 - Field5) using QueryDef , and inserted a new Field (FieldX) into it (see example below) using the .Parameters property, then opened a Recordset based on the new Query in order to attempt to enter data values into the new Field (FieldX) for each record in the query - but cannot assign any data values to this new Field (but can to the existing fields) in the new Query?
Dim DB As Database
Dim rs As DAO.Recordset
Dim qdfNew As QueryDef
Dim strSQL1 As String
Dim strSQL2 As String
Dim Value1 As Integer
[Code] ....
View 6 Replies
View Related
Sep 2, 2014
I've set a database which has a table in which there are 2 fields "Account" and "Total Accounts". I want to have the amount of total summation of accounts in "Total Accounts" field of each record, which is the result of summation of "Account" values in all previous records till the current one. In order to do this purpose, I copied the value of "Amount" field of each record into "Total Accounts" field of the same record, at first. Then, I tried to add the amount of "Total Accounts" field of every record with just the amount of "Total Accounts" of previous one to earn the actual total amount of that record. I found that I need a VBA loop to do this query for all records (except first record) and so I code it as below, but it has the Run-time error '424' : Object required and it seems that I am in a mistake in definition of strSQL variable:
Code:
Private Sub doDataSegm_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("Table1", dbOpenTable)
[Code] .....
View 3 Replies
View Related
Jan 29, 2015
I have a query that randomly selects records from my table:
SELECT TOP 6 *
FROM SM_Import
ORDER BY rnd(INT(NOW*id)-NOW*id);
I would like to loop through those results, and create emails
I can get one email formed (displayed) with the code below but don't know how to get both working together to display the (6) emails:
Private Sub Command3_Click()
Dim ol As Object
Dim CaSubject As String
[Code].....
View 4 Replies
View Related
Jan 25, 2014
I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.
Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2
Dim strsql As String
Dim strsql2 As String
Dim strfilename As String
Dim strpath As String
strpath = "C:UsersDesktopHome"
[Code] ....
View 6 Replies
View Related
Oct 4, 2013
I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.
So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.
1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days))
2) tbl_Register (where I want forecasted transactions to wind up)
I've got 2 saved queries:
1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register'
SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description
FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description
GROUP BY tbl_InitialPoint.Description;
2) qry_InsertTransactions (populates 'tbl_Register')
INSERT INTO tbl_Register ( PostDate, Description, Amount )
SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount
FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description
WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];
And I've got a form called 'HomePage' that has
1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop
2) A button to start the process
It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).
I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.
Public Sub InsertTransactionsBttn_Click()
Dim db As Database
Dim qdfNew As QueryDef
Dim qdfInsertTransactions As QueryDef
Set db = CurrentDb()
[Code] ....
View 11 Replies
View Related
Nov 22, 2014
the below code works fine if i run a crosstab on a whole table however if i pass criteria to the crosstab e.g between [forms]![form]![txtstartdate] and [forms]![form]![enddate] it fails and returns null for every field? even though if set docmd.runquery "qryReductionByPhysician_Crosstab" this opens with the relevant data?
Code:
Set db = CurrentDb
Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab")
indexx = 0
[code].....
View 7 Replies
View Related
Jun 28, 2015
I basically want to Loop through a query (or if not possible trough a table) and show the results in my form. For each record in the table I am doing additional checks when loading the form and showing that result accordingly. I do not want to do these checks in additional queries - I think it is more efficient doing it in the form directly. Here is the code I have:
Dim dbsSR As DAO.Database
Dim rstValQry As DAO.Recordset
Set dbsSR = CurrentDb
Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")
With rstValQry
While (Not .EOF)
[Code] ....
The Loop seems to work because in debug it is going through it 3 times (the number of records I have in the query), but the result is always the same - it seems the above code is not checking / refreshing based on the individual query records.
View 14 Replies
View Related
Feb 11, 2015
I have the following code which successfully opens the dialog box with filters however when I add a function with an Excel import, the filters do not work at all (no files are shown even though they exist in the directory that's opened within the dialog box). If I manually type in the filter (i.e. *.x) it still shows the files.The function at the bottom was provided by cheekybuddha from another form, I'd be lost on getting the Excel file imported into Access.Here's the VBA code (the Function is also included at bottom):
Code:
Private Sub Command0_Click()
On Error GoTo PROC_ERR
Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean
[code]...
View 2 Replies
View Related
Mar 17, 2014
I have an application in Access 2003. It uses VBA to open an Excel file. The file has one tab for charts and one for data. The program counts how many charts needs to be created, one per project. It then will place 4 charts per tab, creating as many chart tabs as necessary, keep one data tab. The program then writes the data and links the data to the corresponding chart. It will also write legends, and scale the charts. This all works fine under Access 2003/Excel 2003.The error I get is "Application-defined error or object defined error".The line that gives me the error is: Set chCopy = xlsheet.ChartObjects(idx).I am using the Multi-Chart option frmChartType = 1
Now I'm moving to the Office 2010 world. It is not working. Below is the code I run to do the magic. I guess something is happening in Excel 2010 that is different from 2003.I found when I comment out the error line, and other lines dependent on chCopy it creates the charts, but all the charts are pictures and not charts!!
Code:
Public Sub GenerateChart()
' Generate an Excel chart
Dim db As Database
Dim rst As DAO.Recordset
Dim xlapp As Object
[code]...
View 2 Replies
View Related
Oct 1, 2013
I would like to update the following code I have been using to send emails, from Access 2010 via Outlook 2010, to include my signature block with hard returns in between each line and a few above the signature.
Lets say the signature would be:
My Name
My Company Name
My phone number
My Reference
I tried next to: stMessage = Me![EmailMessage] &"My Name" & Chr(10) & Chr(13) & "My Company Name"
etc.
It does not work..
Here is the coding :
Code:
Private Sub SendEmail_Click()
On Error GoTo ProcErr
Dim stLinkCriteria As String
[Code]....
View 5 Replies
View Related
Nov 26, 2013
Here is my project: For a duty plan the employees are assigned to positions in a certain area.
For example:
Name. Position. Description. Map
Brown. 1. backdoor. 1
Miller. 3. frontdoor. 1
Norman. 4. left wing. 2
Hoover. 6. right door. 2
....
Now I want to show the positions within circles on the maps, with the names within dynamically, as the employees change every week.
The coordinates of the positions do not change, but not every position is needed in every duty plan.
So, my idea is to draw all possible shapes on all possible maps and make them visible or not depending on the assignement.
The duty plan is my table in access and looping through all records I would like to fill the needed shapes.
All maps are on slides (40 slides and about 400 positions) and should be filled accordingly.
I can open my presentation, although I only could do it by late binding ( a reference to ppt 14 obj. Lib fails with an error!! - why??) but I cannot any solution how to loop through the records and do the job.
View 2 Replies
View Related
