PDA

View Full Version : Need help with VBA passing an argument



Pasi12
07-17-2014, 02:31 PM
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
07-17-2014, 02:56 PM
11973

westconn1
07-18-2014, 05:11 AM
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
07-18-2014, 05:28 AM
If person_id is a Double that means your have a numeric key, therefor you should not be enclosing it in single quotes.

Pasi12
07-18-2014, 08:17 AM
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
07-18-2014, 08:19 AM
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
07-18-2014, 01:54 PM
Dim person_id As String

Pasi12
07-18-2014, 03:02 PM
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
07-18-2014, 03:23 PM
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
07-18-2014, 03:38 PM
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
07-19-2014, 12:58 AM
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 applicationi 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
07-19-2014, 06:00 AM
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
07-19-2014, 05:38 PM
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
07-21-2014, 06:18 AM
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
07-21-2014, 09:10 AM
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
07-21-2014, 11:46 AM
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
07-21-2014, 02:30 PM
I just need a way to figure out how to assign person_id I get from applicationwhich application are you talking about?

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

Pasi12
07-21-2014, 02:45 PM
This a medical record application.

Bob Phillips
07-22-2014, 01:12 AM
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
07-22-2014, 08:05 AM
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

Bob Phillips
07-22-2014, 08:51 AM
It's very hard to amend you code when we have no ide where the variables come from. You say logged in user which you can get from Environ("Username"), but is that @person_id or @user_id (I suspect the latter)? You will still need to pass the other.

Pasi12
07-22-2014, 09:47 AM
I may have to remove user_id . All I care about is person_id. Person_id is in "perons" table, in ngweb_bulk_nerollment table. Please see attached . I tried to be clear as possible. Thanks for your help.!



It's very hard to amend you code when we have no ide where the variables come from. You say logged in user which you can get from Environ("Username"), but is that @person_id or @user_id (I suspect the latter)? You will still need to pass the other.

Bob Phillips
07-23-2014, 04:01 AM
I'll have another go. If you want the id, and you know the first and last name you will need something like


.CommandText = "SELECT person_id FROM person WHERE last_name = '" & Last_name & "' AND first_name = '" & first_name & "';"

and you will have to declare string variables last_name and first_namde, and assign values to them. For instance


Sub Auto_open()
Dim last_name As String
Dim first_name As String


last_name = "test"
first_name = "test"

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 = "SELECT person_id FROM person WHERE last_name = '" & last_name & "' AND first_name = '" & first_name & "';"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True
End With
End Sub


I don't think I can be any clearer than that.

Pasi12
07-23-2014, 08:19 AM
Thanks xld. this gives me only all person_ids for all patients with their name test, test. I want to be able to get person id dynamically every time I am logged into different patient. The example I gave you was only for one patient called test , test and I have about 10-15 of them, the only thing is how do I get the person Id dynamically each time I am logged into different patient and get that patient. its a good start for me . Thanks !
Pasi.


I'll have another go. If you want the id, and you know the first and last name you will need something like


.CommandText = "SELECT person_id FROM person WHERE last_name = '" & Last_name & "' AND first_name = '" & first_name & "';"

and you will have to declare string variables last_name and first_namde, and assign values to them. For instance


Sub Auto_open()
Dim last_name As String
Dim first_name As String


last_name = "test"
first_name = "test"

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 = "SELECT person_id FROM person WHERE last_name = '" & last_name & "' AND first_name = '" & first_name & "';"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True
End With
End Sub


I don't think I can be any clearer than that.

Bob Phillips
07-23-2014, 02:25 PM
Give me an example of the logged-in person, and that person's equivalent name, first and last, in the database.

Pasi12
07-23-2014, 02:51 PM
xld, can you use the same attachment I gave before? would that work? it has all the info.

Pasi12
07-23-2014, 03:08 PM
here is an example.

Bob Phillips
07-23-2014, 04:00 PM
If you can't tell us how to map some value that you have onto the database table, there is no chance we will ever solve this for you.

