Unbound Form vs rst

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used this great, simple code, but I have an issue that I cannto resolve.

I am looping throught the recordset, and for each field in it, I pass it the value of the field on the form withthe same name. However, not every field in the underlying table is included on the form,a nd it creates an error when it runs. I can trap it, but it errors out if it passes a second time. There should be a simple way to do this.

dim fld as Field
for each fld in rst.Fields
me(fld.name) = rst(fld.name)
next fld

I trap error 2465, and make it go back through, but it doesnt help. Setting the rst to just the fileds I use seem ssomewhat silly, but if I have to, it is going to be a major pain!

Thanks in advance

Sean Henry
 
Sean Henry said:
I have used this great, simple code, but I have an issue that I
cannto resolve.

I am looping throught the recordset, and for each field in it, I pass
it the value of the field on the form withthe same name. However, not
every field in the underlying table is included on the form,a nd it
creates an error when it runs. I can trap it, but it errors out if it
passes a second time. There should be a simple way to do this.

dim fld as Field
for each fld in rst.Fields
me(fld.name) = rst(fld.name)
next fld

I trap error 2465, and make it go back through, but it doesnt help.
Setting the rst to just the fileds I use seem ssomewhat silly, but if
I have to, it is going to be a major pain!

Thanks in advance

Sean Henry
Click to expand...

I'm not sure what exactly you're doing such that it "errors out if it
passes a second time," but you could try something like this:

Dim fld As Field

On Error Resume Next
For Each fld In rst.Fields
Err.Clear
With fld
Me(.Name) = .Value
Select Case Err.Number
Case 0, 2465
' Ignore - either successful, or no such control
Case Else
MsgBox Err.Description, vbExclamation, "Error " &
Err.Number
Exit For
End Select
End With
Next fld

On Error GoTo 0 ' or your error-handler
 
Sean,

Why are you set on using an unbound form? One of Access'
greatest attributes is the ease with which it handles data
in bound forms. There are, of course, valid reasons to
use unbound forms, but if you are trying to fill a form
with data, bound forms is best way to go.

You also indicate that "Setting the rst to just the fileds
I use seems somewhat silly, but if I have to, it is going
to be a major pain!". You need to get in the habit of
only selecting the columns you need. Use of the * in
SELECT statements, when not all the fields are needed, can
significantly slow down your application.

HTH
Dale
-----Original Message-----
I have used this great, simple code, but I have an issue that I cannto resolve.

I am looping throught the recordset, and for each field
Click to expand...
in it, I pass it the value of the field on the form
withthe same name. However, not every field in the
underlying table is included on the form,a nd it creates
an error when it runs. I can trap it, but it errors out if
it passes a second time. There should be a simple way to
do this.
dim fld as Field
for each fld in rst.Fields
me(fld.name) = rst(fld.name)
next fld

I trap error 2465, and make it go back through, but it
Click to expand...
doesnt help. Setting the rst to just the fileds I use seem
ssomewhat silly, but if I have to, it is going to be a
major pain!
 
I am beginning theprocess of moving my app from Access back end to SQL, and then will be moving the front end to an ADP. Eventually I will be redeveloping the application with VB.net, ASP.net and some other pieces, and I might as well begin to test unbound forms now. I have a production db that we sell and market.

Creating a SQL string for the 70-80 fields I need from this table, and excluding just the 4-5 I dont use inthis table just seemed crazy to have to do, but if that is the way I have to then thats the way it goes.

I am going to try the code in the reply. . Thanks
 
Thanks for the help. I wanted to postthe code here (there is a correction to it). Thanks much.

If rst.EOF And rst.BOF Then
rst.AddNew
Else
rst.Edit
End If

On Error Resume Next
For Each fld In rst.Fields
Err.Clear
With fld
.Value = Me(.Name)' I had to swap positions as it made the form fields null instead of writing to the Table.
Select Case Err.Number
Case 0, 2465
' Ignore - either successful, or no such control
Case Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Exit For
End Select
End With
Next fld

On Error GoTo 0 ' or your error-handler
 
Sean Henry said:
Thanks for the help. I wanted to postthe code here (there is a
correction to it). Thanks much. [...]
With fld
.Value = Me(.Name)' I had to swap positions as it made
the form fields null instead of writing to the Table.
Click to expand...
[...]

That's the opposite of what the code snippet you posted was doing, on
which I based my suggested code.
 
Sean,

I am also working on a project with Access as the front end and SQL Server
as the back end. What I did was use the Tag property to hold the name of
the table field name. It seems to offer more flexibility.

Mike
Sean Henry said:
I have used this great, simple code, but I have an issue that I cannto resolve.

I am looping throught the recordset, and for each field in it, I pass it
Click to expand...
the value of the field on the form withthe same name. However, not every
field in the underlying table is included on the form,a nd it creates an
error when it runs. I can trap it, but it errors out if it passes a second
time. There should be a simple way to do this.
dim fld as Field
for each fld in rst.Fields
me(fld.name) = rst(fld.name)
next fld

I trap error 2465, and make it go back through, but it doesnt help.
Click to expand...
Setting the rst to just the fileds I use seem ssomewhat silly, but if I have
to, it is going to be a major pain!
 
Back
Top