populate multiple rows using one form (looping?)

Hi,
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.

Example:

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.
Peter


Post your answer or comment

comments powered by Disqus
Does anyone know how to populate fields in multiple tables from data entered on one form??

Thanks
Mark

Hi Folks,
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.

Hi everyone,

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.

Thanks

Hello newbie here!

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. Attached Thumbnails   Reply With Quote 09-09-2011, 02:07 PM #2 pbaldy Who is John Galt? Windows XP Access 2007 Join Date Feb 2010 Location Nevada, USA Posts 9,228 Is this what you're after?

BaldyWeb - Autofill

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.

thanks,

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!

Hello -

I am new to forums and not sure how to start this thread - so please be patient. I have however, up to this point, thought myself to be fairly proficient with Access, that is until this issue.

I found a thread (Add Multiple Records on One Form 8/13/10) from William McKinely that is similar to my problem. He was able to get an answer from PBaldy - but it was a little short on details.

I am looking for the details of PBaldy's solution to create / run an append query to pre-populate a table in order to create / show a form for data entry with multiple new records for easy update.

Since I did not know if was appropriate to contact them directly, I thought I would post my request for more information in a new thread.

thanks

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

Code: 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 Else DoCmd.OpenForm, strForm, acNormal, , strWhere Me.Visible = False End If 2. frmLessonCard: Mainform receiving the filter criteria from frmDivSelect; code that is behind the listbox.

Code: Private Sub lblLessonList_AfterUpdate() DoCmd.SearchForRecord , "", acFirst, "[LNumber] = " & "'" & Screen.ActiveControl & "'" 3. qryLessonCardDataFilter: The underlying record source for frmLessonCard.

Code: Field: Inactive Table: tblLCData Criteria: 0 or: 0 Field: LDivision Table: tblLCData Criteria: [Forms]![frmDivSelect]![cboDivSelect] Field: [Forms]![frmDivSelect]![cboDivSelect] Show: unchecked 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.

Thanks, Sean

"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 functions)

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

Hello All,

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:

Product........................................... .......Footnote
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:

Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1

Any help would be greatly appreciated.

I have a table that has multiple rows for one employee.
I would like to combine them into one row.

For example

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.
For example:
EmployeeID, Firstname, Lastname, plan description1, plan Category1,plan description2, plan Category2, plan description3, plan Category3


ID1, John, Smith, Delta dental, Single, lifeinsurance, Basic, longterm disablity, 2/3 salary.

How can I do it?

Thanks a lot

Hi all,

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 button A.

Any suggestions welcome :-)

Many Thanks!

I'm trying to connect multiple queries into one form. Structure of the queries is as follows

ID cat Result 1 1 5 2 1 6 3 etc. etc.


ID cat Result 1 2 8 2 2 10 3 etc. etc.
The form should then be capable of filling in the results for each ID and for each category. Something like this:
ID Query1 Result Query2
Result Queryn
Result 1 5 8 2 6 10 3


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 suggestions?

Menno

ps Not sure wheather this is a reasonable question, please don't hesitate to tell me if it isn't

Hello there.

What I'm trying to do is use a Form to populate the 'Field" row in a query - I'm not trying to add criteria in the criteria section.

e.g

I've got a table of targets, split Mth 1, Mth2, Mth3 etc...

I want to run a query which will pick up the YTD target so for Month 3, the "Field" row would have to read [Mth1] + [Mth2] + [Mth3].

I hoped to be able to do this using a form and a small bit of code and an 'after update' event procedure, but I can't get it to work.

Can anyone help please. I am not too hot with coding, so as simple as possible would be perfect.

Thanks.

Hi there, this is a popular example that I'm using (located here):
http://www.techonthenet.com/access/forms/search2.php

What I would like to do though is:
1) Simply have the User type text into "one text field" and "Search" through "multiple" fields on the form.

Currently, this code allows you to select one form field (cboSearchField.Value) and search that field (so I have to do one at a time). Thanks!

Here's the code I'm currently using to filter/search through one field at a time:

Private Sub cmdSearch_Click()
Dim GCriteria As String

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter frmCustomers based on search criteria
Form_proj_gpo.RecordSource = "select * from Praject_Input_Qry_Invoice where " & GCriteria
Form_proj_gpo.Caption = "Praject_Input_Qry_Invoice (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If

End Sub

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 window.

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
End If
Next ctl

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:

