Topics Search

subform record loop in VBA

subform record loop in VBA
Views: 130
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:

Similar posts...


2010 VBA "do while" code in a macro

2010 VBA "do while" code in a macro Icon
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.

Loop and update each record in datasheet

Loop and update each record in datasheet Icon
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?

record type for loop

record type for loop Icon
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

For Each Loop help - for each Items in an Order Icon
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

Loop through subform records Icon
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

Creating Labels Icon
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

VBA loop to check data Icon
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

Record updates on a subform

Record updates on a subform Icon
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.

Loop in access 2010 macro

Loop in access 2010 macro Icon
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

Get Subform to point at first record

Get Subform to point at first record Icon
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