Modules & VBA :: Loop Through Tables Updating Field In One From Field In Other
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.
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.
Table A Field X Field Y 1 a 1 b 1 c 1 d 2 a 2 b 2 c
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
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
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
My father tables contains all the relevant fields with info of my products (which are listed in records/rows). My sister table contains less fields but are all present in the father table. The info is imported from excel daily into the sister table - all the fields here change regularly except for the unique ID which is present in both tables. Because the sister tables' information for the records/rows changes, it is difficult to update the selected fields of the father table.
I want to update selected fields within my father table based on the information I upload from the excel sheet (which becomes the sister table when uploaded)
When I try to run a simple update query written in VBA I get no errors but the field that I am trying to update just gets a 0 instead of the appropriate ID that I am passing.
Code: Function ExtractProjects() On Error GoTo ErrHandler: Dim db As Database, rs As DAO.Recordset, rs2 As DAO.Recordset, var() As Variant, i As Long, qdf As DAO.QueryDef, ii As Long Set db = CurrentDb
[code]....
The funny thing is if I run the query itself it works by using a parameter query but when I try and run it like this all I get is 0's in the field that it's supposed to be updating.The field [(SDSK) Charges Master].PID properties are as followed and is a linked table:
Type: Integer Indexed: Yes (Duplicates Allowed)
A debug.print of the results of the query trying to be ran is the follows:
Code: UPDATE [(SDSK) Charges Master] SET [(SDSK) Charges Master].PID = 1 WHERE ((([(SDSK) Charges Master].[IBB Date]) Between #10/24/2014# And #11/19/2014#) AND (([(SDSK) Charges Master].[Charge Num]) Like '*BAA*' And ([(SDSK) Charges Master].[Charge Num]) Is Not Null));
As you can see it is providing a number to be set to but instead it just fills it in with a 0.
I want a field that I will enter the current work experience of an employee years and months and i would like it to update as time passes. I am willing to accept it displayed as a decimal, i.e 4 years 7 months would be 4.7, I would just need to have it calculate on base 12 but I don't know how?
So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.
In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to
Code: =date()
So that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.
I have three options that I know of(or think I know of) to update but can't commit to one in particular :
1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update.
2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.
3) set to text field and convert the field to date field for comparison in queries.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
I have an access table call "Department" and two fields calls "DSec" and "DCode" like this
DSec --------DCode 50 --------70.1587 60 ---------80.6987 80 ---------60.8521 70 ---------50.1512I like to make a query so that DSec -------DCode 50 --------50.1512 60 --------60.8521 80 -------80.6987 70 -------70.1587Please Help Thanks
I have a Contact table and imported data into it from another Access Contact table. All but one field inserted correctly - the "Notes" field from the table I was importing FROM was inserted into the WebPage field of my current table. So now I am trying to update my Notes field in the Contact table from the WebPage field in the Contact table - or move the data over rather. I created an update query but when I try to run it it is asking for the Notes and WebPage parameters.
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code: Set rs = CurrentDb.OpenRecordset("r1") Set rs2 = CurrentDb.OpenRecordset("r1_old") columnCount = CurrentDb.TableDefs("r1").Fields.count strSQL = "" For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND r1.Field(1) = r2.Field(1) AND r1.Field(2) = r2.Field(2) AND .....
I am trying loop through a datasheet so that I can copy the contents of one field into another form.
I have tried using the bellow code to do this on a button click event. However when i run it I get an error telling me that the object doesn't support this property or method. Im not quite sure what this means.
Dim rst As DAO.Recordset Set rst = Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].RecordsetClone Do Until rst.EOF [Roll Out - Sign items added].Form![Code] = [Roll Out - Sign items pick list].[Form]![Item Category]
I inherited an old database. Not designed too well. Nearly 100 tables with different types of names. Some table names start with and "_", some with "tbl" and some just names.
I want to add a new field called "TimeStamp", date/time, and default value Now() to all the tables.
Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.
I am not quite sure how to lay this out. I am using Access 2010.
I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)
Tables: * Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player) * Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box) * Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box * Venue - similar to Competition table * Opponent - Similar to above two tables * Match - MatchID, Competition, Venue, etc (form corresponding to table attached)
Forms: * Player form * Match form
Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).
I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00
In my form, I've also set the field property to fixed, but it displays the value as 1.
From a table I want a text field which has a path to a file to be copied automatically into a another field of the same table with a hyperlink text type...
I (will have) a form which a user fills in to enter new data.
I have two fields,
"Branch" and "Branch ID" (Branch ID will not be on the form, just in the table)
Branch will be fed by a combo box with seven choices. I would like it so that when "Branch" is populated it autopopulates Branch ID with a code which relates to the branch, so for example
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have a main table which is autonumbered on unitid, I have another table which is linked via the unitid which holds items purchased for the main table, so the second table is a subform of the suppliers and will have multiple entries of the same unitid. What I would like to do is when an item is entered into the second table, check the main table to see if it exists, if it does populate the unitid field with the unitid from the main table, and if not exist create a new record in the main table with the next unitid number.
This in essence is what I want, bearing in mind that the tblinvoices is a subform and the tblmain is not open.
If ([tblinvoices]![Unit] = [tblmain]![Unit]) Then [tblinvoices]![UnitID] = [tblinvoices]![UnitID] End If
I currently use forms for adding new pupils, and updating pupil subjects/arrangements/levels.
I also have forms to search for specific pupils, and to create lists for faculty heads showing which pupils are taking subjects within their faculty and the arrangements we expect to provide.
I use the forms to run queries, which can then output to reports for printing.
Where I am currently having an issue is the faculty field in the tbl-SubjectLevelArrangements. (If I didn't have to report to faculty heads I would just leave it out, but management will insist.)
Currently I have a form with dropdowns for adding subject, faculty, level and arrangement manually. This is acceptable for the subject, level and arrangement because they are completely interchangeable and dynamic throughout the academic year as pupils may drop down a level, or change the type of arrangement they require.
However as faculties are inextricably linked to subjects, I want to remove the possibility of human error. i.e. when a user (me) chooses either geography, history, or RE, then the faculty will always be Humanities, likewise if the user chooses French, German, or Spanish, then the faculty can only be Modern Languages etc.
I'm convinced there must be a very simple way to ensure that the faculty field prefills based on the limited keywords available in the subject field, but I just cannot figure it out.
