subform record loop in VBA

I know everybody here squinches at the mentioning of mailmerge so lets forget that

how do I extract values from a subform with the same master field value as the main form.

master and sub tables are linked with an idNumber, so there can by many records in the subForms table with the master tables idNumber

I just want to get all the values (rows) in one field in the subforms table that are displaying, for the master forms current record (current records idNumber)

Im thinking that I have to have some kind of if statment together with a SQL query.

Sorry for double posting but I think my previous post was explained a bit to complicated.

Sponsored Links:

Related Topics

Loop and update each record in datasheet
I have a bound subform in datasheet format on an unbound form. I need to loop through each record, perform a calculation, update 3 fields on the record, then loop and get the next one, continuing until all records have been updated. How do I do this?

2010 VBA "do while" code in a macro
In Access 2010, want to run perform a "do while" loop in a macro. I understand VBA is the way to do this. So, I think I want to run a VBA script in a macro.

This Macro runs against a Form that has a SubFrom (MainForm named "Expense ID" & SubForm, "Expense"). Macro is initiated by pressing a button.

I would like help with some VBA code showing how to perform the following:

1. Evaluate a field ("Activity") in the first record of Subform ("Expense") against condition "Is not Null".

2. If condition is true, set value of another field ("Post") in the same SubForm record to "1".

3. Move to next record and repeat steps 1 & 2 until all Subform records are evaluated.

4. Return to macro that initiated the VBA script to perform the next action in the macro.

record type for loop
I strongly feel that when looping through records in a record set I should be using a for loop. However I am unable to find a record type, that means I need to add in a superfluous extra line .

While a for loop would automatically increment itself. I'm just trying to use a for loop in a situation it was designed for but I seem do be stuck with a while loop.

For Each Loop help - for each Items in an Order
I want to click on a button that executes code that will loop through each of the items in an order. The order is split into the order header and the order details - the order details are shown within the order form in a subform.

The subform contains all the order items ordered. I want to be able run code from within the order form to carry out an action for allthe items in the subform.

I hoped that using the For Each Loop would be the way forward and that it would be quite straight forward. But I'm struggling to work out defining the Object and the Collection. I guess the Collection is the list of relevant order items and the Object is the specific order item - but I'm struggling to convert this to VBA.

Is the For Each Loop the most appropriate way to solve this?

Loop through subform records
I'm having a dumb moment, but can someone help me to remember how to use the docmd.gotorecord . next record on a subform. I can code it fine to go through the records on the main form but not the subform.

Creating Labels
I'll try and make this as concise as possible!

I have a record with a subform. Within the subform there are, say, 3 individual records. Each record has it's own text field and number required. I want to print labels to a sheet that loops through each record and creates the number of labels required with the text field data, so:

Record 1 DLawson 3
Record 2 MLawson 10
Record 3 CLawson 5

All on one sheet of Avery labels.

I know I need a loop with variables - I'm assuming on the OnPrint event of the label report, but this is totally stumping me and I'm now at screaming point. If anyone can point me in the right direction I would be most grateful.

There will be the eventual added complication of printing a batch of records off in one go, so an extra loop through, but one thing at a time.

VBA loop to check data
I have a field on a sub form that needs data to be in a specific format based on another fields information.
Example: Main Form(Field)1 needs SubForm(Field) data to be A#A#A#
Main Form(Field)2 needs SubForm(Field) data to be ######

Problem: I have the check the accuracy of the data part but what I am missing to how to check the multiple records that exist because the data is on a sub form which is a continuous form.

I thought I could just add a autonumber and create a loop using the first autonumber generated and the quantity of records.
---This would not work because the next autonumber in the table might be taken by another user for another record

Loop in access 2010 macro
I am trying to create a macro to loop through all the records in a form and replace a number field until it hits a record with a specific entry.
I am unable to find the command in the macro drop-down box. The command I am looking for is Do Until.
I would prefer to work through macros rather than in VBA as I don't know how to program using VBA

Loop Problem: looping all records
I have a form "frmTest" & subform "subfrmTestDetail". The subform "subfrmTestDetail" displays the recordset. I have a cmd button on "frmTest" with the following code.....
The idea is for the code to loop all records displayed in subform & update. It only updates the very first record & does not loop the entire recordset. Can anyone see where i've gone wrong & why it isn't looping all records?

