Populating Table After Search On Another
Jan 8, 2008
Hi, Im trying to, upon the click of a forms button, search the records in one table for all records matching a certain number. I then want to update another table with all these records...but am stuck in the coding. This is what i have so far...
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAllInfo", dbOpenDynaset)
With rs
.MoveFirst
Do Until .EOF
If .Fields(1).Value Is 1 Then
event_num = .Fields(1).Value
Sport = .Fields(2).Value
team = .Fields(3).Value
Date = .Fields(4).Value
Time = .Fields(5).Value
rs.Update
Set rs = db.OpenRecordset("tblCurrentEvent", dbOpenDynaset)
rs.AddNew
rs![Event_No] = event_num
rs![Sport] = sport
rs![Team_Player] = team
rs![Date] = Date
rs![Time] = Time
DoCmd.OpenForm "frmInfo" 'whose record source is tblCurrentEvent
But I really need to be updating the latter table within the loop but dont know how to do that without resetting the loop pointer?? Or would it be better to create a form for tblAllInfo. Any help would be appreciated! Tania smile
View Replies
ADVERTISEMENT
Jan 23, 2006
Hi Experts! I'm having a problem that I hope some of the experts can help me with. I am in the process of designing a database for a health insurance company that does health fairs.
I need to have a data entry form for the nurses to enter the Health Fair results. I created a form for them to search for the Employer group. They type first few letters of the group, click "find group" button and it brings up a subform (frmGroup_sub) with the filtered group. I then have the group number (field PARGRP) hyperlinked to bring up another entry form (frmHF_entry) when clicked. I need the user to be able to find the group, click on the group number, then bring up another form which autopopulates that PARGRP from frmGroups_sub into the new form, frmHF_entry and stores that group number in the table tblHF. Further, they will need to do more than one "employee" for the pargrp. So I was going to make a subform of frmHF_entry where they could enter multiple records for that same PARGRP.
I just confused myself and probably all of you...but the problem I am having is I cannot get PARGRP from frmGroups_sub when clicked to open and auto populate the form frm_HFEntry.
Here is a screen print, maybe it will help explain what I need.
http://img82.imageshack.us/img82/1065/frmhfentry1ed.png
thanks in advance for your help.
View 6 Replies
View Related
Aug 29, 2006
I have a form that I am working on.
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox).
:confused:
View 1 Replies
View Related
Jan 26, 2005
I created a form and in form I have created a combo box, and I can make as many selection I want through multiple box properties; however, its not populating in the table. Now if I keep select non in multiple menu I can input the value in table through that form. How can I make this field populated with more than one value.
Need help urgently. Thanks in advance :)
View 1 Replies
View Related
Feb 11, 2005
I have an expression in a field on a report. I would like that calculation to populate my table. Do I ad something to the expression in that field that would tell it to take the sum of the field and place it in the table? What would the expression look like?
The report is created from the results of a data entry form.
View 1 Replies
View Related
Apr 17, 2008
I need a table that has all the year dates in a field (e.g. from 01 Jan 2008 to 31 Dec 2008)
This means some 365 records! I hardly can type them all
Wanted to know how to populate this table automatically.
Thanks
View 4 Replies
View Related
Jun 17, 2005
I have search the forum but dont seem to be able to find the solution i am looking for. I am hoping its because it has a simple solution .......
I have a database which records grant applications for the current year. What i also have is a table which stores all the grant applications from last year and i have a search form which the user can search to see if the applicant applied last year. What i want the user to be able to do is if the applicant did apply last year - to be able to double click on the particular applicant on the search form and populate the fields in the new applicant table, show on the new applicant form, and delete the applicant from last years table. This would save the user time because they would not have to retype name, address etc. Can someone please point me in the right direction? I seem to have a mental blank with this one.
View 5 Replies
View Related
Oct 26, 2004
I’m looking for a formula that will take the last two digits of a “Code”, in Query, and search for the “Range”, in a Table. (Sometimes the “Range” may only have one code in it.) The value returned should of another column. (“Desc”.)
The formula should look at “Code: 123456”, in the Query, take the last to digits in the code “56” and find it in the “Range”, in the Table, returning a value of Supplies.
Query 1: Code: 123456
Table 1:
Code-------Range-------Desc.
10 ----------10------------Direct
20---------20-29--------Indirect
50---------50-59-------Supplies
Any help or guidance would be greatly appreciated.
View 11 Replies
View Related
May 5, 2008
I have a form that contains two controls for determining the law that applies for specific contracts. When you select the first control, there is a drop down from which you select the country. If you select US, then the second control has a drop down with the US states available for selection. If you select Canada, then the second control lists the provinces of Canada. If you select any other country, the state box is greyed out.
At input time, this works fine, however, I am missing something to get the second control to save the data to the underlying table. I am sure it is a simple code, but I am still a novice at code and can use your help. Here is the code for the first section. I appreciate any help. The second control cboState is currently unbound and I am sure that is part of the problem but am unsure of the solution.
Private Sub cboTermsCountryLaw_AfterUpdate()
On Error Resume Next
Select Case cboTermsCountryLaw.Value
Case "United States"
CboState = Null
CboState.RowSource = "tblStates"
CboState.Enabled = True
Case "Canada"
CboState.RowSource = "tblProvince"
CboState.Enabled = True
Case Else
CboState = "N/A"
CboState.Enabled = False
End Select
End Sub
Alan
View 8 Replies
View Related
Mar 25, 2008
Hi Guys
Im really confused on what to do
Basically I have a form, and inside that form there is the "Total Cost" field which is a field in the "tbl_Transactions" table; to display some values from a subform inside of the "Total Cost" field i changed the control source of the "Total Cost" text box to the fowllowing expression:
=[Forms]![frm_Transaction]![frm_ProductReceipt].[Form]![Cost]
But obviously because of the fact that the control source has been changed these values brought up by the expression are not put into the table so the "Total Cost" column in the transactions table (tbl_Transactions) is just blank.
Is there any way I can make it so that the values drawn up by the expression will also be entered into the " Total Cost" field in the transactions table as well as being displayed on the form?
View 2 Replies
View Related
Dec 5, 2005
Hi,
I have a form with various list boxes that display options based on a query. When options are selected the text relating to them is populated in a seperate text box at the side.
The problem im having is when I select options from the list box I want it to store the selected options in the table that holds each record when the form has been filled out and submitted. At the minute it populates everything into the table apart from the options selected from the list boxes.
Does anyone know how to do this?
Thanks in advance
Edit: I have got the control souurce of the list boxes set to the correct field in the table.
View 8 Replies
View Related
May 15, 2006
created a main form called "frmPatientDemographics" that contains
txtFirstName (Text Box)
txtLastName (Text Box)
txtDOB (Text Box)
txtCountry (Text Box)
cboGender (Combo Box)
it's record source is a table called "tblPatientDemo" that are populated based
on the information place in the above text boxes
Here is my problem
I have a subform called "frmPatientLanguageSub"
Source Object......... frmPatientLanguge
link Child Fields..... PatientID
link Child Fields..... PatientID
When list box Multi Select is set to "Simple" and I add a new patient and click on
multiple selections in the list box it does not populate the "tblPatientLanguge"
But if I change the Multi Select is set to "None" it populates the "tblPatientLanguge"
for that patient
Any Ideas why access populates the "tblPatientLanguge" if I set the Multi Select property to "None" but not for "Simple"?
If so How do I make access poplulate when the table when muliple selections are selected in the list box?
View 7 Replies
View Related
Nov 3, 2004
I hope you can help - I have two simple problems and one slightly more complicated problem (bearing in mind I'm quite an Access Novice!)
Problem 1.
I have set up a simple form for a Timehsheet where the user can select a project and then enter hours worked in 7 text boxes for Mon-Sun. Then I have create a Total Text box at the end of the row. I need the Total box to Sum up the hours from the 7 boxes dynamically. How does one do this?
Problem 2.
How do I then ensure this calculated field populated the field hoursID in tblHours? (I am able to do this with a normal field, but with a calculated field, doesn't the formula go into the Controlsource field??)
Problem 3.
I plan to put this form as a sub-form into a main form 4-5 times so that a user can select 4-5 projects to enter hours against. I would like to have a SUBMIT button on the main form, so that all the calculated hours and selected projects populate the respective tables ONLY after this button is pressed.
Can this be done?
Thanks in advance!
Sunil
View 2 Replies
View Related
Mar 15, 2015
I am trying to populate fields from one table into another field in a linked table. Specifically, I have a giving table and a persons table. I have the giving table have the persons primary ID field in the giving table. I cannot seem to automatically link them if I am adding a "gift" record. The gift is a dollar amount given by that person. I have a unique primary key for each financial contribution which is supposed to be tied to a person's primary key and last name in my "person" table. Am I missing something in this design?
View 3 Replies
View Related
Aug 31, 2011
I am trying to create a main form that selects a series of values allowing the user to enter new data or edit current data (using other forms). I have a series of cascading combo boxes that populate accordingly and become visible after update. I can successfully create the series and add new data and edit data as in my code.
How to populate the junction table from the forms so for example when editing or adding a new site it not only populates the tblSites and the tblSitesContacts it also populates the tblClientSitesJunction with values from the frmSites and also cmbClients. If I use the wizard and try and add values from all 3 tables to form it doesn't work and I am not sure how to add the ClientID and SiteID manually to frmSites.
I have attached a copy of my database :
Attachment 4334
View 14 Replies
View Related
Aug 18, 2011
I am generating a report from my database that I would like to turn into an invoice. My report generates perfectly, but I need to add an invoice number when I print the report and I need to take the data Customer ID, Invoice Total, and a maybe other data from the report and populate the Invoice Record table. I have VB code that is generating my Invoice No, creating a new record in the Invoice Record table and populating the Invoice Record Table with the Invoice number. I want to add this Invoice No to my report at time of printing, and populate the Invoice Record Table with the fields from the report as stated above.
View 10 Replies
View Related
Jul 16, 2012
I know auto-populating has been asked before for forms, but I am looking to auto-populate a couple of fields in a table based on an ID Number that corresponds to another table. I realize it is not the best practice to duplicate data in a DB, however, I am using an ID card program that requires tables to populate the ID card information. My hope is to have two separate tables for 2 versions of ID cards.
The first table is called Firefighter info with fields of ID Number, First Name, Last Name, and Firefight Certs, EMS Certs, and emergency contact information. The second table is called EMS Personnel with fields of ID Number, First Name, Last Name, EMS Certs, and other fields that will be entered manually. I would like the First Name, Last Name, and EMS Certs fields to auto-populate in the EMS Personnel table when the ID Number is entered. I have a relationship already listed between the two table that links the ID Numbers in both.
The Firefighter Info contains the information for all members of the department, regardless of whether they are EMS, or strictly firefighters. The EMS personnel table contains information strictly regarding to the EMS members. Thus, the ID number you are entering into the EMS table will always correspond to an existing entry in the Firefighter Info table.
View 6 Replies
View Related
Aug 11, 2005
Hi all ... been awhile since I have had to create a database so I have gotten a little rusty. :eek:
I have a form where some of the fields I have formulated to calculate an amount. Example... =[GrossAmt]*[FeePercent] This is calculating into the form correctly but not writing to my table for that field. What am I doing wrong or missing here?
View 5 Replies
View Related
Jul 10, 2006
I have a client table with a field called location. On a reports form that I have, I want to make a combobox for all of the locations, so it could show all the clients from a particular location and also it would reduce the errors due to someone spelling a place name wrong. I could set the source to the location field in the table, but that would show them all in the order they come out and there would be duplicates.
Any help would be cool. Cheers
Bob
View 1 Replies
View Related
May 5, 2008
Sorry for posting into another thread about this, but here's my problem:
Hello All. Finally getting my pride out of the way and asking this. I've searched and this is the closest question pertaining to the problem I'm having. I have created a form to populate a table in Access. I've created Lookup fields for parts of the table that will get data from another table/tables. I want a certain part of the form to auto-populate data based off of the last name I put in the initial Drop-down box. Say, I have a person with a last name (and there are several with the same last names), I want the remainder of that line in the form/report to populate with his/her data needed for the form letter. Unfortunately right now, I have it where we have to select each from a drop down menu, for both last names, first names and other data needed. I've tore my brain up trying to figure out how to link the data in each one of the combo boxes to the first combo box. Any help would be appreciated. These are driving me nuts as I should be able to figure this out and it's just escaping my vision I guess. Thanks in advance for the help.
View 2 Replies
View Related
May 6, 2008
I have currently been given a task to re-work some current coding within Access. This is well above my understanding of Access and was hoping to get a little assistance with some simple coding language.
The database I am manipulating has data collected from tourists about their destinations and origins.
I wish to create 2 tables based on a current table. One table will be short trips (trip length <= 150km) and the other long trips (trip length < 150kms)
I wish for these tables to be created from a table of raw data upon the click of a button (GUI interface).
If you are puzzled by my question. I'll try to make it simplier by asking:
1. what line of code will create a new table overriding old
2. what type of code is required to create this table with specific column headings?
3. How do I import information from current available tables with the condition: 'Row Y' is > X
View 3 Replies
View Related
Nov 23, 2012
I have created a database which has 9 tables, the first table being the contact information, but in all tables there are fields for firstname, lastname. Is there a way when you enter the firstname/lastname fields into the main table that it can populate the same information in to the same fields in the other tables? If so how?
View 5 Replies
View Related
Jun 14, 2015
As simple as it sounds. I have set up a table with a list of items and the costs. I then created a form with a list comprising 10 combo boxes. I want to be able to select one record from the underlying table per combobox on the form.
I am able to do this but the records in the underlying table keeps switching to the primary key field as I go along using the combobox.
View 8 Replies
View Related
Jun 7, 2013
I am trying to build a build calender to display customer orders on given days. I have the calender in place with intentions of having a list box on each day to display the orders.We have a daily report that is an excel file that lists these orders. I have linked this file to an access table in my db.
I am able to use this linked table to run a query and generate a report without any problems.When I try to use the information in this table to populate a list or combo box the only fields that will display are all numbers. If I use a field like "Customer Name" I get no information displayed. The data in excel and access are both set as text.
View 2 Replies
View Related
May 30, 2013
I have the following tables:
Inquiry (This is the main table I want to populate through the use of a form)
Programs
My question refers to two columns in the Inquiry Table
Program
Group
The program column is populated by a drop down menu that is linked from a programname column in the Programs table.
I I want is the Group column in the Inquiry table to autopopulate based on the selected programname (There is a group column associated in the Programs table) so the form field will be autopopulated.
What I have tried is autopopulating the form (form name is Inquiries) Field called Group by using =[Program].[Column](1) in the source code of the text box. This works well, but it then does not populate the main table.
I need to either
- Learn how to populate the main table column called Group based on the form autopopulating
or
- Learn how to autopopulate the column Group in in the table so the form will autopopulate
View 3 Replies
View Related
Jun 9, 2014
I have created two tables. One table list of 100 Categories that I monitor each month. The Categories are never changing month-to month, however, each might be associated with a different client month-to-month. Presently, I am manually typing in the Client information month-to-month with information pulled from the Client's table. The Client's table has a Category Field which is populated when a Client has purchased space to use it.
In essence, I have the Category Table (Fields: Record Number; CategoryName; Client Name; beginning date the client will use the Category and Ending Date the client will stop using it). The Client's Table has a lot more fields/information but it still has the same fields as the Category Table. I am trying to be able to use the Category Table and have it automatically populate with the client who is using the Category at that time. Any Category not being used by a Client then the Query should write "Open" in the Client's name field. I have tried many different scenarios but can't seem to get it to work. I must see all 100 Categories each time I run a report. It doesn't have to say "Open" but where ever a client did not use the Category it should be left blank.
View 1 Replies
View Related
