Referencing a field name common to two or more tables...

[patjones]

Hi:

I have two tables in my Access database, tblBase and tblRIP. Both of these tables have field names in common. The fields contain different data, but simply have the same names. For example, both tables have a field called fldAsstCoordinator. It's just that the numbers under this field name in tblBase are different from the numbers under the same field name in tblRIP...

My question is this. When referencing these tables in Visual Basic, how do I differentiate between the two? Right now, when I put in

.txtBaseRate = rst![tblBase].[fldLfgd] 

where txtBaseRate is just a textbox on my main form, and rst is the recordset based on a SQL query containing all the tables in my database, I get an error "3265 - Item not found in this collection".

I'm explicitly telling VB which table to pull the information from (i.e. tblBase and not tblRIP) - so why is it giving me an error?

Thanks in advance for any help that you can offer...

Pat

[Jim Doherty]

Hi:

I have two tables in my Access database, tblBase and tblRIP. Both of these tables have field names in common. The fields contain different data, but simply have the same names. For example, both tables have a field called fldAsstCoordinator. It's just that the numbers under this field name in tblBase are different from the numbers under the same field name in tblRIP...

My question is this. When referencing these tables in Visual Basic, how do I differentiate between the two? Right now, when I put in

.txtBaseRate = rst![tblBase].[fldLfgd] 

where txtBaseRate is just a textbox on my main form, and rst is the recordset based on a SQL query containing all the tables in my database, I get an error "3265 - Item not found in this collection".

I'm explicitly telling VB which table to pull the information from (i.e. tblBase and not tblRIP) - so why is it giving me an error?

Thanks in advance for any help that you can offer...

Pat

if you are pulling data into rst from one table at a time then to assign the value to your textbox from your rst using rst!fldLfgd is sufficient because there exists no conflict of name within the rst defined by the SQL.

However if you are JOINING tables together in SQL where a conflict of name would arise in the defined columns in SQL then you need to alias the instances of conflicting named columns ie:

 
SELECT tblMyTable1.myid, tblMyTable1.fldLfgd, tblMyTable1.myfield3, tblMyTable1.myfield4, tblMyTable2.fldLfgd AS MyfldLfGdAlias, tblMyTable1.myfield6, tblMyTable1.myfield7, tblMyTable1.myfield8 
FROM tblMyTable1 
INNER JOIN tblMyTable2 ON tblMyTable.myid=tblMyTable2.myid
 
 

You would then refer to the sql defined ALIAS provided to your rst in your textbox assignment like this

txtBaseRate = rst!MyfldLfDgAlias

The naming of your alias for the column can obviously be whatever you like, it need not be as I have given here necessarily

Regards

Jim :)

[patjones]

if you are pulling data into rst from one table at a time then to assign the value to your textbox from your rst using rst!fldLfgd is sufficient because there exists no conflict of name within the rst defined by the SQL.

However if you are JOINING tables together in SQL where a conflict of name would arise in the defined columns in SQL then you need to alias the instances of conflicting named columns ie:

 
SELECT tblMyTable1.myid, tblMyTable1.fldLfgd, tblMyTable1.myfield3, tblMyTable1.myfield4, tblMyTable2.fldLfgd AS MyfldLfGdAlias, tblMyTable1.myfield6, tblMyTable1.myfield7, tblMyTable1.myfield8 
FROM tblMyTable1 
INNER JOIN tblMyTable2 ON tblMyTable.myid=tblMyTable2.myid
 
 

You would then refer to the sql defined ALIAS provided to your rst in your textbox assignment like this

txtBaseRate = rst!MyfldLfDgAlias

The naming of your alias for the column can obviously be whatever you like, it need not be as I have given here necessarily

Regards

Jim :)

Thank you...I'll give this a try today and report back on how it went!

[patjones]

Works perfectly! Thanks so much...

Pat

[Jim Doherty]

Works perfectly! Thanks so much...

Pat

You're welcome and thanks for reporting back

Jim :)

[NeoPa]

