run append query from form

N

newuser44

I have a table to which I need to append data on a monthly basis from an
excel table that I import manually into Access. What I would like to do is
select the table on a form, click a button, and have only new records
appended to the main data table. I pretty much have all of this figured out,
but I can't seem to combine setting the table name in VBA, so that the SQL
insert query runs on the most current table I selected on the form.

Here is the VBA code I have been using:

Private Sub Command1_Click()

Dim strSQL As String
Dim tbl_new As String

tbl_new = Me![Combo2]

strSQL = "INSERT INTO Table_Patient ( DOB, PATIENT_SEX, PATIENT_FIRST_NAME,
PATIENT_LAST_NAME, PATIENT_CITY, PATIENT_STATE_2, MRN ) " & _
"SELECT DISTINCT
DateSerial(Right([PATIENT_DATE_OF_BIRTH],4),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],1,2),Left([PATIENT_DATE_OF_BIRTH],1)),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],3,2),Mid([PATIENT_DATE_OF_BIRTH],2,2)))
AS DOB, '" & tbl_new & "'.PATIENT_SEX, '" & tbl_new & "'.PATIENT_FIRST_NAME,
'" & tbl_new & "'.PATIENT_LAST_NAME, '" & tbl_new & "'.PATIENT_CITY, '" &
tbl_new & "'.PATIENT_STATE_2, '" & tbl_new & "'.MRN " & _
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

CurrentDb.Execute strSQL
End Sub



Here is the error I get:

run time error 3450
syntax error in query. incomplete query clause.


Can anybody help with this?

thanks!
 
K

Klatuu

I did not scour the entire statement, but I do see one obvious error. As
written, you are putting qoutes around the name of the table in the SQL
statement. That is not correct. You only put qoutes around values you want
to insert into a text field.
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

Should be:
"FROM " & tbl_new & _
" LEFT JOIN Table_Patient ON [" & tbl_new & "].MRN = Table_Patient.MRN " & _
"WHERE (((" & tbl_new & ".PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"
 
N

newuser44

This helped for sure... but now I get an syntax error in the FROM clause:

any thoughts?

Private Sub Command1_Click()

Dim strSQL As String
Dim tbl_new As String

tbl_new = Me![Combo2]

strSQL = "INSERT INTO Table_Patient ( DOB, PATIENT_SEX, PATIENT_FIRST_NAME,
PATIENT_LAST_NAME, PATIENT_CITY, PATIENT_STATE_2, MRN ) " & _
"SELECT DISTINCT
DateSerial(Right([PATIENT_DATE_OF_BIRTH],4),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],1,2),Left([PATIENT_DATE_OF_BIRTH],1)),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],3,2),Mid([PATIENT_DATE_OF_BIRTH],2,2)))
AS DOB, [" & tbl_new & "].PATIENT_SEX, [" & tbl_new & "].PATIENT_FIRST_NAME,
[" & tbl_new & "].PATIENT_LAST_NAME, [" & tbl_new & "].PATIENT_CITY, [" &
tbl_new & "].PATIENT_STATE_2, [" & tbl_new & "].MRN " & _
"FROM " & tbl_new & " " & _
"LEFT JOIN Table_Patient" & _
"ON [" & tbl_new & "].MRN = Table_Patient.MRN " & _
"WHERE ((([" & tbl_new & "].PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

CurrentDb.Execute strSQL



End Sub



Klatuu said:
I did not scour the entire statement, but I do see one obvious error. As
written, you are putting qoutes around the name of the table in the SQL
statement. That is not correct. You only put qoutes around values you want
to insert into a text field.
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

Should be:
"FROM " & tbl_new & _
" LEFT JOIN Table_Patient ON [" & tbl_new & "].MRN = Table_Patient.MRN " & _
"WHERE (((" & tbl_new & ".PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"


--
Dave Hargis, Microsoft Access MVP


newuser44 said:
I have a table to which I need to append data on a monthly basis from an
excel table that I import manually into Access. What I would like to do is
select the table on a form, click a button, and have only new records
appended to the main data table. I pretty much have all of this figured out,
but I can't seem to combine setting the table name in VBA, so that the SQL
insert query runs on the most current table I selected on the form.

Here is the VBA code I have been using:

Private Sub Command1_Click()

Dim strSQL As String
Dim tbl_new As String

tbl_new = Me![Combo2]

strSQL = "INSERT INTO Table_Patient ( DOB, PATIENT_SEX, PATIENT_FIRST_NAME,
PATIENT_LAST_NAME, PATIENT_CITY, PATIENT_STATE_2, MRN ) " & _
"SELECT DISTINCT
DateSerial(Right([PATIENT_DATE_OF_BIRTH],4),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],1,2),Left([PATIENT_DATE_OF_BIRTH],1)),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],3,2),Mid([PATIENT_DATE_OF_BIRTH],2,2)))
AS DOB, '" & tbl_new & "'.PATIENT_SEX, '" & tbl_new & "'.PATIENT_FIRST_NAME,
'" & tbl_new & "'.PATIENT_LAST_NAME, '" & tbl_new & "'.PATIENT_CITY, '" &
tbl_new & "'.PATIENT_STATE_2, '" & tbl_new & "'.MRN " & _
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