Pasi12
07-23-2014, 04:16 PM
xld, I am not sure what your asking? I gave you a sample in 2 attachments one yesterday and one today. what else you need? below is the sql query. Please let me know what else you need?
Thanks.

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

westconn1
07-24-2014, 04:05 AM
to get the logged in user from the other application looks like it would require the use of findwindow, findwindowex and sendmessage API functions
no one can really do this for you, as it would be specific to the application running on your machine

you will need to research on those API calls to find how to make them work for you
you could probably get more information, about the client windows, using spy++ or similar utilities

until you find a way to get person_id from other application, your sql can not work
a possibility, until you can workout something else, would be to get userinput

person_id = inputbox("paste or type person_id from application")
you could change this when you get some more sophisticated method, but at least it might get you started

Bob Phillips
07-24-2014, 07:14 AM
No, he can just use Environ("Username"). But I don't think he wants/needs user id, he seems to want the person_id meeting some criteria, so he needs some other user criteria, but what that might be seems impossible to ascertain as the understanding gap seems unbridgeable.

Pasi12
07-24-2014, 08:06 AM
Thank you both for your insights! I understand your points. All I want to grab the person which I am currently logged into and grab that person_id which is in "person" table and is the same id in bulk_enrollments table, once I grab that, then I can pull their names and paste it into excel.

xld, you right I don't want to use the Environ("Username"). this gives me the Windows log on not SQL log on that my application is tied into and used the SQL dB.

Westconn, you may be right, I may need an API for this but I thought Excel would be smart/easy enough to get this for me but I think it requires more research and may be I can't do it in exel vba?

Pasi.


No, he can just use Environ("Username"). But I don't think he wants/needs user id, he seems to want the person_id meeting some criteria, so he needs some other user criteria, but what that might be seems impossible to ascertain as the understanding gap seems unbridgeable.

Pasi12
07-24-2014, 08:40 AM
Westconn, one thing how do I prompt for both first name and last name? I think I am getting some where! is there a way to ask for first name last name in one input box not 2 input box? I managed to prompt user for their first and last name and it is working now!!! yeyyyyy! how can I put in a msg saying" the person was not enrolled in db" if the first and last name don't match.?
here is the code:

Sub Auto_open()
Dim last_name As String
Dim first_name As String
Dim person_id As String
' last_name = "test"
'first_name = "test"
' person_id = "@person_id"
last_name = InputBox("Patient last Name")
first_name = InputBox("Patient first Name")
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 status = '1' and b.last_name = '" & last_name & "' AND first_name = '" & first_name & "'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True
End With
End Sub


Thanks.

westconn1
07-24-2014, 02:31 PM
may be I can't do it in exel vba?i would assume it can, but may be complex


how can I put in a msg saying" the person was not enrolled in db" if the first and last name don't match.?
if the record count is 0 then put a msgbox "no matching records found"
i did suggest pasting the name to prevent typing errors


how do I prompt for both first name and last name?you could use a small userform instead of an ugly inputbox, then can validate both names are filled

the logged in database user should be returnable, but it may be a better question to ask in an appropriate database forum

Bob Phillips
07-24-2014, 03:18 PM
Use one inputbox for the full name, then split it into its constituent parts, or even use


" from ngweb_bulk_enrollments a INNER JOIN person b ON b.person_id = a.person_id where status = '1' and (b.first_name + ' ' + b.last_name) = '" & inputbox_value & "'")

Pasi12
07-24-2014, 03:52 PM
thanks westconn.! Sorry don't mean to be a pain in a B... ! but how do I word/set it up within the code for msg box "no matching record found"? I am a newby with VBA and haven't done any error checking before.:blush:think:...:banghead: here is how I did it but not sure how/where to put the "Else" after if? if the last_name and first name don't match then msg , else proceed to rest of the code.


Sub Auto_open()
Dim last_name As String
Dim first_name As String
Dim person_id As String

