473,695 Members | 1,853 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

patjones
931 Recognized Expert Contributor
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 fldAsstCoordina tor. 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

Expand|Select|Wrap|Line Numbers
  1. .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
Nov 11 '07 #1
7 6241
Jim Doherty
897 Recognized Expert Contributor
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 fldAsstCoordina tor. 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

Expand|Select|Wrap|Line Numbers
  1. .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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT tblMyTable1.myid, tblMyTable1.fldLfgd, tblMyTable1.myfield3, tblMyTable1.myfield4, tblMyTable2.fldLfgd AS MyfldLfGdAlias, tblMyTable1.myfield6, tblMyTable1.myfield7, tblMyTable1.myfield8 
  3. FROM tblMyTable1 
  4. INNER JOIN tblMyTable2 ON tblMyTable.myid=tblMyTable2.myid
  5.  
  6.  
You would then refer to the sql defined ALIAS provided to your rst in your textbox assignment like this

Expand|Select|Wrap|Line Numbers
  1. 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 :)
Nov 11 '07 #2
patjones
931 Recognized Expert Contributor
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT tblMyTable1.myid, tblMyTable1.fldLfgd, tblMyTable1.myfield3, tblMyTable1.myfield4, tblMyTable2.fldLfgd AS MyfldLfGdAlias, tblMyTable1.myfield6, tblMyTable1.myfield7, tblMyTable1.myfield8 
  3. FROM tblMyTable1 
  4. INNER JOIN tblMyTable2 ON tblMyTable.myid=tblMyTable2.myid
  5.  
  6.  
You would then refer to the sql defined ALIAS provided to your rst in your textbox assignment like this

Expand|Select|Wrap|Line Numbers
  1. 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!
Nov 11 '07 #3
patjones
931 Recognized Expert Contributor
Works perfectly! Thanks so much...

Pat
Nov 11 '07 #4
Jim Doherty
897 Recognized Expert Contributor
Works perfectly! Thanks so much...

Pat
You're welcome and thanks for reporting back

Jim :)
Nov 11 '07 #5
NeoPa
32,569 Recognized Expert Moderator MVP
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub TestItOut
  2.   Dim varThis As Variant
  3.   Dim rst As DAO.Recordset
  4.  
  5.   '... Whatever you do to open your recordset
  6.   For Each varThis In rst.Controls
  7.     Debug.Print varThis.Name
  8.   Next varThis
  9. End Sub
Nov 12 '07 #6
patjones
931 Recognized Expert Contributor
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub TestItOut
  2. Dim varThis As Variant
  3. Dim rst As DAO.Recordset
  4.  
  5. '... Whatever you do to open your recordset
  6. For Each varThis In rst.Controls
  7. Debug.Print varThis.Name
  8. Next varThis
  9. End Sub
Hi NeoPa:

I put your code into my subroutine as follows:

Expand|Select|Wrap|Line Numbers
  1.  Sub DisplayEmployeeInfo(rstEmployee As DAO.Recordset) 
  2.  
  3. On Error GoTo Err_DisplayEmployeeInfo
  4.  
  5.     Dim varThis As Variant
  6.  
  7.     With Me
  8.  
  9.         .txtNameLast = rstEmployee!fldNameLast
  10.         .txtNameFirst = rstEmployee!fldNameFirst
  11.         .txtERN = rstEmployee!fldERN
  12.         .txtLocationPrevious = rstEmployee!fldLocationPrevious
  13.         .txtTitlePrevious = rstEmployee!fldTitlePrevious
  14.         .txtCurrentSeason = rstEmployee!fldSeasonCurrent
  15.  
  16.     End With
  17.  
  18.     For Each varThis In rstEmployee.Fields
  19.         Debug.Print varThis.Name
  20.     Next varThis
  21.  
  22.     Set rstEmployee = Nothing
  23.  
In other words, I pass my recordset to this subroutine for printing to my form. Also note that I changed your code from rstEmployee.Con trols to rstEmployee.Fie lds since there doesn't seem to be a Controls property available for this object. The code runs fine, and the Immediate window shows:

