Need help with VBA passing an argument

[Pasi12]

Hi,

I need help on passing a "person_id" from the application into VBA code below, please help: Here is what I am using but not working because my argument is not correct.
Thanks!!

Sub Auto_open()
   
   Dim person_id As Double
   
   
   person_id = ""
   
   With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
   "ODBC;DSN=ngdevl;Description=NGDevl;UID=sa;pwd="";APP=Microsoft Office 2010;;DATABASE=NGDevl" _
      , Destination:=Range("$A$30")).QueryTable
     .CommandText = Array( _
     " select user_name, password,* from ngweb_bulk_enrollments a INNER JOIN person b ON b.person_id = a.person_id where b.person_id = '" + person_id + "'" _
      )
      .RowNumbers = False
      .FillAdjacentFormulas = False
     .PreserveFormatting = True
       .RefreshOnFileOpen = False11
       .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
       .SavePassword = False
        .SaveData = True
       .AdjustColumnWidth = True
      .RefreshPeriod = 0
       .PreserveColumnInfo = True
       .ListObject.DisplayName = "Table_Query1"
        .Refresh BackgroundQuery:=False
    End With

[Pasi12]

excel error.jpg

[westconn1]

should that not be AS a and AS b?

the + should be &, for string concatenation, while + will work in many places, it is incorrect and may cause error or incorrect result

[Bob Phillips]

If person_id is a Double that means your have a numeric key, therefor you should not be enclosing it in single quotes.

[Pasi12]

Thanks West conn1, good point, How do I set it up at the beginning and pass it on to my code? I am just not thinking right!

should that not be AS a and AS b?

the + should be &, for string concatenation, while + will work in many places, it is incorrect and may cause error or incorrect result

[Pasi12]

You may be right but the person id has characters and numbers in there so I may have to set it up as string? how do I do that?
Thanks.

If person_id is a Double that means your have a numeric key, therefor you should not be enclosing it in single quotes.

[Bob Phillips]

Dim person_id As String

[Pasi12]

I have done that but I am still getting error . see attached. Any thoughts?
Thanks!

here is my code again.

Sub Auto_open()

Dim person_id As String
' perosn_id = "a.person_id"


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=ngdevl;Description=NGDevl;UID=sa;;APP=Microsoft Office 2010;;DATABASE=NGDevl" _
, Destination:=Range("$A$30")).QueryTable
.CommandText = Array( _
" select user_name, password,a.Create_timestamp, security_answer,b.last_name,b.first_name from ngweb_bulk_enrollments a " _
, _
"INNER JOIN person b ON b.person_id = a.person_id where b.person_id = " & prerson_id)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False11
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True
End With

Dim person_id As String

[westconn1]

try like

" select user_name, password,a.Create_timestamp, security_answer,b.last_name,b.first_name from ngweb_bulk_enrollments AS a " _
, _
"INNER JOIN person AS b ON b.person_id = a.person_id where b.person_id = " & prerson_id)

if person_id is now a string, you would again need the single quotes (as you had before), and correct your spelling of variable

also it would appear that person_id has no value when the code is run, so probably no records would be returned anyway

[Pasi12]

Thanks westconn! I am not following you? I am trying to get the The person_id pulled from application . I am running this code from application and want VAB to give me the person_id that I am logged in to currently. Can you perhaps give me the example on my code as what you mean? I am not following you when you are putting "AS" in the query? The "a" are alias to the table in dB query.
Thanks a bunch!

try like

" select user_name, password,a.Create_timestamp, security_answer,b.last_name,b.first_name from ngweb_bulk_enrollments AS a " _
, _
"INNER JOIN person AS b ON b.person_id = a.person_id where b.person_id = " & prerson_id)

if person_id is now a string, you would again need the single quotes (as you had before), and correct your spelling of variable

also it would appear that person_id has no value when the code is run, so probably no records would be returned anyway

[westconn1]

from ADO help

Instructs the Microsoft Jet database engine to return information from the database as a set of records.

Syntax

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

you can either alias each field, or all fields by aliasing a table

I am trying to get the The person_id pulled from application

i did not see anywhere in your code that does this, except one place commented out and one place (in the original post) assigned an empty string

[Bob Phillips]

What he is saying is that the code is returning the name, password and other stuff for a given person_id. So your code has to give person_id a value before calling the query, otherwise you are looking for a record with a null person_id, and as that is likely a key, there shouldn't be one.

[Pasi12]

XLD,you are correct. All I need to pull the person_id from application. The other stuff like user_name, pass word, first and last name I am pulling it but I don't know how to assign the parameter "person_id" to the vba, so when I am logged into application and I am seeing person "A" I grab person "A"'s person Id . match in my table "bulk_enrollment table and get the user_name and password from that table for person "A"and pass it on to my excel sheet.

Weestconn1 . I am already using alias in my codes such as "a" for bulk_enrollment table and and "b" for person table.