' person_id = "@person_id"
last_name = InputBox("Patient last Name")
first_name = InputBox("Patient first Name")
If last_name <> " & last_name & " Or first_name <> " & first_name & " Then MsgBox ("Patient not enrolled yet")


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 status ='1' and b.last_name = '" & last_name & "' AND b.first_name = '" & first_name & "'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True
End With



' Prints the current active workbook in Excel
' ActiveWorkbook.PrintOut
End Sub





i would assume it can, but may be complex


if the record count is 0 then put a msgbox "no matching records found"
i did suggest pasting the name to prevent typing errors

you could use a small userform instead of an ugly inputbox, then can validate both names are filled

the logged in database user should be returnable, but it may be a better question to ask in an appropriate database forum

Bob Phillips
07-25-2014, 02:55 AM
You have 68 posts, you should have learned how to add VBA tags around your code by now.

Pasi12
07-25-2014, 07:59 AM
Unfortunately I have not used any tags and not sure how to use them? I just reply to posts...


You have 68 posts, you should have learned how to add VBA tags around your code by now.

Pasi12
07-25-2014, 08:05 AM
hope this works?.. tried tagging.


Unfortunately I have not used any tags and not sure how to use them? I just reply to posts...

Sub Auto_open()
Dim last_name As String
Dim first_name As String
Dim person_id As String

' person_id = "@person_id"
last_name = InputBox("Patient last Name")
first_name = InputBox("Patient first Name")
If last_name <> " & last_name & " Or first_name <> " & first_name & " Then MsgBox ("Patient not enrolled yet")


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 status ='1' and b.last_name = '" & last_name & "' AND b.first_name = '" & first_name & "'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True
End With



' Prints the current active workbook in Excel
' ActiveWorkbook.PrintOut
End Sub

Bob Phillips
07-25-2014, 12:33 PM
Nope, that is quotes not code tags.

Pasi12
07-25-2014, 01:41 PM
:dunno:crying:

Pasi12
07-28-2014, 11:03 AM
Can you guys help me with msg box I have set it up to prompt for Blank as well as if last name and fist name don't match/or does not exist in table prompt me but even though the patient already in dB/table and enrolled I still get the prompt for patient not enrolled , what am I doing wrong?? Thanks!! below is the code.. I am using "userform"

I am using tag hope it works...


:dunno:crying:



Private Sub CmdOK_click_Click()
Dim RowCount As Long
Dim Last_name As String
Dim First_name As String

If Me.First_name.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "Enrollment"
Me.First_name.SetFocus
Exit Sub
End If
If Me.First_name.Value <> "& first_name &" Then
MsgBox "Patient not enrolled yet.", vbExclamation, "Enrollment"
Me.First_name.SetFocus
Exit Sub
End If

If Me.Last_name.Value = "" Then
MsgBox "Please enter a Last Name.", vbExclamation, "Enrollment"
Me.Last_name.SetFocus
Exit Sub
End If
If Me.Last_name.Value <> "&Last_name &" Then
MsgBox "Patient not enrolled yet.", vbExclamation, "Enrollment"
Me.Last_name.SetFocus
SendKeys "{Enter}", True ' press the OK button instead of clicking "OK"
Exit Sub
End If
RowCount = Worksheets("Sheet1").Range("$A$30").CurrentRegion.Rows.Count
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,b.date_of_birth as DOB" _
, _
" from ngweb_bulk_enrollments a INNER JOIN person b ON b.person_id = a.person_id where b.last_name = '" & Last_name & "' AND b.first_name = '" & First_name & "'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True

End With

Unload Me
End Sub

westconn1
07-29-2014, 02:59 AM
If Me.First_name.Value <> "& first_name &" Then
MsgBox "Patient not enrolled yet.", vbExclamation, "Enrollment"
Me.First_name.SetFocus
Exit Sub
End Ifthis makes no sense to me as first_name at this point is an empty string
so if me.first_name not = "" then msgbox, i think your logic is reverse

