Looping gets Runtime Error 3251 (1 Viewer)

[krissy21]

Hello all,

I'm fairly new to programming vba in Access so please forgive my simple tries and help me understand what I'm doing wrong.

What I want to have happen when I click the "button", is for it to loop through each 'Code' in the 'Type of Training' table and enter the specified information from the current form into the 'tblTrainingDates' table.

The vba code inside my loop works because I've switched out 'fld' for the actual "Training Code" and it works fine. However, when I run it through the FOR EACH loop, I get the Runtime Error 3251: Operation is not supported for this type of object. The error falls on line: 'For Each fld In tdf'

I've also tried a DO WHILE loop and that doesn't work either.

What am I doing wrong? Thanks for all your help!


Sub cmdAddNewEmployeeTraining_Click()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs("Type of Training")
Set fld = tdf.Fields("Code")

For Each fld In tdf
'Do While fld.Value <> ""

CurrentDb.Execute "INSERT INTO tblTrainingDates ([Employee ID],

,[Date Effective]) VALUES (" & Me.txtEmployeeID & ",'fld','""')"
     
Next
'Loop Until IsEmpty(fld)

End Sub

 

[CJ_London]

not quite sure what you are trying to do, but you have your objects mixed up

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field


Set db = CurrentDb()
Set rst = db.OpenRecordset("Type of Training")

Not clear to me about your loop, perhaps you can explain in simple words what you are trying to do

 

[pbaldy]

The fact that you want to loop fields would imply that the codes are in fields rather than records. If so, that would be a design mistake:

http://www.r937.com/Relational.html

 

[krissy21]

Sorry, CJ. You are right. That was in a mix of trying different ways and I forgot to reverse my changes before I copied and pasted here. See the original post edited.

The form is meant to enter in new Employee information and the "Button", to enter all the "Training Codes" listed in the table 'Type of Training' so that each new Employee has every 'Code'(Field in that column of the table) with an empty "Training Date Effective" field.

The error with my current loop falls in the line:
For Each fld In tdf

Do you know what my mistake might be?

 

[Galaxiom]

I agree with Paul's assessment of a design error.

Anyway none of this is necessary. The form's RecordSource query can be constructed to make all of the records in the Codes table appear on the form without actually creating a record until a value is entered in the Effective Date field.

Aside from not storing what are effectively empty records, there are other important advantages with this technique. Any records added to the Codes table will automatically appear for every Employee, existing or new, any time the form is opened. Moreover it supports the validation of record completeness and uniqueness at table level which is not possible when done with the partial fill query.

See the example database in post 3 of this thread. It is not exactly the same situation as your requirement but the same principle would work.

 

[Galaxiom]

The error with my current loop falls in the line:
For Each fld In tdf

Do you know what my mistake might be?

You have already defined the fld object variable.

Set fld = tdf.Fields("Code")

It cannot be also used as the Item variable in the loop through the Collection.

 

[krissy21]

Thank you pbaldy and Galaxiom! I believe I tried it that way originally but it failed so my next objective was to try the loop. I will go back through and figure out where exactly I went wrong with my form/queries.

Thanks a bunch!!

 

[pbaldy]

Happy to help and welcome to the site by the way!

 

[CJ_London]

looks like you could do this with one sql query rather than a vba loop

CurrentDb.Execute "INSERT INTO tblTrainingDates ([Employee ID],

) SELECT " & me.EmployeeID & ", " [Code] FROM [Type of Training]"
  
 no need to put anything in the date field - as a new record it would be blank