Return the name of a record's source table

[unknown2u]

Anyone...? Please?

I fixed all the issues you mentioned but the code is still needed to open the form depending on the record...

 

[gemma-the-husky]

[deleted original post]

 

[Galaxiom]

If there are many differences in the data to be recorded another approach for the extra fields is warranted. However putting the different types of connections into different tables and then trying to work out which one they come from isn't one of the recommended solutions.

Use a related table with the following fields:

ConnectionID (the foreign key to the main table)
AttributeID. This is the key to a table of attributes that may be applied.
AttributeValue. This stores the value of the attribute.

In some caes there may be another field for UnitID.

A junction table records which Attributes belong to which connectiontype.

The attributes are displayed in a subform. As many as you like can be added.

 

[unknown2u]

The relations between the tables is no more relevant...

I fixed that problem.
But I still need the code to get the record's source table..
Is it really that complicated to accomplish...?

 

[Galaxiom]

Derive another field in the RecordSource query to hold the name of the table or form associated with the record and read that into the OpenForm command.

 

[unknown2u]

Hmm.... interesting idea.
I'll give it a shot and let you know if it worked.

Thanks!

 

[unknown2u]

OK, I managed to return the names of the tables into a query using this SQL code I found:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

Now the problem is I'm not sure how to use one of the values from the queries in the VBA code I got (as I said I don't know VBA much...):

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

Please help...

 

[jdraw]

I'm not sure where you are on this at the moment. You should be aware that a form's record source can be changed programmatically, so you may never get things 100%. I have looked specifically at textBox controls.

I have a couple of routines.

1)Get all controls from all forms in a database; put the form name, control type and control name into a table.

2)Get the record source for every form in a database and put the form name, record source type (table, saved query def, or SQL string) in a table.

3) A query that uses these above tables to identify the form, the control, and the record source for the form.

It does not match the control on the form to a specific field, but it does show that the form control name is in the specific record source.

It could be used to get the specific field in the record source with a little work, but as I said it wouldn't be 100%.

Post if you have questions.

 

[unknown2u]

Ok, first of all thank you jdraw

But it seems what I need help with is still not clear so I'll try and make it even simpler.

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

This code works! It's great! It does exactly what I need.

The reason I want to return the name of a record's source table is so I can use it IN THIS CODE.

It won't do any good to get records' source tables from a query if I can't use them in this code.

I need to change the DocName line to something like this:

* DocName = Record.SourceForm

The idea is to open the respective form, but I'm guessing that since the form is dependant on the table (and was created according to the table) it would be easier to get the table name and then find a way to work it around.

Hope this makes things clearer

 

[jdraw]

It does not make things any clearer to me. You say the code you have works great, so what exactly is the issue.
I am willing to help you, but I need specifics. Since you do not know vba, and have limited experience with relational database, can you please tell us in "plain English" (no jargon/access terms) what is it you want to do.
Make up an example if necessary. But for us to help you, we have to understand you and the issue. So an example would show inputs and outputs.

I believe the routines I have identified will help, it's just a matter of where/when to execute them (or some part) from your requirements.

Post a copy of part of your db - at least then we have something to discuss.

 

[unknown2u]

OK. Don't get me wrong I really appreciate it that you're trying to help.

I'll try and give an example about the code I wrote above that works and what I need to change:

Let's say you have this:

- 3 Tables: TypeA and TypeB
TypeA has 100 lines, let's take one for example "10-123456"
TypeB has 200 lines, let's take one for example "88-654321"
LineID has 300 lines, which are all the lines above from TypeA and TypeB 100+200.

The tables ARE linked using table relations - I fixed that, works fine the way you all said it should be.

- 2 Forms: FormForTypeA, FormForTypeB
FormForTypeA, displays all 100 the lines that are in table TypeA to the users
FormForTypeB, displays all 200 the lines that are in table TypeB to the users

- 1 Report that is linked to 1 Query with the criteria: Like "*" & [Search Line:] & "*"

Of course I made this much simpler than how things actually work but it shouldn't matter because the concept is exactly the same. The DB works fine.

Now, when you open the report, it runs the query, then you enter for example one of the lines above, let's say I search for 123456.
The report shows the result 10-123456.

So far so good.
Using this code:

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

* DocName = "FormForTypeA"
* strWhere = "[LineID]='" & LineID & "'"
* DoCmd.OpenForm DocName, acNormal, , strWhere
End Sub

When I click on the LineID 10-123456 IN THE REPORT it opens the form I entered in DocName which is FormForTypeA, with all the details of the line.
That's what I mean when I say the code works - the concept works great.

But I want to take it further by writing a string variable for DocName instead of the name of the form.
If I entered 654321 in the query it wouldn't open 88-654321 in FormForTypeA because that form doesn't display that line - it's not a part of it's table.

So I want to be able to get the table OR form source for a line (in the example 10-123456 or 88-654321) so I put it in a variable and when I click ANY type of line it will open the correct form.

By the way, using 1 form for all the line is NOT an option because there's a big difference between the types and forms.

Hope this helps.
Thanks again for you help!

 

[jdraw]

OK. Let's step back a little. You said 3 tables - which one is table 3?
Tell us about the Lines/ Why is a line assigned to TypeA vs TypeB?
If all Lines are in the third Table, why can't it be searched in that Table?

