Check my code please

[George Too]

Would this code work? The part I'm not sure about is
"rst(Field.Name) = control.Value "

'*************************************************
Set rst = dbs.OpenRecordset("tblRepeats", dbOpenDynaset)
Criteria = "Project= '" & Me.txtProject & "'"
rst.FindFirst Criteria
'**** do if no Project was found
If rst.NoMatch Then
rst.AddNew
For Each control In Me.subfrmRepeats.controls
If Not IsNull(control.Value) Then
rst(Field.Name) = control.Value
End If
Next control
rst.Update
rst.Close
dbs.Close
End If
'************************************************

Thanks,
George

 

[WayneRyan]

George,

What is field.name and control.value?

Wayne

 

[George Too]

Those are taken from fields in a form. The reason I'm using the For..Next command is that I dont want to code 30 some fields that I have in my form. I figure this way I would have cleaner code.

Again, the values are taken from txtbox1, txtbox2,... in the form and I want those values in field1, field2,... in my table.

Can it be done?

Thanks,

George

 

[WayneRyan]

George,

If the table has fields like field1 ... field30
and
the form has controls have names like field1 ... field30
then you can programatically do something with it.

I don't do this but ...

For I = 1 to 30
strField = "Field" & str(i)
rst(strField) = Me.Controls(strField)
Next I

I'm just guessing here.

Wayne

 

[George Too]

You said "I don't do this but ... " So what would be your advise?
What I see from your last post is that I should ditch the "For Each control " part in favor of the "For I = 1 to 30 " What problems will I encouter later on? (if any).

Or should I go as originally planed, mainly, write a line for each field?

Thanks Wayne.

George

 

[WayneRyan]

George,

This is a broad topic. Generally, for bound forms, you can use
the table design (Required = Yes, Allow zero length = No).
Most times I will only have a few fields that I will check with
code for appropriate values.

With your 30 fields, I couldn't see making a general rule that
as long as they entered "something" that it was OK.

There are examples in this forum on short routines that will
loop through all of your controls, and if they are "acTextBox"
then you can do your message box.

Wayne

 

[George Too]

Point taken Wayne, quite frankly, I do a intensive search before posting just so that I don't waste people's time. In regard to my fields, I just needed to clean my code a bit. I don't want to have the form pass the values directly to the table because of duplicate records issues. So, by pressing a save button, the code checks for null values and takes care of all those fields that are not. That part I got working but is kind of long (it's 30 fields).

Again, thanks for your time. I really appreciate it.

Regards,
George