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.
View Replies
ADVERTISEMENT
Sep 13, 2013
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).
View 1 Replies
View Related
Sep 20, 2013
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
View 5 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
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 14 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 19, 2015
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)
View 1 Replies
View Related
Nov 20, 2014
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.
View 4 Replies
View Related
Oct 9, 2014
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?
View 1 Replies
View Related
Nov 6, 2013
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.
View 2 Replies
View Related
Dec 1, 2013
I have a table in my DB called, devices. This contains all the information about various devices we have deployed in the field.
These devices are also contained in 2 other separate MySQL DB's.
What I need to do is add 2 additional field to my access table for the DeviceRecno and DeviceID of the same device from the MySQL DB's.
Adding the field is easy, but I cant think of a way to enter the recno and ID from the other DB's without typing them in manually for each one.
The common between them all is the serial number of the device, and I can get a list of serial numbers, recno's and ID in an excel sheet.
like a vlookup in excel to easily populate all the existing records with the recno and id's from the other db's?
When I created the access system there was no intention to link it to the other DB's for any reason, but that has now changed due to a lot of reasons.
View 4 Replies
View Related
Feb 12, 2015
I have a main form and a subform.
Both forms have the field called JobID in common.
Both forms have a field called JobStatus.
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?.
View 3 Replies
View Related
Jun 27, 2005
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
View 14 Replies
View Related
Sep 28, 2012
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.
View 3 Replies
View Related
Mar 11, 2014
I can't seem to get my date field to update to today when I add information to another field.
My table has a lookup field, date field, text field, text field, text field.
I have set the date field to default to today for each new record.
I work with this table as a subform.
I would like to have the date field update to the date I go back to the record and update the first text field.
I have tried using after update, and an if - then statement, but nothing seems to work.
View 10 Replies
View Related
Jul 11, 2014
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
.....
View 2 Replies
View Related
Apr 19, 2013
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]
Loop
View 6 Replies
View Related
Apr 16, 2015
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.
View 5 Replies
View Related
Feb 28, 2013
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.
View 8 Replies
View Related
Feb 16, 2014
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).
View 1 Replies
View Related
Nov 16, 2013
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.
View 2 Replies
View Related
Nov 3, 2012
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...
View 5 Replies
View Related
Jun 27, 2014
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
BRANCH BRANCH ID
Braintree BRA
Colchester COL
View 4 Replies
View Related
Jun 18, 2014
Table1 contain Two fields (3Months) and (6Months)
Table2 contain Two fields (3Months) and (6Months)
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
View 1 Replies
View Related
Dec 5, 2012
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
View 3 Replies
View Related
Dec 3, 2013
I'm trying to complete a database.
It is to manage details of pupils with additional support needs, and plan for the extra arrangements the school will provide for assessments.
It has 2 Tables
tbl-PupilDetails
-ScottishCandidateNumber primary key (Unique number which identifies pupils to the exams board)
-Forename
-Surname
-DOB
-YearGroup
-Class
-NatureOfNeed (memo)
-EvidenceOfNeed (memo)
tbl-SubjectLevelArrangement
-ID Primary key, Autonumber
-Pupil foreign key to tblPupilDetails
-Subject
-Faculty
-Level
-Arrangement
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.
View 6 Replies
View Related