Loop: Each subform in Form
All i need is the following pseudo code translated into actual working VBA code. For each value in a public array(1), I am attempting to loop through all fields(3) within all the subforms(2) on one particular form. If there is a match, the field is visible, if not, the field is hidden (I am using datasheet view)...

Record updates on a subform
I have a subform, lets say subform A, which displays the contents of a table in tabular form. I have another subform, lets say subform B,

One of the columns in subform A is computed with another column in subform A, and a column in subform B, and I'd like that column to be updated whenever the user changes the values pertinent to that calculation.

and the after_update events work. but the change doesn't appear on the subform.

I have to change the record twice each time, and then the data shown is always one change back. I've tried requery, refresh, repaint, etc on that form after the update is done, and I can'tseem to get it to display the updated value, despite the fact that I know the calculation occurred and saved the record in vba via .edit and .update.

Writing vba ouput into excel file
I am running a for loop 100 times. each loop giving an output. and I wish to print those outputs into an excel file. I tried using

1) Worksheet("sheet1").Range(A1:A101) = VBA.abs(value)
But the trouble is that it prints the same value a 100 times for every loop, with every loop output over writing the previous one's output. Meaning the output I can see is of the 100th loop printed in a 100 cells(A1:A101). Where as I want all the 100 (different) loop outputs to be printed.

Get Subform to point at first record
I have a form (frmQuestionDetail) with a subform (frmQandRsubform) that opens in Datasheet view. The subform is opening with the record indicator pointing to the New Record line. How can I get the pointer on the subform to show at the first record instead of new record

How do I delete a record on a subform?
I need to know how to use a For Each/Next loop to delete all records on a subform that have the same values in a field. The subform is a form based on a linking table with both fields combined as the primary key and the field that hold the criteria is the same as the primary key of the main form (I hope that makes sense).

I have tried making the field null using the Form!MainForm!SubForm.Form!Control.Value = null for both fields, but it just deletes the first record and then when I leave it, it says I can't leave the primary key field blank. So I thinkthat I have to delete the record.

The fields in the subform are KeyFobSN and BranchNumber. The subform is linked to the main form by the KeyFobSN. Each KeyFob will have many records as each has access to many branches.

Form text box loop
I have a form text box field which links to my query. So that when a user enters a value in the field of the textbox in the form and presses the Start button, the datab from the textbox is passed to the query and the query generates the desired output table.

I have another table that I want it to essentially "loop" through and take each record set value, one row by row and pass it to this text box field and run the query through sort of a vba loop.

My criteria field in the query is [Forms]![MainForm]![Textbox_Entry] My query name is Test_Query
The table that I want to loop through is called Loop_Table
My vba query is DoCmd.OpenQuery "Test_Query", acViewNormal, acEdit

How to get a new record the second time
I have a subform, with Allow Additions = Yes, that have three fields in datasheet view. To add a new record just click on the blank fields and type it in. There is * symbol at the far left and it changes to a pencil symbol after you start entering data in this record. The only way I can add another new record is to move to different form record or subform record. What my idea is after the third field is populated use an event, like After Update, to run vba code to somehow add a new blank record. Im unable to figure out what that should be

Requerying Parent form is causing endless loop
I have the following code from a subform in datasheet view which I want to requery the Parent form after a new record is created on the subform by making a selection in a combo box:

store loop results as increment variable?
I'm using a Do Until loop that is basically writing the body text of an email for me. It's working great, but I need to add one more thing.

It is storing the Body text that it is creating as a BodyText string within the loop. Works good. However, I really need the Body text variable to increment each time the loop is ran, or the loop becomes pointless.

User enters 5 form entries.
User hits 'Send Email'
VBA runs code to build email for each entry submitted
VBA sends the full body text of all 5 form entries to Lotus Notes ()

slightly modified for better viewing:


After this, The problem I'm having is that the BodyText variable is only remembering one loop. I need a way (array?) to name BodyText within in the loop so that each form entry text that is built has a different name and can be combined, I.e. Body1, Body2, Body3.

open a subform as a new record
I have a form with a subform. when opening the form it says that subform is not open so it cant open a new record.

is there anyway of getting round this. I tried creating the subform seperately and using macros to open in a new record. just a note, the record bar at the bottom of the subform allows me to select a new record but I cant find a way for it to happen automatically.

How to show new record at the top of a subform
I have a database in access 2007. The main form has a subform and I would like a blank new record as the first record of a subform. Is there a way to do that without making two separate subforms (one for a blank new record subform and the other for records that already exist