same for lastname, why would you compare to empty variable?
have you omitted some code from your procedure?

Pasi12
07-29-2014, 08:02 AM
Thanks Westconn... That's where I have the problem I don't know where/how to correct this? I thought this would be how you set it up? Can you correct this for me? I have ran out of ideas! I may have to remove the "if me.first_name not = "" then msgbox" as well, but what is the correct syntax/code to prompt the user if the first name/last name does not exist , then prompt msg ?

Thanks!
Pasi.



this makes no sense to me as first_name at this point is an empty string
so if me.first_name not = "" then msgbox, i think your logic is reverse

same for lastname, why would you compare to empty variable?
have you omitted some code from your procedure?

westconn1
07-29-2014, 02:01 PM
If Me.First_name.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "Enrollment"
Me.First_name.SetFocus
Exit Subthis appears to be what you want, for each textbox, assuming this is for a textbox on userform

the second part (as above) is basically the inverse so it can never proceed, also you can not know at this point if the patient is not enrolled, that would be determined by the query to the database

as you never assign the values from the textboxes to the variables any names entered are not passed to your SQL
the variables are not really required, as you can just replace the variables in the SQL with the textbox values

you should avoid using variables with the same names as controls as it is possible to cause a conflict in code

Pasi12
07-29-2014, 02:42 PM
Wesconn!. Thanks for the tips. Here is how I set it up but not working? the textboxes called "firstName" and "lastName" and the prompt msg before the "With" statement but its not working? could you pls take a look and correct this for me? I replaced the variables in sql with the textboxs. I really appreciate it! sorry a bit lost!.




Private Sub CmdOK_click_Click()


If Me.LastName <> LastName Then
MsgBox "Patient not enrolled yet.", vbExclamation, "Enrollment"
End If

'rowcount = worksheets("Sheet1").Range("$A$30").CurrentRegion.Rows.Count
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 AS PWD,a.Create_timestamp, security_answer,b.last_name,b.first_name,convert(char(10), CAST(CAST(b.date_of_birth AS VARCHAR(10)) AS DATE), 101) As DOB " _
, _
" from ngweb_bulk_enrollments a INNER JOIN person b ON b.person_id = a.person_id where b.last_name = '" & Me.LastName & "' And b.first_name = '" & Me.FirstName & "' ")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query1"
.Refresh BackgroundQuery:=True

End With


Unload Me

End Sub



this appears to be what you want, for each textbox, assuming this is for a textbox on userform

the second part (as above) is basically the inverse so it can never proceed, also you can not know at this point if the patient is not enrolled, that would be determined by the query to the database

as you never assign the values from the textboxes to the variables any names entered are not passed to your SQL
the variables are not really required, as you can just replace the variables in the SQL with the textbox values

you should avoid using variables with the same names as controls as it is possible to cause a conflict in code

westconn1
07-30-2014, 02:40 AM
If Me.LastName <> LastName Thenyou can not validate if lastname (or firstname) is an enrolled patient until you query the database

all you can do is validate that some text is entered into the textbox
this should work fine


If Me.First_name.Value = "" Then
' or better
if len(trim(me.first_name)) > 0 then ' check for string additional to spaces
MsgBox "Please enter a First Name.", vbExclamation, "Enrollment"
Me.First_name.SetFocus
Exit Subrepeat for last name

to check either first or last name are valid, query the database with a count query on the textbox value

Pasi12
07-30-2014, 09:32 AM
Thanks you sir!


you can not validate if lastname (or firstname) is an enrolled patient until you query the database

all you can do is validate that some text is entered into the textbox
this should work fine


If Me.First_name.Value = "" Then
' or better
if len(trim(me.first_name)) > 0 then ' check for string additional to spaces
MsgBox "Please enter a First Name.", vbExclamation, "Enrollment"
Me.First_name.SetFocus
Exit Subrepeat for last name

to check either first or last name are valid, query the database with a count query on the textbox value