SQL instead of RS how? 2

[vttech]

Normally would use recordset to insert information into a table in MS ACCESS

Example:

rs.addnew
rs("PatientID") = Forms!frmPatientDemographics!PatientID
rs.update

How would I do that using SQL? would this example work? If not please show me how to perform this task..

INSERT INTO tblBloodworkResults (PatientID)
VALUES (Forms!frmPatientDemographics!PatientID)

I assume that I would still have to connect to the database using something like ADODB

Newbie in search of knowledge

 

[Zygor]

Go to Queries - New
Choose Design View and select the table you need to get the info from.
Go to Query - append query.

Make the query and then go to view SQL view.

It will look something like this.

INSERT INTO Table2 SELECT Table1.* FROM Table1;

 

[Remou]

Perhaps:
[tt]strSQL="INSERT INTO tblBloodworkResults (PatientID) " _
& "VALUES ( " & Forms!frmPatientDemographics!PatientID & ")"
CurrentDB.Execute strSQL[/tt]

DAO is the usual for Access and Jet.

 

[snyperx3]

you wouldnt have to connect. you could use

DoCmd.RunSQL "INSERT INTO tblBloodworkResults (PatientID) VALUES (" & Forms!frmPatientDemographics!PatientID & ");"

-Pete

 

[vttech]

Pete,

Your example worked but when I tried the full code