It's good policy to alias confusing fields in SQL, but in this case it gets around the fundamental problem rather than answering it (not a criticism - I'm just interested in the answer to the original question Jim).
Could you try running the following code against your recordset and see what it returns for the names of the two fields in question. The names should be obvious once they come out and will indicate the general principle for future reference.

Public Sub TestItOut
  Dim varThis As Variant
  Dim rst As DAO.Recordset
 
  '... Whatever you do to open your recordset
  For Each varThis In rst.Controls
    Debug.Print varThis.Name
  Next varThis
End Sub

[patjones]

It's good policy to alias confusing fields in SQL, but in this case it gets around the fundamental problem rather than answering it (not a criticism - I'm just interested in the answer to the original question Jim).
Could you try running the following code against your recordset and see what it returns for the names of the two fields in question. The names should be obvious once they come out and will indicate the general principle for future reference.

Public Sub TestItOut
Dim varThis As Variant
Dim rst As DAO.Recordset
 
'... Whatever you do to open your recordset
For Each varThis In rst.Controls
Debug.Print varThis.Name
Next varThis
End Sub

Hi NeoPa:

I put your code into my subroutine as follows:

 Sub DisplayEmployeeInfo(rstEmployee As DAO.Recordset) 
 
On Error GoTo Err_DisplayEmployeeInfo
 
    Dim varThis As Variant
 
    With Me
 
        .txtNameLast = rstEmployee!fldNameLast
        .txtNameFirst = rstEmployee!fldNameFirst
        .txtERN = rstEmployee!fldERN
        .txtLocationPrevious = rstEmployee!fldLocationPrevious
        .txtTitlePrevious = rstEmployee!fldTitlePrevious
        .txtCurrentSeason = rstEmployee!fldSeasonCurrent
 
    End With
 
     For Each varThis In rstEmployee.Fields
         Debug.Print varThis.Name
     Next varThis
 
    Set rstEmployee = Nothing
 

In other words, I pass my recordset to this subroutine for printing to my form. Also note that I changed your code from rstEmployee.Controls to rstEmployee.Fields since there doesn't seem to be a Controls property available for this object. The code runs fine, and the Immediate window shows:

fldERN
fldNameLast
fldNameFirst
fldTitlePrevious
fldLocationPrevious
fldAgencyStartDate
fldSeasonCurrent
tblLifeguards.fldBaseAddition
fldAsstCoordinatorBase
fldLfgdCoordinatorBase
fldLfgdBase
fldBoroCoordinatorBase
fldChiefBase
fldLieutenantBase
fldAsstCoordinatorRIP
fldLfgdCoordinatorRIP
fldLfgdRIP
fldBoroCoordinatorRIP
fldChiefRIP
fldLieutenantRIP
fldSeason
fldLongevityAmt
tblBaseAdditionCategories.fldBaseAddition
fldBaseAdditionAmt
 

But this is with the alias in effect in the SQL code, so all the fields are appearing with their aliased names. For instance, fldAsstCoordinator appears as fldAssCoordinatorBase for one table and fldAssCoordinatorRIP for the other table..is this what you were looking for? Or do you want me to do this prior to the aliasing?

Are you trying to say that I should simply give my fields more sensible names in the tables? : - )

Thanks...

Pat

[NeoPa]

...Also note that I changed your code from rstEmployee.Controls to rstEmployee.Fields since there doesn't seem to be a Controls property available for this object. The code runs fine, and the Immediate window shows:
...

Good spot. My bad :)
Notice that the fields in lines #8 & #23 have the same fundamental name, but Access has given references to the fields which include the table names. This is exactly what happens when the same fieldnames are used in a multi-table query (in a simple QueryDef).

But this is with the alias in effect in the SQL code, so all the fields are appearing with their aliased names. For instance, fldAsstCoordinator appears as fldAssCoordinatorBase for one table and fldAssCoordinatorRIP for the other table..is this what you were looking for? Or do you want me to do this prior to the aliasing?

Not all ;)

Are you trying to say that I should simply give my fields more sensible names in the tables? : - )

NOOOooo. I wouldn't dream of it.
Actually, there are often times where tables need to use the same fieldnames as other tables. In a query though, I would always alias fields which are not uniquely named within the query. It just makes life so much easier. Why wouldn't one?