Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coding question again!!! (2K)

    Hi,
    I have a form, FrmNewMedia, where the user selects Media Type (DVD, Video, etc) and the amount of copies.
    There is then a control on this form with the following code (quite long):

    Private Sub cmdOK_Click()
    On Error GoTo Err_Handler

    Dim lngIntID As Long
    Dim lngMediaType As Long
    Dim lngCopies As Long
    Dim lngLast As Long
    Dim lngCount As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    If IsFormLoaded("frmInterviews") Then
    lngIntID = Nz(Forms!frmInterviews!IntID, 0)
    If lngIntID = 0 Then Exit Sub
    ElseIf IsFormLoaded("frmDefendant") Then
    lngIntID = Nz(Forms!frmDefendant!frmInterviewssub!IntID, 0)
    If lngIntID = 0 Then Exit Sub
    Else
    MsgBox "No"
    Exit Sub
    End If

    lngCopies = CLng(Me!txtCopies)
    lngMediaType = CLng(Nz(Me!cboMediaType, 0))
    lngLast = CLng(Nz(DMax("[MedCopyNo]", "tblMedia", "[MedIntID]=" & lngIntID), 0))

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblMedia")

    For lngCount = lngLast + 1 To (lngLast + lngCopies)
    rst.AddNew
    rst!MedIntID = lngIntID
    rst!MedMtpID = lngMediaType
    rst!MedCopyNo = lngCount
    rst.Update
    Next lngCount

    DoCmd.Close acForm, Me.Name

    If IsFormLoaded("frmDefendant") Then
    Forms!frmDefendant!frmInterviewssub!sbfSub1.Form.R equery
    Forms!frmDefendant!frmInterviewssub!sbfSub2.Form.R equery
    Else
    Forms!frmInterviews.sbfSub1.Form.Requery
    Forms!frmInterviews.sbfSub2.Form.Requery
    End If

    Exit_Handler:
    If Not rst Is Nothing Then
    rst.Close
    Set rst = Nothing
    End If

    If Not dbs Is Nothing Then
    Set dbs = Nothing
    End If
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
    Resume Exit_Handler
    End Sub

    This data is then transfered over to frmInterviewssub!sbfsub1.
    The table is based on a query which is subsequently based on TblMedia.
    In this table I have added a field Loggedby.
    I want Loggedby to record the NetUser name whenever new media is added via FrmNewMedia.

    So for instance, FrmNewMedia the user adds two DVD's. Clicks cmdOK and that media is added to sbfsub1. Beside those entries is Joe.Bloggs in the field Loggedby.
    The next day two further DVD's are added, Josephine.Bloggs is recorded in Loggedby.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding question again!!! (2K)

    Thanks Hans, that done the job!

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Coding question again!!! (2K)

    If I remember correctly, you already have code to retrieve the network login name. If not, you can use the GetNetUser function from <post#=446,010>post 446,010</post#>. That code should be copied into a standard module.

    Insert the following line somewhere between rst.AddNew and rst.Update:

    rst!LoggedBy = GetNetUser

    (replace GetNetUser with the name of the function you use to retrieve the network login name)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •