1

I am using access to demonstrate the power of a database for my firm to track issues that arise during construction. I will use SQL Server as a backend eventually (before we actually start to use this) but I need to be able to show everyone what this brings to us before the IT guy will jump on board with giving me (and everyone else) permissions on the SQL Server database for this purpose.

I have a form that will add a record to one of my tables, and I planned on doing this with a QueryDef calling a parametric query like so:

    Dim qdfAddNewRecord As DAO.QueryDef
    Set qdfAddNewRecord = dbs.QueryDefs("InsertObservation")
    qdfAddNewRecord.Parameters("P_ReportID").Value = Me.ReportID
    qdfAddNewRecord.Parameters("P_Description").Value = CStr(Me.Description)
    qdfAddNewRecord.Parameters("P_Division").Value = CInt(Me.cmbDivision.Column(0))
    qdfAddNewRecord.Parameters("P_ObservationNum").Value = CInt(Me.ObservationNum)
    qdfAddNewRecord.Execute dbFailOnError

But I get an error that I am not setting the [Table].Division field (listed as required in the table definition). When I run the query from the sql view it prompts me for the 4 parameters and then executes successfully. I was stumped, so I decided to try using a recordset to do this like so:

    Set recset = dbs.OpenRecordset("Observations", dbOpenDynaset)
    With recset
        .AddNew
        !ReportID = Me.ReportID
        !Description = Me.Description
        !Division = Me.cmbDivision.Column(0)
        !ObservationNum = Me.ObservationNum
        .Update
    End With

This method works very well, so there must be something wrong with my query, or the way I set the parameters. I am at a loss having looked at this for several days, simplifying the query to only these 4 fields (there are many more in the table that I'll want to set) etc. My query is below, note that access automatically converts my Insert Into . . . Values pattern into the Select as expr pattern:

PARAMETERS P_ReportID Short, P_Description LongText, P_Division Short, P_ObservationNum Short;
INSERT INTO Observations ( ReportID, Description, Division, ObservationNum )
SELECT P_ReportID AS Expr1, P_Description AS Expr2, P_Division AS Expr3, P_ObservationNum AS Expr4;

One further detail: before setting the Division field as required the insert would work but only set the first 2 fields (ReportID and Description). In the debugger the parameters for the QueryDef are set with valid values and types that match. I have tried with and without the CInt() conversion with the same results. I have tried using index parameter calls (parameters(0).Value) and I have tried with and without the .Value all with the same results.

EDIT: I also tried using:

Dim divno as Integer
divno = CInt(Me.cmbDivision.Column(0))

And then using divno to set the qdf parameter value for Division. This resulted in the same outcome (just the first two parameters entered in the new record).

I will just use the working method for now, and when I move this to SQL Server will use a proper WPF/ MVVM front end, but I am bothered that something this simple won't work as expected. Any thoughts?

Paul Gibson
  • 622
  • 1
  • 9
  • 23
  • 2
    Frankly, the idea of replicating those WTF model views with Access makes me kind of sick. Access is a RAD, no need for a CRUD. Just link your SQL Server tables (or views) into your MDB/ACCDB, build your forms on those linked tables and let the tool handle the rest. I built a ERP this way with remote users in different countries, and it worked pretty well, even with tables >100.000 recs. And let the IT guy shout. – iDevlop Aug 14 '18 at 14:55
  • A saved query can't access a combo column form reference, so that might be your problem in the query def , but assuming column 0 is the bound column try simply setting it to the default property : Me.cmbDivision – Minty Aug 14 '18 at 15:37
  • @Minty I had the same thought about the combo, and have also used a separate variable to be sure it was getting the right value. Still it does not work (see my edit for actual declaration and assignment of the divno variable). – Paul Gibson Aug 14 '18 at 17:05
  • 1
    The problem is probably the `LongText` parameter, see above. But as Patrick wrote, why don't you use a bound form? – Andre Aug 14 '18 at 17:15
  • 1
    @Andre Thanks for that link! The LongText is the last field to correctly write, so it seems that it must be something about it causing the problem. I guess the AddNew method is the right way to do this. – Paul Gibson Aug 14 '18 at 17:31

0 Answers0