Trying to populate an unbound form 1

[CaptainBob007]

Hi everybody -

I'm working on populating a form in an unbound database for keeping tack of municipal vehicle stickers. I have successfully written a way to search vehicle owners (via a form) and am able to selectg the correct record and have it populate an "edit owner" form with all the necessary data. I am now trying to do the exact same thing with the vehicle records themselves, but to no avail. I just cant figure out what I'm not doing right. Any advice that can be offered would be most appreciated.

As I said, the "vehicle search" form works fine, and in cases where the search returns more than one record, they populate a list box (called "SearchResults"). The bound (and not visible) column in SearchResults is "VehID", the primary key of "tblVehicles", which is the table where all the vehicle data is held. Selecting a record in this list box and clicking the proper button will run this code:

If IsNothing(Me.SearchResults) Then
    MsgBox "You haven't selected a record!", vbExclamation, getProgName()
    Exit Sub
End If

    ' Open Edit vehicle Dialog
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open Source:="EXEC procVehicleSelect " & Me.SearchResults, ActiveConnection:=CurrentProject.Connection

    If rst.EOF Then
        MsgBox "Record cannot be found.", vbCritical, getProgName()
        Exit Sub
    Else
        DoCmd.OpenForm "frmVehicleEdit"
        For Each fld In rst.Fields
           Forms!frmVehicleEdit!(fld.Name).Value = fld.Value
        Next
    End If

DoCmd.Close acForm, Me.Name

The underlying code for the stored procedure "procVehicleSelect" is this:

Alter PROCEDURE procVehicleSelect
	@VehicleID varchar(9) = NULL
AS

SELECT VehID, OwnID, ConcurrencyID, StickerYear, StickerClass, StickerNumber, Price, Penalty, 
CarYear, CarMake, CarModel, CarColor, LicPlate, LicPlateSt, Notes as TheNotes, AddBy, AddDate, 
ModBy, ModDate
From tblVehicles
Where VehID = @VehicleID

"VehID" is indeed a varchar(9) field, though the parameters getting passed are not always 9 characters in length. All of the fields I'm trying to select from tblVehicles do exist there.

As a runtime scenario, I am doing a vehicle search and selecting a record with VehID of "2P1112". When trying to open the edit vehicle form, the error I keep on getting is:

Line1: Incorrect syntax near 'P1112'.

The line it highlights in the code debugger is the "rst.Open Source...." line from the VBA code segment. In looking this over, the entire VehID "2P1112" is indeed getting passed to procVehicleSelect. Why it parses this string after the first character is beyond me.

If I remove the "=NULL" part from the parameter declaration of procVehicleSelect, and try to run it, it will prompt me for a value for @VehicleID. If I enter "2P1112", it works just fine.

If anybody has any ideas as to why this isnt working, or another way I can populate this form, it would be very much appreciated!

~Bob

 

[nigelrivett]

rst.Open Source:="EXEC procVehicleSelect '" & Me.SearchResults & "'", ActiveConnection:=CurrentProject.Connection

Have a look at

http://www.nigelrivett.net/VB6DataAccessLayer.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[CaptainBob007]

Thank you so much! Ahhhh what a difference some apostrophes make!