Opening ADO recordset problems

[Gibson]

I am using the following code to attenot ti open a recordset and add a new
record to it. I'm using Access2003 and the databases are split ( backend
with data is in different directory front end with all forms etc) Is this a
proper way to open a recordset with a split database? I,m new to ADO. Every
time I run this code (it's behind Onclick event of command button) I receive
an error about Function is not available in expressions in table-level
validation expression. There are no validations rules or text in the fields
of this table. Any thoughts or maybe a place to look for answers. I'm at
my wits end.

Thanks

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
End if

 

[JaRa]

You are missing the rs.update method.

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
rs2.Update
End if
rs.close
set rs = nothing

- RAoul

 

[Gibson]

Thanks for the response. I neglected to put the rs.Update in the code I
posted. It is there though. I left it out because within the code I Dimmed
a variable called "Line" and updated its value by one for every line of
code. In the error section of the Sub I display a message box with the value
of the line and the error message. This way I can find the find the last
value of the line and know the code failed on the next line. In my case it
is failing on the "If Not rs2.EOF" and "rs2.AddNew" lines. This is why I
thought the recordset was not set correctly but I am at a loss.

Thanks

You are missing the rs.update method.

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
rs2.Update
End if
rs.close
set rs = nothing

- RAoul

I am using the following code to attenot ti open a recordset and add a
new
record to it. I'm using Access2003 and the databases are split ( backend
with data is in different directory front end with all forms etc) Is this
a
proper way to open a recordset with a split database? I,m new to ADO.
Every
time I run this code (it's behind Onclick event of command button) I
receive
an error about Function is not available in expressions in table-level
validation expression. There are no validations rules or text in the
fields
of this table. Any thoughts or maybe a place to look for answers. I'm
at
my wits end.

Thanks

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
End if

 

[Chaim]

Gibson,

Is this your code as it is in the module? Among other things, you close 'rs'
rather than 'rs2'. Also, this will run through the statements between 'If'
.... 'End If' only if the table is empty. You should also be able to do the
following, since you are only adding one new record:

rs2.AddNew "Field1", Forms!frm1!Field1

Better might be: rs2.AddNew "Field1", Me!Field1.value

The 'Me' is an alias for the current form. I prefer not to depend on default
properties which is why I added the '.value' but that may be a matter of
taste.

Good Luck!

Thanks for the response. I neglected to put the rs.Update in the code I
posted. It is there though. I left it out because within the code I Dimmed
a variable called "Line" and updated its value by one for every line of
code. In the error section of the Sub I display a message box with the value
of the line and the error message. This way I can find the find the last
value of the line and know the code failed on the next line. In my case it
is failing on the "If Not rs2.EOF" and "rs2.AddNew" lines. This is why I
thought the recordset was not set correctly but I am at a loss.

Thanks

You are missing the rs.update method.

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
rs2.Update
End if
rs.close
set rs = nothing

- RAoul

I am using the following code to attenot ti open a recordset and add a
new
record to it. I'm using Access2003 and the databases are split ( backend
with data is in different directory front end with all forms etc) Is this
a
proper way to open a recordset with a split database? I,m new to ADO.
Every
time I run this code (it's behind Onclick event of command button) I
receive
an error about Function is not available in expressions in table-level
validation expression. There are no validations rules or text in the
fields
of this table. Any thoughts or maybe a place to look for answers. I'm
at
my wits end.

Thanks

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
End if

 

[Gibson]

Once again, I apologize for the sloppy posting. I think I've been up to long
working on this.
Following is the actual code I am attempting to use. I try to open the
recordset, move to the first record and then find a
record where Field1 matches the variable strRef. If I get to the end of the
recordset and don't find it then I add a new record, if
I find it then I edit the record.

Thanks so much for your input.

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
rs2.MoveFirst
rs2.Find "[Fileld1]= '" & strRef & "'"
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = strRef
rs2.Update
Else
rs2.Edit
'I edit the record'
rs2.Update
End if
rs2.close
set rs2 = nothing

Gibson,

Is this your code as it is in the module? Among other things, you close
'rs'
rather than 'rs2'. Also, this will run through the statements between 'If'
... 'End If' only if the table is empty. You should also be able to do the
following, since you are only adding one new record:

rs2.AddNew "Field1", Forms!frm1!Field1

Better might be: rs2.AddNew "Field1", Me!Field1.value

The 'Me' is an alias for the current form. I prefer not to depend on
default
properties which is why I added the '.value' but that may be a matter of
taste.

Good Luck!

Thanks for the response. I neglected to put the rs.Update in the code I
posted. It is there though. I left it out because within the code I
Dimmed
a variable called "Line" and updated its value by one for every line of
code. In the error section of the Sub I display a message box with the
value
of the line and the error message. This way I can find the find the last
value of the line and know the code failed on the next line. In my case
it
is failing on the "If Not rs2.EOF" and "rs2.AddNew" lines. This is why I
thought the recordset was not set correctly but I am at a loss.

Thanks

You are missing the rs.update method.

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
rs2.Update
End if
rs.close
set rs = nothing

- RAoul


:

I am using the following code to attenot ti open a recordset and add a
new
record to it. I'm using Access2003 and the databases are split (
backend
with data is in different directory front end with all forms etc) Is
this
a
proper way to open a recordset with a split database? I,m new to ADO.
Every
time I run this code (it's behind Onclick event of command button) I
receive
an error about Function is not available in expressions in table-level
validation expression. There are no validations rules or text in the
fields
of this table. Any thoughts or maybe a place to look for answers.
I'm
at
my wits end.

Thanks

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
End if

 

[JaRa]

the error is not caused by the add but at the edit ado recordsets don't
support the edit method edit is automated by changing a value for the current
record.

I also clean the code a bit to make it a bit more performant.

- Raoul

your code should become:
Dim rs2 As New ADODB.Recordset
rs2.Open "SELECT Field1 FROM tbl1 WHERE Field1='" & strRef & "'",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = strRef
Else
' rs2.Edit is not used with ADO
'I edit the record'
rs2![Field1] = strRef
End if
rs2.update
rs2.close
set rs2 = nothing

Once again, I apologize for the sloppy posting. I think I've been up to long
working on this.
Following is the actual code I am attempting to use. I try to open the
recordset, move to the first record and then find a
record where Field1 matches the variable strRef. If I get to the end of the
recordset and don't find it then I add a new record, if
I find it then I edit the record.

Thanks so much for your input.

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
rs2.MoveFirst
rs2.Find "[Fileld1]= '" & strRef & "'"
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = strRef
rs2.Update
Else
rs2.Edit
'I edit the record'
rs2.Update
End if
rs2.close
set rs2 = nothing

Gibson,

Is this your code as it is in the module? Among other things, you close
'rs'
rather than 'rs2'. Also, this will run through the statements between 'If'
... 'End If' only if the table is empty. You should also be able to do the
following, since you are only adding one new record:

rs2.AddNew "Field1", Forms!frm1!Field1

Better might be: rs2.AddNew "Field1", Me!Field1.value

The 'Me' is an alias for the current form. I prefer not to depend on
default
properties which is why I added the '.value' but that may be a matter of
taste.

Good Luck!

Thanks for the response. I neglected to put the rs.Update in the code I
posted. It is there though. I left it out because within the code I
Dimmed
a variable called "Line" and updated its value by one for every line of
code. In the error section of the Sub I display a message box with the
value
of the line and the error message. This way I can find the find the last
value of the line and know the code failed on the next line. In my case
it
is failing on the "If Not rs2.EOF" and "rs2.AddNew" lines. This is why I
thought the recordset was not set correctly but I am at a loss.

Thanks
You are missing the rs.update method.

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
rs2.Update
End if
rs.close
set rs = nothing

- RAoul


:

I am using the following code to attenot ti open a recordset and add a
new
record to it. I'm using Access2003 and the databases are split (
backend
with data is in different directory front end with all forms etc) Is
this
a
proper way to open a recordset with a split database? I,m new to ADO.
Every
time I run this code (it's behind Onclick event of command button) I
receive
an error about Function is not available in expressions in table-level
validation expression. There are no validations rules or text in the
fields
of this table. Any thoughts or maybe a place to look for answers.
I'm
at
my wits end.

Thanks

Dim rs2 As New ADODB.Recordset
rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If rs2.EOF Then
rs2.AddNew
rs2![Field1] = Forms!frm1!Field1
End if