Dao Reference a Field

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

Guest

I have a field with the same name in two tables. My routine in DAO errors when trying to update one of these fields. How do you reference a field that exists in >1 tables? I have both an Heirs and Estate table with a field called "Counter". Here is a snippet:

With rs
If rs.Updatable Then MsgBox "ok" 'I get the ok
.Edit
![Heirs].[Counter] = assigned_existing_estate_value '<-----ERRORS HERE*********
.Update
End With

rs.Close
 
what is rs - is it a recordset based on table heirs?
if so then I think it should be something like

with rs
if rs.updateable then msgbox "ok"
.edit
.fields("Counter") = some value
.update
end with

HTH

Perico said:
I have a field with the same name in two tables. My routine in DAO errors
Click to expand...
when trying to update one of these fields. How do you reference a field
that exists in >1 tables? I have both an Heirs and Estate table with a
field called "Counter". Here is a snippet:
With rs
If rs.Updatable Then MsgBox "ok" 'I get the ok
.Edit
![Heirs].[Counter] = assigned_existing_estate_value '<-----ERRORS HERE*********
.Update
End With

rs.Close
Click to expand...
 
It's a recordset based on a Querydef

Dim db As dao.Databas
Dim qry As dao.QueryDe
Dim rs As dao.Recordse
Set db = CurrentD
Set qry = db.QueryDefs!qryMai
Set rs = qry.OpenRecordset
 
It's based on a query that has both tables that have fields called "Counter", which is the basis for the problem.
 
I have a field with the same name in two tables. My routine in DAO errors when trying to update one of these fields. How do you reference a field that exists in >1 tables? I have both an Heirs and Estate table with a field called "Counter". Here is a snippet:

With rs
If rs.Updatable Then MsgBox "ok" 'I get the ok
.Edit
![Heirs].[Counter] = assigned_existing_estate_value '<-----ERRORS HERE*********
.Update
End With

rs.Close
Click to expand...

What's the error? Note that if [Counter] is an Autonumber type field
you CANNOT update it. If it's a foreign key to some other table it
should be a Long Integer rather than an autonumber.
 
Instead of using the ! (implicit use of the fields
property), use .Fields:

With rs
If rs.Updatable Then MsgBox "ok" 'I get the ok
.Edit
.Fields("Heirs.Counter") =
assigned_existing_estate_value
.Update
End With

rs.Close


Whenever there are two fields of the same name pulled into
1 query, Access assigns them the name Table.Field.


Chris
-----Original Message-----
I have a field with the same name in two tables. My
Click to expand...
routine in DAO errors when trying to update one of these
fields. How do you reference a field that exists in >1
tables? I have both an Heirs and Estate table with a
field called "Counter". Here is a snippet:
With rs
If rs.Updatable Then MsgBox "ok" 'I get the ok
.Edit
![Heirs].[Counter] =
Click to expand...
assigned_existing_estate_value '<-----ERRORS HERE*********
 
Back
Top