CODEWITHSUNDEEP
ms-accessinsertvbaprimary-key
Jason Steward
Jason Steward

Reputation: 39

Insert a value into a table while creating a record in another table

I'm out of my league on this... Another developer before me did something similar to what I want to do by adding a value in a table while updating another table. However, he was running updates as well as inserting, and his primary key was text. Mine PK is integer. Here's his code (works great) that I am trying to reverse engineer and apply to my situation:

Dim sqlQuery As String
sqlQuery = "IF EXISTS (SELECT ReportPK FROM 
ACIST_MobilePipelineReportReviewed WHERE ReportPK = '" & ReportPk & "') " & 
_
    " UPDATE ACIST_MobilePipelineReportReviewed set Status = 'Approved' 
WHERE ReportPK = '" & ReportPk & "'" & _
    " ELSE " & _
    " INSERT INTO ACIST_MobilePipelineReportReviewed ([ReportPK], 
   [PipelineUID],[ReportDate],[ReportInspector],[Status]) VALUES (" & _
    "'" & ReportPk & "','" & Me!PipelineUID & "','" & Me!ReportDate & "','" 
& Me!ReportInspector & "','Approved')"
End Sub

Here's what I'm doing: I have a combo box on a form called FacilityEntryF. That form is tied to my FacilityT table. I am selecting "CampaignID" from the CampaignT table and adding it to the FacilityT using that combo box in the aforementioned form. No biggie there... Works great.

The FacilityT has many columns of which I have [FacilityID] which is the primary key and is an autogenerated integer. It also has a column for [CampaignID] which is a foreign key from the CampaignT table.

After adding the CampaignID and starting a new FacilityID in FacilityT, I also want to create a new record in my CampaignFacilityParticipationT table. That table consists of only three columns: CampaignFacilityParticipationID, CampaignID, ParticipantFacilityID. I want to take the new [FacilityID] and the [CampaignID] I added to that table and insert them into the CampaignFacilityParticipationT table (FacilityID goes into the ParticipantFacilityID column). Here's the code below that didn't work (which I'm not surprised because I don't know what I'm doing):

Dim sqlQuery As String
sqlQuery = "IF EXISTS (SELECT FacilityID FROM FacilityT WHERE FacilityID = 
" & FacilityID) " & _
" INSERT INTO    CampaignFacilityParticipationT ([CampaignFacilityParticipationID],[CampaignID],[ParticipantFacilityID]) VALUES (" & _
 "" & CampaignFacilityParticipationID,'" & Me!CampaignID," & Me!ParticipantFacilityID, CampaignID)"
End Sub

Using MS Access 2013 with Microsoft SQL backend.

Thanks!!!

Upvotes: 0

Views: 39

Answers (2)

Abraham Gumba
Abraham Gumba

Reputation: 21

Is it possible for you to use a subform on FacilityEntryF on which you select the CampaignID? That will eliminate the need for this code.

Upvotes: 0

June7
June7

Reputation: 21370

Concatenation is not correct. If apostrophes are needed to delimit parameters then make sure they are used consistently. Normally in Access, apostrophes would only be used for text fields, # for date/time and nothing for number. Maybe because backend is MS SQL apostrophes are needed for all field types? Why do you repeat CampaignID in VALUES clause?

sqlQuery = "IF EXISTS (SELECT FacilityID FROM FacilityT WHERE FacilityID = '" & Me!FacilityID & "')" & _
" INSERT INTO CampaignFacilityParticipationT ([CampaignFacilityParticipationID],[CampaignID],[ParticipantFacilityID])" & _
" VALUES ('" & Me!CampaignFacilityParticipationID & "','" & Me!CampaignID & "','" & Me!ParticipantFacilityID & "')"

Upvotes: 0

Related Questions