DoCmd.OpenForm "frmListBoxes", acNormal, "", "", , acHidden
Forms!frmMAIN.Sub.SourceObject = "frmLBsub"

*I also have a close command set on timer 50 for the listbox form since I dont need it once the values are loaded to my display form.

4. Now you can reference the data in those fields respectively such as:

Me.MyNumber = Forms!frmListBoxes.ListBox1.Column(0)
Me.SecondSource = Forms!frmListBoxes.AnotherBox.Column(9)



Like I said it's quick and dirty but works great for my application, was fast to put together, and is very flexible for me to expand in the future with little to no additional code.

The more experienced guru's here can tell me if I'm on the crazy train or not

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 stock amount.

Table 2 - Stock Shrinkage

I created another table, STOCK SHRINKAGE TABLE with the following fields - ID; Date; ProductName; Quantity; Reason; User ID.

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

Gareth

Hello,
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 tables:
tblRequests
tblCustomer
tblSalesperson
tblSupervisor

I think I have created the relationship fairly well. Most of them one-to-many . Suggestions/input is welcome on these as well.
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

Hi All,

I am trying to create a form for my supervisors to update their daily employee production hours. The supervisors have requested that all of their employees show up on one form, allowing them to simply tab down to the next person to add the employees daily hours. I've tried a few things but I am coming up short. Attached is the form design for my closest attempt. I added a bunch of different text boxes linked to the table that I want to populate with each 'User ID' field having a default value of each employee. The form looks the way I want it to when opened, however when I try to edit the first record it switches all of the fields to that particular employee. I guess it thinks all the text boxes are one record? I attached a few examples of how I need it to look and what happens when I try to add a record.

Any ideas? Attached Thumbnails     Reply With Quote 08-13-2010, 03:45 PM #2 ajetrumpet Banned Windows Vista Access 2007 Join Date Mar 2010 Location N/A Posts 2,698 the continuous form is perfect for this type of thing. try using it

I´m trying to improve my appl. but I´m stuck trying to figure out how to append multiple rows on a table using the Append query.

My problem comes since I have 4 tables with the following fields:
1) Orders
OrderID (number)
CustomerID (number)
Date (date)
etc...
2) OrderDetails
OrderDetailID (number)
OrderID (number)
ProductID (number)
QuantitySold
UnitPrice
etc...
3) TmpOrders
OrderTmpID (Autonumber)
CustomerID (number)
Date
etc...
4) TmpOrderDetails
OrderDetailTmpID (Autonumber)
OrderID (number)
ProductID
QuantitySold
etc...

The problem comes since the Tmp tables are used just to record temporarly the information before the transaction is completed.
Whe the salesman at the desk finish the sale, a command button is presseed and an append query runs to transfer the data from Tmp tables to the Definitive tables. Another query (Delete) is excuuted inmediately after the append qry. and it deletes the information recorded on Tmp tables.

I need to reset the autonumber or create a field that records the line number so I can add up to the Maximum OrderDetailID found on the definitive table, but the problem is how to create the "controlled" autonumber.

Please help.

Hi,

I am trying to populate Query Criteria using a form, but I am looking for a way to just link the text box from the form to the actual criteria box in the query.

I am aware of the [forms]![form name].[text box name] method, but this seems to only set the criteria to "= whatever is in the text box".

If in the text box, I have something like " 55 And 58 And 31", this would work in the criteria box, but it wouldn't work if I put it in the text box.

Is there any way for me to just directly link the text box and criteria box? I want what appears in the user input text box to be exactly what appears in the criteria box in the query.

Thanks very much,

Daniel

I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.

Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.

I am currently using Access 2010.

Any help please?

Thanks

Kevin

Good evening:
I have an Access query that lists customer subscriptions to our products for
the current month and the 7 preceeding months (One column for each month). A customer can subscribe to more than one product,
therefore the subscriptions are listed multiple times each month. (one record for each
subscription).
I need to combine the records for each customer, for their current
product(s).

Example:

ABC COMPANY__________SUBSCRIPTION 1
ABC COMPANY__________SUBSCRIPTION 2
ABC COMPANY__________SUBSCRIPTION 3

How do I combine the subscriptions into one column instead or multiple rows?
(see below) Possibly, but not necessarily, comma delimited.

Like this...
ABC COMPANY_________SUBSCRIPTION 1, SUBSCRIPTION 2, SUBSCRIPTION 3


Not finding an answer? Try a Google search.