How to find a table name using VBA?

[KingBroil]

Thanks for reading this,

I have a command button on a continuous form(form 1) and I need this button to open another form(form 2) when I press on it. So far so good.

When I press the button, I need some VBA to open the form(form 2) , search for a particular table name based on the open form(form 1) current record and use that table name as the newly opened form (form 2) data source. I have ways to do most of those task but for one thing:

How do I make access search for a table name containing a particular string? Here's what I am working with:

Private Sub Commande26_Click()
On Error GoTo Err_Commande26_Click
     Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stDataSource As String
    
 
    stDocName = "frmSuiviLogger"
    stDataSource = Me.[#série]
    DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
 
Forms!frmSuiviLogger.RecordSource = "SELECT MySysObjects.Name FROM MySysObjects WHERE MySysObjects.Name = " * " &stDataSource& " * ""
 Exit_Commande26_Click:
    Exit Sub
 Err_Commande26_Click:
    MsgBox Err.Description
    Resume Exit_Commande26_Click
    
End Sub

I probably don't need to write it but, I'm not a programmer so I hope my explanation is clear enough.

Thank you very much, the satisfaction of writing some code and succede in acchieving the task is so rewarding, I love it!

 

[Ranman256]

Do you mean:
you are on a continuous form on client 123
now open a form to show the record for 123 only?... if so:

DoCmd.OpenForm "frmSuiviLogger", acFormDS, , "[ClientID] = " & Me.txtClientID

 

[CJ_London]

not sure what you are trying to do but this

....WHERE MySysObjects.Name = " * " &stDataSource& " * ""

should be constructed like this

....WHERE MySysObjects.Name Like '*" & stDataSource & "*'"

 

[KingBroil]

Actually, I have 44 "clients" displaying on my main form. I also have 44 tables, one for each clients that stores the tracking info for them.
I then have one single form for entering client tracking info on which I only change the form's data source property to the table that I want to edit.
Now I want to be able to go to client #23 on my main continuous form, click on the button and automatically open my other form with the client #23 tracking table as data source so I can edit the tracking info for this particular client. My problem is actually right here:

Forms!frmSuiviLogger.RecordSource = "SELECT MySysObjects.Name FROM MySysObjects WHERE MySysObjects.Name = " * " &stDataSource& " * ""

How can I tell Access: Find me the table with this string of text (stDataSource) in the table's name. stDataSource is just a variable with the current client's serial number, which are part of the tracking tables names.

One solution would be to re-name all my tables with only the serial number exactly as typed in my serial number field on my main form, I just thought there was a neater solution.

Sorry guys, I find it really hard to explain and thanks for trying to understand!

I will try Ranman256 suggestion first and see what I can get..

 

[CJ_London]

Whoa! Have you not heard of normalisation? you should have one table for all clients, with an additional column to identify the client.

Before you go any further, you really need to learn and understand how databases work - if you had the one table, then Ranmans solution will work.

otherwise try

DoCmd.openform "frmSuiviLogger", acFormDS, , , , , "[" & me.datasource & "]"

then in your frmSuiviLogger form open event put

me.recordsource=openargs

However I suggest the following with regards your navigation form if you do not want to normalise your data (there can be valid reasons when switching between test and live data for example):

1. have it as a continuous or datasheet form with a recordsource of

select [name] as tablename from mysysobjects where [type]=-32768

the following code should then be put in the tablename control doubleclick event

DoCmd.openform "frmSuiviLogger", acFormDS, , , , , "[" & me.tablename & "]"

 

[KingBroil]

I have one table for all clients. Clients are actualy devices that goes out in the field and back at the warehouse, in and out all the time. The 44 tables are for recording the "ins" and "out" of each individual devices. Each of the tables have fields like:
OutBy, OutDate, ApprovedBy, InBy, InDate, VerifiedBy, Comments
And each records represent one time the device went in the field, which I need to keep track of and history. So one Device Tracking Table for each of the 44 devices.

I thought about normalization when doing this but couldn't think of any alternate way.

Was I wrong, and if so, any lead on a better structure would be much appreciated.

I will also try your suggestions, but not right now as I only have so much time. I'll keep you guys posted.

Cheers

 

[Solo712]

I have one table for all clients. Clients are actualy devices that goes out in the field and back at the warehouse, in and out all the time. The 44 tables are for recording the "ins" and "out" of each individual devices. Each of the tables have fields like:
OutBy, OutDate, ApprovedBy, InBy, InDate, VerifiedBy, Comments
And each records represent one time the device went in the field, which I need to keep track of and history. So one Device Tracking Table for each of the 44 devices.

I thought about normalization when doing this but couldn't think of any alternate way.

The alternative is to have one table for your clients (devices) and one table for tracking them. Instead of having 44 tables you only need to add one field that identifies the tracked device to what you already have. This will allow you to have multiple records for each device in the tracking table. This kind of relationship (device vs. device tracking) is called one-to-many and there are only two ways you can create them: the right way and the completely insane way. Take your pick !

Best,
Jiri

 

[KingBroil]

The alternative is to have one table for your clients (devices) and one table for tracking them. Instead of having 44 tables you only need to add one field that identifies the tracked device to what you already have. This will allow you to have multiple records for each device in the tracking table. This kind of relationship (device vs. device tracking) is called one-to-many and there are only two ways you can create them: the right way and the completely insane way. Take your pick !

Best,
Jiri

That's an answer for a beginner like me! I know I learn the hard way but you made it clear in my mind now. I just had a hard time getting in my head that all my tracking info needs to be all mixted up in one table and queries and forms will get the data I need when I need. I know the normalization rules but just need to learn how to apply them.

Anyway, I can get to work and undo some of my work to build it the correct way now. Because it is definitely the way that I want to take.

Thanks for taking the time to help even the beginners with their silly errors

Cheers!