I am new to access and I am using MS access 2010 .MDB database.
I have a table called "ClientTbl" with several fields. I am trying, by using a single form, create (populate) several records (rows) in this table. All the rows supposed to be the same except “Client_nickname” (text), which value supposed to be letters “PB” + “Client_ID"(autonumber) .
1) first i need by typing a number in specific field of the form to set the number of new records (rows)
2) second I would type values into fields of the same form - like (DateOfEvent (date), PlaceOfEvent (text), event (text) etc.)
3) than the loop or macro or whatever you think the best solution is - would use the “Customer_ID”(autonumber) in "ClientTbl", add letters “PB” and use them for the “Client_nickname”(text) field and populate the rest with values set in the form (DateOfEvent (date), PlaceOfEvent (text), event (text) etc.) and repeat this as many times as set by the customer.
I would start with this ClientTbl table:
Client_ID - Client_nickname - DateOfEvent - PlaceOfEvent - Event
1 - PB1 - 16.2.2011 - Boston - Annual Academy Award
After filling the form and sending the data it should look like this:
Client_ID - Client_nickname - DateOfEvent - PlaceOfEvent - Event
1 - PB1 - 16.2.2011 - Boston - Annual Academy Award
2 - PB2 - 21.2.2011 - Los Angeles - My Academy Award 2011
3 - PB3 - 21.2.2011 - Los Angeles - My Academy Award 2011
4 - PB4 - 21.2.2011 - Los Angeles - My Academy Award 2011
5 - PB5 - 21.2.2011 - Los Angeles - My Academy Award 2011
6 - PB6 - 21.2.2011 - Los Angeles - My Academy Award 2011
If anybody can help I would by very grateful.
1. Populate multiple tables from one form
2. can I populate multiple tables in one go, from a single form?
3. Update case number field in multiple Tables using one form
4. HELP! How to auto-populate multiple fields using a single combo-box
5. Use one form but have different record sets
6. Multiple rows in a form
7. Add Multiple Records on One Form (part2?)
8. Using one form to view different filters of the same table
9. Pulling data from multiple tables into one form or report
10. Update Multiple tables from one form
11. Combining fields from multiple rows into one row & field
12. combine multiple rows into one
13. Using one form with different buttons
14. Linking multiple queries to one form
15. populating a query using a form
16. Search or Filter through Multiple fields using one text field
17. Multiple queries data on one form
18. the same field in 2 tables but using one form same field
19. Multiple Tables into one form
I am creating a database for a friend, the database has four main tables and and one intermediary table ( to remove any many
to many relationships) I have popluated the DB with some test data manually and my queries thus far appear to be working
correctly. What I need to know now is, is is possible to populate all these tables using one form?
For example, one table consists of four foriegn keys, all concatenated to form one primary key, to take care of any possible
data redundancy or duplication in any of the other four tables.
My friend collect star wars autographs, so if you can imagine that one actor can play more than one role in more than one
film and that different actors can play the same character as someone else in the same film.
So my immediate problem is to populate the four main tables and each primary key from each table to the intermediary table.
If this is possible, couls someone please give me a pointer on how to handle inserting the the main data and primary keys
into each table using a command button please?
I have posted a copy of my database if this will make things clearer, bearing in mind it is only populated with a small
amount of test data. You can see from the Character Query that the results appear quite corrrectly. It's the VBA that I need
to get to grips with.
I am hoping there is an intelligent individual here somewhere, from whom I could I gain some advice on this.
Thanks to all in advance, any and all replies accepted gratefully.
I created a Database with 10 tables, Case number is PK in Table one and all other tables are related by Case Number but the
case number is not PK in the other 9 tables. Is there a way to populate Case number in all 10 tables using one field on a
single form? I am a beginner and do not know much. Any help will be highly appreciated.
I just created a new db using Microsoft's Contact List db as a template. I have modified the fields and imported all my
data, now I just need to tweark it to make it more functional.
One thing I want to do is populate multiple fields on a form using a single combo box drop down. I have included a screen
shot of the form for a visual...but Im clueless on where to begin. I am fairly familiar with how to create tables and how to
link the data to the fields, but cant imagine what else is needed to link a single option to multiple fields. Any assistance
would greatly be appreciated.
Reply With Quote
09-09-2011, 02:07 PM
Who is John Galt?
Windows XP Access 2007
Join Date Feb 2010
Location Nevada, USA
Is this what you're after?
lets see if i can explain this correctly. If there is a better way please point that out to me also. Thanks.
I have multiple forms that do just about the same thing. I want to clean up the database so I am going to use one form and
switches to vary the data or displays if needed. the form can be opened directly, linked to a button and used as a subform.
the form is based off one table, but depending on how its opened, the criteria for which record is shown changes. when used
in the subform it needs to be based of a field in the main form. When used on the form with the button that opens the form
it is using data from the button.
I have 2 different qry's for each problem. I just can't get the form to pick the correct qry va VBA code. I want to have
the forms switch to determine which qry to use.
I am new to Access. I am trying to make this form with 11 fields that takes in data and put it in a table. The form also has
10 rows. However one field in the form has some default value and is different for different rows. But the rest field will be
input by the user. So the user will input 100 out of the 110 fields.
How do I create multiple rows in a form? How do I do that?
Any Help would be greatly appreciated!
I am trying to use one form to view two different categories of the same information. In database speak I mean that all of
the information is coming from the same table but I use a field in the table to classify the record as either active or
inactive (yes or no control).
Here are the categories:
Lessons that are active
Lessons that are inactive
These two categories can be further filtered by all lesson that are inactive or active or a subset of lessons within a
specific division that are active or inactive.
When the user opens the form he/she is presented with a dialog form with a combobox and an option group control. The user
select the appropriate division from the combobox and then selects either Active or Inactive from the option group control. I
have added an row in the combobox so that the user has the option to view all the records that are active or inactive. Now
when the user clicks the go button on the dialog form, I want the main form to filter the records that it displays based on
the combobox and option group control from the previous form. In addition, I have a listbox on the main form that displays
the Lesson Number and Lesson Title of all the records in the filtered division. The user can quickly select a lesson from the
listbox to view detailed information about the lesson.
I can get this to work perfectly if all I have on the dialog form is a combobox to select a division. However, when I add the
option group control all the filters fail.
Details of the fail's:
1. Listbox is empty but details of the first record in the table display in the fields on the form.
2. Listbox is limited to the filters from the dialog form but the first record in the table, which is not part of the filter,
displays in the rest of the form fields.
3. All records display when inactive is selected in the option group control.
I am trying to find a way to do this without having to create another form and query with different filter values.
Here is my current code by object that relates to this issue. Again this code works but I want to expand on it to accomplish
what I stated above.
1. frmDivSelect: Dialog form that opens when the user opens the mainform
Private Sub gotoLessonCard_Click()
Dim strForm As String
Dim strWhere As String
strForm = "frmLessonCard"
strWhere = "[qryLessonCardDataFilter]![LDivision] = " & Me!cboDivSelect & ""
If Me.cboDivSelect.Value = 0 Then
Me.cboDivSelect.Value = Null
DoCmd.OpenForm strForm, acNormal
Me.Visible = False
DoCmd.OpenForm, strForm, acNormal, , strWhere
Me.Visible = False
2. frmLessonCard: Mainform receiving the filter criteria from frmDivSelect; code that is behind the listbox.
Private Sub lblLessonList_AfterUpdate()
DoCmd.SearchForRecord , "", acFirst, "[LNumber] = " & "'" & Screen.ActiveControl & "'"
3. qryLessonCardDataFilter: The underlying record source for frmLessonCard.
or: Is Null
Yes, this is along post, but if you have made it to this sentence I hope that you will be kind enough to add some water to my
knowledge bucket and not throw another log on my fire.
"We, the willing, led by the unknowing, are doing the impossible for the ungrateful. We have now done so much for so long
with so little, we are now capable of doing anything with nothing." - Unknown
Might have built my DB backwards but here is my problem:
I need to pull data from multiple tables in order to show a "financial summary"
Currently I have: Company; BalanceSheet; Debt; Liabilities; Income
All tables have a lot of information (which is why I built them in multiple tables).
I need to build a form where I can use a combo box to select a company from a list.
Once selected - I need to the form to pull selected information from each of the above tables. (As well as perform some math
I've been struggling with the relationships (They don't seem to make a difference) and I believe I am above and beyond what
the wizards will accomplish.
I have read thread after thread but cannot seem to find a specific answer on how to accomplish this.
To make matters more complex - Once finished I want to be able to select multiple companies and create a report from the
fields mentioned above (IE: pick company A, B, and C and have all of there "current Assets" add up on one report)
This task was originally achieved using an Excel spreadsheet but it has become to confusing for users and difficult to save
information for future use.
I believe all of my fields are constructed correctly - Now I just need to learn how to compile the data from multiple tables
into one form/report
Also - first time with a "Forum" in my life so please be patient.
I have been away from Access for a while and now I am going to start on a new project. I need to refresh my memory. I am
going to want to update multiple tables from one form....will I be able to do so? Any special code needed? Also I am going
to want to be able use a drop down to find and select a specific value/name in one table and pass the record ID only on to
another table.....ie: look up a name and pass the record ID for the name but not the name. Am I going to be able to do so?
Your help is appreciated. Thanx, Kenk
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
V.I. Capital Appreciation Fund.................3, 5 ,1
I have a table that has multiple rows for one employee.
I would like to combine them into one row.
EmployeeID, Firstname, Lastname, plan description, plan Category
ID1, John, Smith, Delta dental, Single
ID1, John Smith, Life insurance, Basic
ID1, John Smith, Long term disablity, 2/3Salary
ID2, Mary, Olson, Delta dental, family,
ID2, Mary, Olson, Preferred one, Single.
I would like to put each employee into one row.
EmployeeID, Firstname, Lastname, plan description1, plan Category1,plan description2, plan Category2, plan description3, plan
I have searched but it has all been fruitless! We have developed a lovely database ;-) which I am now trying to tidy up!
Currently we have (for example) a form for users to add new people, and a form for admin's to delete people, make changes to
data etc. Essentially these are the same form, one with an extra button (the admin one).
What I am trying to do, is just use one form for this by hiding/displaying the delete button depending on where the form was
opened from. Any ideas on the code to use? (Access 2000.... we haven't moved into modern technology yet!) For example,
three forms - A, B, C. If I open form C from form A, is shows button A. If I open form C from form B, it doesn't show
I'm trying to connect multiple queries into one form. Structure of the queries is as follows
The form should then be capable of filling in the results for each ID and for each category. Something like this:
As soon as I seem to connect the two queries to one form, I am no longer able to edit the results scores (not sure why). Any
ps Not sure wheather this is a reasonable question, please don't hesitate to tell me if it isn't
I know this is a topic that has been searched and asked alot since this afternoon I did it myself and found lots of questions
and no simple answers.
Therefore I thought I would share the simple quick and dirty solution for displaying data from multiple sources on one form
without recordset code I came up with.
It should be noted that for my purpose I only needed to return one line from each query and used TempVars as criteria from
other actions before my form(s) were loaded. However a little creativity could make this method work for quite a few
scenarios I persume.
1. Create a form of ListBoxes that contain the data you want. Create the query in the data source editor of the properties
2. Place this code in that forms "On Load" event:
Dim ctl As Control
'This selects the first record in each listbox. If column headers are set to yes the selected value should be (1).
For Each ctl In Me.Controls
If ctl.ControlType = acListBox Then
ctl.Selected(0) = True
3. Trigger this form to open as hidden just before the command for your display form. In my case I am displaying the results
on a tab control subform so my on click event looks like:
Hi, I am very new to Access. But I am learning pretty quickly!
I have created a number of tables and I am now moving onto forms, but struggling to find out how I can get data captured in
one form to populate into 2 tables(if that is even possible).
Table 1- Inventory
Ok, so I have a INVENTORY TABLE where all the stock items, amounts and calculations are done. In this table I have a field
called "Shrinkage" so putting in an amount in under "shrinkage" for a particular stock item would deduct it from the total
Table 2 - Stock Shrinkage
I created another table, STOCK SHRINKAGE TABLE with the following fields - ID; Date; ProductName; Quantity; Reason; User
So those are my 2 tables, I have created a SHRINKAGE FORM, using the same fields in the STOCK SHRINKAGE TABLE.
What I would like is when a user enters an amount under the "Quantity" field in the SHRINKAGE FORM, I want that exact amount
to be stored in the STOCK SHRINKAGE TABLE under "Quantity" and also in the INVENTORY TABLE under "Shrinkage" for that
particular stock item.
Sorry if my explanation is not clear, but would appreciate any help, cheers
I am fairly new with Access and was working on something need few questions answered. Appreciate all answers/feedback.
I have one form (Request Form) which has quite bit of information on it. So I decided that I would need the following
I think I have created the relationship fairly well. Most of them one-to-many . Suggestions/input is welcome on these as
I created the form but now I whenever person is making entry I want the information from one form to go into multiples
tables. What's the best to go about this?
I have tried couple different ways, Made a query from the tables and then using that as Record Source for the form as well.
It doesn't seem to work properly. Please help. Thanks