Insert query quit working

[Dave44000]

I have a query that worked months ago, but after adding additional fields to the target table, the query no longer runs. It took values from text controls on a form. It no longer finds them, so it displays parameter messages instead.

I shortened it up just to test it and even with only one field it doesn't work.

The Patient ID, aka the Me.txt62 field is never null. I have tried setting the focus, but that didn't help.

Short form:

DoCmd.RunSQL "INSERT INTO PatientReferralEditsTable (PatientID) VALUES (Me.txt62)"

Originally, it was in the form:

DoCmd.RunSQL "INSERT INTO PatientReferralEditsTable (PatientID, PhysicianID, PhysicianOfficeID, InsuranceCarrierID, InsuranceCarrierContractID, " & _
"ICD9Code1ID, ICD9Code2ID, ICD9Code3ID, ServiceDiscipline1ID, ServiceDiscipline2ID, ServiceDiscipline3ID, " & _
"PhysicianPhysicianOfficeID, PatientInsuranceCarrierID, PatientReferralID, PatientPhysicianID, PatientPhysicianOfficeID, " & _
"PRICD9Code1ID, PRICD9Code2ID, PRICD9Code3ID,  " & _
"PRServiceDiscipline1ID,PRServiceDiscipline2ID,PRServiceDiscipline3ID) " & _
 
" VALUES (Me.txt62, Me.txt63, Me.Txt64, Me.Txt65,  Me.Txt66), " & _
"Me.Txt67, Me.Txt68, Me.Txt69, Me.Txt106, Me.Txt107, Me.Txt108," & _
"Me.Txt70, Me.Txt71, Me.Txt72, Me.Txt73,  Me.Txt74, " & _
"Me.Txt75, Me.Txt76, Me.Txt77, Me.Txt136, Me.Txt137, Me.Txt138)" 

David

[Rabbit]

This

DoCmd.RunSQL "INSERT INTO PatientReferralEditsTable (PatientID) VALUES (Me.txt62)" 

Needs to be this

DoCmd.RunSQL "INSERT INTO PatientReferralEditsTable (PatientID) VALUES ('" & Me.txt62 & "')" 

Assuming PatientID is a string, if it's a number then leave out the single quotes.

The SQL engine has no idea what Me.txt62 is referring to. You need to append the value, not the reference.

The original form should not have worked either. If that's how the code truly looked, there's no way it should have run without error.

[NeoPa]

How to Debug SQL String will give you the tools and experience to understand what Rabbit's saying more easily.

He's perfectly correct, of course. There must be some misunderstanding as the code you've posted could not possibly work as shown.

When you've played with it a bit you'll appreciate why that is and why working with SQL in VBA can be confusing for so many.