Return the name of a record's source table

[unknown2u]

Hey I'm new to Access and VB but I managed to write parts of what I want access to do.*

By tweaking a code I found on the internet, I managed to write a small Subroutine that allows the user to click on one of the fields of a record in a report and then Access opens the form on that specific record.*

My code goes something like this:*

Private Sub Edit_Click()
* Dim strWhere As String
* Dim DocName As String

* DocName = "FormName"
* strWhere = "[Field Name]='" & Field & "'"
* DoCmd.OpenForm DocName, acNormal, , strWhere
End Sub

Now I want to get the name of the Table where the record exists.*
So, let's say when I click on the "Field" it gets the name of the table where the record with that field exists and sets it in a variable.

OR even better would be, get the name of the Form where that record exists but I guess that's a little more complicated since the record is directly linked to the table...*

How do I do that?
Thanks

 

[Galaxiom]

Your requirements strongly suggest you are using structure to encode data. A properly designed data structure would never do this.

Post a sample of your database or a relationships diagram.

 

[unknown2u]

It actually has nothing to do with data encoding...
I don't even have any relationships between tables.

It's irrelevant and I'm sure there is a simple way to get the name of a record's table...

 

[jdraw]

PLease show an example of what you are talking about.

 

[Mile-O]

It actually has nothing to do with data encoding...
I don't even have any relationships between tables.

It's irrelevant and I'm sure there is a simple way to get the name of a record's table...

If you were using forms properly you would just refer to either the form's RowSource or a control's ControlSource property to get where the record comes from. Of course, you would, for better practice, bind these to a query based on your table(s) rather than the table(s) themselves.

What possible purpose you could have for your approach, I can't think. That you have no relationships between tables...what's the point in using a relational database?

 

[unknown2u]

As I said, I'm new to Access, therefore have I haven't had the chance to mess around with relations but the fact of the matter is I already have a quite huge DB which works completely fine.

I'm just trying to take it a few steps ahead.

I don't have an example to show... I simply asked how to get the name of a table... Just as I can write "msg = Me.RecordSource" which will set the value of msg (as a string) to the name of the record source that was clicked on.

In the same manner, I want to get the name of the table...
I don't think what I use it for matters much...

 

[JohnLee]

In order for anyone to help you they need to have a clear understanding of what it is you are doing and the purpose of what you are doing in order that they can provide you with the best help.

If your unwilling to provide that information or examples of what you have by way of screenshots or a sample of the DB, then that help will be difficult to provide.

I find it strange that you state that you have a large DB that is working fine, and by the sounds of it without any relationships, as already stated what's the point of using a relational database if there are no relationships established. It sounds like you using it like a spreadsheet which if this is the case, you'll run into problems as you attempt to be more creative and functional in what your DB is doing whatever that might be.

 

[unknown2u]

Ok I'll try and be more clear about the purpose of my DB.
Btw, I never defined it as a "relational database"...

I created a DB that contains and sorts many types of communication lines.

Let's say there are 3 types of lines, then there are 3 different tables, each table contains different fields that are relevant to the specific type of line.
For example, side a and side b of the line, ID code, rate, interface and so on...

For each table I created a form to display the records in a convenient way to the users, and for certain types of fields I also created queries to get reports on specific criteria for each type of line.
For example - a query that produces a report of all the lines of Type A that are from a certain place A to certain place B (a string is entered by the user)

So far it works great.
The problem is I'm trying to create 2 things to make it easier for users to search and access records:

1) As I said, when a line ID is clicked on in a report, it opens the relevant form and displays the record - so far also works great with the code I wrote above.

2) The users usually open a form and search for a value (simple Ctrl+F search) to find a specific record.
The big goal is to make it so they don't have to search in every separate form but do a general search in all the DB.

I wanted to combine 1+2 to make things easier - creating a query that searches a string entered by the user for the same field that exists in all tables, then produce a report, and when clicked on, the DB opens the associated form.

For everything that works so far I didn't use any table relations.
Hope I was clear enough...

 

[jdraw]

How about posting a copy of your db or your tables in zip format?
If you are planning on using the features of Access, then I strongly suggest you do some research on relational database principles and database design.

