Use a variable in SQL string in my VBA (1 Viewer)

MushroomKing

Registered User.
Local time
Yesterday, 18:47
Joined
Jun 7, 2018
Messages
100
Hi guys!

Small question here I cant seem to find any answer for.
Maybe im searching with the wrong terminology?


I would like to use a variable to adres a table in my SQL bit inside my VBA code.

What I try to do is this:


Code:
    Dim db As Database
    Dim rec As Recordset
    Dim ToControlString As String
    ToControlString = Me.fieldname23
    Set db = CurrentDb
[B]    Set rec = db.OpenRecordset("Select * FROM ToControlString ")[/B]

Is it even possible to have a string to adres a table???
(see bold text).

Would be great if i could...Thanks in advance guys!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:47
Joined
Oct 29, 2018
Messages
21,358
Hi. Do you mean something like this?
Code:
strTableName = "MyTable"
strSQL = "SELECT * FROM " & strTableName
Set rs = CurrentDb.OpenRecordset(strSQL)
 

Mark_

Longboard on the internet
Local time
Yesterday, 18:47
Joined
Sep 12, 2017
Messages
2,111
theDBguy is not only correct, this is preferable to trying to write your SQL in the .Openrecordset itself. By filling a string you can use either debug.print or a msgbox to see exactly what you will be passing prior to passing it. This helps out immensely in working out the proper SQL to pass.

It has the added advantage that you can build the SQL string you wish to pass in more than one way without having to have separate calls to CurrentDb.OpenRecordSet depending on which of several where or order by clauses you wish to use.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Jan 20, 2009
Messages
12,849
It might not be the case here but often a need to select different table to be involved in otherwise similar or identical processes suggest that the data structure might not be ideal.

If you have the same kind of data in more than one table then this would be the case.
 

MushroomKing

Registered User.
Local time
Yesterday, 18:47
Joined
Jun 7, 2018
Messages
100
Hey everyone!

Thanks for the replies. Correct, I should always to this. Thanks for reminding me :) Good tip


However, what I was actually trying to achieve I still didn't manage to do.

I made some form with a drag and drop function. You drag values around in text fields. These are saved (for specific reasons) into a table by using a recordset.


I have named the fields, the same as my table names.
This I did to keep it dynamic. What I am trying to do, is write the data to a table, using this code below, and use the field NAME , to adres the correct table.

Im doing something wrong here...the values are not written to the table.


Code:
    Dim ToControlString As String
    Dim db As Database
    Dim rec As Recordset
    ToControlString = CurrentControl
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * FROM " & ToControlString)
    rec.AddNew
    rec("BoneNumber") = Me.BoneNumber
    rec("description") = Controls(ToControl.Name)
    rec("posted") = "Yes"
    rec.Update
    Set rec = Nothing
    Set db = Nothing
 

JHB

Have been here a while
Local time
Today, 02:47
Joined
Jun 17, 2012
Messages
7,732
Post your database with some sample data + name of the "problem" form!
 

MushroomKing

Registered User.
Local time
Yesterday, 18:47
Joined
Jun 7, 2018
Messages
100
Hi JHB,

Problem with that is that its company application with a split backend and such.
So thatll be kinda tricky.

What i basically try to do is use the fieldname as my table name.

Set rec = db.OpenRecordset("Select * FROM MYCURRENTFIELDNAME ")

My full sub looks like this:

Code:
Private Sub SwapElementBasics(FromControl As Object, ToControl As Object)
    On Error Resume Next

    Dim Testbox As String
    Dim Field_tablename As String
    Testbox = Controls(FromControl.Name)

'Controls(FromControl.Name) = Controls(ToControl.Name)
Controls(ToControl.Name) = Testbox


    Dim ToControlString As String
    Dim db As Database
    Dim rec As Recordset
    
    ToControlString = CurrentControl
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * FROM " & ToControlString)
    rec.AddNew
    rec("BoneNumber") = Me.BoneNumber
    rec("description") = Controls(ToControl.Name)
    rec("posted") = "Yes"
    rec.Update
    Set rec = Nothing
    Set db = Nothing
    
    'Controls(ToControl.Name) = Null

End Sub
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Jan 20, 2009
Messages
12,849
The code shows your tables have the same fields. This confirms my suspicions.

There should be one table for all the data with an extra field to record what you are currently storing by directing the record to a particular table.
 

MushroomKing

Registered User.
Local time
Yesterday, 18:47
Joined
Jun 7, 2018
Messages
100
I figured it out! :)


Code:
    Dim db As Database
    Dim rec As Recordset
    Dim ToControl As Object
    strTableName = "MyTable"
    strSQL = "SELECT * FROM " & ToControl.Name
    Set db = CurrentDb
    Set rec = db.OpenRecordset(strSQL)
        rec.AddNew
        rec("BoneNumber") = Me.BoneNumber
        rec("description") = Controls(ToControl.Name)
        rec("posted") = "Yes"
        rec.Update
        Set rec = Nothing
        Set db = Nothing

Thanks everyone!
 

Cronk

Registered User.
Local time
Today, 12:47
Joined
Jul 4, 2013
Messages
2,770
I doubt that code as it stands, will work. ToControl is declared as an Object but no where instantiated so ToControl.name is meaningless.
 

Users who are viewing this thread

Top Bottom