If a person in the line at McDonalds,-- who you don't know, who doesn't know your business, who knows nothing about database or Access -- said "What do you do and what issue are you having at the moment?"

What would you say? simple, clear terms - like talking to a 6 year old.

 

[unknown2u]

OK. Let's step back a little. You said 3 tables - which one is table 3?

Oh sorry, 3 tables : TypeA, TypeB and LineID.

Tell us about the Lines/ Why is a line assigned to TypeA vs TypeB?

As I mentioned before, the lines are assigned to different types because they ARE different.
They're all communication lines but of different characteristics.
They have many similar fields but also many different ones so putting them in 1 form or table would result in a huge amount of fields and information.

I don't wanna complicate things but you could think of TypeA as Layer 1 lines, TypeB as Metro lines (Layer 2), TypeC as IP lines (Layer 3), TypeD would be simple telephone lines, so they don't all work the same.

If all Lines are in the third Table, why can't it be searched in that Table?

They can! That's what I said about the query... the topic of this disscussion is not how to search for the lines - that's irrelevant since I already managed that part using relational tables.

If a person in the line at McDonalds

"Line at McDonalds"??? I did mention we're talking about communication lines right??
So to talk to someone at Mcdonalds would be irrelevant.

Bottom line: My problem is I want to return the name of a form or a table when I click on an object in Access. The table or form should be that object's source table or form.

 

[unknown2u]

Is it really that complicated to return a value in VBA???

 

[Galaxiom]

We tend to lose interest when a poster is more intent on insisting how they are already doing it right than trying to understand what is being explained to them.

TypeA has 100 lines, let's take one for example "10-123456"
TypeB has 200 lines, let's take one for example "88-654321"
LineID has 300 lines, which are all the lines above from TypeA and TypeB 100+200.

The tables ARE linked using table relations - I fixed that, works fine the way you all said it should be.

The fact is you haven't a clue about the nature of a relational data structure.

 

[unknown2u]

I gave an oversimplified explanation of the idea of my DB...

You all seem to focus too much on the how my DB works instead of simply answering a very simple question in VB which is how to return a value of an object.

I do appreciate your help with the relations since I did change it and discovered how it might aid my in the future.
Don't blame me for wasting 3 pages on something I didn't ask.
If you told me what I want to do is impossible or that there's no such option in VBA I would accept that and try to find another solution.

This question has nothing to do with the DB itself.
Return the NAME of a source table of a record! That's it! If you told me that you don't know I would accept that too, it's better than wasting time on telling me I know nothing.

 

[Galaxiom]

So let me try to understand. Is your reqiurement for VBA code to go looking through tables to find which one holds a particular value currently displayed in the form?

 

[jdraw]

Bottom line: My problem is I want to return the name of a form or a table when I click on an object in Access. The table or form should be that object's source table or form.

Perhaps you could tell us explicitly what you mean by OBJECT in the quote above.

It isn't so much that talking to someone at McDonalds is irrelevant. It's an analogy to help you communicate to us, and since you admittedly don't Access, we're trying to get you to communicate in English. We're at post 36 and Galaxiom is still asking what you are trying to do. He's saying it in English so you can respond with a yes or no.

I have routines than can relate any control on any form and guide you to the underlying Table, Query or SQL string. I said that previously, but you didn't seem to want it, and told me how you have built something.

We can't help you until we understand what you need. You continue to tell us you have built a database and it works.

We're still here to help.

 

[unknown2u]

So let me try to understand. Is your reqiurement for VBA code to go looking through tables to find which one holds a particular value currently displayed in the form?

No.

We're still here to help.

I know, and I truly appreciate it.

Sorry I still couldn't make myself clear.
I finally found some time to try and create a DB that shows how mine works and upload it, since uploading mine is a problem.
It's exactly the same idea only smaller and with less details.
I hope it'll make things easier for you to help me.

Let me know if there's anything else you think I should change in the way it works, I'm always willing to learn.

View attachment Example.zip

Regarding the data itself, I totally made up stuff so don't really take it seriously. Of course the design is also very basic comparing to what I did but it doesn't matter.
But if you look into it you'll understand why there are different types of lines.

To understand what I want, do the following:
Open the Report "Search" then type a Line ID's last digits, for example type 015.
A report will open with the Line ID 10-000015.
Now click on the Line ID itself (10-000015) and the form Transmission Lines will open with the line and its details.

If you search for another line that is also a Transmission line it'll still work.
But if you search for an IP line (for example search for 123 to find 88-123456) and click on the result, it won't open!
Why? Because the form Transmission Lines doesn't have the details on an IP line. So you'll get Transmission Lines opened with a blank record.

The form IP Lines has details on 88-123456. So I need it to open instead.
Now look at the VBA code I used:

Private Sub Line_ID_Click()
 
 Dim strWhere As String
 Dim DocName As String
 
 DocName = "Transmission Lines"
  strWhere = "[Line ID]='" & Line_ID & "'"
  DoCmd.OpenForm DocName, acNormal, , strWhere

End Sub

Instead of

 DocName = "Transmission Lines"

I need DocName to get the name of the form that has the record you clicked on after searching for a line.

That's the idea.

The reason I asked for a table is because that's also an option to do that with many "if else" statements...

I did my best, I hope this'll help.
Thank you again

 

[unknown2u]

Galaxiom, jdraw is it better now?
If you need any additional details let me know.