Function Question

I have the following Function and I need to add one field to be appended to the tlog table.  In the table named tlog, besides User, SourceTable, Operation and Date I have a new field I want to add called BaseIDNum which comes from the same recordset in a field named BaseID, which is an Autonumber field and Primary Key.  What code do I need to add to this Function so that it can append the BaseID into the BaseIDNum field of Table tlog?  Thanks



Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Const LOG_UPDATE = 1
Public Const LOG_DELETE = 2

Const ADD = "Add"
Const EDIT = "EDIT"
Const DELETE = "DELETE"



Public Sub pAddToLog(frmParent As Form, intTypeUpdate As Integer)

    Dim rec As Recordset
    Dim strOperation As String
   
    On Error GoTo Err_fAddToLog
   
    If intTypeUpdate = LOG_UPDATE Then
        If frmParent.NewRecord Then
            strOperation = ADD
           
        Else
            strOperation = EDIT
         
        End If
    Else
        strOperation = DELETE
       
    End If
   
   
    Set rec = CurrentDb.OpenRecordset("tLog", dbOpenDynaset, dbAppendOnly)
    rec.AddNew
   
   
    rec("User") = CurrentWinUser
    rec("Operation") = strOperation
    rec("Date") = Now
    rec("SourceTable") = frmParent.RecordSource
    rec.Update

    rec.Close
    Set rec = Nothing
   
    Exit Sub
   
Err_fAddToLog:
    MsgBox "Error in function fAddToLog: " & Err.Description
    Exit Sub

End Sub
Public Function CurrentWinUser() As String
   
    Dim strName As String
    Dim lngSize As Long
    Dim lngRep As Long
   
    lngSize = 255
    strName = Space(lngSize)
    lngRep = GetUserName(strName, lngSize)
    If lngRep = 1 Then
        strName = Trim(strName)
        strName = Left(strName, Len(strName) - 1)
    Else
        strName = ""
    End If
    CurrentWinUser = strName

End Function
charmingAsked:
Who is Participating?
 
BrianWrenConnect With a Mentor Commented:
The way to refer to a subform's controls, is to refer to the main form's subfomr control's 'Form' property.

If the code that's running is the main form:

    Me!ctrlSubForm.Form.<whatever_control>

As for nulls, the AgentID should be an autonumber, (or at least a 'required, no nulls' constraint in the table design), and so should not have any nulls.

You can trap for nulls in code:

  If IsNull(<fld_whatever>) Then
    .
    .
    .

or you can 'handle' them

   Dim a As Variant
   a = Null
   Debug.Print Nz(a)  ' will print 0
   Debug.Print Nz(a, "Sorry") ' will print Sorry
   a = 10
   Debug.Print Nz(a)  ' will print 10
   Debug.Print Nz(a, "Sorry") ' will print 10

If you link child and master fields of the sub and main forms, using the ID fields, then anytime you create a new record on the sub form, the ID field will automatically be filled in with the Main form record's ID number.

Brian
0
 
charmingAuthor Commented:
Adjusted points to 147
0
 
BrianWrenCommented:
     .
      .
      .

    Set rec = CurrentDb.OpenRecordset("tLog", dbOpenDynaset, dbAppendOnly)
    rec.AddNew
     
     
    rec("User") = CurrentWinUser
    rec("Operation") = strOperation
    rec("Date") = Now
    rec("BaseIDNum") = frmParent!BaseID
    rec("SourceTable") = frmParent.RecordSource
    rec.Update

    rec.Close
    Set rec = Nothing
     
    Exit Sub
      .
      .
      .

This presumes that the data is on the form that is passed into this function called 'frmParent', that 'BaseID' is the name of the field in that form's recordset that you want to have in your new field, and that the field BaseIDNum exists in the table 'tLog'.

Brian
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
charmingAuthor Commented:
Adjusted points to 237
0
 
charmingAuthor Commented:
That worked great.  One last question Brian, what if I have a subform what would be the context for that
0
 
charmingAuthor Commented:
Adjusted points to 277
0
 
charmingAuthor Commented:
How do nulls affect theAgentID field.  In other words suppose I have a subform of AgentID whose Primary Key is LoanID and I edit that record but not the main agent record or I edit the Agent but not the loan.  How can I resolve Nulls?
0
 
charmingAuthor Commented:
Could you show me an example of this in my database if I sent it to you?  The database is very small.
0
 
BrianWrenCommented:
Yes.  My e-mail is in my profile.

I cannot access 2000 files, just Access 97.
0
 
brewdogCommented:
wow, is Brian ever moving up . . .
0
 
BrianWrenCommented:
Charming,

I await the arrival of the database...

brewdog:  It looks good up there in the clouds...  I wanna be there 2.

Brian
0
 
JimMorganCommented:
2?  You can be there 6 but nothing above that.
0
 
BrianWrenCommented:
Sorry charming, I got bogged down.  I'll look at it today.
0
 
charmingAuthor Commented:
Adjusted points to 300
0
 
charmingAuthor Commented:
That's Great Brian.  Thanks for the update
0
 
JimMorganCommented:
I can't believe that Brian got bogged down.  What can I do to help, Brian, ... to get you bogged down more often.  :-)

BTW, this looks like a good place for some stored queries.

Jim
0
 
charmingAuthor Commented:
Last post solved it.  Thanks, Brian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.