Loop Field Names - Update per Combo Box 1

[air1access]

Ok - I have a form, with a combo box.
The combo box contains "Jan-2015", Feb-2015", etc.

I have a table with field names "Agent_SAN", "Jan-2015", Feb-2015", etc.
I want the user to be able to select a month-year from the combo box, then update that matching column/field in the table...
The table has a ID field - Agent_SAN to link too - so I need to loop the records, find a match on Agent_SAN, and update the field that was selected from the combo box...
Sure hope this makes sense..!!

Below is what I'm working with...
I have been able to get to loop through the field names, and find the match per the combo box but I am lost as to how to get the selected field updated...
Any suggestions/ideas would be super appreciated..!!!

-----------------------------------------------------------------

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rstBase As DAO.Recordset
Dim rstVarying As DAO.Recordset

Set db = CurrentDb
Set tdf = db.TableDefs("Monthly_GT3_MainCounts")
Set rstBase = db.OpenRecordset("Monthly_GT3_MainCounts", dbOpenDynaset)
Set rstVarying = db.OpenRecordset("Agent_PreviousMonth_Counts", dbOpenDynaset)

For Each fld In tdf.Fields
If fld.Name = Me!cboMonthDate Then

rstVarying.MoveFirst
Do Until rstVarying.EOF
rstBase.FindFirst "[Agent_SAN] = " & "'" & rstVarying!Agent_SAN & "'"
If Not rstBase.NoMatch Then
rstBase.Edit
fld.Name = Nz(rstVarying!PreviousMonth_Count, 0)
rstBase.Update
End If
rstVarying.MoveNext
Loop

End If
Next fld

 

[Andrzejek]

If your data looks like this:

[pre]
Agent_SAN Jan-2015 Feb-2015 Mar-2015
123 ValueA ValX
234 ValXYZ [blue]ValU[/blue]
345 ValP
456 ValG
[/pre]
And you know you have chosen Agent 234 for Feb-2015, you may just do:
[tt]
Update TableName
Set Feb-2015 = SomeNewValue
Where Agent_SAN = 234[/tt]

BTW – what a terrible data/table structure :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

Please use TGML to make your posts more readable.

I have to agree with Andy regading the table structure and the possible use of SQL rather than recordsets. However if you are stuck with both the tables and the recordsets, you might try:

 Dim db As DAO.Database
 Dim tdf As DAO.TableDef
 Dim fld As DAO.Field
 Dim rstBase As DAO.Recordset
 Dim rstVarying As DAO.Recordset

 Set db = CurrentDb
 Set tdf = db.TableDefs("Monthly_GT3_MainCounts")
 Set rstBase = db.OpenRecordset("Monthly_GT3_MainCounts", dbOpenDynaset)
 Set rstVarying = db.OpenRecordset("Agent_PreviousMonth_Counts", dbOpenDynaset)

 For Each fld In tdf.Fields
  If fld.Name = Me!cboMonthDate Then
 
   rstVarying.MoveFirst
   Do Until rstVarying.EOF
    rstBase.FindFirst "[Agent_SAN] = " & "'" & rstVarying!Agent_SAN & "'"
    If Not rstBase.NoMatch Then
     rstBase.Edit
      rstBase(fld.Name) = Nz(rstVarying!PreviousMonth_Count, 0)
     rstBase.Update
    End If
    rstVarying.MoveNext
   Loop
  End If
 Next fld

Duane
Hook'D on Access
MS Access MVP

 

[air1access]

Thank you dhookum...

I have the TGML flagged checked...
I don't know how to use it. Where can I learn...?

 

[dhookom]

Using TGML is about the same and as easy as formatting text in any word processor. Just select the content to format and click one of the little icons above the input box. Use the Code tag for any VBA or SQL statements.

Duane
Hook'D on Access
MS Access MVP