Another INSERT problem 2

[JAES]

OK, this INSERT command is going to be the death of me. The underlined code keeps opening "enter parameter Value" boxes wanting input of vInvNum and vVehID. When I run the cursor over the Dim areas the correct values are shown. To get to this code the vehicle license number must exist in the VehiclesInvestigations table.

Once again any help would be appreciated. Jeff


Private Sub vLicenseNum_AfterUpdate()

Dim vLicNum, response, stDocName As String
Dim vInvNum, vVehID As Integer

vLicNum = Me.vLicenseNum
vInvNum = [Forms]![frmEditInvestigationInformation]![InvestigationNumber]
vVehID = (DLookup("[VehIDNum]", "Vehicles", "[VehLicenseNumber]='" & vLicNum & "'"))
stDocName = "FrmEnterVehicles"

'Check to see if the vehicle license exists in database
If IsNull(DLookup("[VehLicenseNumber]", "Vehicles", "[VehLicenseNumber]='" & vLicNum & "'")) Then
response = MsgBox("This vehicle is not in the system. Would you like to add it?", vbOKCancel)

'Vehicle is not in the database
'On "OK" open the Enter Vehicles form
If response = vbOK Then
DoCmd.OpenForm stDocName

'And close this form
DoCmd.Close acForm, "frmEnterLicenseNumber"

Else

DoCmd.Close acForm, "frmEnterLicenseNumber"

End If

Else

'If the Vehicle License Number exists then add vehicle information to VehiclesInvestigations table
DoCmd.RunSQL "INSERT INTO VehiclesInvestigations (InvestigationNumber,VehIDNum) VALUES (vInvNum, vVehID)"
DoCmd.Close acForm, "frmEnterLicenseNumber"

End If

End Sub

 

[Remou]

You need to put the variables as values:

[tt]DoCmd.RunSQL "INSERT INTO VehiclesInvestigations (InvestigationNumber,VehIDNum) VALUES (" & vInvNum & ", " & vVehID & ")"[/tt]

You will need single quotes for text fields.

Please use [ignore]

[/ignore] tags for code (see Process TGML, below).

 

[PHV]

DoCmd.RunSQL "INSERT INTO VehiclesInvestigations (InvestigationNumber,VehIDNum) VALUES (" & vInvNum & "," & vVehID & ")"

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

 

[JAES]

Thanks to both of you. I'm starting to get it!

PHV, you mentioned last time to use Option Explicit instruction but I don't know exactly where and how to insert it.

Jeff

 

[PHV]

Just below the Option Compare Database instruction.