INSERT INTO advice needed 1

[JAES]

Here is my current roadblock!

The table tabCaseFileSuspects contains all the suspects related to a case. When I open another Investigation (many investigations to one case file) I need to insert the existing suspect(s) information into the new investigation. Because there can be different suspects applied to different investigations within the same case file, I use another table tabInvestigationSuspects.

The form that I use to enter a new investigation has the vCaseFileNumber, vInvestigationNumber and vDateCaseOpened data that needs to be inserted. What I need help with is getting the SuspectID and SuspectType from the tabCaseFileSuspects to the tabInvestigationSuspects where the case file numbers are the same. I think I’m close but when to use quotes, etc still baffles me. The following code is one line in my AfterUpdate procedure where I enter a case file number. I broke it down for ease of viewing.

DoCmd.RunSQL "INSERT INTO tabInvestigationSuspects (CaseFileNum,SuspectID, SuspectType, InvestigationNum, EntryDate)
VALUES (vCaseFileNumber, [tabCaseFileSuspects].[SuspectID], [tabCaseFileSuspects].[SuspectType], vInvestigationNumber, vDateCaseOpened)
WHERE ([tabCaseFileSuspects].[CaseFileNum] = vCaseFileNumber)"

Any assistance is appreciated. Jeff

 

[maxhugen]

when to use quotes, etc still baffles me

It can be a bit confusing at times. Generally, if you are passing a text value, it needs to be quoted, whereas numeric fields don't. Date strings need to be enclosed by hashes (eg #7/17/2007#).

For example, if vCaseFileNumber is a text variable, eg 'B356', the where clause would be:

[tt]WHERE [tabCaseFileSuspects].[CaseFileNum] = '" & vCaseFileNumber & "'"[/tt]

HTH

Max Hugen
Australia

 

[TheAceMan1]

How are ya JAES . . .

Any form controls, variables or functions have to be concatenated as well (taking into account numeric, text, date)! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

How about:

Set db=CurrentDB

'SELECT rather than VALUES, because a second table is used
'You might as well get CaseFileNum from tabCaseFileSuspects

strSQL="INSERT INTO tabInvestigationSuspects " _
& "(CaseFileNum,SuspectID, SuspectType, InvestigationNum, EntryDate) " _
& "SELECT ( [tabCaseFileSuspects].[CaseFileNum], " _
& "[tabCaseFileSuspects].[SuspectID], " _
& "[tabCaseFileSuspects].[SuspectType], " _
& vInvestigationNumber & ", #" _
& vDateCaseOpened & "# ) " _
& "FROM tabCaseFileSuspects " _
& "WHERE [tabCaseFileSuspects].[CaseFileNum] = " _
& vCaseFileNumber

'Avoid advice messages, but check for errors 
'DoCmd.RunSQL strSQL
db.Execute strSQL, dbFailOnError

It is easier to debug if the SQL is in a separate string.

 

[JAES]

Max, The vCaseFileNumber is text. The numbers are like TF07-0123 where the TF07 is "TaskForce" and 07 is the year. This is how they have done it and is not subject to change. On another front I need to figure out how to find the next available number but that is for later.

When I correct the WHERE clause I get a "Missing semicolon ( at the end of SQL statement" error. I've tried to add that little bugger everywhere to no avail. Here is the code I'm now using.

DoCmd.RunSQL "INSERT INTO tabInvestigationSuspects (CaseFileNum,SuspectID, SuspectType, InvestigationNum, EntryDate) VALUES ([tabCaseFileSuspects].[CaseFileNumber], [tabCaseFileSuspects].[SuspectID], [tabCaseFileSuspects].[SuspectType], vInvestigationNumber, vDateCaseOpened) WHERE [tabCaseFileSuspects].[CaseFileNum] = '" & vCaseFileNumber & "'"

Remou, I tried the SELECT in place of VALUES but then the entire SELECT statement had a "syntax error (comma) in query expression" I did try changing the VALUES and SELECT statement from vCaseFileNum to [tabCaseFileSuspects].[SuspectID] but I don't think that will matter.

Jeff

 

[PHV]

And what about this ?

DoCmd.RunSQL "INSERT INTO tabInvestigationSuspects (CaseFileNum,SuspectID,SuspectType,InvestigationNum,EntryDate) SELECT CaseFileNumber,SuspectID,SuspectType," & vInvestigationNumber & ",#" & vDateCaseOpened & "#" FROM tabCaseFileSuspects WHERE CaseFileNum='" & vCaseFileNumber & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JAES]

Hi PHV, I get a "compile error: Expected:end of statement" and the FROM is highlighted when I try that. Would using the FROM work correctly when some of the data does not come from that table? I do this succesfully in other forms but all the data is from one table, not from the form and table.