CurrentDb.Execute strSQL
End Sub



Here is the error I get:

run time error 3450
syntax error in query. incomplete query clause.


Can anybody help with this?

thanks!
Click to expand...
Click to expand...
 
K

Klatuu

Run your code in debug mode with a breakpoint on the Excecute line.
Then look at th strSQL variable to see what it looks like.
--
Dave Hargis, Microsoft Access MVP


newuser44 said:
This helped for sure... but now I get an syntax error in the FROM clause:

any thoughts?

Private Sub Command1_Click()

Dim strSQL As String
Dim tbl_new As String

tbl_new = Me![Combo2]

strSQL = "INSERT INTO Table_Patient ( DOB, PATIENT_SEX, PATIENT_FIRST_NAME,
PATIENT_LAST_NAME, PATIENT_CITY, PATIENT_STATE_2, MRN ) " & _
"SELECT DISTINCT
DateSerial(Right([PATIENT_DATE_OF_BIRTH],4),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],1,2),Left([PATIENT_DATE_OF_BIRTH],1)),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],3,2),Mid([PATIENT_DATE_OF_BIRTH],2,2)))
AS DOB, [" & tbl_new & "].PATIENT_SEX, [" & tbl_new & "].PATIENT_FIRST_NAME,
[" & tbl_new & "].PATIENT_LAST_NAME, [" & tbl_new & "].PATIENT_CITY, [" &
tbl_new & "].PATIENT_STATE_2, [" & tbl_new & "].MRN " & _
"FROM " & tbl_new & " " & _
"LEFT JOIN Table_Patient" & _
"ON [" & tbl_new & "].MRN = Table_Patient.MRN " & _
"WHERE ((([" & tbl_new & "].PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

CurrentDb.Execute strSQL



End Sub



Klatuu said:
I did not scour the entire statement, but I do see one obvious error. As
written, you are putting qoutes around the name of the table in the SQL
statement. That is not correct. You only put qoutes around values you want
to insert into a text field.
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

Should be:
"FROM " & tbl_new & _
" LEFT JOIN Table_Patient ON [" & tbl_new & "].MRN = Table_Patient.MRN " & _
"WHERE (((" & tbl_new & ".PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"


--
Dave Hargis, Microsoft Access MVP


newuser44 said:
I have a table to which I need to append data on a monthly basis from an
excel table that I import manually into Access. What I would like to do is
select the table on a form, click a button, and have only new records
appended to the main data table. I pretty much have all of this figured out,
but I can't seem to combine setting the table name in VBA, so that the SQL
insert query runs on the most current table I selected on the form.

Here is the VBA code I have been using:

Private Sub Command1_Click()

Dim strSQL As String
Dim tbl_new As String

tbl_new = Me![Combo2]

strSQL = "INSERT INTO Table_Patient ( DOB, PATIENT_SEX, PATIENT_FIRST_NAME,
PATIENT_LAST_NAME, PATIENT_CITY, PATIENT_STATE_2, MRN ) " & _
"SELECT DISTINCT
DateSerial(Right([PATIENT_DATE_OF_BIRTH],4),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],1,2),Left([PATIENT_DATE_OF_BIRTH],1)),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],3,2),Mid([PATIENT_DATE_OF_BIRTH],2,2)))
AS DOB, '" & tbl_new & "'.PATIENT_SEX, '" & tbl_new & "'.PATIENT_FIRST_NAME,
'" & tbl_new & "'.PATIENT_LAST_NAME, '" & tbl_new & "'.PATIENT_CITY, '" &
tbl_new & "'.PATIENT_STATE_2, '" & tbl_new & "'.MRN " & _
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

CurrentDb.Execute strSQL
End Sub



Here is the error I get:

run time error 3450
syntax error in query. incomplete query clause.


Can anybody help with this?

thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using filter results to append records 2
"no records" message for append query 2
VBA code issue...Would love expert help. 5
Run Append Query from ControlButton on Form 3
Duplicate Records from Append Query 8
Append query with no results 6
Append Query Re-run 3
Clear form after append query 1

Top