Looping through all 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 for your help

 

[BSman]

I'm not sure what you are trying to update. If updating the data on the main form should generate a change in every related record in the subform, then you should be able to do that with a query that updates the subform table records and then you just need to requery the subform object so the changed values are displayed (assuming they are displayed on the subform).

Could you provide a better idea of the data you are trying to update; i.e., what change on the main form would cause a change to the data on the subform/table and what the change would be in the subform/table?

 

[lexi0088]

BSman,

Thank you for replying. 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. If a query is the answer, could you please explain in detail on how to do this, as I would not know how to write that in.

 

[BSman]

Sounds like you would be better importing the details (subform data) into a "transfer" table first. After importing, run a simple update query to change every imported record as necessary. Then use an append query to append the data to your actual data table that is the data source for the subform and delete the records in the transfer table.

The transfer table let's you easily adjust any formatting or other problems with the imported data before actually adding it to your database.

Bob S.

 

[lexi0088]

Thank you VERY VERY VERY VERY much. That worked perfectly