Append parameter query via code

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

I would like to create a command button that, when
clicked, would:

1) PROMPT the user to enter some parameters (a start and
end date) and
2) APPEND the PatientID of records in tblPatients which
meet the parameter criteria TO the PatientID in
tblStudyEnrollment.

But because tblStudyEnrollment requires both PatientID
and StudyID, the StudyID for all of the patients being
appended needs to be updated/inserted as well (with the
value of StudyID currently listed in the form's control
[i.e., forms!frmStudies!StudyID]).

Below is the code I've come up so far. Two problems:

1) The code doesn't address the StudyID value, because I
didn't know how to call that value from the form and
insert it into the append process.
2) The current code gives this error: "Too few parameters
expected. Expected 2."

Dim db As DAO.Database
Set db = CurrentDb

db.Execute "INSERT INTO tblStudyEnrollment (
PatientID ) " & _
"SELECT tblPatients.PatientID " & _
"FROM tblPatients " & _
"WHERE (((tblPatients.DOB) Between [StartDate] And
[EndDate]));", dbFailOnError

###

Any ideas? Thanks.

Kurt
 
Kurt said:
I would like to create a command button that, when
clicked, would:

1) PROMPT the user to enter some parameters (a start and
end date) and
2) APPEND the PatientID of records in tblPatients which
meet the parameter criteria TO the PatientID in
tblStudyEnrollment.

But because tblStudyEnrollment requires both PatientID
and StudyID, the StudyID for all of the patients being
appended needs to be updated/inserted as well (with the
value of StudyID currently listed in the form's control
[i.e., forms!frmStudies!StudyID]).

Below is the code I've come up so far. Two problems:

1) The code doesn't address the StudyID value, because I
didn't know how to call that value from the form and
insert it into the append process.
2) The current code gives this error: "Too few parameters
expected. Expected 2."

Dim db As DAO.Database
Set db = CurrentDb

db.Execute "INSERT INTO tblStudyEnrollment (
PatientID ) " & _
"SELECT tblPatients.PatientID " & _
"FROM tblPatients " & _
"WHERE (((tblPatients.DOB) Between [StartDate] And
[EndDate]));", dbFailOnError
Click to expand...


Try something like this:

db.Execute "INSERT INTO tblStudyEnrollment " _
" (PatientID, StudyID) " & _
" SELECT PatientID, " & Me.txtNewStudyID _
" FROM tblPatients " & _
" WHERE DOB Between #" & Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#", dbFailOnError

where txtNewStudyID is the text box on the form where the
user enters the new study ID. Add a couple more text boxes
for the users to enter the start and end dates so you can
eliminate the pop-up prompts for the date range parameters
(which won't be handled automatically in the VBA
environment).
 
Try something like this:
db.Execute "INSERT INTO tblStudyEnrollment " _
" (PatientID, StudyID) " & _
" SELECT PatientID, " & Me.txtNewStudyID _
" FROM tblPatients " & _
" WHERE DOB Between #" & Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#", dbFailOnError
Click to expand...

I'm getting a syntax error "in query expression '6FROM
tblPatients WHERE DOB Between #01/01/2000# And
#01/01/2007#'. (The dates reflect what I entered in
txtStartDate and txtEndDate. And where is that '6' before
the 'FROM' coming from?)

Here's the code I had come up with, and I've tried
tweaking it to no avail:

db.Execute "INSERT INTO tblStudyEnrollment " & _
"(PatientID, StudyID) " & _
"SELECT PatientID, " & Me.StudyID & _
"FROM tblPatients " & _
"WHERE DOB Between #" & Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#", dbFailOnError

###

Thoughts? Thanks. - Kurt


-----Original Message-----
Kurt said:
I would like to create a command button that, when
clicked, would:

1) PROMPT the user to enter some parameters (a start and
end date) and
2) APPEND the PatientID of records in tblPatients which
meet the parameter criteria TO the PatientID in
tblStudyEnrollment.

But because tblStudyEnrollment requires both PatientID
and StudyID, the StudyID for all of the patients being
appended needs to be updated/inserted as well (with the
value of StudyID currently listed in the form's control
[i.e., forms!frmStudies!StudyID]).

Below is the code I've come up so far. Two problems:

1) The code doesn't address the StudyID value, because I
didn't know how to call that value from the form and
insert it into the append process.
2) The current code gives this error: "Too few parameters
expected. Expected 2."

Dim db As DAO.Database
Set db = CurrentDb

db.Execute "INSERT INTO tblStudyEnrollment (
PatientID ) " & _
"SELECT tblPatients.PatientID " & _
"FROM tblPatients " & _
"WHERE (((tblPatients.DOB) Between [StartDate] And
[EndDate]));", dbFailOnError
Click to expand...


Try something like this:

db.Execute "INSERT INTO tblStudyEnrollment " _
" (PatientID, StudyID) " & _
" SELECT PatientID, " & Me.txtNewStudyID _
" FROM tblPatients " & _
" WHERE DOB Between #" & Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#", dbFailOnError

where txtNewStudyID is the text box on the form where the
user enters the new study ID. Add a couple more text boxes
for the users to enter the start and end dates so you can
eliminate the pop-up prompts for the date range parameters
(which won't be handled automatically in the VBA
environment).
Click to expand...
 
Kurt said:
I'm getting a syntax error "in query expression '6FROM
tblPatients WHERE DOB Between #01/01/2000# And
#01/01/2007#'. (The dates reflect what I entered in
txtStartDate and txtEndDate. And where is that '6' before
the 'FROM' coming from?)

Here's the code I had come up with, and I've tried
tweaking it to no avail:

db.Execute "INSERT INTO tblStudyEnrollment " & _
"(PatientID, StudyID) " & _
"SELECT PatientID, " & Me.StudyID & _
"FROM tblPatients " & _
"WHERE DOB Between #" & Me.txtStartDate & _
"# And #" & Me.txtEndDate & "#", dbFailOnError
Click to expand...

You removed the space in front of the word FROM, the 6 is
the study ID you're trying to insert.
 
Back
Top