DoCmd.RunSQL "INSERT INTO tblBloodworkResults (PatientID,HepBAntibodySur,HepBAntigenSur,HepBCoreAntibody,HepCAntibody,"
TSH,HemaWithDiff,HIVAntibody,RPRSyphSero,HemoElectro,VaricAntibodyTit,MMRTiter)
VALUES (" & Forms!frmPatientDemographics!PatientID, Forms!frmBloodWorkResults!cboHepBAntibodySur,
Forms!frmBloodWorkResults!cboHepBAntigenSur,Forms!frmBloodWorkResults!cboHepBCoreAntibody,
Forms!frmBloodWorkResults!cboHepCAntibody,Forms!frmBloodWorkResults!cboTSH,
Forms!frmBloodWorkResults!cboHemaWithDiff,Forms!frmBloodWorkResults!cboHIVAntibody,
Forms!frmBloodWorkResults!cboRPRSyphSero,Forms!frmBloodWorkResults!cboHemoElectro,
Forms!frmBloodWorkResults!cboVaricAntibodyTit,Forms!frmBloodWorkResults!cboMMRTiter & ");"

I get a syntax error.. I tried putting and underscore "_" but no luck..What is the error in the code and how do you
span a sql statement that has multiple lines

Newbie in search of knowledge

 

[Remou]

Did you notice my post? It shows how to span.

 

[snyperx3]

vttech,

you will have to seperate the bottom values with & "," & instead of just the ,

example

VALUES (" & Forms!frmPatientDemographics!PatientID & "," & Forms!frmBloodWorkResults!cboHepBAntibodySur

-Pete

 

[Golom]

... and, if the values you are inserting are text then you need to enclose them in single quotes. Similarly dates need to be enclosed in #...# markers.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[vttech]

I did the changes but know I get the following error

Compile error: Expected: end of statement

and it brings be to the first ","

located at

& Forms!frmPatientDemographics!PatientID& ","

the code know looks like

DoCmd.RunSQL "INSERT INTO tblBloodworkResults (PatientID,HepBAntibodySur,HepBAntigenSur,HepBCoreAntibody,HepCAntibody," _
& "TSH,HemaWithDiff,HIVAntibody,RPRSyphSero,HemoElectro,VaricAntibodyTit,MMRTiter)" _
& "VALUES (" _
& Forms!frmPatientDemographics!PatientID& "," &Forms!frmBloodWorkResults!cboHepBAntibodySur& "," _
& Forms!frmBloodWorkResults!cboHepBAntigenSur& "," & Forms!frmBloodWorkResults!cboHepBCoreAntibody& "," _
& Forms!frmBloodWorkResults!cboHepCAntibody& "," & Forms!frmBloodWorkResults!cboTSH& "," _
& Forms!frmBloodWorkResults!cboHemaWithDiff& "," & Forms!frmBloodWorkResults!cboHIVAntibody& "," _
& Forms!frmBloodWorkResults!cboRPRSyphSero& "," & Forms!frmBloodWorkResults!cboHemoElectro& "," _
& Forms!frmBloodWorkResults!cboVaricAntibodyTit& "," & Forms!frmBloodWorkResults!cboMMRTiter & ");"

Newbie in search of knowledge

 

[snyperx3]

you need spaces before and after the concatenation (&)

Forms!frmPatientDemographics!PatientID& "," &Forms!frmBloodWorkResults!cboHepBAntibodySur& ","

should be

Forms!frmPatientDemographics!PatientID & "," & Forms!frmBloodWorkResults!cboHepBAntibodySur & ","

-Pete

 

[vttech]

I change the SQL statement to

Dim strSQL As String


strSQL = "INSERT INTO tblBloodworkResults (PatientID,HepBAntibodySur,HepBAntigenSur,HepBCoreAntibody,HepCAntibody," _
& "TSH,HemaWithDiff,HIVAntibody,RPRSyphSero,HemoElectro,VaricAntibodyTit,MMRTiter)" _
& " VALUES (" _
& Forms!frmPatientDemographics!PatientID & "," & Me!cboHepBAntibodySur & "," _
& Me!cboHepBAntigenSur & "," & Me!cboHepBCoreAntibody & "," & Me!cboHepCAntibody & "," _
& Me!cboTSH & "," & Me!cboHemaWithDiff & "," & Me!cboHIVAntibody & "," _
& Me!cboRPRSyphSero & "," & Me!cboHemoElectro & "," _
& Me!cboVaricAntibodyTit & "," & Me!cboMMRTiter & ");"

Debug.Print strSQL

CurrentProject.Connection.Execute strSQL

and know I get this message

No Value given for one or more required parameters

you will notice that place the line below in the code

Debug.Print strSQL

when I look at the immediate window I get

INSERT INTO tblBloodworkResults (PatientID,HepBAntibodySur,HepBAntigenSur,HepBCoreAntibody,HepCAntibody,TSH,HemaWithDiff,HIVAntibody,RPRSyphSero,HemoElectro,VaricAntibodyTit,MMRTiter) VALUES (9,Negative,Negative,Negative,Negative,Negative,Negative,Negative,Negative,Negative,Negative,Negative);

when I look in the table tblBloodworkResults

I see

0 Negative Negative Negative Negative Negative Negative	Negative Negative Negative Negative Negative

some how the PatientID is not going into the tblBloodworkResults from the SQL statement. Any Ideas??


Newbie in search of knowledge

 

[Zion7]

is PatientID an AutoNumber?

 

[vttech]

no it's a number field

Newbie in search of knowledge

 

[vttech]

Funny thing if I type in number like 1,2,3,4, etc.. it works fine

I am wondering since it's string value begin passed is their a special simple that needs to surround the controls in my SQL statements?
example if it was dates I would have to have # #

Newbie in search of knowledge

 

[JerryKlmns]

strSQL = "INSERT INTO tblBloodworkResults (PatientID, HepBAntibodySur, HepBAntigenSur, HepBCoreAntibody, HepCAntibody, TSH, HemaWithDiff, HIVAntibody, RPRSyphSero, HemoElectro, VaricAntibodyTit, MMRTiter) " & _
"VALUES (" & Forms!frmPatientDemographics!PatientID & ", '" & Me!cboHepBAntibodySur & "', '" & Me!cboHepBAntigenSur & "', '" & Me!cboHepBCoreAntibody & "', '" & Me!cboHepCAntibody & "', '" & Me!cboTSH & "', '" & Me!cboHemaWithDiff & "', '" & Me!cboHIVAntibody & "', '" & Me!cboRPRSyphSero & "', '" & Me!cboHemoElectro & "', '" & Me!cboVaricAntibodyTit & "', '" & Me!cboMMRTiter & "');"

String values should be enclosed with '

 

[NvrSaDi]

The debug.strsql command is very helpful.
I like to take the results, open a new query and paste the results into the SQL view of the new query. This often provides additional visual clues, error messages and insights.