Expand|Select|Wrap|Line Numbers
  1. fldERN
  2. fldNameLast
  3. fldNameFirst
  4. fldTitlePrevious
  5. fldLocationPrevious
  6. fldAgencyStartDate
  7. fldSeasonCurrent
  8. tblLifeguards.fldBaseAddition
  9. fldAsstCoordinatorBase
  10. fldLfgdCoordinatorBase
  11. fldLfgdBase
  12. fldBoroCoordinatorBase
  13. fldChiefBase
  14. fldLieutenantBase
  15. fldAsstCoordinatorRIP
  16. fldLfgdCoordinatorRIP
  17. fldLfgdRIP
  18. fldBoroCoordinatorRIP
  19. fldChiefRIP
  20. fldLieutenantRIP
  21. fldSeason
  22. fldLongevityAmt
  23. tblBaseAdditionCategories.fldBaseAddition
  24. fldBaseAdditionAmt
  25.  
But this is with the alias in effect in the SQL code, so all the fields are appearing with their aliased names. For instance, fldAsstCoordina tor appears as fldAssCoordinat orBase for one table and fldAssCoordinat orRIP 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
Nov 15 '07 #7
NeoPa
32,569 Recognized Expert Moderator MVP
...Also note that I changed your code from rstEmployee.Con trols to rstEmployee.Fie lds 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, fldAsstCoordina tor appears as fldAssCoordinat orBase for one table and fldAssCoordinat orRIP 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?
Nov 15 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
1517
by: Jim G | last post by:
I have two tables: TestA and TestB. Both tables have 3 fields: ID, Name, and RunDate. I need to create a query which will join the two tables first on Name but then I need to match up the RunDates even though the RunDates won't be the same. CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate DATETIME) CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate DATETIME)
4
6812
by: Andy Hutchings | last post by:
Hi everybody - hope you can help out here. I have a form in a database, which is a columnar form from one of the tables in the db - there is a sub-form to the form which is a datasheet view of another table in the db. The LinkChilds property is set to show connected records from both tables. What I'd like to do is copy and paste, or use some other method of writing, the contents of one field in one table in the form, to a field in the...
1
3438
by: Don Leverton | last post by:
Hi Folks, I have been given a CD with approx 130 .xls files (bean-counters!) that I would like to import and merge to ONE table (tblTradeshow). The XL files are *similarly*, but not identically structured, and the first row does NOT contain field names. Some (actually most) of the column names *are* the same in all of the spreadsheets.
2
2417
by: Rooksarii | last post by:
Hello folks, Let me first apologize for any impropper terminology I may use as I am presently trying to broaden my Office knowledge by diving into Access head on. My specific problem is this. I have a field in one of my tables that references a value in another table via a list box. Next to it, instead of manually creating another list box to go along with it, I would like to have this other field acknowledge that the value in it's...
9
4754
by: Pam Ammond | last post by:
I need the code to update the database when Save is clicked and a text field has changed. This should be very easy since I used Microsoft's wizards for the OleDBAdapter and OleDBConnection, and DataSet; and all I'm doing is showing one record in text fields, allowing the user to modify the text fields, and then updating the database again when the user clicks the Save button. The fields already show the correct data record since I have...
5
1801
by: Amelyan | last post by:
I am struggling here trying to determine what is a good programming practice as far as referencing your URLs. When you use Response.Redirect, do you use 1) Hard-coded string -- Response.Redirect("MyPage.aspx"); 2) Constants -- Response.Redirect(STRMyPage); // where input parameter is -> const string STRMyPage = "MyPage.aspx"; 3) Something entirely differnent?
39
3215
by: windandwaves | last post by:
Hi Folk I have to store up to eight boolean bits of information about an item in my database. e.g. with restaurant drive-through facility yellow windows
5
4095
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each table in design mode and then add the new field and set its properties. Thanks. --
3
2066
by: dstorms | last post by:
Hi there, I'm developing a database for my company, and for the most part I've been successful, with some complex sub-routines already working fine. But this one has me stumped! Some background on this database: It has numerous and tables and queries, and a few tables have identical field names, and I've created a union query, named QryItemList, to create a list containing the unique identifier number in UniqueID, and included the field...
0
8630
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9115
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8845
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8825
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6491
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5839
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2272
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1976
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.