Good luck with your project.

 

[Galaxiom]

It actually has nothing to do with data encoding...

Oh yes it does.

Let's say there are 3 types of lines, then there are 3 different tables, each table contains different fields that are relevant to the specific type of line.
For example, side a and side b of the line, ID code, rate, interface and so on...

Exactly as I suspected. You are using structure to encode data (putting lines into different tables to indicate their type).

The line records should all be in one table where they can be searched in one place. The attributes of those lines can go into a related table and be displayed in a subform.

Your problems stem from not using a relational data model.

 

[unknown2u]

Wow... ok... that explains a few things..
Well, the part I forgot to mention is I actually learned by trial and error and everything after taking an existing DB that someone else made before me...

So I now unedrstand he didn't work properly from the beginning... which made me make these mistakes...

jdraw thanks. I'll read the files you sent me and try and build my DB from scratch.... though it might be a nightmare with about 3000 records...

If anyone has some more good sources to fix this I would greatly appreciate it
Thank you!

 

[unknown2u]

My big problem in creating a big table such as the one you describe is that each type of line has different fields...
So I'm suppose to create one big table with no fields and small tables with many different fields I don't get it...

 

[Mile-O]

Do the lines have any similar attributes at all?

 

[unknown2u]

Actually they have a lot of similar attributes since they're all communication lines.

It's just that some of them have extra or a few less attributes.

 

[Galaxiom]

There are a number of different ways to deal with this.

It is important that we get more of a picture of what your requirements are before going any further because that will affect the decision on the most appropriate way to go.

Obviously, the main table can start by including all the common attributes. Sometimes it is easier to just include all the other fields too if there are very few variations and simply ignore the ones that don't apply.

This can be refined by using Conditional Formatting to disable the controls that do not apply. In your case you will have a ConnectionTypeID field which defines what you are currently using to put the connections into different tables. The ConditionalFormat for the controls displaying the optional attributes would use an Expression to decide if they should be enabled. It is quite straightforward to set up.

If you want to get fancier, code can be used to hide the controls not inapplicable to particular records and even move the rest of them up. However this only works in Single Forms. The Conditional Format technique works in all types of forms.

Then come the technique where the optional attributes are held in a related table and displayed in a subform. It is more complex to implement and has a few issue to deal with. However it is very powerful.

One huge potential advantage of this technique is that it allows new attributes to be added by users without modifying the tables or forms. It also allows an effectively unlimited number of attributes to be recorded.

 

[unknown2u]

I see what you mean but as you say, I need some kind of tutorial to understand these concepts and how to use them, then I can implement them in my existing DB.

I googled for some relations tutorial and some seem ok but still not very informative...
If you can send me a good tutorial that helps understand the Conditional Formatting that would be great

I'm not sure where that option is in Access 2010...

 

[Galaxiom]

ConditionalFormatting is available with a right click on the control.

One of its options under the same dropdown as "Field Equals" is "Expression Is".

You have three ConnectionTypes. Say a particular optional control is FrameSize and it is available for Connection Type 1 and 3.

Set the Enabled property for FrameSize to No. In its ConditionalFormatting put the following as the expression and tick the Enable box.

[ConnectionTypeID] In (1,3)

It will become enabled for any records that have 1 or 3 as the ConnectionTypeID.

 

[gemma-the-husky]

the only way I could think of doing this is to interrogate the underlying record source, to locate the control source of the field - which would identify the field's source table.

 

[unknown2u]

Galaxiom, I tested your idea and it works nice.
There are 2 problems with it though:

1) If I end up using conditional formatting in all of my DB I would end up with a huge form with tons of fields enabled/disabled... since I do have many types and many optional fields for each type...

2) I'd still not be using relational tables... which according to what I understand throughout this whole post, is the right way to create a good DB...

gemma-the-husky, can you show an example in VB code?

Thank you

 

[unknown2u]

Ok so I read and tried the concept of relating tables... I guess it really does help solve this thing and is not as complicated as I thought.

But still I need the code that I requested even after using relations since I can now search for a specific line ID but I'd still want to click on the line ID and then have it open the relevant form.

So the question still remains:
How do I return the name of a record's source table..?