Use a variable in SQL string in my VBA

MushroomKing

Registered User.
Local time
Today, 10:13
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!
 
Hi. Do you mean something like this?
Code:
strTableName = "MyTable"
strSQL = "SELECT * FROM " & strTableName
Set rs = CurrentDb.OpenRecordset(strSQL)
 
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.
 
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.
 
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
 
Post your database with some sample data + name of the "problem" form!
 
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
 
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.
 
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!
 
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

Back
Top Bottom