I run this code just prior to create the new investigation and it works fine but all the information is coming from the unbound form.

DoCmd.RunSQL "INSERT INTO tabInvestigationDetails (InvestigationNumber,CaseFileNumber,EnterDate) VALUES (vInvestigationNumber,vCaseFileNumber,vDateCaseOpened)"

Jeff

 

[PHV]

OOps, sorry for the typo:

DoCmd.RunSQL "INSERT INTO tabInvestigationSuspects" _
 & (CaseFileNum,SuspectID,SuspectType,InvestigationNum,EntryDate)" _
 & " SELECT CaseFileNumber,SuspectID,SuspectType," & vInvestigationNumber & ",#" & vDateCaseOpened & "#" _
 & " FROM tabCaseFileSuspects WHERE CaseFileNumber='" & vCaseFileNumber & "'"

If InvestigationNum is defined as text in tabInvestigationSuspects then you have to use single quotes around vInvestigationNumber.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JAES]

Thanks PHV. I'm real close now. I made a mistake and the tabCaseFileSuspects is really CaseFileSuspects. I changed that and the only problem now is the WHERE clause does not recognize the '" & vCaseFileNumber & "'". The case file numbers are TF07-0012. If I use WHERE CaseFileNumber = 'TF07-0012'" it works fine. vCaseFileNumber is defined as String.

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JAES]

Private Sub Form_Open(Cancel As Integer)
Me.vCaseFileName.Visible = False
Me.vDateCaseOpened.Visible = False
Me.vAgentName.Visible = False
Me.vCrimeCommitted.Visible = False
Me.Command6.Visible = False

'Get next Investigation Number
vInvestigationNumber = DMax("[InvestigationNumber]", "[tabInvestigationDetails]") + 1

End Sub

Private Sub vCaseFileNumber_AfterUpdate()
Dim Response As String
Dim vCaseFileNumber As String
Dim vInvestigationNumber As Integer
Dim vDateCaseOpened As Date
Dim vAgentName As String
Dim vCrimeCommitted As String
Dim stCriteria As String
Dim vNextInvNum As String
Dim stDocName As String
Dim stLinkCriteria As String



If IsNull(DLookup("[Case File Number]", "Case Files", "[Case File Number]='" & Me.vCaseFileNumber & "'")) Then
Response = MsgBox("This CASE FILE NUMBER does not exist. Would you like to open a new case file?", vbYesNo)

If Response = vbYes Then
'Case Files does not exist and you want to make a new one
Me.vCaseFileName.Visible = True
Me.vDateCaseOpened.Visible = True
Me.vAgentName.Visible = True
Me.vCrimeCommitted.Visible = True
Command6.Visible = True

Label16.Visible = False
vCaseFileName.SetFocus

Else
'Case File does not exist and you do not want to make a new one
DoCmd.Close acForm, "frmNewInvestigation"
End If
Else
'START HERE IF CASE FILE NUMBER EXISTS TO ADD NEW INVESTIGATION - This is where the code I'm working on starts
vCaseFleNumber = Me.vCaseFileNumber
vDateCaseOpened = Date

'Add investigation number to tabInvestigationDetails
DoCmd.RunSQL "INSERT INTO tabInvestigationDetails (InvestigationNumber,CaseFileNumber,EnterDate) VALUES (vInvestigationNumber,vCaseFileNumber,vDateCaseOpened)"

'Add existing parties to this investigation
DoCmd.RunSQL "INSERT INTO tabInvestigationSuspects (CaseFileNum,SuspectID,SuspectType,InvestigationNum,EntryDate) SELECT CaseFileNumber,SuspectID,SuspectType,vInvestigationNumber ,vDateCaseOpened FROM CaseFileSuspects WHERE CaseFileNumber='" & vCaseFileNumber & "'"


stLinkCriteria = "[InvestigationNumber]=" & Me![vInvestigationNumber]
stDocName = "frmEditInvestigationInformation"

DoCmd.OpenForm stDocName, , , stLinkCriteria


End If

End Sub

 

[PHV]

Replace this:
vCaseFleNumber = Me.vCaseFileNumber
with this:
vCaseFileNumber = Me.vCaseFileNumber

Some notes:
Use the Option Explicit instruction.
Don't declare variables with same name as controls or fields.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JAES]

Wow, It still took me a minute or so to find the difference in your posting. I have ordered a new set of glasses because mine are all scratched up. That's my excuse and I'm sticking to it.

I made your changes and then changed to WHERE CaseFileNumber = vCaseFileNumber" and it works great.

Thanks PHV for the fix and the tip on Option Explicit. I'm learning by trial and every tip helps. I think I'm going to set up a monthly contribution from my Paypal account and beat my head against the wall for only a day or two before asking for help!

Jeff