Loop through records in a subform

[lexi0088]

I have a field in a subform that updates every time you click in it. However, I would like every record in the subform to update when I perform an action in my main form. I have no experience with loops in Access so I am totally lost. Any help would be appreciated.

Main Form: [Chargeback]
Sub Form: [Chargeback Details]
Field that needs to update:[NDC]


Dim rs As ADODB.Recordset
Set rs = Me![Chargeback Details].Form
rs.MoveFirst
Do While Not rs.EOF
Forms![Chargeback]![Chargeback Details]![NDC].SetFocus
rs.MoveNext
Loop

when I try to run this code I get a type mismatch error

Thank you in advance for your help

 

[TheAceMan1]

How are ya lexi0088 . . . . .

You may not need to do any looping.

[blue]Depends on just what this action is your performing in the mainform?[/blue]

See Ya! . . . . . .

 

[lexi0088]

Thank you for trying to help me out yet again. I am so lost when it comes to writing in new code. Here is the situation:

1. With the click of a command button from my main form, I am importing an excel file into a table in my database. Then I am copying the relevant data columns from that table into my subform.

2. The main form saves the header information into tblchargeback and the imported subform saves the details into tblchargeback_details.

3. One of the columns in the subform (NDC) needs to be converted to a new value. For example, the import table has the ndc as 00525801990 which I need to see in my subform table as 8019-90off cb. And I also need a seperate column in the subform that looks up and inputs the NDC Name.
- the way that I am currently doing this is by having a got focus formula of:

Private Sub NDC_GotFocus()
Begins = Left(NDC, 5)
If Begins = "00525" Then
NDC = DLookup(("[NDC]"), "NDC", "[CBNDC]= Forms![Chargeback]![Chargeback Details]![NDC]")
End If
ProductName = DLookup(("[ProductName]"), "NDC", "[NDC]= Forms![Chargeback]![Chargeback Details]![NDC]")

The problem with this is that you have to go into each record on the subform and click on each NDC to populate these values. I would like to see every record being updated as I am importing the columns into my subform.

Please give me any suggestions possible.

 

[TheAceMan1]

lexi0088 . . . . .

Hard to find the head or tail of this, but it appears an [blue]Append[/blue] query that inserts the proper converted values is at the source of your needs.

In your DLookUp:

[blue]NDC = DLookup(("[NDC]"), "[purple][b]NDC[/b][/purple]", "[[purple][b]CBNDC[/b][/purple]]= Forms![Chargeback]![Chargeback Details]![NDC]")[/blue]

You indicate you have an [purple]NDC[/purple] lookup table where ping [purple]CBNDC[/purple] against the current NDC in the subform and retrieve this [blue] 8019-90off cb[/blue] value! . . . I have no idea what to tell you here (totally lost).

There also appears to be more under than hood than shows (what little there is). My current standing on this thread is that it would take too many questions to bring to the surface the necessary viable info.

I only wish I had a more informative response for ya.

[purple]Goodluck with this![/purple]

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

See Ya! . . . . . .