If you guys can help just to get the person_id from application and match/look it up in bulk_enrollment table, grab the user_name an dpass word fopr that person id I am done!

That's what I need help with.

Rgds.

What he is saying is that the code is returning the name, password and other stuff for a given person_id. So your code has to give person_id a value before calling the query, otherwise you are looking for a record with a null person_id, and as that is likely a key, there shouldn't be one.

[Bob Phillips]

So you have to set the variable person_id cas has been said several times already. Where are you identifying whose details you want to retrieve, when you say ... when I am logged into application and I am seeing person "A" I grab person "A"'s person Id?

[Pasi12]

Not sure what you are asking? I just need a way to figure out how to assign person_id I get from application and pass it on to excel?


code:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=ngdevl;Description=NGDevl;UID=sa;:APP=Microsoft Office 2010;;DATABASE=NGDevl" _
, Destination:=Range("$A$30")).QueryTable
.CommandText = Array( _
" declare @person_id varchar(36) select user_name, password,a.Create_timestamp, security_answer,b.last_name,b.first_name from ngweb_bulk_enrollments a " _
, _
"INNER JOIN person b ON b.person_id = a.person_id where b.person_id = @person_id") ----> Here is what I have setup the person id, but How do I assign it in vba? right now I am using "@" to assign person_id to application whim is done in SQL, now I have to use some command to assign the person_id in vba, I have used "&person_id but its not working? I have declared it at the beginning as Dim person_id As String, what else do I have to do?
Thanks.

[Pasi12]

I modified the code and now I am getting sql error:

when I run it I get error saying " conversion failed when converting from character string to uniqueidentifier"
Thanks!

Sub Auto_open()

   'Private Function GetPersonID$(ByVal person_id As String)

  Dim person_id As String
   'perosn_id = ("a.person_id")
   'perosn_id = ("unique identifier")


  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
   "ODBC;DSN=ngdevl;Description=NGDevl;UID=sa;:APP=Microsoft Office 2010;;DATABASE=NGDevl" _
      , Destination:=Range("$A$30")).QueryTable

     .CommandText = Array( _
     " declare @person_id  varchar(36) select user_name, password,a.Create_timestamp, security_answer,b.last_name,b.first_name from ngweb_bulk_enrollments  a " _
     , _
     "INNER JOIN person  b ON b.person_id = a.person_id  where b.person_id = '" & person_id & "'")

      .RowNumbers = False
      .FillAdjacentFormulas = False
     .PreserveFormatting = True
       .RefreshOnFileOpen = False11
       .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
       .SavePassword = False
        .SaveData = True
       .AdjustColumnWidth = True
      .RefreshPeriod = 0
       .PreserveColumnInfo = True
       .ListObject.DisplayName = "Table_Query1"
        .Refresh BackgroundQuery:=True
    End With

[westconn1]

I just need a way to figure out how to assign person_id I get from application

which application are you talking about?

your original code seemed to make more sense than the last posts

[Pasi12]

This a medical record application.

[Bob Phillips]

You are still not making any sense. You say that you are trying to get the person_Id from the database, yet the SQL you build looks like this

declare @person_id varchar(36) select user_name, password,a.Create_timestamp, security_answer,b.last_name,b.first_name from ngweb_bulk_enrollments a INNER JOIN person b ON b.person_id = a.person_id where b.person_id = ''

which is not trying to get the person_id, but is getting a lot of details based upon a given person_id. But seeing as you are not passing a value for person_id, it is blank, so you are looking up a value that I sincerely doubt exists on your database.

I don't think we are capable of bridging the understanding gap bbetween yourself and ourselves.

[Pasi12]

OK. I don't know how to explain this better? if you guys can modify my code they way you guys( the masters) can please go ahead and do it and I will try it to see if it work. The Bulk_enrollment table is where patients are registered through application and holds the user_name.password, person_id , create_timestamps.. Now I like to pass on the person_id that's is currently logged into which we are trying to enroll him/her ( the patient) and pass that into VBA. Is this doable? here is another way I did it and I still don't get the person and it comes out blank. I am not sure how to pass the person_id to VBA?? I have added @user_id which is the person like me logged into application trying to register the patient.

Thanks.

Sub Auto_open()


'Dim person_id As String
perosn_id = ("unique identifier")


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=ngdevl;Description=NGDevl;UID=sa;;APP=Microsoft.ACE.OLEDB.12. 0 ;;DATABASE=NGDevl" _
, Destination:=Range("$A$30")).QueryTable
.CommandText = Array(" declare @person_id varchar(36),@user_id int select user_name, password,a.Create_timestamp, security_answer,b.last_name,b.first_name from ngweb_bulk_enrollments a" _
, _
" INNER JOIN person b ON b.person_id = a.person_id where b.person_id = @person_id and a.created_by=@user_id ")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False11
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True
End With