0

I have made an app which is running well but I know I am not following correct procedures.

My current code is this:

  strCreatePx = "INSERT INTO tblPatients (dispenseid,chemistID,firstname,lastname,address,postcode,phonenumber," & _
    repatnumber,medicarenumber,suburb,consentPharmID) " & _
                    "VALUES (" & oPxID & "," & chemid & ",'" & firstName & "','" & lastName & "','" & Address & "','" & postcode & _
"','" & phone & "','" & repat & "','" & medicareNo & "', '" & Suburb & "'," & conPharm & ")"
        '
        'Add new patient then get their gPXID

        gPxID = getNewID(strCreatePx)

Sorry about the formatting.. it looks better in VBA but perhaps I copied it incorrectly into here.

Now all the variables are declared correctly and then set to text boxes and combo boxes.

This is the function that runs:

Public Function getNewID(strSQL As String, Optional skip As Boolean) As Long
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Dim strGETID As String
Dim NewRST As DAO.Recordset



strGETID = "SELECT @@IDENTITY"

With db
        'strSQL = ""
Set qdf = db.CreateQueryDef("")

            With qdf
                .ReturnsRecords = False
                .Connect = oCon
                .SQL = strSQL
                Debug.Print strSQL
                .Execute
            End With


            With qdf
                .ReturnsRecords = True
                .Connect = oCon
                .SQL = strGETID
                'Debug.Print strSQL
                Set NewRST = .OpenRecordset(dbOpenDynaset)
                getNewID = NewRST(0)
            End With
End With

End Function

This is all working well but I want to fix it by using parameters. This issue is I have lots of different calls to this function and each one has different parameters and different amounts of them.

I'm a novice at VBA and this is my first dive into correct parametization.

I know I need to do some kind of loop and potentially I should send the parameters through as a collection to the function? My thoughts are:

  • Create a string using parameter <> notations
  • Create a collection of the variables
  • Send the collection and string to function
  • In the function I need to loop through each item in the collection and add it to the string as a QDF parameter for the function.

I think that is correct but I just don't know where to start or whether the Collection idea is the "right way" to do this.

halfer
  • 19,824
  • 17
  • 99
  • 186
Glenn Angel
  • 381
  • 1
  • 3
  • 14
  • I don't think parameters are critical in an internal db app, meaning not delivered via web and not taking inputs from external sources. I've never used in my db's, concatenation has served well enough. Other methods control and validate user inputs (such as combobox LimitToList property). But, here is a tutorial https://www.vitoshacademy.com/vba-using-parameters-in-a-vba-sql-query-to-a-database/ – June7 Apr 01 '20 at 03:25
  • So I am linking to an online MYSQL database running pass through queries. I do have numbers of String text boxes which I guess could hold DROP clauses which would be passed on to the page?? – Glenn Angel Apr 01 '20 at 03:27
  • Then does sound like using parameters is justified. However, your question is quite broad and basically asks for someone to write a procedure for you. You have working code and there isn't a problem to solve, so question not really appropriate for SO. – June7 Apr 01 '20 at 03:33
  • Generally, you just use parameters without any loops, and assign them by position. An alternative way is to have a general function that executes queries and returns the recordset, I've shared an example of that [here](https://evona.nl/simple-parameterized-queries-using-ado-in-vba/) on an external site. – Erik A Apr 01 '20 at 07:25

0 Answers0