Results 1 to 11 of 11
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    How specify property as a variable in procedures

    I don't know if propery is the correct term,
    this is just part of a subroutine that copies a record in a form:

    Code:
     Dim rs1 As Recordset, rs2 As Recordset, F As Field
      Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM " & aTblNm1 & " WHERE " _
        & aWhereCond)
        Set rs2 = CurrentDb.OpenRecordset(aTblNm2)
        rs2.AddNew
    ...
        rs2.Update
        rs2.Bookmark = rs2.LastModified
        temp1 = gSp(6)
        gSp(6) = rs2!temp1
    The procedure is in a module that isn't the form module, hence arguments are passed back and forth.
    I'm trying to get the Id of the new record that was created by the form command button copy.
    If I do ?rs2!EntityAid I get the correct value for the record in the immediate window.

    But I can't see how to use a variable for the field name.


    Can a variable be used here, and if so how?
    I used the array, and then non array reference, but it fails with a run time error 3265 on the line with the temp1 variable.
    Last edited by twgonder; 09-14-2022 at 08:04 PM.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not much to go on there but I suspect you could accomplish this via the Fields collection of a recordset.
    So rather than rs.SomeFieldName (which is a direct reference to a field in a recordset by way of its name)

    rs.Fields(variableNameHere)

    variableNameHere would have to have a value of the field name you wish to use. Untested, but I think that should work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps just use sql and @@identity?

    Code:
    dim db as dao.database
    dim newPK as long
    sqlstr="INSERT INTO " & aTblNm2 & " SELECT * FROM " & aTblNm1 & " WHERE " & aWhereCond
    set db=currentdb
    db.execute sqlstr
    newPK=db.openrecordset("SELECT @@Identity")(0)
    ....
    whatever else you want to do
    always better to be specific with database, recordset, field etc - should be DAO, but might be ADO which is different
    note you need to use db (or whatever name you want) as that maintains the instance. Using currentdb creates a new instance every time you use it.

    but for your method, if the PK is always the first field you can use

    rs2.Bookmark = rs2.fields(0)

    or as Micron suggests (just seen the post)

  4. #4
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Mostly there, now transfer to the new record?

    Thanks, that was enough to get me to the next step, which is to transfer to the record that has the new id.
    I'm still studying up on all the DAO vs ADO. I got this sample code from a site and modified it.
    The best solution seems to be: gSp(6) = rs2.Fields(0) assuming that (0) is always the primary key.
    I knew there had to be a syntax I'd yet to come across.

    The code in the form module that calls this procedure is:
    Code:
    Private Sub cmbCopy_Click()  gSp(2) = ""
      gSp(3) = "]EntityAid]"
      gSp(4) = "]Ps]UpdtUserId]UpdtPgm]UpdtStmp]"
      gSp(5) = "]1]*User*]" & FrmNm & "]*Now*]"
    
    
    Call sFrmCopyRecord(SrcTable, "tbl_TWG_Entity", "EntityAid =" & Me!EntityAid)
    DoCmd.GoTorecord acDataTable gSp(6)
    Stop
    End Sub
    I thought from reading the DoCmd.GoTorecord documentation that the line before the stop might get me to the new record, but no, it won't compile.
    gSp(6) contains the id of the newly copied record.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    With regards assigning values to make your function a bit more general you can use something like

    dim fld as dao.field

    Code:
    for each fld in rs1.fields
      if fld.name<>”????” Then rs2.fields(fld.name)=fld.value 
    next fld
    Use the ???? To exclude fields you don’t want to copy - here for an example

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Not sure I'm following what your trying to do but if using rs.AddNew I've found the easiest way of getting the ID of the new records is like so

    Code:
    Dim db As DAO.Database
    Dim rs  As DAO.Recordset
    Dim SQL_AddNew  As String
    Dim NewID as long
    
    
    SQL_AddNew = "Select " & _ 
              "PartyID,FirstName,MiddleName,LastName,Suffix " & _ 
              "From tblPeople " & _ 
              "Where PartyID = 0"
    
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(SQL_AddNew)
    
    
    
    
    With rs
         .AddNew
              !FirstName = Me.FirstName
              !MiddleName = Me.MiddleName
              !LastName = Me.LastName
              !Suffix = Me.Suffix
              NewID = .Fields("PartyID")   '<<<< The new ID
         .Update
    End With
    
    
    MyExit:
    
    
         rs.Close
         Set rs = Nothing
         Set db = Nothing
    You can even do this as a function to return the new ID.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    assuming that (0) is always the primary key
    That is irrelevant, really. The pk field does not have to be the first field in a recordset fields collection.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Agree doesn’t have to be the first field. Doesn’t cater for multi field pk’s either

  9. #9
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    The full Monty

    Okay, I thought I included enough to get the idea, here is the routine as I have it now.
    gSp(n) is a global variable, so I can pull values needed.
    There's some additional cleanup needed in the error checking section as I just changed some of the arguments to variant.
    As I said, I copied it from another site, it works, it's just that I'm not sure how to "move" to the new record.
    If there is a better approach, I'm all ears (or eyes in this case):

    Code:
    Public Sub sFrmCopyRecord(aTblNm1 As Variant, aTblNm2 As Variant, aWhereCond As Variant)
      ' Moves a record to a new one in the same/other table
      ' gSp(1-5) = 'reserved
      ' gSp(6) = list of fields to exclude in "]FieldNm]" format
      ' All fields copied, some can be modified to new values
      ' gSp(7) = list of fields to modify when included
      ' gSp(8) = list of replacement values for text that's included, must match fields with gSp(7)
      ' Error check values entered with
      
      Dim temp1 As Variant, temp2 As Variant
      If IsNull(aTblNm1) Or IsNull(aTblNm2) Then
        gError = "sFrmCopyRecord.1"
        Exit Sub
      End If
      If aTblNm1 = "" Or aTblNm2 = "" Then
        gError = "sFrmCopyRecord.2"
        Exit Sub
      End If
      If IsNull(aWhereCond) Then
        gError = "sFrmCopyRecord.3"
        Exit Sub
      End If
      temp1 = Left(gSp(6), 1): temp2 = Right(gSp(6), 1)
      If gSp(6) <> "" And (temp1 <> "]" Or temp2 <> "]") Then
        gError = "sFrmCopyRecord.4"
        Exit Sub
      End If
      ' check 3, 4, 5 for bracket errors
      temp1 = fSarrayCount(gSp(7), "]"): temp2 = fSarrayCount(gSp(8), "]")
      If temp1 <> temp2 Then
        gError = "sFrmCopyRecord.5"
        Exit Sub
      End If
      temp1 = Left(gSp(7), 1): temp2 = Right(gSp(7), 1)
      If gSp(7) <> "" And (temp1 <> "]" Or temp2 <> "]") Then
        gError = "sFrmCopyRecord.6"
        Exit Sub
      End If
      temp1 = Left(gSp(8), 1): temp2 = Right(gSp(8), 1)
      If gSp(8) <> "" And (temp1 <> "]" Or temp2 <> "]") Then
        gError = "sFrmCopyRecord.7"
        Exit Sub
      End If
    
      Dim fimr As String
      Dim rs1 As Recordset, rs2 As Recordset, F As Field
        
      Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM " & aTblNm1 & " WHERE " _
        & aWhereCond)
      While Not rs1.EOF
        Set rs2 = CurrentDb.OpenRecordset(aTblNm2)
        rs2.AddNew
        For Each F In rs1.Fields
          temp1 = "]" & F.Name & "]"
          If InStr(1, gSp(6), temp1) Then
            'don 't include the skipped item
          Else
            fimr = ""
            If InStr(1, gSp(7), temp1) Then
            ' write extraction routines
              temp2 = fSarrayPosition(gSp(7), F.Name, "]")
              fimr = fSarrayExtract(gSp(8), temp2, "]")
              Select Case fimr
              Case "*User*"
                fimr = SysCtrl(2, 1)
              Case "*Now*"
                fimr = Now()
              Case Else
                ' use data passed in
              End Select
            End If
            If fimr = "" Then
              rs2(F.Name) = rs1(F.Name).Value
            Else
              rs2(F.Name) = fimr
            End If
          End If
          
        Next
        rs2.Update
        rs2.Bookmark = rs2.LastModified
        gSp(1) = rs2.Fields(0)
        rs1.MoveNext
      Wend
      Stop
      rs2.Close
      rs1.Close
      Set rs2 = Nothing
      Set rs1 = Nothing
        
    ExitCd:
      Exit Sub
    
    ErrCd:
      '    Select Case Err
      '    Case 998, 999: command
      '    Case Else
      Stop
      gError = ErrUnhandled(Err, Error, "sFrmCopyRecord")
      '    End Select
      Resume ExitCd
        
    End Sub

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    looks pretty complicated to me for what looks like essentially inserting a record - and presumably there is additional work to do to populate your arrays when you call the sub. So wouldn't like to say whether or not there is a better way. Personally I'm a sql guy and would just use code to construct a 'dynamic' insert query as I outlined in post #3

    As far as your code is concerned, recommend you document it so you know what it is doing as I struggled to understand all those if's e.g.

    If gSp(6) <> "" And (temp1 <> "]" Or temp2 <> "]") Then 'means what exactly?

    It may be of use to you in the future but you can pass arrays as a parameter
    https://docs.microsoft.com/en-us/off...rameter-arrays


    You can also construct your own types and pass them as a parameter as well. e.g.

    in a standard module

    Code:
    Public Type gp
    
        PK as long
        DT as Date
        
    end Type

    a function/sub would have parameter

    Code:
    myFunction(gpp as gp)
    
    end function
    and to call it

    Code:
    dim agp as gp
    agp.PK=1
    agp.DT=now()
    
    myfunction agp

  11. #11
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    @ CJ_London Thanks for the parameter array tip. I just read the link. That will come in handy when I know there won't be cascading subroutines that need to "pass-on" global variables.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-16-2017, 05:48 PM
  2. Combining Two Procedures
    By natonstan in forum Macros
    Replies: 3
    Last Post: 08-26-2016, 02:06 PM
  3. Replies: 2
    Last Post: 06-28-2014, 01:16 PM
  4. Event procedures
    By GIS_Guy in forum Forms
    Replies: 1
    Last Post: 05-11-2010, 02:34 PM
  5. Help with functions / procedures
    By curnil in forum Programming
    Replies: 3
    Last Post: 03-09-2010, 05:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums